ThinkPHP5整合PHPExcel导出2——一个Excel多个工作表

遇到个内部需求 “财务人员需要导出订单数据，必须包含两个工作表在一个Excel里面”，业务需求不得不做，一顿操作猛如虎之后总结方法如下，前面部分和《TinkPHP5整合PHPExcel导出》的方法一样，只是写了个可扩展工作表的类

1、安装PHPExcel到thinkphp5
直接使用composer require phpoffice/phpexcel 命令即可自动下载到框架目录vendor下，如果不会使用可直接下载好PHPExcel插件文件夹手动放入vendor\phpoffice\下
2、使用
1）这里我写了一个扩展类到框架扩展文件夹extend\org下类文件如下

<?php
namespace org;
use PHPExcel_Cell_DataType;
use PHPExcel_IOFactory;
use PHPExcel;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Style_NumberFormat;

class ExcelWorksheets{
    private $PHPExcel;
    private $PHPSheet;
    private $PHPWriter;
    private $title;
    private $rows;
    private $menus;

    private $sysmenu = ["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"];
    /**
     * 架构方法 设置参数
     */
    public function __construct()
    {
        // 创建Excel设置第一个工作表为操作表
        $this->PHPExcel  = new PHPExcel();
        $this->PHPExcel->setActiveSheetIndex(0);
        $this->PHPSheet  = $this->PHPExcel->getActiveSheet();
        $this->PHPWriter = PHPExcel_IOFactory::createWriter($this->PHPExcel,'Excel2007');
    }
    // 开始下载表格
    public function downExcel($title,$rows,$menus)
    {
        // 工作表信息
        $this->title = $title;
        $this->rows  = $rows;
        $this->menus = $menus;
        // 判断有多少个工作表
        for ($i=0;$i<count($title);$i++){

            if($i>0){
                $msgWorkSheet = new \PHPExcel_Worksheet($this->PHPExcel, $this->title[$i]); //创建一个工作表
                $this->PHPExcel->addSheet($msgWorkSheet); //插入工作表
                $this->PHPExcel->setActiveSheetIndex($i); //切换到新创建的工作表
                $this->PHPSheet  = $this->PHPExcel->getActiveSheet();
            }

            $this->PHPSheet->setTitle($this->title[$i]);

            $this->setMenu($i);

            $this->setContents($i);

        }
        $this->PHPExcel->setActiveSheetIndex(0); //切换到新创建的工作表

        $filename = "订单数据导出".date("YmdHis",time()).".xlsx";
        ob_end_clean();
        header("Content-Type: application/force-download");
        header("Content-Type: application/octet-stream");
        header("Content-Type: application/download");
        header('Content-Disposition:inline;filename="'.$filename.'"');
        header("Content-Transfer-Encoding: binary");
        header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
        header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
        header("Pragma: no-cache");
        $this->PHPWriter->save('php://output');
    }
    /**
     * 设置标题栏
     */
    private function setMenu($index)
    {
        $max = count($this->menus[$index]);

        for ($im=0; $im < $max; $im++) {
            $this->PHPSheet->setCellValue($this->sysmenu[$im]."1",$this->menus[$index][$im]);
            $this->PHPSheet->getColumnDimension($this->sysmenu[$im])->setAutoSize(true);
        }
        $this->PHPSheet->getStyle('a1:'.$this->sysmenu[$max-1]."1")->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $this->PHPSheet->getStyle('a1:'.$this->sysmenu[$max-1]."1")->getFill()->getStartColor()->setARGB('FF0070C0');
        $this->PHPSheet->getStyle('a1:'.$this->sysmenu[$max-1]."1")->getFont()->getColor()->setARGB('FFFFFFFF');
    }
    /**
     * 设置内容显示
     */
    private function setContents($index)
    {
        $r = 2;
        $insertdata = $this->rows[$index];
//        print_r($insertdata);
        foreach ($insertdata as $k => $v) {
            $this->setRowsContent($r,$v);
            $r++;

        }
    }
    /**
     * 设置每行数据
     */
    private function setRowsContent($rownum,$data)
    {
//        print_r($data);
        $styleThinBlackBorderOutline = array(
            'borders' => array (
                'outline' => array (
                    'style' => PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                    //'style' => PHPExcel_Style_Border::BORDER_THICK,  另一种样式
                    'color' => array ('argb' => 'FF000000'),          //设置border颜色
                ),
            ),
        );
//        $keyarr = array_keys($data);

        for ($ri=0; $ri < count($data); $ri++) {
            $keyarr = array_keys($data);
            $keyvalue = $keyarr[$ri];
            $this->PHPSheet->setCellValue($this->sysmenu[$ri].$rownum,$data[$keyvalue]);
            $this->PHPSheet->getStyle($this->sysmenu[$ri].$rownum)->applyFromArray($styleThinBlackBorderOutline);
            $this->PHPSheet->getColumnDimension($this->sysmenu[$ri])->setAutoSize(true);
            $this->PHPSheet->getStyle($ri)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);

        }

    }
}

2）用法
控制器文件引入扩展use org\ExcelWorksheets;
① 获取数据库数据
② 实例化下载类
③ 传入文件名、结果集、菜单数组
通过以上三步即可开始下载excel文件

  public function index()
  {
          $rows = db("projects_animals")->where('isdel',0)->select();
          $order = new orderlogic();
          $rows = $order->outOrderExcel($ordernumber, $expnumber, $isspilt, $state, $paytype, $seallername,$sname,$getperson,$starttime, $endtime);// 获取结果集
          $exc = new ExcelWorksheets();// 实例化导出对象
          $title = array("订单","货品"); // 工作表数组，有多少个工作表传多少数据
          $menus = array(
              array("id","宠物医院","销售","价格","是否拆单","订单编号","支付类型","状态","生成时间"),
              array("订单编号","编号","品名","规格","单位","数量","赠送","单价","合计")
          ); // 按工作表顺序传入第一行标题数据

          $datas = array(
              $rows["data"], // 订单数据
              $rows["detail"]// 详情数据
          );// 这里根据自己需求传入对应数量的数组，我这里是两个工作表 所以传入的数组长度是2
          $exc->downExcel($title,$datas,$menus);// 开始导出
}