PHPExcel用法有感

今日项目需求导出excel。我用最简单的header头方式导出了，但是需求部门退回了，说不满足他们的需求，需要我按照他们的模板来导出。

然后想到了PHPExcel，没用过，走了不少弯路。

1.去官网下载最新的包。

2.普通的导出（和普通Header头导出的一样）。

                 import("Org.Util.PHPExcel");

         import("Org.Util.PHPExcel.Writer.Excel5");

         import("Org.Util.PHPExcel.IOFactory.php");

         import("Org.Util.PHPExcel.Worksheet.Drawing");

         $date = date("Y_m_d",time());

         $fileName = $date.".xls";

         $objPHPExcel = new \PHPExcel();

         $objProps = $objPHPExcel->getProperties();

         $objActSheet = $objPHPExcel->setActiveSheetIndex(0);

         $objActSheet->setCellValue('A1', '订单号');

         $objActSheet->setCellValue('B1', '退货单号');

         $objActSheet->setCellValue('C1', '生成时间');

         $objActSheet->setCellValue('D1', '商品名称');

         $objActSheet->setCellValue('E1', '物料编码');

         $objActSheet->setCellValue('F1', '退货工单号');

         $objActSheet->setCellValue('G1', '退款单号');

         $n=2;

         foreach($list as $value){

             $where = array(

                 'back_id'=>$value['back_id']

             );

             $getDetial = $db->table('back_goods')->where($where)->find();

             $objActSheet->setCellValueExplicit('A'.$n, $value['order_sn']);

             $objActSheet->setCellValueExplicit('B'.$n, $value['back_sn']);

             $objActSheet->setCellValue('C'.$n, date('Y-m-d H:i:s',$value['add_time']));

             $objActSheet->setCellValue('D'.$n, $getDetial['goods_name']);

             $objActSheet->setCellValue('E'.$n, $getDetial['goods_sn']);

             $objActSheet->setCellValue('F'.$n, '');

             $objActSheet->setCellValue('G'.$n, '');

             $n++;

         }

         $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');

         $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

         $objWriter->save('php://output'); //文件通过浏览器下载

3.写入到模板excel。

 import("Org.Util.PHPExcel");

         import("Org.Util.PHPExcel.Writer.Excel5");

         import("Org.Util.PHPExcel.IOFactory.php");

         import("Org.Util.PHPExcel.Worksheet.Drawing");

         $PHPReader = new \PHPExcel_Reader_Excel5();

         $fileName="./Public/a.xls";

         $PHPExcel = $PHPReader->load($fileName);

         $objActSheet = $PHPExcel->getSheet(0);

         $allColumn = $objActSheet->getHighestRow();

         $n=1+$allColumn;

         if($data){

             foreach($data as $k=>$v){

                 $objActSheet->setCellValue('A'.$n, $k+1);//编号

                 $objActSheet->setCellValue('B'.$n, $v['name']);//姓名

                 $n++;

             }

         }

 $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);

 $objWriter->save($fileName);

4.根据模板写入，并不改变模板，另存为下载。

 import("Org.Util.PHPExcel");

         import("Org.Util.PHPExcel.Writer.Excel5");

         import("Org.Util.PHPExcel.IOFactory.php");

         import("Org.Util.PHPExcel.Worksheet.Drawing");

         $PHPReader = new \PHPExcel_Reader_Excel5();

         $fileName="./Public/a.xls";

         $PHPExcel = $PHPReader->load($fileName);

         $objActSheet = $PHPExcel->getSheet(0);

         $allColumn = $objActSheet->getHighestRow();

         $n=1+$allColumn;

         if($data){

             foreach($data as $k=>$v){

                 $objActSheet->setCellValue('A'.$n, $k+1);//编号

                 $objActSheet->setCellValue('B'.$n, $v['name']);//姓名

                 $objActSheet->setCellValue('C'.$n, "");//性别?

                 $n++;

             }

         }

         $file_name="test.xls";

         $fileName = iconv("utf-8", "gb2312", $file_name);

         header('Content-Type: application/vnd.ms-excel');

         header("Content-Disposition: attachment;filename=\"$fileName\"");

         header('Cache-Control: max-age=0');

         $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5');

         $objWriter->save('php://output'); //文件通过浏览器下载

注：如果遇到xlsx模板，把new类改成2007即可。普通的xls模板直接5