乐趣区

关于php:禅道集成PhpSpreadsheet

jiezi

背景

PHPEXCEL 停更很久，无奈反对最新的 Offce 版本，因而宽泛的采纳 phpSpreadsheet 来作为 excel 的导出计划，github 地址 https://github.com/PHPOffice/PhpSpreadsheet，文档地址 https://phpspreadsheet.readthedocs.io/en/latest/

环境

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

装置 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 的文件夹以及若干文件

禅道短信类编写

在 module 目录下，新建一个 excel 文件夹，其中依照禅道构造，新建一个 lang 文件夹和 lang 文件，以及 model 文件

1、zh-cn.php

临时无内容，有须要再增加

2、model.php

<?php


require_once  '../../www/excel/vendor/autoload.php';
use PhpOffice\PhpSpreadsheet\Helper\Sample;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style;

class excelModel extends model
{function export($name,$title,$data)
    {
       
        
        // Create new Spreadsheet object
        $spreadsheet = new Spreadsheet();
        $sheet = $spreadsheet->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;
    }
    
    
}

应用禅道短信类

在任意的 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 结束！

退出移动版