PHPExcel笔记, mpdf导出

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();