Taking too much time to create xlsx file

Topics: Developer Forum
Feb 22, 2011 at 10:27 AM

Hi All,

First of all great script to create different files. Great Work.

I am using latest version of PHPExcel (1.7.5) and and PHP of 5.2.x.

Now when i am trying to generate very large xlsx file, it will take too much time to create xlsx file, my file has around 500 rows and 649 columns

I am using below code to generate the file....

for($s1=0;$s1<$max;$s1++)
{
    for($s2=0;$s2<$header;$s2++)
    {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value[$id[$p]][$s1][$s2]);
        $col ++;
    }
}

where $max is around 20 and $header is around 35, while $value is multidimensional array with all require value to display in xlsx file.

To generate this file it is taking around 15 minutes.
Can you please let me whether am i doing anything wrong?
If not then why will it take too much time?

Waiting for Replay...Thanks in Advance.

- Regards

Coordinator
Feb 22, 2011 at 8:35 PM

Is it taking 15 minutes to populate the PHPExcel object with this loop? or 15 minutes to write the file?

If it's the writer that's taking the time, and your worksheet contains formulae, then you can try:

$objWriter->setPreCalculateFormulas(false);

before calling the save method.

 

Otherwise, PHPExcel version 1.7.5 runs pretty much as fast as I can get it to run.

Feb 24, 2011 at 10:29 AM

Hey Mark, Thanks for reply.

I have checked again and it seems it takes much time on below code...

        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value[$id[$p]][$s1][$s2]);...................................(1)

and also in writing the file

        $objPHPExcel->setActiveSheetIndex(0);
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save("download_docs/Report.xlsx");....................................................................(2)

Sometime It will take more time on writing the file code(2) or sometime in code(1).....
Also there is no any formula contains by xlsx file it is simple data writing....then also i have put your suggested line in code but it cant make any differences..

As i have said earlier that file has to write around 500 rows and 649 columns....These are samples.....
actually it has to write around 500 rows and 4000columns for each row depending on user selection.

Coordinator
Feb 24, 2011 at 9:13 PM

I don't think there's much I can suggest to help speed things up. If there was a simple code change that could cut your 15 minutes down to 1 minute, I'd have made that change months ago. There are a few tricks that you can try when building your worksheet, such as using row/column styling (or applying styles to ranges of cells) rather than setting styles for each individual cell, and applying styles from a style array is faster than issuing a whole series of lines to set several style features. There's also a host of discussions here on the board describing how you can improve performance with the way you write your code.

For example:

If you're using

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $value[$id[$p]][$s1][$s2]);

for every cell that you set, change your code to:

$worksheet = $objPHPExcel->getActiveSheet();

and then within your loop, use

$worksheet->setCellValueByColumnAndRow($col, $row, $value[$id[$p]][$s1][$s2]);

This save the code from having to call getActiveSheet() for every single cell.

 

Alternatively, look at the fromArray() method to populate a number of cells with a single statement; or at call chaining.

Oct 6, 2011 at 10:49 PM
Edited Oct 6, 2011 at 10:49 PM

I had a similar problem on my laptop dev machine. The script would take about 20 seconds to generate a small xlsx file. Running the same code on the server resulted in the xlsx file being generated in about 1 to 2 seconds. So, the problem is probably not with your code or with PHPExcel....