All geek questions in one place

PHP exports huge excel files

I am using the PHPExcel class in php to export data from mysql table. The php page just writes excel data as a loop:

$objPHPExcel = new PHPExcel(); # Query result goes to array $result # ... $i = 1; for ($r = 0; $r < count($result); $r++) { # Set $value1, $value2, $value3 from $result $objPHPExcel->setActiveSheetIndex(0) ->setCellValue("A$i", $value1) ->setCellValue("B$i", $value2) ->setCellValue("C$i", $value3); $i++; } $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save("/path/to/exported_file.xlsx");

This code works fine when the exported file is not huge. I was able to export about 10,000 records. However, I get the following error when the file gets larger (more records are returned from the request).

 Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 71 bytes)

I increased the memory limit in /etc/php5/apache2/php.ini to 1024M. Even after this increase, I still get this error.

I tried to create several excel files instead of one, limiting the results of the query by adding LIMIT to the SELECT , but this also did not solve the problem.

Any idea how to solve this problem?

I use php-5.2.4 and apache-2.2.8 on an ubuntu server, if that matters.

+4
source share
2 answers
  • Use PHPExcel caching (if you haven’t already)
  • Do not populate the array by looping through the database query results, and then populate PHPExcel by going through this array; populate PHPExcel directly by looping through the database query results
+3
source

Export your records as multiple excel files and merge them together. On joining, see How can I connect to Excel documents using PHPExcel?

+1
source

Source: https://habr.com/ru/post/1384880/

More articles:


All Articles