Yii2 使用PhpSpreadsheet扩展导出多sheet表格

先composer安装PhpSpreadsheet扩展

composer require "phpoffice/phpspreadsheet": "^1.11"

 直接贴代码

use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

public function exportExcel($title, $header, $sheet_title, $data)
{
        $count = count($data);
        $spreadsheet = new Spreadsheet();
        for($i=0;$i<$count;$i++){
            $spreadsheet->createSheet();//创建sheet
            $objActSheet = $spreadsheet->setActiveSheetIndex($i);//设置当前的活动sheet
            $objActSheet->setTitle($sheet_title[$i]);
            foreach ($header[$i] as $index => $item) {
                $cellnum = Coordinate::stringFromColumnIndex($index + 1) . "1";
                $objActSheet->setCellValue($cellnum, $item);
                $objActSheet->getStyle($cellnum)->getFont()->setBold(true); //标题栏加粗
                $objActSheet->getStyle($cellnum)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
            }
            $loop = 2;
            foreach ($data[$i] as $values) {
                foreach ($values as $index => $val) {
                    $cellnum = Coordinate::stringFromColumnIndex($index + 1);
                    $objActSheet->setCellValue($cellnum . $loop, $val);
                    $objActSheet->getStyle($cellnum. $loop)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER); //居中
                }
                $loop++;
            }
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename=' . $title . '.xlsx');
        header('Cache-Control: max-age=0');
        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 = new Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);

        exit;
}

数据格式说明：

$title = '班级1快速问答报告';

//每个sheet的表头
$header = Array
(
    [0] => Array
        (
            [0] => 题号
            [1] => 题型
            [2] => A
            [3] => B
            [4] => C
            [5] => D
            [6] => 正确
            [7] => 错误
            [8] => 正确答案
            [9] => 正确率
        )

    [1] => Array
        (
            [0] => 姓名
            [1] => 1
            [2] => 2
            [3] => 3
            [4] => 正确率
        )

    [2] => Array
        (
            [0] => 姓名
            [1] => 1
            [2] => 2
            [3] => 3
        )

)
//每个sheet的名称
$sheet_title = Array
(
    [0] => 题目统计
    [1] => 学生统计
    [2] => 原始数据
)
//数据，对应每个表格的表头
$data = Array
(
    [0] => Array
        (
            [0] => Array
                (
                    [0] => 1
                    [1] => 多选题
                    [2] => 39
                    [3] => 37
                    [4] => 3
                    [5] => 1
                    [6] => --
                    [7] => --
                    [8] => AB
                    [9] => 87.5%
                )

        )

    [1] => Array
        (
            [0] => Array
                (
                    [0] => 诸葛大师
                    [1] => ✔
                    [2] => ○
                    [3] => ✔
                    [4] => 66.67%
                )
        )

    [2] => Array
        (
            [0] => Array
                (
                    [0] => 诸葛大师
                    [1] => AB
                    [2] => 错误
                    [3] => A
                )

        )

)

好啦，就这样吧，自己测试成功了