<?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'] : ''); } }