禅道集成PhpSpreadsheet 禅道集成PhpSpreadsheet

背景 PHPEXCEL停更很久，无法支持最新的Offce版本，因此广泛的采用phpSpreadsheet来作为excel的导出方案，这里以禅道测试phpSpreadsheet的导出集成。
环境

PHP 7.2.34
Apache httpd 2.4
Mysql5.7.33
禅道开源版15.4

参考文档 github地址https://github.com/PHPOffice/PhpSpreadsheet
文档地址https://phpspreadsheet.readthedocs.io/en/latest/
安装SDK 1、安装 Composer
Windows 环境：请访问 Composer 官网下载安装包并进行安装，地址https://getcomposer.org/downl...。'
Linux环境：请运行一下代码

curl -sS [https://getcomposer.org/installer](https://getcomposer.org/installer) | php sudo mv composer.phar /usr/local/bin/composer

2、获取SDK文件
在禅道的www目录下，新建一个excel目录，命令行切到www/excel目录下，运行一下命令

composer require phpoffice/phpspreadsheet

运行成功后，会生成一个vendor的文件夹以及若干文件
禅道EXCEl导出类编写在module目录下，新建一个excel文件夹，其中按照禅道结构，新建一个lang文件夹和lang文件，以及model文件

文章图片

1、zh-cn.php
【禅道集成PhpSpreadsheet】暂时无内容，有需要再添加
2、model.php

getActiveSheet(); //设置列宽度（这个可以做一个数组配置成输入参数，这里偷懒了) $sheet->getColumnDimension('A')->setWidth(10); $sheet->getColumnDimension('B')->setWidth(10); $sheet->getColumnDimension('C')->setWidth(10); $sheet->getColumnDimension('D')->setWidth(10); $sheet->getColumnDimension('E')->setWidth(10); $sheet->getColumnDimension('F')->setWidth(10); $sheet->getColumnDimension('G')->setWidth(10); $sheet->getColumnDimension('H')->setWidth(10); $sheet->getColumnDimension('I')->setWidth(10); $sheet->getColumnDimension('J')->setWidth(12); $sheet->getColumnDimension('K')->setWidth(12); $sheet->getColumnDimension('L')->setWidth(10); $sheet->getColumnDimension('M')->setWidth(10); $sheet->getColumnDimension('N')->setWidth(15); $sheet->getColumnDimension('O')->setWidth(10); $sheet->getColumnDimension('P')->setWidth(10); $sheet->getColumnDimension('Q')->setWidth(18); $sheet->getColumnDimension('R')->setWidth(10); $sheet->getColumnDimension('S')->setWidth(18); //左对齐 $styleArray1 = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT, ], ]; $sheet->getStyle('G')->applyFromArray($styleArray1); //右对齐 $styleArray2 = [ 'alignment' => [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT, ], ]; $sheet->getStyle('I')->applyFromArray($styleArray2); //单元格线框 $styleArray3 = [ 'borders' => [ 'outline' => [ 'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN, 'color' => ['argb' => '0000000'], ], ], ]; // 方法二，使用 setCellValue //表头 //设置单元格内容 $titCol = 'A'; foreach ($title as $key => $value) { // 单元格内容写入 $sheet->setCellValue($titCol . '1', $value); //设置线框 $sheet->getStyle($titCol . '1')->applyFromArray($styleArray3); //设置背景 $sheet->getStyle($titCol . '1')->getFont()->setBold(true); $titCol++; } $row = 2; // 从第二行开始 foreach ($data as $item) { $dataCol = 'A'; foreach ($item as $value) { // 单元格内容写入 $sheet->setCellValue($dataCol . $row, $value); //设置线框 $sheet->getStyle($dataCol . $row)->applyFromArray($styleArray3); $dataCol++; } $row++; }// Redirect output to a client’s web browser (Xlsx) header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'); header('Content-Disposition: attachment; filename="'.$name.'"'); header('Cache-Control: max-age=0'); // If you're serving to IE 9, then the following may be needed header('Cache-Control: max-age=1'); // If you're serving to IE over SSL, then the following may be needed 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 = IOFactory::createWriter($spreadsheet, 'Xlsx'); $writer->save('php://output'); exit; }}

使用禅道EXCEL导出类在任意的control文件里

$this->loadModel('excel');

然后构造excel导出参数，并使用导出类导出EXCEL

$title = array(); $title[] = $this->lang->car->status; $title[] = $this->lang->car->id; //获取数据，根据实际情况来构建 $datas = $this->data->getDataLists(); foreach ($datas as $data) { unset($rowdata); $rowdata[] = $this->lang->data->statusList[$data->status]; $rowdata[] = $data->id $alldatas[] = $rowdata; }$this->excel->export("导出示例".helper::now().".xlsx",$title,$alldatas);

至此，在禅道里如何集成PhpSpreadsheet完毕！