PHPexcel多sheet导出

不念不忘少年蓝@ 2023-06-08 04:58 65阅读 0赞

## 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;
    }

**效果图**  
![主要是样式][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxOTU4Ng_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MzcxOTU4Ng_size_16_color_FFFFFF_t_70]: /images/20230531/559188b7f5b544eba02aae84cb3aa1a5.png

发表评论取消回复

表情：

评论列表（有 0 条评论，65人围观）

还没有评论，来说两句吧...

相关阅读

相关 EasyExcel导出（单Sheet导出、多Sheet导出、多文件导出）

[EasyExcel官方文档 - 基于Java的Excel处理工具 | Easy Excel][EasyExcel_ - _Java_Excel_ _ Easy Excel]

叁歲伎倆/ 2024年03月17日 15:20/ 0 赞/ 114 阅读

相关 Easypoi 多sheet数据导入导出

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过

妖狐艹你老母/ 2024年02月23日 07:27/ 0 赞/ 92 阅读

相关 easyexcel导出多sheet

easyexcel导出多sheet public void exportLedger(HttpServletRequest request, HttpServletR

蔚落/ 2024年02月05日 19:03/ 0 赞/ 87 阅读

相关 poi带表头多sheet导出

导出工具类 package com.hieasy.comm.core.excel; import com.hieasy.comm.core

亦凉/ 2023年10月15日 09:45/ 0 赞/ 137 阅读

相关 easyPOI导出多Sheet

easyPOI导出多Sheet > 声明，本文基于[JAVA操作Excel（POI、easyPOI、easyExcel\_我认不到你的博客-CSDN博客][JAVA_Ex

╰+攻爆jí腚メ/ 2023年10月12日 15:34/ 0 赞/ 71 阅读

相关 PHPexcel多sheet导出

PHPexcel多sheet导出上一篇是单个sheet的导出\[https://blog.csdn.net/weixin\_43719586/article/detai

不念不忘少年蓝@/ 2023年06月08日 04:58/ 0 赞/ 66 阅读

相关 Excel中多sheet导出csv

Sub Macro1() Dim sh As Worksheet, p$ With Application.FileDialog(mso

港控/mmm°/ 2022年11月22日 05:12/ 0 赞/ 316 阅读

相关 easypoi一个excel导出多sheet

easypoi一个excel导出多sheet public void downloadTheImporTemplate(HttpServletRequest re

Dear 丶/ 2022年11月17日 03:50/ 0 赞/ 281 阅读

相关 .net多sheet页导出文件

using System; using System.Data; using System.Linq; using System.Configu

拼搏现实的明天。/ 2022年06月14日 02:21/ 0 赞/ 229 阅读

相关 PHPExcel 导入数据导多个工作区(sheet)

导出数据导EXCEL中有时需要分开保存到多个工作区内，实现代码如下： PS：貌似只能导出总共几万条数据，并不能每个工作区都几万条。 include_once

以你之姓@/ 2022年06月06日 12:51/ 0 赞/ 278 阅读