php - Export big mysql table into .xls format
470
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 inwhile cycle at every iteration when I make alsoforeach cycle, this takes very many time at php file execution.
Possible to optimize this code?
Answer
Solution:
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
Answer
Solution:
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.Answer
Solution:
Consider dumping the data into a csv file, and then importing it into Excel. Should be a lot faster.
Answer
Solution:
If you get a php timeout, you can reset the limit by adding this inside the while or for loop:
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.