tp6安装、封装phpspreadsheet
1、安装phpspreadsheet
composer require phpoffice/phpspreadsheet
2、封装类
<?php namespace excel; use PHPExcel_IOFactory; 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; class Excel { // public function test() // { // $data = [ // ['title1' => '111', 'title2' => '111'], // ['title1' => '222', 'title2' => '222'], // ['title1' => '333', 'title2' => '333'] // ]; // $mergeCells = [ // ['A1:B1'=>'第一行标题','C1:F1'=>'第一111行标题'], // ['A2:B2'=>'第一行标题','C2:E2'=>'第一222行标题'], // ]; // $tableHeader = [ // ['第一行标题','第一行标题'], // ['第二行标题', '第二行标题'] // ]; // $fileName = "8888.xlsx"; // // $data = Excel::saveFile($data, $fileName, $tableHeader,$mergeCells); // // var_dump($data); // exit; // } public static $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 $setBold = false; public static $setName = '宋体'; public static $setSize = '12'; public static $setBgRGB = 'FFFFFFFF'; public static $setFontRGB = 'FF000000'; public static $styleArray = [ 'alignment' => [ 'horizontal' => Alignment::HORIZONTAL_CENTER, 'vertical' => Alignment::VERTICAL_CENTER ], 'borders' => [ 'allBorders' => [ 'borderStyle' => Border::BORDER_THIN, 'color' => ['argb' => '000000'], ], ], ]; /** * 读取excel * @param $filePath * @param int $pageIndex * @param int $readRow * @return array * @throws \PhpOffice\PhpSpreadsheet\Exception */ public static function read($filePath, $pageIndex = 0,$readRow = 0) { //加载文件 $spreadSheet = IOFactory::load($filePath); //获取文件内容 $workSheet = $spreadSheet->getSheet($pageIndex)->toArray('', true, true, false); //删除表头几行 if ($readRow > 0) { for ($i = 0; $i < $readRow; $i++) { array_shift($workSheet); } } return $workSheet; } /** * 下载文件 * @param $data * @param $fileName * @param array $tableHeader * @param array $mergeCells * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */ public static function download($data, $fileName, $tableHeader = [], $mergeCells = [], $suffix = 'xlsx') { $spreadsheet = self::write($data, $tableHeader, $mergeCells); // 将输出重定向到客户端的网络浏览器(Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment;filename="' . $fileName . '"');//文件名 header('Cache-Control: max-age=0'); // 如果你服务于IE 9，那么以下可能是需要的 header('Cache-Control: max-age=1'); // 如果您通过SSL为工业工程服务，那么可能需要以下内容 header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified header('Cache-Control: cache, must-revalidate'); // HTTP/1.1 header('Pragma: public'); // HTTP/1.0 $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, ucwords($suffix)); $writer->save('php://output'); } /** * 保存文件 * @param $data * @param $fileName * @param array $tableHeader * @param array $mergeCells * @throws \PhpOffice\PhpSpreadsheet\Exception * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception */ public static function saveFile($data, $fileName, $tableHeader = [], $mergeCells = [], $suffix = 'xlsx') { $spreadsheet = self::write($data, $tableHeader, $mergeCells); $writer = IOFactory::createWriter($spreadsheet, ucwords($suffix)); $writer->save($fileName, true); exit; } /** * 写入数据 * @param $data * @param $tableHeader * @param $mergeCells * @return Spreadsheet * @throws \PhpOffice\PhpSpreadsheet\Exception */ public static function write($data, $tableHeader, $mergeCells) { // 创建excel对象 $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $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->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); } } return $spreadsheet; } /** * 设置单元格样式 * @param $sheet * @param $col * @param $totalCol * @param $row */ public static function setStyle($sheet, $col, $totalCol, $row) { //设置单元格居中 $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); } }
