王新阳

wangxinyang

生成CSV格式excel基本规则

以下为CodeIgniter中导出csv的完整示例

知识点：输出缓冲控制 http://php.net/manual/zh/book.outcontrol.php

//导出数据
public function export(){
	/*
	 * 不使用PHPExcel生成excel文件，因为太占内存
	 * 生成csv基本规则
	 * 1、列内容用逗号分隔
	 * 2、\r\n换行
	 * 3、如果当前字段内容中有回车、换行、逗号等任意内容时，字段两头要加双引号
	 * 4、如果当前字段内容中有双引号，但不包含回车、换行、逗号等任意内容时，直接输出不用处理；否则字段中的双引号要进行转义（两个双引号代替1个），且字段两头加又引号
	 * 5、超过11位的整数要写成 ="123456789012" 形式，防止因excel自动采用科学计数法而导致内容失真（如身份证）
	 */
	
	//获取表字段并删除无用项
	$arr_table_fields = array_diff_key($this->tableinfo(), array('id'=>0, 'sn'=>0, 'updatetime'=>0, 'createtime'=>0));
	//重置要查询的字段
	$select_fields = implode(',', array_keys($arr_table_fields));
	
	//清空缓冲区
	if (ob_get_level() !== 0 && @ob_end_clean() === FALSE)@ob_clean();
	//输出headers
	header('Content-Type: application/octet-stream');
	header('Content-Disposition: attachment; filename="观众数据-'. date('YmdHis') .'.csv"');
	header('Expires: 0');
	header('Content-Transfer-Encoding: binary');
	//header('Content-Length: '.$filesize);
	header('Cache-Control: private, no-transform, no-store, must-revalidate');
	
	$str = '';
	//输出表头
	foreach($arr_table_fields as $row){
		$str .= $row['comment'].',';
	}
	$str .= "\n";
	echo utf82gbk($str);
	
	//获取记录总数
	$num = $this->db->count_all('my_guanzhong');
	if(! $num)return;
	//每次获取1000条
	$pagesize = 1000;
	//循环获取数据并输入
	//如果数据记录很多而直接全部读取到内存会导致内存溢出
	ob_start();
	for($i=0; $idb->select($select_fields)->order_by('id DESC')->limit($pagesize, $i*$pagesize)->get('my_guanzhong');
		while($row=$res->unbuffered_row()){
			foreach($row as $item){
				if(is_numeric($item)){ //数值型
					if(preg_match("/^-?\d{1,11}$/", (string)$item)){ //不超过11位的整数
						echo $item, ',';
					}else{
						echo '="', $item, '",';
					}
				}else{
					if(strpos($item, ',') !== FALSE || strpos($item, "\r") !== FALSE || strpos($item, "\n") !== FALSE){ //逗号、回车、换行
						if(strpos($item, '"') !== FALSE){ //有双引号
							echo '"', str_replace('"', '""', $item), '",';
						}else{
							echo '"', $item, '",';
						}
					}else{
						echo $item, ',';
					}
				}
			}
			echo "\n";
		}
		//echo format_bytes(memory_get_peak_usage()), ', ', microtime(1)-NOW_TIME, "\n";
		//获取缓冲区内容并转为gbk编码
		$str = utf82gbk(ob_get_contents());
		//清空缓冲区
		ob_clean();
		//重新输出转码后的内容
		echo $str;
		//发送至浏览器
		ob_flush();
	}
	flush();
}

回应

PHPExcel使用过滤器按块读取excel

PHPExcel读取EXCEL非常消耗内存（尤其是上万条数据，很容易出现内存不足），可以使用分批、异步读取，思路：
1、获取表中记录总行数
2、每次读取N条写入数据库，并返回最后一条的行号
3、根据上次返回的行号使用AJAX等方式循环第2步
相应代码可从下方示例中分离

//加载PHPExcel类库
require_once FCPATH.'app/manage/libraries/PHPExcel/IOFactory.php';
//按块读取EXCEL,减少内存占用
class chunkReadFilter implements PHPExcel_Reader_IReadFilter{
	private $_startRow = 0;
	private $_endRow = 0;
	//设置要读取的块开始行、结束行
	public function setRows($startRow, $chunkSize) {
		$this->_startRow	= $startRow;
		$this->_endRow		= $startRow + $chunkSize;
	}
	//确定当前行是否读取
	public function readCell($column, $row, $worksheetName = '') {
		//读取首行（标题行）和块区间内的行数据
		//return (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) ? TRUE : FALSE;
		//只读取块区间内的行数据
		return ($row >= $this->_startRow && $row < $this->_endRow) ? TRUE : FALSE;
	}
}

//执行导入
function import_do($arr_fields){
	$excelfields = P('fields'); //要导入的字段和顺序
	$excelfile = P('excelfile'); //要导入的excel文件
	$skiplines = P('skiplines'); //跳过行数
	$deletefile = P('deletefile'); //导入成功后删除excel文件
		
	if(!$excelfields){
		return $this->_display('请添加要导入的字段');
	}else{
		$excelfields = explode('|', $excelfields);
	}
	if(! $excelfile)return $this->_display('请上传要导入的文件');
	is_lng($skiplines) OR $skiplines=0;
	$deletefile = $deletefile=='1' ? TRUE : FALSE;
		
	$time=$this->input->server('REQUEST_TIME');
		
	//设置超时时间为99秒
	set_time_limit(99);
		
	$excelfile = FCPATH.substr($excelfile, 1);
	$inputFileType = PHPExcel_IOFactory::identify($excelfile);
	$objReader = PHPExcel_IOFactory::createReader($inputFileType);
	$objReader->setReadDataOnly(true);
		
	$chunkSize = 500; //每次读取记录数
	$chunkFilter = new chunkReadFilter(); //实例化按块读取过滤器
	$objReader->setReadFilter($chunkFilter); //使用过滤器
	$chunkFilter->setRows(1,1); //从第一条开始读取1条，据此获取activeShieetIndex
	$objPHPExcel = $objReader->load($excelfile);
	$activeSheetIndex = $objPHPExcel->getActiveSheetIndex();
		
	$worksheetData = $objReader->listWorksheetInfo($excelfile); // 列出工作表列表
	$activeSheetInfo = $worksheetData[$activeSheetIndex];
/*
 * $activeSheetInfo
Array(
    [worksheetName] => 信息
    [lastColumnLetter] => AQ
    [lastColumnIndex] => 42
    [totalRows] => 21
    [totalColumns] => 43
)
*/
	for ($startRow = $skiplines+1+5000; $startRow <= $activeSheetInfo['totalRows']; $startRow += $chunkSize) {
		echo 'Loading WorkSheet using configurable filter for headings row 1 and for rows ',$startRow,' to ',($startRow+$chunkSize-1),'<br />';
		//设置本次循环要读取的记录
		$chunkFilter->setRows($startRow,$chunkSize);
		//根据过滤器读取数据
		$objPHPExcel = $objReader->load($excelfile);
		$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
		$sheetData = array_slice($sheetData, $startRow-1);
		//echo '<pre>';
		//print_r($sheetData);
	}
	echo memory_get_peak_usage(), ', ', memory_get_usage(), ', ', (microtime(true)-$time);
	exit;
}

回应

PHPExcel官方示例

PHPExcel已停止开发，需要使用新类库PhpSpreadsheet
https://github.com/PHPOffice/PhpSpreadsheet



转自： http://blog.csdn.net/beyond__devil/article/details/53457849

1.使用 PHPExcel_IOFactory 读取文件
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
2.使用一个特定的读取类，读取文件
$objReader = new PHPExcel_Reader_Excel5();
objPHPExcel = $objReader->load($inputFileName);
3.使用 PHPExcel_IOFactory 创建一个特定的读取类
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
读取类型有：
$inputFileType = 'Excel5';
$inputFileType = 'Excel2007';
$inputFileType = 'Excel2003XML';
$inputFileType = 'OOCalc';
$inputFileType = 'SYLK';
$inputFileType = 'Gnumeric';
$inputFileType = 'CSV';

4.使用 PHPExcel_IOFactory 来鉴别文件应该使用哪一个读取类
$inputFileType = PHPExcel_IOFactory::identify($inputFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
5.只读取数据，忽略里面各种格式等(对于Excel读取，有很大优化)
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($inputFileName);
6.加载Excel所有的工作表
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadAllSheets(); // 加载所有的工作表
$objPHPExcel = $objReader->load($inputFileName);
$objPHPExcel->getSheetCount(); // 获取工作表的个数
$objPHPExcel->getSheetNames(); // 获取所有工作表的名字数组
7.加载单个的命名的工作表
$sheetname = 'Data Sheet #2'; // 单个工作表，传入字符串
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetname); // 加载单个工作表，传入工作表名字(例如：'Data Sheet #2')
$objPHPExcel = $objReader->load($inputFileName);
8.加载多个命名的工作表
$sheetnames = array('Data Sheet #1', 'Data Sheet #2'); // 多个工作表，传入数组
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setLoadSheetsOnly($sheetnames); // 加载多个工作表，传入工作表名字数组
$objPHPExcel = $objReader->load($inputFileName);
9.自定义一个读取过滤器
class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
public function readCell($column, $row, $worksheetName = '') {
// 只读取1-7行&A－E列中的单元格
if ($row >= 1 && $row <= 7) {
if (in_array($column,range('A','E'))) {
return true;
}
}
return false;
}
}
$filterSubset = new MyReadFilter();
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadFilter($filterSubset); // 设置实例化的过滤器对象
$objPHPExcel = $objReader->load($inputFileName);
10.同样是自定义一个读取过滤器，但可配置读取的行和列范围
class MyReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0; // 开始行
private $_endRow = 0; // 结束行
private $_columns = array(); // 列跨度
public function __construct($startRow, $endRow, $columns) {
$this->_startRow = $startRow;
$this->_endRow = $endRow;
$this->_columns = $columns;
}
public function readCell($column, $row, $worksheetName = '') {
if ($row >= $this->_startRow && $row <= $this->_endRow) {
if (in_array($column,$this->_columns)) {
return true;
}
}
return false;
}
}
$filterSubset = new MyReadFilter(9,15,range('G','K'));
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadFilter($filterSubset); // 设置实例化的过滤器对象
$objPHPExcel = $objReader->load($inputFileName);
11.分块读取Excel，原理还是：自定义读取过滤器
class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0; // 开始行
private $_endRow = 0; // 结束行
public function __construct($startRow, $chunkSize) { // 我们需要传递：开始行号&行跨度(来计算结束行号)
$this->_startRow = $startRow;
$this->_endRow = $startRow + $chunkSize;
}
public function readCell($column, $row, $worksheetName = '') {
if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
return true;
}
return false;
}
}
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$chunkSize = 20; // 定义每块读取的行数
// 就可在一个循环中，多次读取块，而不用一次性将整个Excel表读入到内存中
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
$chunkFilter = new chunkReadFilter($startRow, $chunkSize);
$objReader->setReadFilter($chunkFilter); // 设置实例化的过滤器对象
$objPHPExcel = $objReader->load($inputFileName);
// 开始读取每行数据，并插入到数据库
}
12.分块读取Excel的第2个版本
class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0; // 开始行
private $_endRow = 0; // 结束行
// 定义了一个读取指定范围行的方法
public function setRows($startRow, $chunkSize) {
$this->_startRow = $startRow;
$this->_endRow = $startRow + $chunkSize;
}
public function readCell($column, $row, $worksheetName = '') {
if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
return true;
}
return false;
}
}
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$chunkSize = 20; // 定义每块读取的行数
// 在循环外部，实例化过滤器类，而不用循环内每次实例化(应该更优化)
$chunkFilter = new chunkReadFilter();
$objReader->setReadFilter($chunkFilter);
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
// 循环内部，使用实例化的对象的方法，来调整读取的行范围即可
$chunkFilter->setRows($startRow,$chunkSize);
$objPHPExcel = $objReader->load($inputFileName);
}
13.读取多个CSV文件
$inputFileNames = array('./sampleData/example1.csv','./sampleData/example2.csv'); // CSV文件数组
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
/*
说明下面是干啥的：
1.先载入第一个CSV作为第一个工作表 | 设置工作表的标题
2.依次将多个CSV再入到 objPHPExcel 对象中，依次追加到第N个工作表 | 设置工作表的标题
3.获取Excel此时所有的标题，通过标题来依次获取工作表，然后对工作表进行操作！
*/
$inputFileName = array_shift($inputFileNames); // 第一个CSV文件
$objPHPExcel = $objReader->load($inputFileName); // 读取第一个CSV文件
$objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME)); // 设置标题
foreach($inputFileNames as $sheet => $inputFileName) {
$objReader->setSheetIndex($sheet+1); // 将工作表切换到下个工作表
$objReader->loadIntoExisting($inputFileName,$objPHPExcel); // 将下一个CSV文件，载入到已存在的PHPExcel对象中
$objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME)); // 设置当前工作表的标题
}
// 循环所有的工作表名称
$loadedSheetNames = $objPHPExcel->getSheetNames();
foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
$objPHPExcel->setActiveSheetIndexByName($loadedSheetName); // 通过 '工作表名称' 来设置当前工作表为激活状态
// 接着对当前激活的工作表，进行读取、数据库写入
}
14.将一个大的CSV文件，按 '块' 分成多个工作表(结合了12&13的示例)
class chunkReadFilter implements PHPExcel_Reader_IReadFilter
{
private $_startRow = 0; // 开始行
private $_endRow = 0; // 结束行
// 定义了一个读取指定范围行的方法
public function setRows($startRow, $chunkSize) {
$this->_startRow = $startRow;
$this->_endRow = $startRow + $chunkSize;
}
public function readCell($column, $row, $worksheetName = '') {
if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) {
return true;
}
return false;
}
}
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$chunkSize = 100; // 定义每块读取的行数
// 在循环外部，实例化过滤器类，而不用循环内每次实例化(应该更优化)
$chunkFilter = new chunkReadFilter();
$objReader->setReadFilter($chunkFilter)
  ->setContiguous(true); // 这里出现了一个没见过的方法(先放着，忘记是干啥的了)
$objPHPExcel = new PHPExcel();
$sheet = 0; // 第一个工作表下标
for ($startRow = 2; $startRow <= 240; $startRow += $chunkSize) {
$chunkFilter->setRows($startRow,$chunkSize);
    $objReader->setSheetIndex($sheet); // 切换工作表
    $objReader->loadIntoExisting($inputFileName,$objPHPExcel); // 将读取到的CSV块，载入到工作表
    $objPHPExcel->getActiveSheet()->setTitle('Country Data #'.(++$sheet)); // 设置工作表标题
}
// 循环所有的工作表名称
$loadedSheetNames = $objPHPExcel->getSheetNames();
foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
$objPHPExcel->setActiveSheetIndexByName($loadedSheetName); // 通过 '工作表名称' 来设置当前工作表为激活状态
// 接着对当前激活的工作表，进行读取、数据库写入
}

15.使用 'Advanced Value Binder' 读取通过 'tab' 分隔值的文件
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); // 设置单元格
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setDelimiter("\t"); // 设置分隔符为 '\t'(tab分隔)
$objPHPExcel = $objReader->load($inputFileName);
$objPHPExcel->getActiveSheet()->setTitle(pathinfo($inputFileName,PATHINFO_BASENAME)); // 设置标题
$loadedSheetNames = $objPHPExcel->getSheetNames(); // 获取所有工作表名称

$objPHPExcel->getActiveSheet()->toArray($nullValue, $calculateFormulas, $formatData, $returnCellRef);
mixed $nullValue
单元格不存在时的返回值
boolean $calculateFormulas
是否对单元格进行计算，如 =1+2 True返回3
boolean $formatData
是否格式化值，尤其是时间列，false返回excel时间，true返回如 3/18/17 8:00
false时可以用 PHPExcel_Shared_Date::ExcelToPHP 把返回的excel时间转为php时间戳
boolean $returnCellRef
TRUE返回数组格式array(1=>array('A'=>'', 'B'=>''))
FALSE返回数组格式array(0=>array(0=>'', 1=>''))

1)格式化输出
foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
$objPHPExcel->setActiveSheetIndexByName($loadedSheetName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true); // 注意4个参数的区别
}
2)未格式化输出
foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
$objPHPExcel->setActiveSheetIndexByName($loadedSheetName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,false,true); // 注意4个参数的区别
}
3)单元格原生值
foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
$objPHPExcel->setActiveSheetIndexByName($loadedSheetName);
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,false,false,true); // 注意4个参数的区别
}
16.使用 'try/catch' 控制Excel加载时的异常
try {
$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
} catch(PHPExcel_Reader_Exception $e) {
die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
17.获取Excel的工作表名称列表
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$worksheetNames = $objReader->listWorksheetNames($inputFileName); // 列出工作表名称
foreach($worksheetNames as $worksheetName) {
echo $worksheetName,'<br />';
}
18.不加载整个文件，或者Excel的工作表名称列表
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$worksheetData = $objReader->listWorksheetInfo($inputFileName); // 列出工作表列表
foreach ($worksheetData as $worksheet) {
echo '<li>', $worksheet['worksheetName'], '<br />';
echo 'Rows: ', $worksheet['totalRows'], ' Columns: ', $worksheet['totalColumns'], '<br />';
echo 'Cell Range: A1:', $worksheet['lastColumnLetter'], $worksheet['totalRows'];
echo '</li>';
}
19.全程，有一个方法：
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,false,false,true);
getActiveSheet() - 获取当前激活的工作表
toArray() - 将当前激活的工作表，解析全部放入数组中

回应

PHPExcel笔记（CI框架中使用）

参考：
http://www.jquerycn.cn/a_12224
http://www.jquerycn.cn/a_12222

一、因PHPExcel命名规则不同于CodeIgniter（类名必须与文件名相同），为减少对PHPExcel源代码的修改，所以不使用CI的加载类$this->load->library()，而直接使用 require_once
require_once FCPATH.'libraries/PHPExcel.php';
$phpexcel = new PHPExcel;

必须修改的地方： PHPExcel/IOFactory.php  的构造函数 private function __construct() { } 
private 改为 public

二、设置列格式
1、设置A1单元格为文本格式，设置整列为 getStyle('A')
需要 PHPExcel/Style/NumberFormat.php
$this->phpexcel->getActiveSheet()->getStyle('A1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_TEXT);
注意：此方法对长数字字符串还是以文本方式来显示科学计数法的结果，原因可能php在处理大数字时采用的科学计数法。

2、需要 PHPExcel/Cell/DataType.php
$objPHPExcel->getActiveSheet()->setCellValueExplicit('D1',123456789033,PHPExcel_Cell_DataType::TYPE_STRING);

3、在数字字符串前加一个空格使之成为字符串
$objPHPExcel->getActiveSheet()->setCellValue('D1', ' ' . 123456789033);

推荐第2种方法

回应

1
王新阳
目录浏览
CSS  ASP  PS  PHPExcel  电影  视频  PHOTO  Apple  奇  WINDOWS  书签  SQL  nginx  bootstrap  CI  JS  video  HTML    IT  PHP  正则  HTML5  .htaccess  未分类  IIS  JQUERY  ueditor  layui  jQueryMobile  微信  web.config  微博  SEO  linux
2024-04-29 星期一 农历三月二十一