I'm using PHPExcel which I've used before many times. The problem I have now is when reading Excel2007 files (.xlsx - format). What I'm doing is simply looping the the .xlsx
file and creating an array by row/column and then print_r()
-ing the results after the read operation to make sure the data output is good before importing it into a MySQL database.
Now when reading the Excel2007 .xlsx
file (6MB) the output fails, but whats interesting is if I save the file as the older format .xls
(1992-2004 - Excel5) the file becomes larger (16MB) but outputs correctly. This made me think originally it wasn't a memory problem since the older larger .xls
file (16MB) ran with no problems and was almost 3x the size of the .xlsx
file (6MB).
For test purposes I then copied 25 rows of the 30,000 in the .xlsx
(6MB) file and created a new Excel2007 .xlsx
and ran the import against the smaller 25 row data-set and it output correctly. This then led me think that it is a memory problem however related specifically to the .xlsx
format...
I'm running the server on Amazon Web Services and have C4.Xlarge (16-core, 30GB RAM) so I should have plenty of resources to run this operation.
Question: Why does my output fail when reading a smaller .xlsx
file vs a larger .xls
file, but then succeed with a smaller .xlsx
(25-row) file?
//PHP Function
function parse_xls($file){
ini_set('memory_limit','-1');
$type = PHPExcel_IOFactory::identify($file);
$reader = PHPExcel_IOFactory::createReader($type);
$reader->setReadDataOnly(true);
$xls = $reader->load($file);
$sheet = $xls->getActiveSheet();
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
for($row=2; $row <= ($highestRow+2); $row++){
$import[$row] = [];
for($col=0; $col < $highestColumnIndex; $col++){
$result = $sheet->getCellByColumnAndRow($col, $row)->getValue();
array_push($import[$row],$result);
}
}
print_r($import);
die();
}
for big files i use chunkReadFilter
$iChunkSize=1000; for($iStartRow = $row_start; $iStartRow <= $totalRows; $iStartRow += $iChunkSize) { $objReader = $oExcel->SetCreateReader(); $oChunkFilter = new chunkReadFilter(); $objReader->setReadFilter($oChunkFilter); $oChunkFilter->setRows($iStartRow,$iChunkSize); $objReader->setReadFilter($oChunkFilter); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($files['path']); $objPHPExcel->setActiveSheetIndex($iList); $sFromCell = 'A'.$iStartRow; $aData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,false,$sFromCell); // free memory unset($objPHPExcel); unset($objReader); unset($oChunkFilter); // parse data foreach ($aData as $sKey => $aValue) { ... } // real data rows if (count($aData) < $iChunkSize) { unset($aData); break; } unset($aData); }