PHP Excel 导出

  • Post author:
  • Post published:8月 8, 2019
  • Post category:PHP
  • Post comments:0评论
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

public function export($data)
    {
        $column_params = [
            [
                'title' => '订单号',
                'index' => 'A',
                'width' => '20',
                'key' => 'order_no'
            ],
            [
                'title' => '订单总额',
                'index' => 'B',
                'width' => '10',
                'key' => 'total'
            ],
            [
                'title' => '订单实付',
                'index' => 'C',
                'width' => '10',
                'key' => 'receipts'
            ],
            [
                'title' => '优惠金额',
                'index' => 'D',
                'width' => '10',
                'key' => 'coupon_amount'
            ],
            [
                'title' => '收货人',
                'index' => 'E',
                'width' => '10',
                'key' => 'consignee'
            ],
            [
                'title' => '联系电话',
                'index' => 'F',
                'width' => '15',
                'key' => 'mobile'
            ],
            [
                'title' => '收货地址',
                'index' => 'G',
                'width' => '45',
                'key' => 'fullAddress'
            ],
            [
                'title' => '订单状态',
                'index' => 'H',
                'width' => '10',
                'key' => 'status'
            ],
            [
                'title' => '下单时间',
                'index' => 'I',
                'width' => '20',
                'key' => 'create_time'
            ],
            [
                'title' => '商品信息',
                'index' => 'J',
                'width' => '20',
                'key' => 'goods'
            ],
        ];
        $exclude = [
            'key' => 'goods',
            'items' => [
                [
                    'title' => '商品标题',
                    'index' => 'J',
                    'width' => '20',
                    'key' => 'title'
                ], [
                    'title' => '商品价格',
                    'index' => 'K',
                    'width' => '10',
                    'key' => 'sell_price'
                ], [
                    'title' => '商品数量',
                    'index' => 'L',
                    'width' => '10',
                    'key' => 'qty'
                ], [
                    'title' => '商品规格',
                    'index' => 'M',
                    'width' => '15',
                    'key' => 'spec'
                ],
            ]
        ];
        $rowStart = 1;
        $rowIndex = 2;
        $spreadsheet = new Spreadsheet();
        $activeSheet = $spreadsheet->getActiveSheet();
        foreach ($column_params as $param) {
            if ($param['key'] != $exclude['key']) {
                $activeSheet->getColumnDimension($param['index'])->setWidth($param['width']);
                $activeSheet->setCellValue($param['index'] . $rowStart, $param['title']);
            } else {
                foreach ($exclude['items'] as $item) {
                    $activeSheet->getColumnDimension($item['index'])->setWidth($item['width']);
                    $activeSheet->setCellValue($item['index'] . $rowStart, $item['title']);
                }
            }
        }

        foreach ($data as $value) {
            $rowMergeEnd = false;
            foreach ($column_params as $param) {
                $rowChrIndex = $param['index'] . $rowIndex;
                if ($param['key'] != $exclude['key']) {
                    $activeSheet->setCellValue($rowChrIndex, $value[$param['key']]);
                } else {
                    foreach ($value[$param['key']] as $childIndex => $items) {
                        foreach ($exclude['items'] as $item) {
                            $rowChrChildIndex = $item['index'] . ($rowIndex + $childIndex);
                            $activeSheet->setCellValue($rowChrChildIndex, $items[$item['key']]);
                        }
                    }
                }
                if (isset($value->{$exclude['key']})) {
                    $merge = ($rowNumber = count($value->{$exclude['key']})) > 1; // 判断是否合并
                } else {
                    $merge = false;
                }
                if ($merge) { // 合并
                    $rowMergeEnd = $rowIndex + $rowNumber - 1; // 当前行 + 合并行数
                    $rowChrMergeEnd = $param['index'] . $rowMergeEnd;
                    if ($param['key'] != $exclude['key']) {
                        $activeSheet->mergeCells($rowChrIndex . ':' . $rowChrMergeEnd);
                    }
                }
            }
            if ($rowMergeEnd) {
                $rowIndex = $rowMergeEnd;
            }
            $rowIndex = $rowIndex + 1;
        }

        $activeSheet->getStyle('N1')->applyFromArray($this->setExcelStyle());
        $activeSheet->duplicateStyle(
            $spreadsheet->getActiveSheet()->getStyle('N1'),
            'A1:M' . $rowIndex
        );


        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header(
            "Content-Disposition:inline;filename=" . iconv(
                "utf-8", "GB2312//TRANSLIT", date('Y-m-d') . '.xlsx'
            )
        );
        header('Cache-Control: max-age=0');//禁止缓存
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
        exit;
    }

    private function setExcelStyle()
    {
        $style = [
//            //设置字体样式
//            'font' => [
//                'name' => 'Arial',
//                'bold' => true,
//                'italic' => false,
//                'underline' => Font::UNDERLINE_DOUBLE,
//                'strikethrough' => false,
//                'color' => [
//                    'rgb' => '808080'
//                ]
//            ],
//            //设置边框线样式
//            'borders' => [
//                //allBorders所有的边框线样式
//                //左边框线
//                'bottom' => [
//                    'borderStyle' => Border::BORDER_DASHDOT,
//                    'color' => [
//                        'rgb' => '808080'
//                    ]
//                ],
//                //上边框线
//                'top' => [
//                    'borderStyle' => Border::BORDER_DASHDOT,
//                    'color' => [
//                        'rgb' => '808080'
//                    ]
//                ]
//            ],
            //对齐样式
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            //是否使用前缀
            'quotePrefix' => true
        ];

        return $style;
    }

发表评论