PHPExcel导出数据时字段超过26列出错Invalid cell coordinate [1

最新推荐文章于 2024-07-17 10:36:37 发布

扬子

最新推荐文章于 2024-07-17 10:36:37 发布

阅读量7.9k

点赞数

分类专栏： php 文章标签： php PHPExcel 导出

本文链接：https://blog.csdn.net/dl425134845/article/details/46650961

版权

php 专栏收录该内容

59 篇文章 0 订阅

订阅专栏

以下是解决方案函数

/**
*	方法名:	getExcel
*	作用	:将数据导出excel表格
*	@date	2015/03/26
*	@author	dingling
*	@param1	文件名
*	@param2	字段名
*	@param3	数据
*	@return excel文件
*/	
function getExcel($fileName,$headArr,$data){
	//对数据进行检验
	if(empty($data) || !is_array($data)){
		die("数据必须为数组");
	}
	//检查文件名
	if(empty($fileName)){
		exit;
	}
	//组装文件名
	$date = date("Y_m_d",time());
	$fileName .= "_{$date}.xls";

	error_reporting(E_ALL);
	ini_set('display_errors', TRUE);
	ini_set('display_startup_errors', TRUE);
	date_default_timezone_set('PRC');

	if (PHP_SAPI == 'cli')
		die('只能通过浏览器运行');
	
	//导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
	import("Org.Util.PHPExcel");
	import("Org.Util.PHPExcel.Writer.Excel5");
	import("Org.Util.PHPExcel.IOFactory.php");
	
	//创建PHPExcel对象，注意，不能少了\
	$objPHPExcel = new \PHPExcel();
	$objProps = $objPHPExcel->getProperties();
	
	//设置表头
	<span style="color:#FF0000;">$key = ord("A");//A--65
	$key2 = ord("@");//@--64</span>
	foreach($headArr as $v){
		<span style="color:#FF0000;">if($key>ord("Z")){
			$key2 += 1;
			$key = ord("A");
			$colum = chr($key2).chr($key);//超过26个字母时才会启用  dingling 20150626
		}else{
			if($key2>=ord("A")){
				$colum = chr($key2).chr($key);
			}else{
				$colum = chr($key);
			}
		}</span>
		$objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum.'1', L($v['COMMENT']));
		$key += 1;
	}
	
	$column = 2;
	$objActSheet = $objPHPExcel->getActiveSheet();

	foreach($data as $key => $rows){ //行写入
		<span style="color:#FF0000;">$span = ord("A");
		$span2 = ord("@");</span>
		foreach($headArr as $k=>$v){
			<span style="color:#FF0000;">if($span>ord("Z")){
				$span2 += 1;
				$span = ord("A");
				$j = chr($span2).chr($span);//超过26个字母时才会启用  dingling 20150626
			}else{
				if($span2>=ord("A")){
					$j = chr($span2).chr($span);
				}else{
					$j = chr($span);
				}
			}</span>
			//$j = chr($span);
			$objActSheet->setCellValue($j.$column, strip_tags($rows[$v['FIELD']]));
			$span++;
		}
		$column++;
	}

	$fileName = iconv("utf-8", "gb2312", $fileName);
	
	// $objPHPExcel->getActiveSheet()->setTitle('CNLINK');// 重命名表
	$objPHPExcel->setActiveSheetIndex(0);// 设置活动单指数到第一个表,所以Excel打开这是第一个表 
	
	
	// Redirect output to a client’s web browser (Excel5)
	header('Content-Type: application/vnd.ms-excel');
	header("Content-Disposition: attachment;filename=\"$fileName\"");
	header('Cache-Control: max-age=0');
	
	// If you're serving to IE 9, then the following may be needed
	header('Cache-Control: max-age=1');
	// If you're serving to IE over SSL, then the following may be needed
	header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
	header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
	header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
	header ('Pragma: public'); // HTTP/1.0

	$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
	// $objWriter->save('1.xls');
	// echo str_replace('.php', '.xls', __FILE__);
	$objWriter->save('php://output'); //文件通过浏览器下载
	
	exit;
}

红色部分是解决问题的关键