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