导出方法
$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');
}
}