HTML报表转XLS报表

用phpexcel将table 报表转换成excel形式(依赖我的php-library库)

 
 *
 * 用PHP读取数据库并生成xls报表太复麻烦了（尤其是含有联表查询、单元格合并）
 * 相当于再做一个跟web页面显示一致同样复杂的统计，于是诞生了本页面，
 * 本页面的核心思想是，先用html表格将数据渲染成报表，在点击导出时直接
 * 用js将表格的头部、表格体的所有单元格数据（包括其colspan、rowspan属性）异步POST传递给本页面
 * 保存数据后，再跳转至本页面
 * -- Javascript code like this:
 * function export_func(){ //点击导出按钮的事件处理函数（处理的表格ID:data-table）
 * 	 	var col_set= new Array();
 * 	 	var data_set= new Array();
 *  	$("#data-table thead tr").each(function(){
 *  	 	var col_row = new Array();
 *  	 	$("th", this).each(function(){
 *  	  	col_row.push([$(this).html(), $(this).attr('colspan'), $(this).attr('rowspan')]);
 *  	 	})
 *  		col_set.push(col_row);
 * 	 	});
 * 	 $("#data-table tbody tr").each(function(){
 *		 var data_row = new Array();
 *		 $("td",this).each(function(){
 *			 data_row.push([$(this).html(), $(this).attr('colspan'), $(this).attr('rowspan')]);
 *		 });
 *		 data_set.push(data_row);
 *	 });
 *	 $.post("form-convert-xls.php",{col:$.toJSON(col_set),data:$.toJSON(data_set)},function(data){
 *		 if(data=='ok'){
 *			 window.location="form-convert-xls.php";
 *		 }
 *	 })
 * }
 * -- Html code like this:
 * 
 
  
 * 	
  
    * 
   
     * 
    列标题 * 
    列标题 * 
    列标题 * 
    * 
  
 * 	
  
    * 
   
     * 
    单元格内容 * 
    单元格内容 * 
    单元格内容 * 
    * 
   
     * 
    单元格内容 * 
    单元格内容 * 
    单元格内容 * 
    * 
  
 * 
 
 */

include '../../library/initialize.php';
$loader = Master::singleton();
$pdo = $loader['PDOMySQLDriver'];
$func = $loader['Utility'];
$store = $loader['Session'];
$loginfo = $store->get('loginfo', array());
if (empty($loginfo)) {
	$func->redirect(PROJECT_URL.'backend/login.php');
}
if($_SERVER['REQUEST_METHOD']=='POST'){
	$store->set('xls',array('cols'=>$_POST['cols'], 'data'=>$_POST['data']), 'download');
	exit('ok');
}
$xls_data=$store->get('xls', null, 'download');
if($xls_data==null){
	$func->jsAlert('数据导出失败！', -1);
}
// $loader->contain('PHPExcel.php',VENDOR_PATH.'phpoffice'.DS.'phpexcel'.DS.'Classes');
$phpexcel = new PHPExcel();
$phpexcel->setActiveSheetIndex(0);
$activeSheet = $phpexcel->getActiveSheet();
$cols=json_decode($xls_data['cols']);
$data=json_decode($xls_data['data']);

$row_index = 1;
// 
 

  
$rowspan_set=array();
foreach ($cols as $row) {
	$col_index = PHPExcel_Cell::columnIndexFromString('A');
	foreach($row as $cell_val){
		//此列之前有行合并，需要跳过
		while($rowspan_set[$col_index]!=0){
			--$rowspan_set[$col_index];
			++$col_index; //跳过该列
		}
		$col_letter = PHPExcel_Cell::stringFromColumnIndex($col_index-1);
		$activeSheet->setCellValue($col_letter . $row_index, strip_tags($cell_val[0]));
		//此列需要行合并
		if($cell_val[2]!=null){
			$rowspan_set[$col_index] = $rowspan_set[$col_index] + $cell_val[2]-1;
			$activeSheet->mergeCells($col_letter . $row_index . ':' . $col_letter . ($row_index+$cell_val[2]-1));
		}
		//此行需要列合并
		if($cell_val[1]!=null){
			$col_index += ($cell_val[1]-1); //修改列索引下次循环跳过合并
			$col_letter_next = PHPExcel_Cell::stringFromColumnIndex($col_index-1);
			$activeSheet->mergeCells($col_letter . $row_index . ':' . $col_letter_next . $row_index);
		}
		$col_index++;
	}
	while($rowspan_set[$col_index]!=0){
		--$rowspan_set[$col_index];
		++$col_index; //跳过该列
	}
	++$row_index;
}
// 
 

// 
 

  
$rowspan_set=array();
foreach ($data as $row) {

	$col_index = PHPExcel_Cell::columnIndexFromString('A');
	foreach($row as $cell_val){
		//此列之前有行合并，需要跳过
		while($rowspan_set[$col_index]!=0){
			--$rowspan_set[$col_index];
			++$col_index; //跳过该列
		}
		$col_letter = PHPExcel_Cell::stringFromColumnIndex($col_index-1);
		$activeSheet->setCellValue($col_letter . $row_index, strip_tags($cell_val[0]));
		//此列需要行合并
		if($cell_val[2]!=null){
			$rowspan_set[$col_index] = $rowspan_set[$col_index] + $cell_val[2]-1;
			$activeSheet->mergeCells($col_letter . $row_index . ':' . $col_letter . ($row_index+$cell_val[2]-1));
		}
		//此行需要列合并
		if($cell_val[1]!=null){
			$col_index += ($cell_val[1]-1); //修改列索引下次循环跳过合并
			$col_letter_next = PHPExcel_Cell::stringFromColumnIndex($col_index-1);
			$activeSheet->mergeCells($col_letter . $row_index . ':' . $col_letter_next . $row_index);
		}
		$col_index++;
	}
	while($rowspan_set[$col_index]!=0){
		--$rowspan_set[$col_index];
		++$col_index; //跳过该列
	}
	
	++$row_index;
}
$col_letter = PHPExcel_Cell::stringFromColumnIndex($col_index-2);
//此处注意绘制边框的边界范围
$activeSheet->getStyle('A1:'.$col_letter . ($row_index-1))->applyFromArray( array(
	'borders' => array(
		'allborders' => array(
			//'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的
			'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框
			//'color' => array('argb' => 'FFFF0000'),
		),
	),
));
// 
 
$objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-Disposition: attachment;filename=data_{$dest}_export.xls");
header("Content-Transfer-Encoding: binary");
$objWriter->save("php://output");