Why PHPExcel does not allow to write more than 5000 rows

Question

Can any one please tell me Why PHPExcel does not allow more than 5000 rows. I am using an open-source PHPExcel for report generation on my projects and i could not write more than 5000 rows of data from Mysql-DB. My result set fetch 7230 records when the query is executed. How do i fix it..

Answer 1

Almost certainly this is a timeout or a memory issue. The only PHPExcel limit for worksheets size is 65,536 rows and 256 (IV) columns (when using the Excel5 Writer); or 1,048,576 rows and 16,384 (XFD) columns (when using the Excel2007 Writer).

Ensure that your error logging is always enabled... use try/catch blocks to trap for any PHPExcel Exceptions. And read the PHPExcel site discussion threads on memory and performance.

Answer 2

I had the same problem. You will need to allocate enough time and memory limit.

I have tested my solution on 3 different server here is the result:

About 5000 records (12 columns)

Reading file:
09:48:22 Peak memory usage: 1.25 MB

Reading data:
09:48:31 Peak memory usage: 54.5 MB

After indexing data into an array:
09:48:35 Peak memory usage: 68.5 MB

Records: 4504

I increased the memory and time to read 22.000 records after indexing it went up to 370.00MB

Here is the solution (being given that everything else is correct in the code sequence) where you call PHPExcel in your program/function:

ini_set("max_execution_time", 'time_limit'); //see manual

Do all initialization here so that all objects are ready then allocate memory for reading the file and indexing data into program internal structure:

ini_set('memory_limit', '???'); //your memory limit as string
$excel = $excelReader->load($filePath);
"Memory usage: " . (memory_get_peak_usage(true) / 1024 / 1024) . " MB"
//do the rest of the structure!

A good idea is to have managed all this by some categories of data so you don't run into 400 MB - PRONE TO ERRORS!

Answer 3

Almost certainly this is a timeout or a memory issue. The only PHPExcel limit for worksheets size is 65,536 rows and 256 (IV) columns (when using the Excel5 Writer); or 1,048,576 rows and 16,384 (XFD) columns (when using the Excel2007 Writer).

You can change this line

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

as

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

Then it allows to write records more than 65536 rows.