Apr 28, 2012 at 2:14 PM
Edited Apr 30, 2012 at 9:37 AM
Thanks for the code!
But my ultimate problem was (is) memory. (bleh) This is what drove me to using the technique that Mark Baker has documented a bizzillion places, like this : http://phpexcel.codeplex.com/discussions/242712. Read a chunk, then
process that chunk, then free memory and go back to read chunks...
In my quest for a solution I tried something like what you documented but had various problems. I actually have the chunk reader working now (code below), but now have a STINKY resultant problem: dealing with interim saves of the initially read
The code, below loops just fine and reads all the rows in the spreadsheet. (evidence is the echo statements display what is expected).
My problem is (a) I need to save the applied conditionals and cell notes to the cells, and (b) it needs to be saved onto the server so when the used downloads the spreadsheet they have oodles of information that they can use to update the source data.
My solution for the moment is after each chunk unset($objPHPExcel) and then immediately execute--
$objPHPExcel = new PHPExcel();
$inputFileType = PHPExcel_IOFactory::identify($theDIRandFileName);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
to reestablish the read environment. Seems to do that OK, but here is the deal:
Once completely done and all the annotated spreadsheet cells with colors and notes has been performed and saved to the server directory filename and directory (the cleverly named variable '$theDIRandFileName' in the code, below)
the saved file completely blank spreadsheet. Ohhh POOH.
By the way: the annotations based upon conditionals and the application of cell notes works fine in the non-chunk-but-runs-out-of-memory-version.
Addendum 1 (30 April, 20 2012): I now seem to be in a catch 22.
My ultimate goal is to read the spreadsheet, audit it, and then write the audited spreadsheet with colored cells indicating classes of problems and inserted cell notes explaining what the problem is(are). That part is working fine. Its just the
- If I read in chunks, then before moving onto the next chunk of cells I need to save the revised cell contents. But I can't do this because the chunk filter only makes available the rows specified for the chunk.
- If I just do start-to-finish read/audit/apply conditionals to the spreadsheet rows in a for loop I run out of memory before I get to the last row.
- If I use discISAM caching, it appears to work, memory wise, and I can read/audit/apply conditionals in one for loop (no chunks),
BUT when I go to save the spreadsheet with all this neat stuff I get:
Fatal error: Allowed memory size of 18874368 bytes exhausted (tried to allocate 238592 bytes) in
... /functions/utilities/PHPExcel/Shared/XMLWriter.php on line
Basic and standard write at the end of the for loop:
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save(str_replace('.php', '.xlsx', $theDIRandFileName));