php excel多sheet导出

php excel 多sheet导出函数

安装 phpoffice/phpspreadsheet 包

composer require phpoffice/phpspreadsheet

实现代码如下

<?php
/**
 * Created by PhpStorm.
 * User: xinu x-php@outlook.com
 * Coding Standard: PSR2
 * DateTime: 2020-07-11 21:24
 */
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$sheet = [
    'sheetTitle' => [
        'header' => [
            [
                'title' => '列1', // 表头名
                'key' => 'id', // data中的键名
                'width' => 20, // 列宽度 可不设置
            ],
            [
                'title' => '列2', // 表头名
                'key' => 'name', // data中的键名
                'width' => 20, // 列宽度 可不设置
            ]
        ],
        'data' => [
            [
                'id' => 1,
                'name' => 'test1',
            ]
        ]
    ]
];
function exportXlsx($sheets, $file)
{
    $AZ = [
        'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N'
    ];
    ob_end_clean();
    $spreadsheet = new Spreadsheet();
    $n = 0;
    foreach ($sheets as $sheetTitle => $sheet) {
        $data = array_values($sheet['data']);
        $header = array_values($sheet['header']);
        if (empty($header)) {
            throw new \Exception('excel头不可为空');
        }
        if ($n) {
            $spreadsheet->addSheet(new Worksheet($spreadsheet, (string)$sheetTitle), $n);
        }
        $sheet = $spreadsheet->getSheet($n);
        $sheet->setTitle((string)$sheetTitle);
        $n++;
        foreach ($header as $kIndex => $item) {
            $k = $AZ[$kIndex];
            if (isset($item['width'])) {
                // 设置列宽
                $sheet->getColumnDimension($k)->setWidth(floatval($item['width']));
            }
            $sheet->setCellValue("{$k}1", $item['title']);
            // 设置标题加粗
            $sheet->getStyle("{$k}1")->getFont()->setBold(true);
        }
        foreach ($data as $k => $row) {
            $rowNum = $k + 2;
            foreach ($header as $xIndex => $v) {
                $x = $AZ[$xIndex];
                $sheet->setCellValue("{$x}{$rowNum}", $row[$v['key']]);
            }
        }
    }
    $spreadsheet->setActiveSheetIndex(0);
    $writer = new Xlsx($spreadsheet);
    header("Content-Type: application/octet-stream");
    header("Content-Disposition: attachment;filename=\"{$file}\"");
    header("Cache-Control: max-age=0");
    $writer->save('php://output');
    exit();
}