phpoffice PhpSpreadsheet多个sheet导出

多个sheet导出

use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Exception;


function exportXlsx($sheets, $file='ggfgfg.Xlsx')
{
    $AZ = [
        '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'
    ];
    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);
        }
        $keys = $data[0];//这是你的数据键名
        $count = count($keys);//计算你所占的列数
        $title = (string) $sheet['title'];
        $sheet = $spreadsheet->getSheet($n);
        $sheet->setTitle((string) $sheetTitle);
        $sheet->mergeCells('A1:I1');
        $sheet->setCellValue('A1',$title);
        $sheet->getStyle('A1')->getFont()->setBold(true)->setSize(16);
        $sheet->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $n++;
        foreach ($header as $kIndex => $item) {
            $k = $AZ[$kIndex];
            if (isset($item['width'])) {
                // 设置列宽
                $sheet->getColumnDimension($k)->setWidth(floatval($item['width']));
            }
            $sheet->setCellValue("{$k}2", $item['title']);
            // 设置标题加粗
            $sheet->getStyle("{$k}2")->getFont()->setBold(true);
        }
        foreach ($data as $k => $row) {
            $rowNum = $k + 3;
            foreach ($header as $xIndex => $v) {
                $x = $AZ[$xIndex];
                $sheet->setCellValue("{$x}{$rowNum}", $row[$v['key']]);
            }
        }
        $d = $sheet->getHighestRow();
        $df = $sheet->getHighestRow()+1;
        $sheet->setCellValue("A".$df, '数量:');
        $sheet->setCellValue("B".$df, "=SUM(A3:A".$d.")");

    }
    $spreadsheet->setActiveSheetIndex(0);
    $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
    header('Content-Type: application/vnd.ms-excel');
    $writer->save('php://output');
    exit();
}
			$orderInfo= []
            $header =  [
                [
                    'title' => '商品名称', // 表头名
                    'key' => 'name', // data中的键名
                ],
                [
                    'title' => '商品编号', // 表头名
                    'key' => 'code', // data中的键名
                ],
                [
                    'title' => '规格单位', // 表头名
                    'key' => 'rule', // data中的键名
                ],
                [
                    'title' => '生产厂家', // 表头名
                    'key' => 'producer', // data中的键名
                ],
                [
                    'title' => '数量', // 表头名
                    'key' => 'qty', // data中的键名
                ],
                [
                    'title' => '商品价格', // 表头名
                    'key' => 'price', // data中的键名
                ],
                [
                    'title' => '商品金额', // 表头名
                    'key' => 'original_money', // data中的键名
                ],
                [
                    'title' => '折扣金额', // 表头名
                    'key' => 'condition_price', // data中的键名
                ],
                [
                    'title' => '结算金额', // 表头名
                    'key' => 'goods_money', // data中的键名
                ]
            ];
            $sheet = [];
            foreach ($orderInfo['itemsArray'] as $key=>$vals){
                $data = $vals;
                $sheet[$key] = [
                    'header' => $header,
                    'title' => $orderInfo['order_code'].'-'.$orderInfo['customer']['wldw_name'],
                    'customer' => [],
                    'data' =>$data
                ];
            }
            exportXlsx($sheet,'fsdfsdff.xlsx');

单个导出

function toExcel($fields, $lists, $toFile = false)
{

    $spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
    $PHPSheet = $spreadsheet->getActiveSheet(); //获得当前活动sheet的操作对象
    $letter = 65;
    foreach ($fields as $field => $text) {
        $PHPSheet->setCellValue(strtoupper(chr($letter)) . '1', $text);
        $PHPSheet->getColumnDimension(strtoupper(chr($letter)))->setAutoSize(true);
        $letter++;
    }
    $row = 2;
    foreach ($lists as $item) {
        $letter = 65;
        foreach ($fields as $field => $text) {
            $PHPSheet->setCellValue(strtoupper(chr($letter)) . $row, $item[$field]);
            $spreadsheet->getActiveSheet()->setCellValueExplicit(
                strtoupper(chr($letter)) . $row,
                $item[$field],
                \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING2
            );
            $letter++;
        }
        $row++;
    }
    $PHPWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $fileName = date('Ymd_His').'.xlsx';
    header('Content-Type:application/csv;charset=UTF-8');
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/vnd.ms-excel;");
    header("Content-Type:application/octet-stream");
    header('Content-Disposition: attachment;filename="' . $fileName . '"');
    $PHPWriter->save("php://output");
    exit;
}
  foreach ($data as $key => $val) {
            $lists[$key] = [
                'id' => $val['id'],
                'number' => $val['number'],
                'file_number' => $val['file_number'],
                'name' => $val['name'],
                'spec' => $val['spec'],
                'unit' => $val['unit'],
                'producer' => $val['producer'],
                'flag' => $val['flag'] == 1 ? '已上传' : '未上传',
                'approval_number' => $val['approval_number'],

            ];
        }
        $fields = [
            'id' => '商品id',
            'number' => '商品编号',
            'file_number' => '档案编号',
            'name' => '商品名称',
            'spec' => '商品规格',
            'unit' => '商品单位',
            'producer' => '生产厂家',
            'flag' => '上传状态',
            'approval_number' => '批准文号',
        ];

        $excelUrl = toExcel($fields, $lists, true);