PHPExcel用法有感
今日项目需求导出excel。我用最简单的header头方式导出了，但是需求部门退回了，说不满足他们的需求，需要我按照他们的模板来导出。
然后想到了PHPExcel，没用过，走了不少弯路。
1.去官网下载最新的包。
2.普通的导出（和普通Header头导出的一样）。
1 import("Org.Util.PHPExcel"); 2 import("Org.Util.PHPExcel.Writer.Excel5"); 3 import("Org.Util.PHPExcel.IOFactory.php"); 4 import("Org.Util.PHPExcel.Worksheet.Drawing"); 5 $date = date("Y_m_d",time()); 6 $fileName = $date.".xls"; 7 $objPHPExcel = new \PHPExcel(); 8 $objProps = $objPHPExcel->getProperties(); 9 $objActSheet = $objPHPExcel->setActiveSheetIndex(0); 10 $objActSheet->setCellValue('A1', '订单号'); 11 $objActSheet->setCellValue('B1', '退货单号'); 12 $objActSheet->setCellValue('C1', '生成时间'); 13 $objActSheet->setCellValue('D1', '商品名称'); 14 $objActSheet->setCellValue('E1', '物料编码'); 15 $objActSheet->setCellValue('F1', '退货工单号'); 16 $objActSheet->setCellValue('G1', '退款单号'); 17 $n=2; 18 foreach($list as $value){ 19 $where = array( 20 'back_id'=>$value['back_id'] 21 ); 22 $getDetial = $db->table('back_goods')->where($where)->find(); 23 $objActSheet->setCellValueExplicit('A'.$n, $value['order_sn']); 24 $objActSheet->setCellValueExplicit('B'.$n, $value['back_sn']); 25 $objActSheet->setCellValue('C'.$n, date('Y-m-d H:i:s',$value['add_time'])); 26 $objActSheet->setCellValue('D'.$n, $getDetial['goods_name']); 27 $objActSheet->setCellValue('E'.$n, $getDetial['goods_sn']); 28 $objActSheet->setCellValue('F'.$n, ''); 29 $objActSheet->setCellValue('G'.$n, ''); 30 $n++; 31 } 32 $fileName = iconv("utf-8", "gb2312", $fileName); 33 header('Content-Type: application/vnd.ms-excel'); 34 header("Content-Disposition: attachment;filename=\"$fileName\""); 35 header('Cache-Control: max-age=0'); 36 $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); 37 $objWriter->save('php://output'); //文件通过浏览器下载
3.写入到模板excel。
1 import("Org.Util.PHPExcel"); 2 import("Org.Util.PHPExcel.Writer.Excel5"); 3 import("Org.Util.PHPExcel.IOFactory.php"); 4 import("Org.Util.PHPExcel.Worksheet.Drawing"); 5 $PHPReader = new \PHPExcel_Reader_Excel5(); 6 $fileName="./Public/a.xls"; 7 $PHPExcel = $PHPReader->load($fileName); 8 $objActSheet = $PHPExcel->getSheet(0); 9 $allColumn = $objActSheet->getHighestRow(); 10 $n=1+$allColumn; 11 if($data){ 12 foreach($data as $k=>$v){ 13 $objActSheet->setCellValue('A'.$n, $k+1);//编号 14 $objActSheet->setCellValue('B'.$n, $v['name']);//姓名 15 $n++; 16 } 17 } 18 $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel); 19 $objWriter->save($fileName);
4.根据模板写入，并不改变模板，另存为下载。
1 import("Org.Util.PHPExcel"); 2 import("Org.Util.PHPExcel.Writer.Excel5"); 3 import("Org.Util.PHPExcel.IOFactory.php"); 4 import("Org.Util.PHPExcel.Worksheet.Drawing"); 5 $PHPReader = new \PHPExcel_Reader_Excel5(); 6 $fileName="./Public/a.xls"; 7 $PHPExcel = $PHPReader->load($fileName); 8 $objActSheet = $PHPExcel->getSheet(0); 9 $allColumn = $objActSheet->getHighestRow(); 10 $n=1+$allColumn; 11 if($data){ 12 foreach($data as $k=>$v){ 13 $objActSheet->setCellValue('A'.$n, $k+1);//编号 14 $objActSheet->setCellValue('B'.$n, $v['name']);//姓名 15 $objActSheet->setCellValue('C'.$n, "");//性别? 16 $n++; 17 } 18 } 19 $file_name="test.xls"; 20 $fileName = iconv("utf-8", "gb2312", $file_name); 21 header('Content-Type: application/vnd.ms-excel'); 22 header("Content-Disposition: attachment;filename=\"$fileName\""); 23 header('Cache-Control: max-age=0'); 24 $objWriter = \PHPExcel_IOFactory::createWriter($PHPExcel, 'Excel5'); 25 $objWriter->save('php://output'); //文件通过浏览器下载
注：如果遇到xlsx模板，把new类改成2007即可。普通的xls模板直接5
