phpexcel常用处理
##导入类库
require'PHPExcel/Classes/PHPExcel.php';
require'PHPExcel/Classes/PHPExcel/Writer/Excel5.php';//非07格式的写出类
##基础属性设定
$objPHPExcel=\PHPExcel_IOFactory::load('a.xls');//读入指定excel文件
$objPHPExcel->setActiveSheetIndex(0);//指定活动工作表
$objPHPExcel->getActiveSheet()->getDefaultStyle()->getFont()->setName('宋体');
$objPHPExcel->getProperties()->setTitle('xxx');
##单元格编辑
$objPHPExcel->getActiveSheet()->setCellValue('A3','xxx');//设定A3单元格值为xxx
##单元格绘图
$objDrawing=new\PHPExcel_Worksheet_Drawing();
$objDrawing->setPath('a.jpg');//指定图片路径。若要远程图片需PHPExcel/Classes/PHPExcel/Worksheet/Drawing.php:106处file_exists换成file_get_contents
$objDrawing->setCoordinates('A4');//指定在A4单元格绘图
$objDrawing->setName('Photo');
$objDrawing->setDescription('Photo');
$objDrawing->setHeight(120);
$objDrawing->setWidth(100);
$objDrawing->setOffsetX(7);
$objDrawing->setOffsetY(7);
$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
##excel文件浏览器下载导出
$filename='a.xls';
$encoded_filename=rawurlencode($filename);
$ua=$_SERVER["HTTP_USER_AGENT"];
header('Content-type:application/vnd.ms-excel');
if(preg_match("/MSIE/",$ua)||preg_match("/Trident\/7.0/",$ua)||preg_match("/Edge/",$ua)){
header('Content-Disposition:attachment;filename="'.$encoded_filename.'"');
}elseif(preg_match("/Firefox/",$ua)){
header("Content-Disposition:attachment;filename*=\"utf8''".$filename.'"');
}else{
header('Content-Disposition:attachment;filename="'.$filename.'"');
}
header("Pragma:no-cache");
header("Expires:0");
$objWriter=PHPExcel_IOFactory::createWriter($objPHPExcel,'Excel5');
$objWriter->save('php://output');
##excel文件html显示(可用于调试)
$objWriter=new\PHPExcel_Writer_HTML($objPHPExcel);
$objWriter->save('php://output');
利用mpdf库从phpexcel导出pdf文件
$filename='a.pdf';
$encoded_filename=rawurlencode($filename);
$rendererName=\PHPExcel_Settings::PDF_RENDERER_MPDF;//指定通过mpdf类库导出pdf文件
$rendererLibraryPath='PHPExcel/MPDF57';//指定你下载的mpdf类库路径
if(!\PHPExcel_Settings::setPdfRenderer(
$rendererName,
$rendererLibraryPath
)){
die(
'Pleasesetthe$rendererNameand$rendererLibraryPathvalues'.
PHP_EOL.
'asappropriateforyourdirectorystructure'
);
}
header('Content-type:application/pdf');
if(preg_match("/MSIE/",$ua)||preg_match("/Trident\/7.0/",$ua)||preg_match("/Edge/",$ua)){
header('Content-Disposition:attachment;filename="'.$encoded_filename.'"');
}elseif(preg_match("/Firefox/",$ua)){
header("Content-Disposition:attachment;filename*=\"utf8''".$file_name.'"');
}else{
header('Content-Disposition:attachment;filename="'.$file_name.'"');
}
header("Pragma:no-cache");
header("Expires:0");
$objWriter=new\PHPExcel_Writer_PDF($objPHPExcel);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('php://output');
##############################
##pdf导出失败的一些错误解决方法
##############################
##1pdf中文乱码问题
PHPExcel/Classes/PHPExcel/Writer/PDF/mPDF.php:105处加两行设定：
$pdf->useAdobeCJK=true;
$pdf->SetAutoFont(AUTOFONT_ALL);
##2类库里面多处preg_replace调用使用了元字符e，而部分低版本php不支持正则表达式e元字符
e元字符的不当使用并导致pdf报错的触发点在类库里面大概有五六处吧，
由于e元字符是一个shell下的子进程php调用，所以报错信息不会反馈到当前php进程中，故即便你配置了错误打印到屏幕，页面也不会显示报错信息，必须查看php报错日志
查看php报错日志，把提示的preg_replace中元字符e的调用替换为preg_replace_callback形式的调用
##3部分版本phpexcel类库有单元格样式判断错误
lib/PHPExcel/Classes/PHPExcel/Writer/HTML.php:1236处加个if判断
if(!$this->_useInlineCss){
$cssClass.='style'.$pSheet->getCell($endCellCoord)->getXfIndex();
