PHPExcel导出

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;

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

效果图