phpexcel 笔记

官网：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);
	}else
		return $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) ."
";
	$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:workspacefengyunreportdompdfincludestyle.cls.php on line 1423

解决：命令行中使用  php -d memory_limit=256M /path/to/your/php-file  或修改php.ini中的memory_limit 参数