PHP export huge excel files

Question

I am using PHPExcel class in php to export data from mysql table. The php page simply write the excel data in a loop like:

$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 around 10,000 records. However, I am getting the following error when the file becomes bigger (more records are returned from the query).

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 am still getting this error.

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

Any idea how to resolve this issue?

I am running php-5.2.4 and apache-2.2.8 on ubuntu server if that matters.

What functionality of Excel file you need to create one? Would not csv be enough? Secondly "xlsx" files tend to be the biggest - could you use binary one (xls or xlsb)? — Juliusz, Dec 06 '11 at 11:05

score 3 · Accepted Answer · answered Dec 06 '11 at 10:42

3

Use PHPExcel's cell caching (if you aren't already)
Don't populate an array by looping through the database query resultset, and then populate PHPExcel by looping through that array; populate PHPExcel directly by looping through the database query resultset

answered Dec 06 '11 at 10:42

Mark Baker

209,507
32
346
385

I used the 2nd point and exported the files as csv. It is much lighter on the server. – Khaled Dec 06 '11 at 14:53

score 1 · Answer 2 · edited May 23 '17 at 11:53

1

Export your records as multiple excel files and join them together. About joining, there is already a question in How can I join Excel documents using PHPExcel?

edited May 23 '17 at 11:53

Community

1
1

answered Dec 06 '11 at 10:28

belgther

2,544
17
15

PHP export huge excel files

2 Answers2