PHPExcel用完了256,512和1024MB的RAM
PHPExcel用完了256,512和1024MB的RAM
我不明白。XSLX表大约3MB，甚至1024MB的RAM还不足以让PHPExcel将其加载到内存中？
我可能在这里做了一些可怕的错误：
function ReadXlsxTableIntoArray($theFilePath){
require_once('PHPExcel/Classes/PHPExcel.php');
$inputFileType = 'Excel2007';
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load($theFilePath);
$rowIterator = $objPHPExcel->getActiveSheet()->getRowIterator();
$arrayData = $arrayOriginalColumnNames = $arrayColumnNames = array();
foreach($rowIterator as $row){
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
if(1 == $row->getRowIndex ()) {
foreach ($cellIterator as $cell) {
$value = $cell->getCalculatedValue();
$arrayOriginalColumnNames[] = $value;
// let's remove the diacritique
$value = iconv('UTF-8', 'ISO-8859-1//TRANSLIT', $value);
// and white spaces
$valueExploded = explode(' ', $value);
$value = '';
// capitalize the first letter of each word
foreach ($valueExploded as $word) {
$value .= ucfirst($word);
}
$arrayColumnNames[] = $value;
}
continue;
} else {
$rowIndex = $row->getRowIndex();
reset($arrayColumnNames);
foreach ($cellIterator as $cell) {
$arrayData[$rowIndex][current($arrayColumnNames)] = $cell->getCalculatedValue();
next($arrayColumnNames);
}
}
}
return array($arrayOriginalColumnNames, $arrayColumnNames, $arrayData);}上面的函数将数据从excel表读取到数组。
有什么建议？
起初，我允许PHP使用256MB的RAM。这还不够。然后我把数量增加了一倍，然后尝试了1024MB。它仍然因内存不足而出现错误
汪汪一只猫浏览 486回答 3
3回答
-
幕布斯6054654
PHPExcel包含电子表格的“内存”表示，并且易受PHP内存限制。文件的物理大小在很大程度上是无关紧要的...知道它包含多少个单元格（每个工作表上的行*列）更为重要。我一直使用的“经验法则”平均约为1k / cell，因此5M单元工作簿需要5GB内存。但是，有许多方法可以减少该要求。这些可以组合在一起，具体取决于您需要在工作簿中访问哪些信息，以及您要对其执行的操作。如果您有多个工作表，但不需要加载所有工作表，则可以使用setLoadSheetsOnly（）方法限制Reader将加载的工作表。要加载单个命名工作表：$inputFileType = 'Excel5'; $inputFileName = './sampleData/example1.xls';$sheetname = 'Data Sheet #2'; /** Create a new Reader of the type defined in $inputFileType **/$objReader = PHPExcel_IOFactory::createReader($inputFileType);/** Advise the Reader of which WorkSheets we want to load **/ $objReader->setLoadSheetsOnly($sheetname); /** Load $inputFileName to a PHPExcel Object **/$objPHPExcel = $objReader->load($inputFileName);或者，您可以通过传递一组名称来指定几个工作表，其中一次调用setLoadSheetsOnly（）：$inputFileType = 'Excel5'; $inputFileName = './sampleData/example1.xls';$sheetnames = array('Data Sheet #1','Data Sheet #3'); /** Create a new Reader of the type defined in $inputFileType **/ $objReader = PHPExcel_IOFactory::createReader($inputFileType);/** Advise the Reader of which WorkSheets we want to load **/ $objReader->setLoadSheetsOnly($sheetnames); /** Load $inputFileName to a PHPExcel Object **/$objPHPExcel = $objReader->load($inputFileName);如果您只需要访问工作表的一部分，那么您可以定义一个读取过滤器来识别您实际要加载的单元格：$inputFileType = 'Excel5'; $inputFileName = './sampleData/example1.xls';$sheetname = 'Data Sheet #3'; /** Define a Read Filter class implementing PHPExcel_Reader_IReadFilter */ class MyReadFilter implements PHPExcel_Reader_IReadFilter { public function readCell($column, $row, $worksheetName = '') { // Read rows 1 to 7 and columns A to E only if ($row >= 1 && $row <= 7) { if (in_array($column,range('A','E'))) { return true; } } return false; }}/** Create an Instance of our Read Filter **/ $filterSubset = new MyReadFilter(); /** Create a new Reader of the type defined in $inputFileType **/ $objReader = PHPExcel_IOFactory::createReader($inputFileType);/** Advise the Reader of which WorkSheets we want to load It's more efficient to limit sheet loading in this manner rather than coding it into a Read Filter **/ $objReader->setLoadSheetsOnly($sheetname); echo 'Loading Sheet using filter';/** Tell the Reader that we want to use the Read Filter that we've Instantiated **/ $objReader->setReadFilter($filterSubset); /** Load only the rows and columns that match our filter from $inputFileName to a PHPExcel Object **/$objPHPExcel = $objReader->load($inputFileName);使用读取过滤器，您还可以在“块”中读取工作簿，以便任何时候只有一个块驻留在内存中：$inputFileType = 'Excel5'; $inputFileName = './sampleData/example2.xls';/** Define a Read Filter class implementing PHPExcel_Reader_IReadFilter */ class chunkReadFilter implements PHPExcel_Reader_IReadFilter { private $_startRow = 0; private $_endRow = 0; /** Set the list of rows that we want to read */ public function setRows($startRow, $chunkSize) { $this->_startRow = $startRow; $this->_endRow = $startRow + $chunkSize; } public function readCell($column, $row, $worksheetName = '') { // Only read the heading row, and the rows that are configured in $this->_startRow and $this->_endRow if (($row == 1) || ($row >= $this->_startRow && $row < $this->_endRow)) { return true; } return false; } }/** Create a new Reader of the type defined in $inputFileType **/$objReader = PHPExcel_IOFactory::createReader($inputFileType);/** Define how many rows we want to read for each "chunk" **/ $chunkSize = 20;/** Create a new Instance of our Read Filter **/ $chunkFilter = new chunkReadFilter(); /** Tell the Reader that we want to use the Read Filter that we've Instantiated **/ $objReader->setReadFilter($chunkFilter); /** Loop to read our worksheet in "chunk size" blocks **/ /** $startRow is set to 2 initially because we always read the headings in row #1 **/for ($startRow = 2; $startRow <= 65536; $startRow += $chunkSize) { /** Tell the Read Filter, the limits on which rows we want to read this iteration **/ $chunkFilter->setRows($startRow,$chunkSize); /** Load only the rows that match our filter from $inputFileName to a PHPExcel Object **/ $objPHPExcel = $objReader->load($inputFileName); // Do some processing here // Free up some of the memory $objPHPExcel->disconnectWorksheets(); unset($objPHPExcel); }如果您不需要加载格式化信息，只需要加载工作表数据，那么setReadDataOnly（）方法将告诉读者只加载单元格值，忽略任何单元格格式：$inputFileType = 'Excel5';$inputFileName = './sampleData/example1.xls';/** Create a new Reader of the type defined in $inputFileType **/ $objReader = PHPExcel_IOFactory::createReader($inputFileType);/** Advise the Reader that we only want to load cell data, not formatting **/ $objReader->setReadDataOnly(true);/** Load $inputFileName to a PHPExcel Object **/$objPHPExcel = $objReader->load($inputFileName);使用单元格缓存。这是一种减少每个单元所需的PHP内存的方法，但速度很快。它的工作原理是以压缩格式存储单元格对象，或者存储在PHP内存之外（例如磁盘，APC，内存缓存）......但是保存的内存越多，脚本执行的速度就越慢。但是，您可以将每个单元所需的内存减少到大约300字节，因此假设的5M单元将需要大约1.4GB的PHP内存。单元缓存在开发人员文档的4.2.1节中描述编辑查看代码，您正在使用迭代器，这些迭代器不是特别有效，并且构建了一组单元数据。您可能希望查看已经内置到PHPExcel中的toArray（）方法，并为您执行此操作。 -
幕布斯7119047
使用PHPExcel时，可以采取许多措施来保留较少的内存。我建议您在修改Apache中的服务器内存限制之前采取以下操作来优化内存使用。/* Use the setReadDataOnly(true);*/ $objReader->setReadDataOnly(true);/*Load only Specific Sheets*/ $objReader->setLoadSheetsOnly( array("1", "6", "6-1", "6-2", "6-3", "6-4", "6-5", "6-6", "6-7", "6-8") );/*Free memory when you are done with a file*/$objPHPExcel->disconnectWorksheets(); unset($objPHPExcel);避免使用非常大的Exel文件，请记住文件大小会使进程运行缓慢并崩溃。避免使用getCalculatedValue（）; 阅读细胞时的功能。
随时随地看视频慕课网APP
PHP