project-manager/api/app/common/util/PhpSpreadsheetExportV2.php

<?php

namespace app\common\util;

use PhpOffice\PhpSpreadsheet\Exception;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use think\route\Domain;

class PhpSpreadsheetExportV2
{
    public static array $excelCol = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
        'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];

    public static bool $setBold = false; //是否加粗
    public static string $setName = '宋体'; //字体
    public static string $setSize = '12'; //字体大小
    public static string $setBgRGB = 'FFFFFFFF'; //单元格背景色
    public static string $setFontRGB = 'FF000000'; //字体颜色
    public static array $styleArray = [
        'alignment' => [
            'horizontal' => Alignment::HORIZONTAL_CENTER,
            'vertical' => Alignment::VERTICAL_CENTER
        ],
        'borders' => [
            'allBorders' => [
                'borderStyle' => Border::BORDER_THIN,
                'color' => ['argb' => '000000'],
            ],
        ],
    ];


    /**
     * 保存文件
     * @param object $spreadsheet
     * @param $fileName
     * @param string $suffix
     */
    public static function saveFile(object $spreadsheet, $fileName, string $suffix = 'xlsx')
    {
        $writer = IOFactory::createWriter($spreadsheet, ucwords($suffix));
        $writer->save($fileName, true);
    }

    /**
     * 导出excel文件
     * @param \ArrayAccess $data
     * @param array<array> $tableHeader
     * @param string $fileName
     * @param array $mergeCells
     * @return array
     */
    public static function outputFile($data, $tableHeader, $fileName, $mergeCells = [])
    {
        $path = "/storage/topic/output_excel/" . date('Ymd') . "/";
        $dir = app()->getRootPath() . "public" . $path;
        if (!file_exists($dir)) {
            mkdir($dir, '0755', true);
        }
        $fileName = $fileName . ".xlsx";
        $file = $dir . $fileName;
        $SpreadSheet = new Spreadsheet();
        $sheet = $SpreadSheet->getActiveSheet();
        \app\common\util\PhpSpreadsheetExportV2::write($sheet, $data, $tableHeader, $mergeCells);

        \app\common\util\PhpSpreadsheetExportV2::saveFile($SpreadSheet, $file);

        $url = self::get_domain() . getVirRootDir() . $path . $fileName;
        return ['url' => $url, 'file' => $file];
    }

    /**
     * 写入数据
     * @param $sheet
     * @param $data
     * @param $tableHeader
     * @param $mergeCells
     * @return bool
     */
    public static function write($sheet, $data, $tableHeader, $mergeCells): bool
    {
        $totalCol = 0;

        //设置表头合并单元格
        foreach ($mergeCells as $row => $rows) {
            $i = 0;
            foreach ($rows as $col => $colValue) {
                //合并单元格
                $sheet->mergeCells($col);
                //设置样式
                self::setStyle($sheet, $i, $totalCol, $row);
                //单元格内容写入
                $sheet->setCellValue(substr($col, 0, strpos($col, ":")), $colValue);
                $i++;
            }
        }
        $totalCol = count($mergeCells);

        //设置表头
        foreach ($tableHeader as $row => $rows) {
            $headerRowDatas = array_values($rows);
            foreach ($headerRowDatas as $col => $colValue) {
                //设置样式
                self::setStyle($sheet, $col, $totalCol, $row);
                //单元格内容写入
                $sheet->getCell(self::$excelCol[$col] . ($totalCol + $row + 1))->setValueExplicit($colValue,'s');//统一格式化为字符串
//                $sheet->setCellValue(self::$excelCol[$col] . ($totalCol + $row + 1), $colValue);
            }
        }
        $totalCol += count($tableHeader);

        //设置内容
        foreach ($data as $row => $rows) {
            $rowDatas = array_values($rows);
            foreach ($rowDatas as $col => $colValue) {
                // 单元格内容写入
//                $sheet->setCellValue(self::$excelCol[$col] . ($totalCol + $row + 1), $colValue);
                $sheet->getCell(self::$excelCol[$col] . ($totalCol + $row + 1))->setValueExplicit($colValue,'s');//统一格式化为字符串
            }
        }
        return true;
    }

    /**
     * 设置单元格样式
     * @param $sheet //某个sheet
     * @param $col //某列
     * @param $totalCol //总行数
     * @param $row //某行
     */
    public static function setStyle($sheet, $col, $totalCol, $row): void
    {
        //设置单元格居中
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + $row + 1))->applyFromArray(self::$styleArray);
        //设置单元格
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + $row + 1))
            ->getFill()
            ->setFillType(Fill::FILL_SOLID)
            ->getStartColor()
            ->setRGB(self::$setBgRGB);
        //设置单元格字体样式、字体、字体大小
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + $row + 1))
            ->getFont()
            ->setBold(self::$setBold)
            ->setName(self::$setName)
            ->setSize(self::$setSize);
        //设置字体颜色
        $sheet->getStyle(self::$excelCol[$col] . ($totalCol + $row + 1))
            ->getFont()
            ->getColor()->setRGB(self::$setFontRGB);
    }

    /**
     * 获取当前网站的域名地址
     * 
     * @return string 域名地址
     */
    protected static function get_domain()
    {
        $sys_protocal = isset($_SERVER['SERVER_PORT']) && $_SERVER['SERVER_PORT'] == '443' ? 'https://' : 'http://';
        return $sys_protocal . (isset($_SERVER['HTTP_HOST']) ? $_SERVER['HTTP_HOST'] : '');
    }
}