phpexcel 笔记
官网：phpexcel.codeplex.com
下载：https://github.com/PHPOffice/PHPExcel
01.define('EXCEL_TYPE', 'Excel5'); 02.define('EXCEL_EXT', '.xls'); 03.// define('EXCEL_TYPE', 'Excel2007'); 04.// define('EXCEL_EXT', '.xlsx');
01.function getExcel($name){ 02. if(file_exists(DATADIR . $name . '_bak'.EXCEL_EXT)){ 03. return PHPExcel_IOFactory::load(DATADIR . $name . '_bak'.EXCEL_EXT); 04. }else{ 05. return new PHPExcel(); 06. } 07.}
01.function convertUTF8($str){ 02.<span style="white-space: pre;"> </span>if(empty($str)) return ''; 03.<span style="white-space: pre;"> </span>if(mb_detect_encoding($str)!='UTF-8'){ 04.<span style="white-space: pre;"> </span>return iconv(mb_detect_encoding($str), 'utf-8', $str); 05.<span style="white-space: pre;"> </span>}else 06.<span style="white-space: pre;"> </span>return $str; 07.} 08.function write_weibos($weibos,$name = null){ 09. 10. $objPHPExcel = getExcel($name);//<span style="font-family: arial, sans-serif; white-space: nowrap;">new PHPExcel()</span> 11. $objPHPExcel->getProperties()->setCreator("fengyun");//诸多属性 12. 13. $objPHPExcel->setActiveSheetIndex(0); 14. $objPHPExcel->getActiveSheet()->setCellValue('A1', '微博id'); 15. $objPHPExcel->getActiveSheet()->setCellValue('B1', '发布日期'); 16. $objPHPExcel->getActiveSheet()->setCellValue('C1', '微博内容'); 17. $objPHPExcel->getActiveSheet()->setCellValue('D1', '微博链接'); 18. $objPHPExcel->getActiveSheet()->setCellValue('E1', '转发数'); 19. $objPHPExcel->getActiveSheet()->setCellValue('F1', '评论数'); 20. 21. $objPHPExcel->getActiveSheet()->getColumnDimension("A")->setWidth(18); 22. $objPHPExcel->getActiveSheet()->getColumnDimension("B")->setWidth(18); 23. $objPHPExcel->getActiveSheet()->getColumnDimension("C")->setWidth(60); 24. $objPHPExcel->getActiveSheet()->getColumnDimension("D")->setWidth(40); 25. $objPHPExcel->getActiveSheet()->getColumnDimension("E")->setWidth(10); 26. $objPHPExcel->getActiveSheet()->getColumnDimension("F")->setWidth(10); 27. 28. $i = 2; 29. $mids = array(); 30. foreach($weibos as $weibo){ 31. $objPHPExcel->getActiveSheet()->setCellValueExplicit('A' . $i, $weibo['mid'],PHPExcel_Cell_DataType::TYPE_STRING); 32. $objPHPExcel->getActiveSheet()->setCellValue('B' . $i, strftime('%Y-%m-%d %H:%M:%S',strtotime($weibo['created_at']))); 33. $objPHPExcel->getActiveSheet()->setCellValueExplicit('C' . $i, convertUTF8($weibo['text'])); 34. $objPHPExcel->getActiveSheet()->setCellValue('D' . $i, WeiboTool::id2url('sina',$weibo['mid'], $weibo['user']['idstr'])); 35. $objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $weibo['reposts_count']); 36. $objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $weibo['comments_count']); 37. $i++; 38. } 39. $objPHPExcel->getActiveSheet()->getStyle('A1:AE'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); 40. $objPHPExcel->getActiveSheet()->getStyle('C2:D'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT); 41. 42. $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, EXCEL_TYPE); 43. echo 'create weibo report success . count :'.($i-1) ."\n"; 44. $name = $name!=null?$name:time(); 45. $objWriter->save(DATADIR.$name.EXCEL_EXT); 46.}
遇到问题：
1，写入大数字会变成科学计数法表示
解决：使用setCellValueExplicit方法可以强制内容为字符串,
也可以使用设置数字格式，前提是数字不要超出excel的限制
[php] view plaincopy在CODE上查看代码片派生到我的代码片 01.$objPHPExcel->getActiveSheet()->getStyle('C2:D'.$i) 02.$objStyleA5->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
2，emoji表情（iphone）的编码会破坏生成excel的格式
解决：下载https://github.com/iamcal/php-emoji，使用里面emoji_unified_to_softbank($str);可以去掉iphone表情中不识别的编码字符串
3，生成pdf ，phpexcel pdf writer 包装了三个pdf渲染库，tcPDF，mPDF，DomPDF。在1.7.8以前的版本phpexcel集成了tcPDF，但是从1.7.8开始被去掉了，所以必须安装一个pdf渲染器
tcPDF 5.9 http://www.tcpdf.org/ PDF_RENDERER_TCPDF mPDF 5.4 http://www.mpdf1.com/mpdf/ PDF_RENDERER_MPDF domPDF 0.6.0https://github.com/dompdf/dompdf PDF_RENDERER_DOMPDF
然后需要指明使用的pdf渲染器和所在的路径：（使用的dompdf）
[php] view plaincopy在CODE上查看代码片派生到我的代码片 01.function setpdfRender(){ 02. $rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF; 03. $rendererLibrary = 'dompdf'; 04. $rendererLibraryPath = __DIR__.'/' . 05. $rendererLibrary; 06. echo $rendererLibraryPath; 07. if (!PHPExcel_Settings::setPdfRenderer( 08. $rendererName, 09. $rendererLibraryPath 10. )) { 11. die( 12. 'Please set the $rendererName and $rendererLibraryPath values' . 13. PHP_EOL . 14. ' as appropriate for your directory structure' 15. ); 16. } 17.}
现在可以创建pdf了，不过样式需要调整，而且还有乱码待解决……
4，生成pdf内存问题
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate24 bytes) in D:\workspace\fengyunreport\dompdf\include\style.cls.php on line 1423
解决：命令行中使用 php -d memory_limit=256M /path/to/your/php-file 或修改php.ini中的memory_limit 参数
