PHPexcel多sheet导出
程序员文章站
2022-03-09 23:13:51
...
PHPexcel多sheet导出
上一篇是单个sheet的导出[https://blog.csdn.net/weixin_43719586/article/details/102514412]
这次来个多sheet的
*这是要导出的数据
Array
(
[20191016155711] => Array
(
[deliver_time] => 2019-10-16 16:18:42
[nickname] => hzy
[address] =>
[mobilephone] =>
[order_id] => 20191016155711
[order_food] => Array
(
[0] => Array
(
[stock_code] => 007002
[food_name] => 大头菜
[spec] =>
[unit] => 斤
[process] =>
[number] => 17
[deliver_num] => 17
[reach_num] => 17
[sale_price] =>
[criterion] => 0
)
[1] => Array
(
[stock_code] => 0040003
[food_name] => 鸭锁骨
[spec] =>
[unit] => 斤
[process] =>
[number] => 24
[deliver_num] => 20
[reach_num] => 20
[sale_price] =>
[criterion] => 0
)
[2] => Array
(
[stock_code] => 0040004
[food_name] => 鸭肫（中）
[spec] =>
[unit] => 斤
[process] =>
[number] => 24
[deliver_num] => 24
[reach_num] => 24
[sale_price] =>
[criterion] => 0
)
)
)
[20191016160918] => Array
(
[deliver_time] => 2019-10-16 16:42:58
[nickname] => xinlu
[address] => 杭州市滨江区滨安路1234号
[mobilephone] => 1234567
[order_id] => 20191016160918
[order_food] => Array
(
[0] => Array
(
[stock_code] => 0010001
[food_name] => 白砂糖
[spec] => 100
[unit] => 斤
[process] =>
[number] => 11
[deliver_num] => 11
[reach_num] => 11
[sale_price] =>
[criterion] => 10
)
[1] => Array
(
[stock_code] => 0010002
[food_name] => 白糖
[spec] => 50
[unit] => 斤
[process] =>
[number] => 22
[deliver_num] => 22
[reach_num] => 22
[sale_price] =>
[criterion] => 10
)
)
)
[20191016160931] => Array
(
[deliver_time] => 2019-10-16 16:42:53
[nickname] => xinlu
[address] => 杭州市滨江区滨安路1234号
[mobilephone] => 1234567
[order_id] => 20191016160931
[order_food] => Array
(
[0] => Array
(
[stock_code] => 0010003
[food_name] => 双鱼袋装米醋
[spec] => 32
[unit] => 400ml/瓶
[process] =>
[number] => 33
[deliver_num] => 33
[reach_num] => 33
[sale_price] =>
[criterion] => 10
)
[1] => Array
(
[stock_code] => 0010004
[food_name] => 湖羊黄豆酱
[spec] => 225g*30
[unit] => 13.5斤/箱
[process] =>
[number] => 44
[deliver_num] => 44
[reach_num] => 44
[sale_price] =>
[criterion] => 10
)
)
)
)
封装了一个方法
function send_exports($orders,$fileName){
import("Org.Util.PHPExcel");
import("Org.Util.PHPExcel.Writer.Excel5");
import("Org.Util.PHPExcel.IOFactory");
$objPHPExcel = new \PHPExcel();
$objProps = $objPHPExcel->getProperties();
$headerStyle = [
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
'vertical' => Alignment::VERTICAL_CENTER,
],
'fill' => [
'fillType' => \PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID,
],
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
];
$titleStyle = [
'font' => [
'size' => 24,
'name' => '微软雅黑',
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
],
];
$footStyle = [
'font' => [
'size' => 10,
'name' => '微软雅黑',
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_LEFT,
],
];
$normalStyle = [
'borders' => [
'allBorders' => [
'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
'color' => ['argb' => 'FF000000'],
],
],
];
$bodyStyle = [
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
],
];
$spreadsheet = new Spreadsheet();
foreach ($orders as $index => $order) {
//工作簿标题
$sheet = new Worksheet($spreadsheet, "$index");
$spreadsheet->addSheet($sheet,$index);
$col_arr=range("A","Z");
$count = count($order['order_food']);
$count1 = $count + 6;
$count2 = $count1 + 1;
$count3 = $count1 + 5;
$objPHPExcel->getActiveSheet()->getStyle("A1:L$count2")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
// 设置行高度
$objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(30);
//边框线
$sheet->getStyle("A1:L$count3")->applyFromArray($normalStyle);
//标题样式
$sheet->getStyle("A1")->applyFromArray($titleStyle);
$sheet->getStyle("A2:L$count1")->applyFromArray($bodyStyle);
$sheet->getStyle("A$count3")->applyFromArray($footStyle);
//设置单元格宽度
$sheet->getColumnDimension('A')->setWidth(15);
$sheet->getColumnDimension('B')->setWidth(25);
$sheet->getColumnDimension('C')->setWidth(15);
$sheet->getColumnDimension('D')->setWidth(10);
$sheet->getColumnDimension('E')->setWidth(15);
$sheet->getColumnDimension('F')->setWidth(15);
$sheet->getColumnDimension('G')->setWidth(15);
$sheet->getColumnDimension('H')->setWidth(15);
$sheet->getColumnDimension('I')->setWidth(15);
$sheet->getColumnDimension('J')->setWidth(15);
$sheet->getColumnDimension('K')->setWidth(15);
$sheet->getColumnDimension('L')->setWidth(15);
// 合并
$sheet->mergeCells('A1:L1');
$sheet->mergeCells('D2:F2');
$sheet->mergeCells('H2:L2');
$sheet->mergeCells('B3:L3');
$sheet->mergeCells('A4:B4');
$sheet->mergeCells('C4:D4');
$sheet->mergeCells('E4:F4');
$sheet->mergeCells('H4:L4');
//合计合并
$sheet->mergeCells("B$count1:L$count1");
//页脚合并
$sheet->mergeCells("A$count2:L$count3");
//设置填充的样式和背景色
$sheet->getStyle( 'A2:L4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
$sheet->getStyle( 'A2:L4')->getFill()->getStartColor()->setARGB('0092D050');
$sheet->setCellValue("A1",'正泰集团食堂农副产品订购清单');
/*表头及列出信息*/
$sheet->setCellValue("A2",'订单编号');
$sheet->setCellValue("C2",'配送时间');
$sheet->setCellValue("G2",'订购食堂');
$sheet->setCellValue("A3",'收货地址');
$sheet->setCellValue("A4",'下单人员及联系方式');
$sheet->setCellValue("G4",'配送单位');
$sheet->setCellValue("A5",'品类编码');
$sheet->setCellValue("B5",'菜品');
$sheet->setCellValue("C5",'规格');
$sheet->setCellValue("D5",'单位');
$sheet->setCellValue("E5",'加工要求');
$sheet->setCellValue("F5",'订购数');
$sheet->setCellValue("G5",'配送数');
$sheet->setCellValue("H5",'签收数');
$sheet->setCellValue("I5",'单价');
$sheet->setCellValue("J5",'装箱单位');
$sheet->setCellValue("K5",'金额/元');
$sheet->setCellValue("L5",'装箱数');
$sheet->setCellValue("A$count1",'合计');
//页脚内容
$str = "送货人： 验收人： 核对人： 结算部门（章）： ". chr(10) . chr(10) ."*** ". chr(10) . chr(10) ."*** ";
$sheet->setCellValue("A$count2",$str);
//设置单元格自动换行属性
$sheet->getStyle("A$count2")->getAlignment()->setWrapText(true);
//表头信息
$sheet->setCellValue("B2"," "."$order[order_id]"); //订单号
$sheet->setCellValue("D2",$order['deliver_time']); //订单配送时间
$sheet->setCellValue("H2",$order['nickname']); //订购的食堂
$sheet->setCellValue("H4",$order['nickname']); //配送单位
$sheet->setCellValue("B3",$order['address']); //收货地址
$sheet->setCellValue("C4",$order['nickname']); //下单人员
$sheet->setCellValue("E4",$order['mobilephone']); //联系方式
//赋值
$column = 6; //从第六行写入数据 第五行之前是表头
foreach($order['order_food'] as $key => $rows){ //行写入
$span = ord("A");
foreach ($rows as $keyName => $value) {
$j = chr($span);
if ($keyName != 'order_id_list') {
$sheet->setCellValue($j .''. $column, "\t".$value);
$span++;
} else {
$sheet->setCellValue($j .''.$column, "\t".$value);
$span++;
}
}
$column++;
}
}
if (!empty($orders)) {
$spreadsheet->removeSheetByIndex($spreadsheet->getIndex($spreadsheet->getSheetByName('Worksheet')));
}
$fileName = iconv("utf-8", "gb2312", $fileName);
header('Content-Type: application/vnd.ms-excel');
header("Content-Disposition: attachment;filename=\"$fileName\"");
header('Cache-Control: max-age=0');
ob_end_clean();
$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
die;
}
效果图