phpExcel fails to save large sheet with styles and data validation

Topics: Developer Forum
Feb 6, 2013 at 11:29 AM
Hi,
Straight to the problem. The usual thing - I have data stored in MySQL database and I need to export it to excel. PhpExcel does the job perfectly, except the fact that it fails to save if the data becomes too much. What I mean is the following, If the rows increase up to around 50000 the sheet is still build in the memory, but the save method of the writer fails. Rows consist of about 15 cells(for now, they need to be around 30), some of the cells have validation rules and every row has at least 1 style.

If I leave out the styles, I am able to save slightly more rows, but I really can't omit neither the styles or the data validation.

Also, since this eats up lot of memory I tried to use some sort of cell caching, but no matter what the caching method is I am not able to save the document. If for example I am able to export 50000 rows without styles and caching, if I turn on the caching and try to export the same data - it fails on the save method.

I am populating the cell values, and data validation as I loop through the data from the db, and after that I apply the styles, by ranges. If I try to apply the styles while looping through the data, along with the cell value and data validation - fails in the save again.
Any help is appreciated.

Thanks :)

P.S. - The memory management (cell caching) is not the main problem right now
Coordinator
Feb 7, 2013 at 6:43 AM
It certainly sounds like a memory problem even though you say otherwise. If it isn't, then nothing in your description tells me what else it could be
Feb 7, 2013 at 10:37 AM
Well, can you give me some pointers on how to actually find out what the problem is? There are no error logs. Is there something like debug mode?

I said that it probably isn't a memory problem because I don't get the out of memory error. There were several times when I tried to export all the data, and the script was terminating giving me something like "memory exhausted: tried to allocate * bytes ..." error.

By the way, I was able to speed things up by adding all the data validation on ranges of cell after the data has been populated. Also did the same thing with the styles (where possible). But still no improvement as far as volume of the data goes. Now I am exporting faster but still the same volume of data.
Jun 28, 2013 at 1:08 PM
Hi! Im having a similar problem. The excel shows the styles up to a point and then nothing. If someone have any idea, please let me know.

Im using this headers

header('Content-Description: File Transfer');
header('Content-Type: application/vnd.ms-excel;charset=UTF-8;');
header('Content-Disposition: attachment;filename="Exportacion.xls"');
header('Cache-Control: max-age=0');
header('Content-Transfer-Encoding: binary');
header('Pragma: no-cache');
header('Expires: 0');

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');