0

I use the phpexcel library from codeplex to export a large amount data to an excel file (2007).

The problem is that takes much time to be executed... I used the memory cache options and cache dir options that exist as part of the library and I have unset the objects after the data is returned.
also I feel I should note that in my application I use some variables to count the rows included in the loop.
Any idea or concept to make the script faster or more efficient would be wonderful.

rlemon
  • 17,518
  • 14
  • 92
  • 123

1 Answers1

1

I occasionally have to check their site for performance improvements and often factor these into my solutions:

  • Populating each individual cell is less efficient thqansing the fromArray() method.
  • If you're using formulas, per their site, you can speed up saving with $objWriter->setPreCalculateFormulas(false)

Edit:

Without indepth knowledge of what you're doing, I can only thing I can suggest looking at improving how you populate cells:

Instead of this: $objPHPExcel->getActiveSheet()->setCellValue('A1',$x); $objPHPExcel->getActiveSheet()->setCellValue('B1',$y);

Access getActiveSheet() only one time this way:

$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValue('A1',$x);
$sheet->setCellValue('B1',$y);

Or, combine cell updates like this:

$objPHPExcel->getActiveSheet()->setCellValue('A1',$x)
                              ->setCellValue('B1',$y);
GDP
  • 8,109
  • 6
  • 45
  • 82
  • there are multiple loops included and multiple counters in my script and 51 columns , and with 2000 records, that takes more than 1200 seconds... –  Jun 07 '12 at 17:35
  • How i do, to keep the export action very faster within a large number of records?? –  Jun 07 '12 at 17:43
  • If format doesn't matter too much, I've had better luck saving in Excel5 - it's faster. – GDP Jun 07 '12 at 17:48
  • 2
    1200seconds = 20 minutes - seriously? 'you've got a bigger problem somewhere else, I think. We use this all day long with much bigger datasets than that and have never come close to approaching times like that – GDP Jun 07 '12 at 17:50
  • thank u for the example, i used that syntax like u wrote , the same issue appears... yes i am serious fore data more than 5000 records , that will take 1200 seconds and spmetimes the script is interrupted ... –  Jun 07 '12 at 18:17
  • if that possible, i can send u tomorrow a copy of the script, maybe u can help much more on ur email... –  Jun 07 '12 at 18:19
  • maybe the problem includes in the coding syntax...,i used previously the Excel5, but this has not permission to use the cache dir $objWriter->setUseDiskCaching(true) in the phpexcel library, for this reason i reset the header with the version Excel 2007 ... –  Jun 07 '12 at 18:45
  • 1
    @Mouhamad - whether you use formulae or not, the Writer will still check every cell to see if it contains a formula unless you use setPreCalculateFormulas(false) so it's especially worthwhile doing it – Mark Baker Jun 07 '12 at 19:16
  • but the effective issue that is how i can reduce the time of the execution of the script export data? with a big large records > 6000 records –  Jun 08 '12 at 09:38
  • where is it more efficient to put this instruction :setPreCalculateFormulas(false) ?? before put the data in the cells? or after that? –  Jun 08 '12 at 09:45
  • @Mouhamad - setPreCalculateFormulas is applied to the writer, before you issue the save request... it has absolutely no effect on storing data in cells in the PHPExcel object – Mark Baker Jun 08 '12 at 11:52
  • We still haven't seen any actual code, so until then it's impossible to give you any further advice on how to reduce execution time – Mark Baker Jun 08 '12 at 11:55
  • ok , i can send u the exacat code to be checked , because i cannot posy it here completely , if that's possible , any email cans be suggested??? –  Jun 08 '12 at 12:10
  • also i approve some issue, maybe the problem long time and delay, caused by the nested loop exist in the script also... –  Jun 08 '12 at 12:11