使用PhpOffice\PhpSpreadsheet 实现Excel多sheet导出代码

require "vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as ReaderXlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

  /**
     * 多sheet的导出
     * @author bwy <xxx@xxx.com>
     * @param [type] $data_array
     * @return void
     */
    public  function xtexport($data_array)
    { 
        $name = '团长' . date("Y-m-d", time());
        $spreadsheet = new Spreadsheet();
        foreach ($data_array as $key => $data) {
            $this->opSheet($spreadsheet,$key,$data);
        }
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;
    }



/**
     * 处理多sheet
     * @author bwy <xxx@xxx.com>
     * @param [type] $spreadsheet
     * @param [type] $n
     * @param [type] $data
     * @return void
     */
    public  function opSheet($spreadsheet,$n, $data)
    {
        $spreadsheet->createSheet();//创建sheet
        $objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet
        $keys = $data['rows'][0];//这是你的数据键名
        $count = count($keys);//计算你所占的列数
        $infoNum = ceil(count($data['info']) / 2);//求k-v值的所占行数
        $infoStart = $infoNum + 2 ;//下面的详细信息的开始行数
        $cellName    = array('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');
        $sheet = $spreadsheet->getActiveSheet($n)->setTitle($data['info']['社区团长姓名：']);//设置sheet的名称
        $spreadsheet->getActiveSheet($n)->mergeCells('A1:' . $cellName[$count - 1] . '1'); //合并单元格
        $spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setSize(20); //设置title的字体大小
        $spreadsheet->getActiveSheet($n)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getFont()->setBold(true); //标题栏加粗
        $objActSheet->setCellValue('A1', $data['title']); //设置每个sheet中的名称title


        /**
         * 图中最下面的数据信息循环
         */
        foreach ($data['rows'] as $key => $item) 
        {             
            //循环设置单元格：
            //$key+$infoStart,因为第一行是表头，所以写到表格时   从第数据行开始写 
            for ($i = 65; $i < $count + 65; $i++) 
            {   
                //数字转字母从65开始：
                //$sheet->setCellValue(strtoupper(chr($i)) . ($key + "$infoStart"), $item[[$keys][$i - 65]]);
                $sheet->setCellValue(strtoupper(chr($i)) . ($key+"$infoStart"), $item[$i - 65]);
                $spreadsheet->getActiveSheet($n)->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
            }
        }

        /**
         * 处理图中的中间区 团长名称之类的 
         */
        $rowNumber = 1;
        $infoIndex = 0;
        foreach ($data['info'] as $key => $value) {
            if ($infoIndex % 2 == 0) {
                $rowNumber++;
                $infoCellName1 = 'A' . $rowNumber;
                $infoCellMegreRange = 'B' . $rowNumber . ':C' . $rowNumber;
                $infoCellName2 = 'B' . $rowNumber;
            } else {
                $infoCellName1 = 'D' . $rowNumber;
                $infoCellMegreRange = 'E' . $rowNumber . ':F' . $rowNumber;
                $infoCellName2 = 'E' . $rowNumber;
            }
            $spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName1, $key);
            $spreadsheet->getActiveSheet($n)->mergeCells($infoCellMegreRange);
            $spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName2, $value);
            $infoIndex++;
        }
    }

public function xtexport()
    {
        $data = array(
            array(
                'title' => 'AAA',
                'info' => [
                    '社区团长店铺地址：' => 'AAA',
                    '社区团长姓名：' => 'AAA',
                    '收货地址：' => 'AAA',
                    '联系方式：' => 'AAA',
                    '配送日期：' => 'AAA',
                    '手打阿达：' => 'AAA',
                    '手动蝶阀：' => 'AAA'
                ],
                'rows' => [
                    [
                        'id',
                        'name',
                        'sex',
                        'phone',
                        'res',
                        'aaa',
                        'bbb'
                    ],
                    [
                        1111,2222,3333,4444,5555,6666,7777
                    ],
                    [
                        111,222,333,444,555,666,777
                    ]
                ]
            ), array(
                'title' => 'BBBB',
                'info' => [
                    '社区团长店铺地址：' => 'BBBB',
                    '社区团长姓名：' => 'BBBB',
                    '收货地址：' => 'BBBB',
                    '联系方式：' => 'BBBB',
                    '配送日期：' => 'BBBB',
                    '手打阿达：' => 'BBBB',
                    '手动蝶阀：' => 'BBBB'
                ],
                'rows' => [
                    [
                        'id',
                        'name',
                        'sex',
                        'phone',
                        'res',
                        'aaa',
                        'bbb'
                    ],
                    [
                        1,2,3,4,5,6,7
                    ],
                    [
                        11,22,33,44,55,66,77
                    ]
                ]
            )
        );
        model('Excel')->xtexport($data);
    }