Wednesday, August 13, 2014

PHPExcel Max Rows 65356 Problem

PHPExcel to generate *.xls file with list of data around 140K rows but limited to only
showing 65356 (~65K) rows per worksheet/sheet. Are you having the same problem like me?
If you are, then simply split the data to multiple sheets. Lets say i have 140K of data
then split the datas to 3 worksheets.

Done? Not quite. I had tried to implements the workaround to split the datas but
the loading process of the data, it pop-up a dialog with error message in microsoft-office
while other office application don't, then blank multiple sheets. If you set the name for
each sheet then you will see that the sheets are succesful renamed or set to desired name.

How come? well i do not have the exact answer but after some trials and errors,
i come up with 39999 (~39K) rows max for each sheet. And everything works as expected.
After i set the data to 300K and split the datas to multiple sheets with ~39K rows
per sheet, all the data generated and shown as expected.

So the real max rows for a workbook with multiple sheets is ~39K rows while
~65K rows for a workbook with a single worksheet. Hope this article would help.

Happy coding

No comments: