PHPexcel多sheet导出
上一篇是单个sheet的导出[/weixin_43719586/article/details/102514412]
这次来个多sheet的
*这是要导出的数据
Array([1016155711] => Array([deliver_time] => -10-16 16:18:42[nickname] => hzy[address] => [mobilephone] => [order_id] => 1016155711[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)))[1016160918] => Array([deliver_time] => -10-16 16:42:58[nickname] => xinlu[address] => 杭州市滨江区滨安路1234号[mobilephone] => 1234567[order_id] => 1016160918[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)))[1016160931] => Array([deliver_time] => -10-16 16:42:53[nickname] => xinlu[address] => 杭州市滨江区滨安路1234号[mobilephone] => 1234567[order_id] => 1016160931[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;}
效果图
