Create a formula to span a range

Topics: Developer Forum, User Forum
May 23, 2010 at 5:07 AM

Hello -

Normally in Excel I can create a formula in one cell, and then copy it across a range.  It will then automatically generate the formulas for the other cells based on the changed column letter or row number.  For example, I want to create a formula for a row range C20:I20.  The formula should be '=SUM(C19-C18)'.  So as I copy it across the row, I get '=SUM(D19-D18)', '=SUM(E19-E18)', etc.  How can I accomplish this with the included classes?  My only thought so far is to create a function, pass it an array for the column letters, generate the formula string by looping through the column letter array, and then finally output the PHPExcel code.

Any thoughts?


May 23, 2010 at 5:13 AM

This is what I'm currently using:

// Set formulas
echo date('H:i:s') . " Adding formulas to cells<br/>\n";
for ($i = 'C'; $i <= 'I'; $i++) {
    $formula = '=SUM(' . $i . '19-' . $i . '18)';
    $cell = $i . '20';
    #echo "$formula<br/>\n";


May 26, 2010 at 9:24 AM

Take a look at the PHPExcel_ReferenceHelper->updateFormulaReferences() method.