phpexcel 笔记
来源:互联网 发布:卫星电视直播软件下载 编辑:程序博客网 时间:2024/06/11 13:46
官网:phpexcel.codeplex.com
下载:https://github.com/PHPOffice/PHPExcel
define('EXCEL_TYPE', 'Excel5');define('EXCEL_EXT', '.xls');// define('EXCEL_TYPE', 'Excel2007');// define('EXCEL_EXT', '.xlsx');
function getExcel($name){if(file_exists(DATADIR . $name . '_bak'.EXCEL_EXT)){return PHPExcel_IOFactory::load(DATADIR . $name . '_bak'.EXCEL_EXT);}else{return new PHPExcel();}}
function convertUTF8($str){if(empty($str)) return '';if(mb_detect_encoding($str)!='UTF-8'){return iconv(mb_detect_encoding($str), 'utf-8', $str);}elsereturn $str;}function write_weibos($weibos,$name = null){$objPHPExcel = getExcel($name);//new PHPExcel()$objPHPExcel->getProperties()->setCreator("fengyun");//诸多属性$objPHPExcel->setActiveSheetIndex(0);$objPHPExcel->getActiveSheet()->setCellValue('A1', '微博id');$objPHPExcel->getActiveSheet()->setCellValue('B1', '发布日期');$objPHPExcel->getActiveSheet()->setCellValue('C1', '微博内容');$objPHPExcel->getActiveSheet()->setCellValue('D1', '微博链接');$objPHPExcel->getActiveSheet()->setCellValue('E1', '转发数');$objPHPExcel->getActiveSheet()->setCellValue('F1', '评论数');$objPHPExcel->getActiveSheet()->getColumnDimension("A")->setWidth(18);$objPHPExcel->getActiveSheet()->getColumnDimension("B")->setWidth(18);$objPHPExcel->getActiveSheet()->getColumnDimension("C")->setWidth(60);$objPHPExcel->getActiveSheet()->getColumnDimension("D")->setWidth(40);$objPHPExcel->getActiveSheet()->getColumnDimension("E")->setWidth(10);$objPHPExcel->getActiveSheet()->getColumnDimension("F")->setWidth(10);$i = 2;$mids = array();foreach($weibos as $weibo){$objPHPExcel->getActiveSheet()->setCellValueExplicit('A' . $i, $weibo['mid'],PHPExcel_Cell_DataType::TYPE_STRING);$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, strftime('%Y-%m-%d %H:%M:%S',strtotime($weibo['created_at'])));$objPHPExcel->getActiveSheet()->setCellValueExplicit('C' . $i, convertUTF8($weibo['text']));$objPHPExcel->getActiveSheet()->setCellValue('D' . $i, WeiboTool::id2url('sina',$weibo['mid'], $weibo['user']['idstr']));$objPHPExcel->getActiveSheet()->setCellValue('E' . $i, $weibo['reposts_count']);$objPHPExcel->getActiveSheet()->setCellValue('F' . $i, $weibo['comments_count']);$i++;}$objPHPExcel->getActiveSheet()->getStyle('A1:AE'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);$objPHPExcel->getActiveSheet()->getStyle('C2:D'.$i)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, EXCEL_TYPE);echo 'create weibo report success . count :'.($i-1) ."\n";$name = $name!=null?$name:time();$objWriter->save(DATADIR.$name.EXCEL_EXT);}
遇到问题:1,写入大数字会变成科学计数法表示
解决:使用setCellValueExplicit方法可以强制内容为字符串,
也可以使用设置数字格式,前提是数字不要超出excel的限制
$objPHPExcel->getActiveSheet()->getStyle('C2:D'.$i)$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)
function setpdfRender(){$rendererName = PHPExcel_Settings::PDF_RENDERER_DOMPDF;$rendererLibrary = 'dompdf';$rendererLibraryPath = __DIR__.'/' .$rendererLibrary;echo $rendererLibraryPath;if (!PHPExcel_Settings::setPdfRenderer($rendererName,$rendererLibraryPath)) {die('Please set the $rendererName and $rendererLibraryPath values' .PHP_EOL .' as appropriate for your directory structure');}}现在可以创建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 参数
- phpexcel 笔记
- phpExcel 笔记
- PHPExcel学习笔记
- phpexcel 应用笔记
- PHPExcel 学习笔记
- PHPExcel 学习笔记
- PHPExcel学习笔记
- PHPExcel使用笔记
- PHPExcel 学习笔记
- phpexcel
- phpexcel
- phpexcel
- PHPExcel
- PHPExcel
- PHPexcel
- PHPEXCEL
- phpexcel
- phpexcel
- 源代码加密、源代码防泄密,的软件分析
- 源生态JS设置COOKIE函数及用法
- CHD4B1(hadoop-0.23)实现NameNode HA安装配置
- Linux输入子系统分析(一)
- 使用vs2008创建.net2.0时网站时,使用Ajax控件会提示:Microsoft JScript 运行时错误: 'Sys' 未定义解决方案
- phpexcel 笔记
- mtd-utils工具的编译,
- 判断素数代码
- jquery 跨域发送请求 java做服务端
- [转]TCP的TIME_WAIT状态
- 基于Chrome开源提取的界面开发框架开篇
- Python 图形界面开发环境搭建(Python2.7 + pyQT4 + Eric4)
- Sharepoint学习笔记—Site Definition系列--7、如何在Site Definition中引入Master Page (1、Master Page的引入)
- linux下以系统当前时间前一天命名文件并打包压缩