PHP 通过Spreadsheet导出Excel文件

导出方法

　　　　 $export = new Export();

        $list = [];

        $expCellName = [
            'order_id' => 'Id',
            'order_sn' => '母订单号',
            'jd_order_id' => '子订单号',
            'status_text' => '订单状态',
            'pay_type_text' => '支付类型',
            'paytime_text' => '支付时间',
            'user_info' => '用户信息',
            'consignee_info' => '收货信息',
            'score_fee' => '积分支付数量',
            'activity_type_text' => '营销类型',
            'goods_title' => '商品名称',
            'goods_original_price' => '商品原价',
            'goods_price' => '商品价格',
            'goods_sku_text' => '商品规格',
            'goods_num' => '购买数量',
            'dispatch_status_text' => '发货状态',
            'dispatch_fee' => '发货费用',
            'dispatch_type_text' => '发货方式',
            'aftersale_refund' => '售后/退款',
            'comment_status_text' => '评价状态',
            'refund_fee' => '退款金额',
            'refund_msg' => '退款原因',
            'express_name' => '快递公司',
            'express_no' => '快递单号',
        ];
        $newList = [];
        foreach ($list as $key => $order) {
            $data = [
                'order_id' => $order['id'],
                'order_sn' => $order['order_sn'],
                'type_text' => $order['type_text'],
                'status_text' => $order['status_text'],
                'pay_type_text' => $order['pay_type_text'],
                'paytime_text' => $order['paytime_text'],
                'platform_text' => $order['platform_text'],
                'user_info' => $order['user'] ? ($order['user']['nickname'] . '-' . $order['user']['mobile']) : '',
                'consignee_info' =>  ($order['consignee'] . '-' . $order['phone']. '-' . $order['province_name'].$order['city_name'].$order['area_name'].$order['address']),
                'total_amount' => $order['total_amount'],
                'coupon_fee' => $order['coupon_fee'],
                'gift_card_fee' => $order['gift_card_fee'],
                'pay_fee' => $order['pay_fee'],
                'score_fee' => $order['score_fee'],
            ];
            foreach ($order['item'] as $k => $item) {
                $itemData = [
                    'jd_order_id' => $item['jd_order_id'].' ',
                    'activity_type_text' => $item['activity_type_text'],
                    'goods_title' => $item['goods_title'],
                    'goods_original_price' => $item['goods_original_price'],
                    'goods_price' => $item['goods_price'],
                    'goods_sku_text' => $item['goods_sku_text'],
                    'goods_num' => $item['goods_num'],
                    'dispatch_status_text' => $item['dispatch_status_text'],
                    'dispatch_fee' => $item['dispatch_fee'],
                    'dispatch_type_text' => $item['dispatch_type_text'],
                    'aftersale_refund' => $item['aftersale_status_text'] . '/' . $item['refund_status_text'],
                    'comment_status_text' => $item['comment_status_text'],
                    'refund_fee' => $item['refund_fee'],
                    'refund_msg' => $item['refund_msg'],
                    'express_name' => $item['express_name'],
                    'express_no' => $item['express_no'],
                ];

                $newList[] = array_merge($data, $itemData);
            }
        }

        $export->exportExcel('订单列表-' . date('Y-m-d H:i:s'), $expCellName, $newList);

 

Export.php 导出类

<?php

namespace addons\shopro\library;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

/**
 * 导出类
 */
class Export
{


    public function __construct()
    {
        
    }

    /**
     * @param string $expTitle 文件标题
     * @param array $expCellName 导出字段
     * @param array $expTableData 数据
     * @param array $mergeCellsName 合并单元格
     */
    public function exportExcel($expTitle, $expCellName, $expTableData,$mergeCellsName=[]){
        $fileName = $expTitle;
        $cellNum = count($expCellName);

        $spreadsheet = new Spreadsheet();
        $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(0);

        $styleArray = [
            'alignment' => [
                //'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中
                'vertical' => Alignment::VERTICAL_CENTER, //垂直居中
            ],
        ];

        $sheet->getStyle('A1:' . $cellName[$cellNum - 1] . '1')->getFont()->setBold(true);

        $i = 0;
        foreach ($expCellName as $key => $cell) {
            $sheet->setCellValue($cellName[$i] . '1', $cell);
            $i ++;
        }

        $i = 0;
        foreach ($expTableData as $key=>$item){
            $itemCount=count($item['item']);
            foreach ($item['item'] as $ii=>$goods){
                $data=array_merge($item,$goods);
                $j = 0;
                if($ii===0){
                    foreach ($expCellName as $key => $cell) {
                        if(in_array($key,$mergeCellsName)){
                            $sheet->mergeCells($cellName[$j] . ($i + 2) . ':' . $cellName[$j] . ($i + 2 + $itemCount-1));
                        }
                        $sheet->setCellValue($cellName[$j] . ($i + 2), isset($data[$key])?$data[$key]:'');
                        $j++;
                    }
                }else{
                    foreach ($expCellName as $key => $cell) {
                        if(!in_array($key,$mergeCellsName)){
                            $sheet->setCellValue($cellName[$j] . ($i + 2), isset($data[$key])?$data[$key]:'');
                        }
                        $j++;
                    }
                }

                $i++;
            }
        }
        $sheet->getStyle('A1:' . $cellName[count($expCellName)] . ($i+2))->applyFromArray($styleArray);
        ob_end_clean();
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $fileName . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls"); //attachment新窗口打印inline本窗口打印
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
}