- 安装phpExecl
- tp5中 composer require phpoffice/phpexcel
- 订单导出，同一订单多商品合并
//导出订单
public function exportOrder()
{
set_time_limit(0);
$getData = input('post.');
$join = [
['order_products op', 'op.order_id = o.order_id', 'left'],
['product p', 'p.id = op.product_id', 'left'],
['user u', 'u.user_id = o.user_id', 'left']
];
$fields = 'o.*,p.name,p.model,p.title,p.main_picture,op.price,op.number,op.total_price
as ptotal_price,u.first_name as ufirst_name,u.last_name as ulast_name';
if (isset($getData['orderIds'])) {
//选择导出
$orderIdArr = explode(',', $getData['orderIds']);
$list = model('order')->alias('o')->join($join)->where('o.order_id', 'in', $orderIdArr)
->field($fields)->order('o.order_id DESC')->select()->toArray();
} else {
//全部导出
if (isset($getData['where'])) {
$where = [];
if (!empty($getData['where'])) {
$data = json_decode($getData['where'], true);
if (isset($data['min_price']) && $data['min_price'])
$where[] = ['o.total_price', '>=', $data['min_price']];
if (isset($data['max_price']) && $data['max_price'])
$where[] = ['o.total_price', '<=', $data['min_price']];
if (isset($data['keywords']) && trim($data['keywords']))
$where[] = ['o.last_name|o.first_name|u.last_name|u.first_name
|o.ordersn|o.last_name|o.first_name', 'like', '%' . trim($data['keywords']) . '%'];
}
$list = model('order')->alias('o')->join($join)->where($where)->
field($fields)->order('o.order_id DESC')->select()->toArray();
} else {
return DataReturn('无效导出', -1);
}
}
if (empty($list)) {
return DataReturn('没有可以导出的订单', -1);
}
$res = [];
$goods_info = [];
foreach ($list as $k => $v) {
$res[$v['ordersn']]['ordersn'] = ' ' . $v['ordersn']; //订单编号
$res[$v['ordersn']]['name'] = $v['ufirst_name'] . ' ' . $v['ulast_name'];//下单用户
$res[$v['ordersn']]['total_price'] = ' ' . $v['total_price']; //订单总价
$res[$v['ordersn']]['names'] = $v['first_name'] . ' ' . $v['last_name'];// 收货人
$res[$v['ordersn']]['phone'] = $v['phone']; //手机号
$res[$v['ordersn']]['company'] = $v['company']; //公司名称
$res[$v['ordersn']]['region'] = $v['region']; //地区
$res[$v['ordersn']]['country'] = $v['country']; //国家
$res[$v['ordersn']]['email'] = $v['email']; //邮箱
$res[$v['ordersn']]['address'] = $v['address']; //详细地址
$res[$v['ordersn']]['product_price'] = $v['price'];
$res[$v['ordersn']]['total_number'] = $v['total_number'];
$res[$v['ordersn']]['ctime'] = date('Y-m-d H:i:s', $v['ctime']);
// $res[$v['ordersn']]['products'][] = '产品名称：'.$v['name'].
' 产品单价：'.$v['price'].' 产品数量：'.$v['number'];
$res[$v['ordersn']]['products'][$k]['name'] = $v['name'];
$res[$v['ordersn']]['products'][$k]['model'] = $v['model'];
$res[$v['ordersn']]['products'][$k]['main_picture'] = $_SERVER['REQUEST_SCHEME'] .
'://' . $_SERVER['SERVER_NAME'] . $v['main_picture'];
$res[$v['ordersn']]['products'][$k]['title'] = $v['title'];
$res[$v['ordersn']]['products'][$k]['price'] = $v['price'];
$res[$v['ordersn']]['products'][$k]['number'] = $v['number'];
$res[$v['ordersn']]['products'][$k]['total_price'] = $v['ptotal_price'];
$goods_info[] = '产品名称：' . $v['name'] . ' 产品单价：' . $v['price']
. ' 产品数量：' . $v['number'];
}
$res = array_values($res);
$orderList = [];
#表头
$header = ['订单编号', '下单用户', '订单金额', '订单产品数', '收货人', '订单产品名称',
'产品型号', '产品图片', '产品简介', '产品单价', '产品数量', '产品总价', '手机号', '邮箱',
'公司名称', '国家', '地区', '详细地址', '下单时间'];
#导出内容组合
foreach ($res as $kk => $vv) {
$orderList[$kk + 1]['ordersn'] = $vv['ordersn'];
$orderList[$kk + 1]['name'] = $vv['name'];
$orderList[$kk + 1]['total_price'] = $vv['total_price'];
$orderList[$kk + 1]['total_number'] = $vv['total_number'];
$orderList[$kk + 1]['names'] = $vv['names'];
$orderList[$kk + 1]['products'] = $vv['products'];
$orderList[$kk + 1]['phone'] = $vv['phone'];
$orderList[$kk + 1]['email'] = $vv['email'];
$orderList[$kk + 1]['company'] = $vv['company'];
$orderList[$kk + 1]['country'] = $vv['country'];
$orderList[$kk + 1]['region'] = $vv['region'];
$orderList[$kk + 1]['address'] = $vv['address'];
$orderList[$kk + 1]['ctime'] = $vv['ctime'];
}
//调用导出类，接收返回值（文件路径）
$path = $this->exportOrderExcel2('订单列表', '订单导出', $header, $orderList);
if ($path) {
//路径拼接域名
$path = $_SERVER['REQUEST_SCHEME'] . '://' . $_SERVER['SERVER_NAME'] . '/' . $path;
return DataReturn('导出成功', 0, ['url' => $path]);
}
return DataReturn('导出失败', -1);
}
function exportOrderExcel2($title, $expFileName, $cellName, $data)
{
//引入核心文件
require_once $_SERVER['DOCUMENT_ROOT'] . '/../vendor/PHPExcel/PHPExcel.php';
$time = md5(date('YmdHis', time()));
$expFileName .= "_" . $time . ".xls";
//设置保存路径
$basePath = request()->env('ROOT_PATH') . 'public';
$baseUrl = DIRECTORY_SEPARATOR . implode(DIRECTORY_SEPARATOR,
['excel', date('Y-m-d', time())]) . DIRECTORY_SEPARATOR;
$path = $basePath . $baseUrl;
//路径不存在则创建路径
if (!is_dir($path)) {
mkdir($path, 0777, true);
}
$path = $path . $expFileName;
$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()
->setCreator("Maarten Balliauw")//创建人
->setLastModifiedBy("Maarten Balliauw")//最后修改人
->setTitle("Office 2007 XLSX Test Document")//设置标题
->setSubject("Office 2007 XLSX Test Document")//设置主题
->setDescription("Test document ")//设置备注
->setKeywords("office 2007 openxml php")//设置关键字
->setCategory("Test result file");
//定义配置
$topNumber = 2;//表头有几行占用
$xlsTitle = iconv('utf-8', 'gb2312', $title);//文件名称
$fileName = $title . date('_YmdHis');//文件名称
$cellKey = 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'
);
$objPHPExcel->getActiveSheet()->getDefaultColumnDimension()->setWidth(24);//所有单元格（列）默认宽度
//垂直居中
$objPHPExcel->getDefaultStyle()->getAlignment()->
setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getDefaultStyle()->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//处理表头标题
$objPHPExcel->getActiveSheet()->mergeCells('A1:' . $cellKey[count($cellName) - 1]
. '1');//合并单元格（如果要拆分单元格是需要先合并再拆分的，否则程序会报错）
$objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $title);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()
->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()
->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
//处理表头
foreach ($cellName as $k => $v) {
//设置表头数据
$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellKey[$k] . $topNumber, $v);
}
//处理数据
$start = $topNumber + 1;
$j = $topNumber + 1;
foreach ($data as $k => $v) {
foreach ($v['products'] as $k1 => $v1) {
//计算初步当前单元格标识，以及 需要合并的单元格标识 A B C D E F G M
$end = $start + count($v['products']) - 1;
$objPHPExcel->getActiveSheet()->mergeCells("A" . $start . ':' . "A" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("B" . $start . ':' . "B" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("C" . $start . ':' . "C" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("D" . $start . ':' . "D" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("E" . $start . ':' . "E" . $end);
// $objPHPExcel->getActiveSheet()->mergeCells("F".$start.':'."F".$end); FGHIJKL
$objPHPExcel->getActiveSheet()->mergeCells("M" . $start . ':' . "M" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("N" . $start . ':' . "N" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("O" . $start . ':' . "O" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("P" . $start . ':' . "P" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("Q" . $start . ':' . "Q" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("R" . $start . ':' . "R" . $end);
$objPHPExcel->getActiveSheet()->mergeCells("S" . $start . ':' . "S" . $end);
$objPHPExcel->getActiveSheet()->setCellValue("A" . $start, $v['ordersn']);
$objPHPExcel->getActiveSheet()->setCellValue("B" . $start, $v['name']);
$objPHPExcel->getActiveSheet()->setCellValue("C" . $start, $v['total_price']);
$objPHPExcel->getActiveSheet()->setCellValue("D" . $start, $v['total_number']);
$objPHPExcel->getActiveSheet()->setCellValue("E" . $start, $v['names']);
$objPHPExcel->getActiveSheet()->setCellValue("M" . $start, $v['phone']);
$objPHPExcel->getActiveSheet()->setCellValue("N" . $start, $v['email']);
$objPHPExcel->getActiveSheet()->setCellValue("O" . $start, $v['company']);
$objPHPExcel->getActiveSheet()->setCellValue("P" . $start, $v['country']);
$objPHPExcel->getActiveSheet()->setCellValue("Q" . $start, $v['region']);
$objPHPExcel->getActiveSheet()->setCellValue("R" . $start, $v['address']);
$objPHPExcel->getActiveSheet()->setCellValue("S" . $start, $v['ctime']);
$objPHPExcel->getActiveSheet()->setCellValue("F" . $j, $v1['name']);
$objPHPExcel->getActiveSheet()->setCellValue("G" . $j, $v1['model']);
$objPHPExcel->getActiveSheet()->setCellValue("H" . $j, $v1['main_picture']);
$objPHPExcel->getActiveSheet()->setCellValue("I" . $j, $v1['title']);
$objPHPExcel->getActiveSheet()->setCellValue("J" . $j, $v1['price']);
$objPHPExcel->getActiveSheet()->setCellValue("K" . $j, $v1['number']);
$objPHPExcel->getActiveSheet()->setCellValue("L" . $j, $v1['total_price']);
$j++;
}
$start += count($v['products']);
}
ob_end_clean();//防止乱码
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
//保存路径
$objWriter->save($path);
$filePath = str_replace('\', '/', $baseUrl . $expFileName);
return $filePath;
}