本文主要讲解关于PHPExcel兼容PHP版本和操作execl知识
- 目前解决问题：兼容PHP版本问题。
阐述下：小伙子我改bug的过程，如下：线下将代码写好（无错误），本地Apache服务器测试-->正常，打开导出的execl文件中数据也正常;到服务器上就会报找不到该文件错误，如图：
火狐浏览器，谷歌浏览器是直接说该文件已迁移什么的
- 历程：重新审查代码，定位到
$objWriter->save('php://output')这行出问题，万般无奈看插件源码,找来找去发现save方法返回的对象并没有问题!接着去找百度，说是php.ini中的extension=php_zip模块没有开启，进入conf文件，Windows下面编译好的PHP5.6.31并没有这个配置;没办法了，只剩下最后一个必杀技了(看文档)：查找PHPExcel官方文档，发现是PHPExcel兼容PHP版本问题，线上的PHP7.0，本地的5.6.31；好了，按照官方说法将PHPExcel插件替换成1.8.1的，再次测试，nice，so beautiful.解决问题 - 下面是头部关键代码:
//输出EXCEL格式
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
// 从浏览器直接输出$filename
header('Content-Type:application/csv;charset=UTF-8');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
header("Content-Type:application/force-download");
header("Content-Type:application/vnd.ms-excel;");
header("Content-Type:application/octet-stream");
header('Content-Disposition: attachment;filename="'.$name.'.xls"');
header("Content-Transfer-Encoding:binary");
$objWriter->save('php://output');
- 操作execl知识(设置Excel基本样式信息):
$objPHPExcel = new \PHPExcel();
// 设置excel文档的属性
$objPHPExcel->getProperties()->setCreator("WSTMart")//创建人
->setLastModifiedBy("WSTMart")//最后修改人
->setTitle($name)//标题
->setSubject($name)//题目
->setDescription($name)//描述
->setKeywords("订单")//关键字
->setCategory("Test result file");//种类
// 开始操作excel表
$objPHPExcel->setActiveSheetIndex(0);
// 设置工作薄名称
$objPHPExcel->getActiveSheet()->setTitle(iconv('gbk', 'utf-8', 'Sheet'));
// 设置默认字体和大小
$objPHPExcel->getDefaultStyle()->getFont()->setName(iconv('gbk', 'utf-8', ''));
$objPHPExcel->getDefaultStyle()->getFont()->setSize(11);
$styleArray = array(
'font' => array(
'bold' => true,
'color'=>array(
'argb' => 'ffffffff',
)
),
'borders' => array (
'outline' => array (
'style' => \PHPExcel_Style_Border::BORDER_THIN, //设置border样式
'color' => array ('argb' => 'FF000000'), //设置border颜色
)
)
);
//设置宽
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(12);
$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(50);
$objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID);
$objPHPExcel->getActiveSheet()->getStyle('A1:P1')->getFill()->getStartColor()->setARGB('333399');
$objPHPExcel->getActiveSheet()->setCellValue('A1', '订单编号')->setCellValue('B1', '订单状态')->setCellValue('C1', '收货人')->setCellValue('D1', '收货地址')->setCellValue('E1', '联系方式')
->setCellValue('F1', '支付方式')->setCellValue('G1', '配送方式')->setCellValue('H1', '买家留言')->setCellValue('I1', '发票信息')->setCellValue('J1', '订单总金额')->setCellValue('K1', '运费')
->setCellValue('L1', '实付金额')->setCellValue('M1', '下单时间')->setCellValue('N1', '发货时间')->setCellValue('O1', '收货时间')->setCellValue('P1', '取消/拒收原因');
$objPHPExcel->getActiveSheet()->getStyle('A1:P1')->applyFromArray($styleArray);
- Excel表格内容填充
for ($row = 0; $row < count($page); $row++){
$i = $row+2;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$i, $page[$row]['orderNo'])->setCellValue('B'.$i, $page[$row]['status'])->setCellValue('C'.$i, $page[$row]['userName'])->setCellValue('D'.$i, $page[$row]['userAddress'])
->setCellValue('E'.$i, $page[$row]['userPhone'])->setCellValue('F'.$i, $page[$row]['payTypeName'])->setCellValue('G'.$i, $page[$row]['deliverType'])->setCellValue('H'.$i, $page[$row]['orderRemarks'])->setCellValue('I'.$i, $page[$row]['invoiceClient'])
->setCellValue('J'.$i, $page[$row]['totalMoney'])->setCellValue('K'.$i, $page[$row]['deliverMoney'])->setCellValue('L'.$i, $page[$row]['realTotalMoney'])->setCellValue('M'.$i, $page[$row]['createTime'])->setCellValue('N'.$i, $page[$row]['deliveryTime'])
->setCellValue('O'.$i, $page[$row]['receiveTime'])->setCellValue('P'.$i, $page[$row]['logContent']);
}
网友评论