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 . \'\"\');
} else if (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(
    \'Please set the $rendererName and $rendererLibraryPath values\' .
    PHP_EOL .
    \' as appropriate for your directory structure\'
  );
}
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 . \'\"\');
} else if (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导出失败的一些错误解决方法
##############################
 
##1 pdf中文乱码问题
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();