CODEWITHSUNDEEP
phpphpexcel
Oto Shavadze
Oto Shavadze

Reputation: 42793

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) {
         $objPHPExcel->getActiveSheet()->setCellValue($col.$rowNumber,$cell);
         $col++;
      }

       $rowNumber++;
    }

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

Possible to optimize this code?

Upvotes: 0

Views: 476

Answers (4)

Mark Baker
Mark Baker

Reputation: 212452

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

Upvotes: 2

Developer Esparza
Developer Esparza

Reputation: 13

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.

Upvotes: 0

Mchl
Mchl

Reputation: 62395

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

Upvotes: 1

nickhar
nickhar

Reputation: 20853

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.

Upvotes: 1

Related Questions