Can you stop PHPExcel from performing calculations?

Topics: Developer Forum, User Forum
Dec 10, 2010 at 2:24 PM
Edited Dec 10, 2010 at 2:25 PM

Hi,

 I've just downloaded V1.7.5 today, and love the completeness of this library. Being able to access cell protection and the like is just fantastic.

Unfortunately I've just struck a huge problem. I'm generating a spreadsheet with about 2000 rows, and trying to insert a SUMIF() function on each row to gather data. At first it just crashed out after about 20 seconds. I did a search and discovered the set_time_limit trick, but even pushing this out to 200 doesn't fix the problem - it just sits there longer before failing.

So now I'm between a rock and a hard place. After going to all the effort to convert from a simpler Excel library, I may need to convert back to that library to get it to work, which is a pity because I'll be leaving a lot of nice functionality behind.

Before I do, I'd like to know whether this is related to the fact that PHPExcel maintains an in-memory model of the spreadsheet. If it is, and the "pre-calculation" of cells is causing the problem, is there any way to turn this capability off?

Thanks in advance!

Andrew

 

Coordinator
Dec 10, 2010 at 5:57 PM
$outputFileType = 'Excel5';
$outputFileName = 'test.xls';
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFileType);
$objWriter->setPreCalculateFormulas(false);
$objWriter->save($outputFileName);
Dec 10, 2010 at 8:18 PM

Thanks Mark,

 

I had spotted this and set it on an Excel2007 writer, but no effect. I thought that the problem may have been occuring earlier in the code, but I took the writer code out altogether and it's now responding.

Even a cut and paste of your code into mine resulted in the same delay and ultimate failure.

Any other thoughts?

 

Thanks for your time!

 

Andrew

Coordinator
Dec 10, 2010 at 9:22 PM
adavison17 wrote:

Any other thoughts?

SUMIF() is a more complex formula, and won't work with Excel5, because it isn't a core function in that version of Excel; but it shouldn't cause any problems with Excel2007.

Other than PHP limits on time and memory: no ideas! Version 1.7.5 is faster and uses less memory than previous versions of PHPExcel, but it is still limited, and still not as fast as the older PHP Excel writers (such as the PEAR writer); however, 2000 rows (you don't say how many columns) isn't particularly large. My core performance tests are based on reading a 2048x9 worksheet (1 formula column), to which I add 3 additional formula columns (and a sum line) before writing. The timing and memory results on my little netbook, running Apache/mod_php:

Load Tests from Excel5 file
Call time to create Reader was 0.1702 seconds
Current memory usage: 2.5 MB
Call time to read Workbook was 8.0049 seconds
Current memory usage: 25.75 MB
Add new columns to PHPExcel Object
Call time to add new formulae was 2.2114 seconds
Current memory usage: 31.5 MB
Write Tests to Excel5 file
Call time to instantiate Excel5 writer was 0.0981 seconds
Current memory usage: 32.25 MB
Call time to save Workbook to file was 24.8102 seconds
Current memory usage: 37.5 MB
Peak memory usage: 40.25 MB

PHPExcel's "in-memory" model doesn't include calculation unless this is explicitly requested, either when using the getCalculatedValue() method to get the cell value, or when writing to file.

When writing to xls or xlsx, PHPExcel will execute formulae by default so that it can write both the formulae and the result to the file, and that can slow down the process quite considerably (dependent on the complexities of the formula) but setPreCalculateFormulas(false) disables this calculation, so it only writes the formulae. This can reduce memory usage to just an additional 2-3 MB for the writer itself (no need to include the calculation engine) and double the speed of writing (dependent on the number of formulae in the worksheet). The cost is forcing MS Excel to do the recalculation (not always done automatically) when the resultant file is opened in MS Excel.

Dec 10, 2010 at 10:09 PM
Edited Dec 11, 2010 at 11:33 AM

Thanks for writing back so quickly!

I''' try to extract the offending code and post it...

 

Andrew

Dec 13, 2010 at 3:13 AM

Ok. Further investigation has highlighted the problem. It turns out that the issue appears to be that I've "autosized" the column that the SUMIF function appears in. Process of elimination whilst isolating the code led to the discovery.

 

function test_excel_sumif($form)
{
    include 'PHPExcel.php';
    include 'PHPExcel/Writer/Excel2007.php';

    $objPHPExcel = new PHPExcel();
    $sheet =& $objPHPExcel->getActiveSheet();

    // The SUMIF function will appear in colum J. This next line is the one that causes the failure.
//    $sheet->getColumnDimension('J')->setAutoSize(true);

    for($row=1;$row<=2000;$row++)
    {
        $sheet->setCellValueByColumnAndRow(9,$row,'=SUMIF(C1:C2000,C1,I1:I2000)');
    }   
    $outputFileType = 'Excel2007';
    $outputFileName = 'd:\\test.xls';
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, $outputFileType);
    $objWriter->setPreCalculateFormulas(false);
    $objWriter->save($outputFileName);
    
    return $form;
    
}

When I uncomment the Autosize instruction, the writer seems to go into an infinite loop of some description. I haven't looked into the module sourcecode to identify the source problem nor a fix, as time is limitted, but hopefully this code segment will help make that process quick for someone more familiar with the core code.

For now the key learning is: Don't autosize a column in which you use the SUMIF (and I therefore presume COUNTIF) function.

Cheers!

Andrew

Coordinator
Dec 13, 2010 at 7:57 AM

This isn't specific to SUMIF or to COUNTIF, but to all formulae.

If you're using autosize, then PHPExcel needs to calculate the formula result in order to determine how many characters should be displayed in the cell. It does this for every cell in the column, and uses the largest display width as the autofit size for the column width.... so it isn't a good idea to autosize any column containing formulae. The Writer shouldn't be in an infinite loop, but (depending on the complexity of the formula) it could take a long time to process.

Dec 13, 2010 at 11:47 AM

Thanks Mark,

Maybe one to add as a warning in the documentation on page 27 :)

Can I ask a sidebar Q? Is there a way to set a range on a sheet as being selected?

 

Cheers!

Andrew