Geek Answers Handbook

Why PHPExcel does not allow writing more than 5000 lines

Can someone tell me why PHPExcel does not allow more than 5000 lines. I use open source PHPExcel to generate reports on my projects and I could not write more than 5000 rows of data from Mysql-DB. My result set retrieves 7230 records when the query is executed. How to fix it.

+8
php phpexcel
source share
5 answers

It is almost certainly a timeout or memory problem. The only PHPExcel limit for worksheet sizes is 65,536 rows and 256 (IV) columns (when using Excel5 Writer); or 1,048,576 rows and 16,384 (XFD) columns (when using Excel2007 Writer).

Make sure that error logging is always on ... use try / catch blocks to trap any PHPExcel exceptions. And read the PHPExcel website discussion topics on memory and performance.

+16
source share

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

I tested my solution on 3 different servers, 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 the data into an array:
09:48:35 peak memory usage: 68.5 MB

Records: 4504

I increased my memory and time to read 22,000 records after indexing, to 370.00 MB.

Here is the solution (assuming 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 the initialization here so that all objects are ready, then allocate memory to read the file and index the data into the internal structure of the program:

 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!

It’s a good idea that all of this managed to manage some categories of data, so you don’t start at 400 MB - GRANT ERRORS!

+9
source share

It is almost certainly a timeout or memory problem. The only PHPExcel limit for worksheet sizes is 65,536 rows and 256 (IV) columns (when using Excel5 Writer); or 1,048,576 rows and 16,384 (XFD) columns (when using Excel2007 Writer).

You can change this line

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

but

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

Then it allows you to record over 65,536 lines.

+2
source share

Without your code or class code, it’s quite difficult, I believe ... Do you mean that you cannot write more than 5 thousand lines in an XLS file or inside a worksheet? otherwise, an ugly workaround could write 5K lines on the first sheet, and the rest on the second (so 5K lines of each sheet if the DB gets bigger). I don’t think XLS has a limit of 5 thousand lines, so there should be something wrong or incorrectly configured in your script. Have you tried several times? Does it always print 5k lines? or could it be due to timeouts? (your script or connection)

+1
source share

This type of problem is most likely a server memory problem. What server are you working on, and are you sure that it has enough memory and resources to process large data files? If you cannot say anyway, the best job is to read several thousand records at a time, process them, and then move on to the next fragment. I myself would prefer to split a large data file into manageable parts (files), after which I could subsequently process each of these parts to get the desired result. After processing all the parts, they can be combined together to create a new big data file.

0
source share

More articles:


All Articles