封装一个 PHPExcel 的导出方法

本篇以 ThinkPHP 为示例

public function getExcel($property,$headArr,$data){

	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$objPHPExcel
		-> getProperties()
		-> setCreator($property['creator'])
		-> setLastModifiedBy($property['last'])
		-> setTitle($property['title'])
		-> setSubject($property['subject'])
		-> setDescription($property['description'])
		-> setKeywords($property['keywords'])
		-> setCategory($property['category']);

	$column = 1;
	$span = ord("A");
	foreach($headArr as $v){
		$j = chr($span);
		$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($j.$column,$v);
		$span ++;
	}

	$column ++;
	$objActSheet = $objPHPExcel->getActiveSheet();
	foreach($data as $key => $rows){
		$span = ord('A');
		foreach($rows as $keyName => $value){
			$j = chr($span);
			$objActSheet->setCellValue($j.$column, $value);
			$span ++;
		}
		$column ++;
	}

	$objPHPExcel->setActiveSheetIndex(0);

	ob_end_clean();
	ob_start();

	header("Content-Type: application/force-download");
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($fileName)).'.xlsx');
	header('Cache-Control: max-age=0');

	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter -> save('php://output');
	exit ;
}

调用

public function _index_export(){
	$property = array('title'=>'测试');
	$headArr = array('好','坏');
	$data = array(
		array(
			3,4
		),
		array(
			5,6
		)
	);
	$this->getExcel($property,$headArr,$data);
}

优化当超过 26 列

public function getExcel($property,$headArr,$data){

	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$objPHPExcel
		-> getProperties()
		-> setCreator($property['creator'])
		-> setLastModifiedBy($property['last'])
		-> setTitle($property['title'])
		-> setSubject($property['subject'])
		-> setDescription($property['description'])
		-> setKeywords($property['keywords'])
		-> setCategory($property['category']);

	$column = 1;
	$span = 0;
	foreach($headArr as $v){
		$letter = \PHPExcel_Cell::stringFromColumnIndex($span);
		$objPHPExcel->setActiveSheetIndex(0) -> setCellValue($letter.$column,$v);
		$span ++;
	}

	$objActSheet = $objPHPExcel->getActiveSheet();
	foreach($data as $key => $rows){
		$column ++;
		$span = 0;
		foreach($rows as $keyName => $value){
			$letter = \PHPExcel_Cell::stringFromColumnIndex($span);
			$objActSheet->setCellValue($letter.$column, $value);
			$span ++;
		}
	}

	$objPHPExcel->setActiveSheetIndex(0);

	ob_end_clean();
	ob_start();

	header("Content-Type: application/force-download");
	header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
	header("Content-Disposition:attachment;filename =" . str_ireplace('+', '%20', URLEncode($property['title'])).'.xlsx');
	header('Cache-Control: max-age=0');

	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
	$objWriter -> save('php://output');
	exit ;
}

可以设置列宽的一种方法

public function exportExcel($expTitle,$expCellName,$expTableData){
	$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
	$fileName = $expTitle.date('_Ymd_His');//or $xlsTitle 文件名称可根据自己情况设定
	$cellNum = count($expCellName);
	$dataNum = count($expTableData);
	Vendor('Excel.PHPExcel');
	$objPHPExcel = new \PHPExcel();
	$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
	// $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
	// $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));//第一行标题
	for($i=0;$i<$cellNum;$i++){
		$objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'1', $expCellName[$i][1]);
		$objPHPExcel->getActiveSheet()->getColumnDimension($cellName[$i])->setWidth($expCellName[$i][2]); //设置宽度
	}
	// Miscellaneous glyphs, UTF-8
	for($i=0;$i<$dataNum;$i++){
		for($j=0;$j<$cellNum;$j++){
			$objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
		}
	}
	header('pragma:public');
	header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
	header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	$objWriter->save('php://output');
	exit;
}

调用

public function dumpLogsExcel(){
	//查询数据
	$where['member_card'] = 123456;
	$xlsData = array();//二维数组
	//构建excel
	$xlsName  = "用户评价信息表";
	$xlsCell  = array(
		array('GongSiMc','门店名称',20),
		array('order_num','保养单号',20),
		array('wx_name','微信名',20),
		array('member_card','会员卡号',15),
		array('is_good_name','是否满意',10),
		array('evaluate_item','具体评价',40),
		array('score','得分',10),
		array('create_time','创建时间',20),
	);
	$this->exportExcel($xlsName,$xlsCell,$xlsData);
}