php - Export big mysql table into .xls format


I need exposrt mysql table to.xls format, this is fragment from my code

    $result = mysql_query( /* here query */ );
    $objPHPExcel = new PHPExcel();
    $rowNumber = 1;

    while ($row = mysql_fetch_row($result)) {
       $col = 'A';

       foreach($row as $cell) {


Problem is that, in table is 500 000 rows and inwhile cycle at every iteration when I make alsoforeach cycle, this takes very many time at php file execution.

Possible to optimize this code?




500,000 rows will always take a lot of time to write.... even if you speed it up by using the worksheet's fromArray() method to get rid of your foreach loop; and (as nichar has pointed out) this is too many rows for the xls format to handle unless you split them across multiple worksheets.

You can reduce the memory requirements by enabling cell caching (SQLite gives the best memory usage), but it will still take a long time to execute for 500,000 rows and anything this size should be run as a batch/cron job




This is a point to note rather than a direct answer to your question - but if the Excel file format you're outputting is.xls, the maximum rows would be 65,536 and if it is MS Excel 2007+ format e.g.xlsx, the maximum rows would be 1,048,576.

So without changing the output format to.xlsx (which is an entirely different structure), the files will be too large to open.




Consider dumping the data into a csv file, and then importing it into Excel. Should be a lot faster.




If you get a php timeout, you can reset the limit by adding this inside the while or for loop:

set_time_limit(300); //whatever seconds you want

If you're running it through the browser, your server may be timing out. I recommend you run it on command line to avoid this.

Also, similar to what nickhar mentioned, it can be an excel issue. I would try outputting as a csv file. I think it will allow you to output more lines.

People are also looking for solutions to the problem: PHP: How to replace existing XML node with XMLWriter


Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.