PHPExcel导出

Dear 丶 2023-06-06 13:28 8阅读 0赞

## PHPExcel导出 ##

PHPExcel导出之前只做过简单的，这一次这个有些复杂的，包括单元格合并、字体的样式、边框线、单元格的背景色、单元格内容的自动换行。

## 代码呈上 ##

//引入PHPExcel
            import("Org.Util.PHPExcel");
            import("Org.Util.PHPExcel.Writer.Excel5");
            import("Org.Util.PHPExcel.IOFactory");
            
            // 设置PHPExcel对象
            $objPHPExcel = new \PHPExcel();
            $objProps = $objPHPExcel->getProperties();
    
            // 设置文档属性
              $objPHPExcel->getProperties()->setCreator("qingdan")
              ->setLastModifiedBy("qingdan")
              ->setTitle("Office 2007 XLSX Test Document")
              ->setSubject("Office 2007 XLSX Test Document")
              ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
              ->setKeywords("office 2007 openxml php")
              ->setCategory("Test result file");
    
            //生成列名，A-Z AA-AZ BA-BZ CA-CT
            $col_arr=range("A","Z");
    
            // 设置行高度
            $objPHPExcel->getActiveSheet()->getDefaultRowDimension()->setRowHeight(25);
            $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(35);    //第二行行高
    
            //设置单元格宽度
            $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
            $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
            $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(15);
            $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(15);
    
            // 合并
            $objPHPExcel->getActiveSheet()->mergeCells('A1:L1');
            $objPHPExcel->getActiveSheet()->mergeCells('A4:B4');
            $objPHPExcel->getActiveSheet()->mergeCells('C4:D4');
            $objPHPExcel->getActiveSheet()->mergeCells('E4:F4');
            $objPHPExcel->getActiveSheet()->mergeCells('H4:L4');
            $objPHPExcel->getActiveSheet()->mergeCells('B3:L3');
            $objPHPExcel->getActiveSheet()->mergeCells('D2:F2');
            $objPHPExcel->getActiveSheet()->mergeCells('H2:L2');
            //合计
            $count1 = $count + 1;
            $objPHPExcel->getActiveSheet()->mergeCells("A$count1:B$count1");
            //页脚
            $foot1 = $count1+1;
            $foot3 = $count1+3;
            $objPHPExcel->getActiveSheet()->mergeCells("A$foot1:L$foot3");
    
            //默认居中
            $objPHPExcel->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            $objPHPExcel->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
    
            // 字体和样式
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(24);      //字体大小
            $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('C2')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('G2')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A3')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A4')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('G4')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('A5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('B5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('C5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('D5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('E5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('F5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('G5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('H5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('I5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('J5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('K5')->getFont()->setBold(true);
            $objPHPExcel->getActiveSheet()->getStyle('L5')->getFont()->setBold(true);
    
            //边框线
            $styleThinBlackBorderOutline = array(
                'borders' => array (
                    'allborders' => array (                               //allborders 表示全部线框
                        'style' => \PHPExcel_Style_Border::BORDER_THIN,   //设置border样式
                        'color' => array ('argb' => 'FF000000'),          //设置border颜色
                    ),
                ),
            );
    
            $objPHPExcel->getActiveSheet()->getStyle( "A1:L"."$foot3")->applyFromArray($styleThinBlackBorderOutline);
    
            //设置填充的样式和背景色
            $objPHPExcel->getActiveSheet()->getStyle( 'A2:L4')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
            $objPHPExcel->getActiveSheet()->getStyle( 'A2:L4')->getFill()->getStartColor()->setARGB('0092D050');
    
             $key = 0;
             foreach($col_arr as $v){ 
                //注意，不能少了。将列数字转换为字母\
                $colum = \PHPExcel_Cell::stringFromColumnIndex($key);
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A1",'*******************');
    
                /*表头及列出信息*/
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A2",'订单编号');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("C2",'配送时间');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("G2",'订购食堂');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A3",'收货地址');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A4",'下单人员及联系方式');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("G4",'配送单位');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A5",'品类编码');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("B5",'菜品');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("C5",'规格');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("D5",'单位');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("E5",'加工要求');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("F5",'订购数');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("G5",'配送数');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("H5",'签收数');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("I5",'单价');                                      $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("J5",'装箱单位');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("K5",'金额/元');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("L5",'装箱数');
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A$count1",'合计');
    
                //页脚内容
                 $str = "送货人： 验收人： 核对人： 结算部门（章）： ". chr(10) . chr(10) ."备 注： ". chr(10) . chr(10) ."第一联：****** 第二联：****** 第三联：****** 第四联：********* ";
    
                $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A$foot1",$str);
                //设置单元格自动换行属性
                 $objPHPExcel->getActiveSheet()->getStyle("A$foot1")->getAlignment()->setWrapText(true);
                //左对齐
                 $objPHPExcel->getActiveSheet()->getStyle("A$foot1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_JUSTIFY);
                 $objPHPExcel->getActiveSheet()->getStyle("A$foot1")->getFont()->setSize(10);      //字体大小
                 $objPHPExcel->getActiveSheet()->getStyle("A$foot1")->getFont()->setBold(true);      //字体加粗
                //表头信息
                 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("B2"," "."******");   //订单号
                 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("D2","******");   //订单配送时间
                 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("H2","******");   //订购的食堂
                 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("H4","******");   //配送单位
                 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("B3","******");   //收货地址
                 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("C4","******");   //下单人员
                 $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("E4","******");   //联系方式
                $key += 1;
                }
    
                    $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(false);
    
                    //赋值
                $column = 6; //从第六行写入数据 第五行之前是表头
                $objActSheet = $objPHPExcel->getActiveSheet();
                foreach($order_datas as $key => $rows){  //行写入
                    $span = ord("A");
                    foreach ($rows as $keyName => $value) { 
                        $j = chr($span);
                        if ($keyName != 'order_id_list') { 
                            $objActSheet->setCellValue($j .''. $column, "\t".$value);
                            $span++;
                        } else { 
                            $objActSheet->setCellValue($j .''.$column, "\t".$value);
                            $span++;
                        }
                    }
                    $column++;
                }
    
                $fileName = iconv("utf-8", "gb2312", $fileName);
                $objPHPExcel->setActiveSheetIndex(0);
                ob_end_clean();
                header('Content-Type: application/vnd.ms-excel');
                header("Content-Disposition: attachment;filename=\"$fileName\"");
                header('Cache-Control: max-age=0');
                $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
                $objWriter->save('php://output');
                exit;

**长数字前要拼个空格让其成为字符串，否则会用科学计数法表示。**

**效果图**  
![在这里插入图片描述][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/20230601/fe46d1a15c874665b8c8c05679db1482.png