CODEWITHSUNDEEP
phpphpexcelphpexcel-1.8.0
jayzcos
jayzcos

Reputation: 45

Updating Cell References in copied rows in PHPExcel (PHPSpreadsheet)

I am using the following code to copy rows in PHPSpreadsheet. However I would like to change the formula references in the copied rows to reflect the new row number. (e.g. (A1-B1), (A2-B2), A3-B3 etc.) Any suggestions?

function copyRowFull(&$ws_from, &$ws_to, $row_from, $row_to) {
  $ws_to->getRowDimension($row_to)->setRowHeight($ws_from->getRowDimension($row_from)->getRowHeight());
  $lastColumn = $ws_from->getHighestColumn();
  ++$lastColumn;
  for ($c = 'A'; $c != $lastColumn; ++$c) {
    $cell_from = $ws_from->getCell($c.$row_from);
    $cell_to = $ws_to->getCell($c.$row_to);
    $cell_to->setXfIndex($cell_from->getXfIndex()); 
    $cell_to->setValue($cell_from->getValue());
  }
}

Upvotes: 0

Views: 951

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

You should be able to use the updateFormulaReferences() method of the PHPExcel_ReferenceHelper class to update cell references in a formula string

/**
 * Update references within formulas
 *
 * @param    string    $pFormula    Formula to update
 * @param    int        $pBefore    Insert before this one
 * @param    int        $pNumCols    Number of columns to insert
 * @param    int        $pNumRows    Number of rows to insert
 * @param   string  $sheetName  Worksheet name/title
 * @return    string    Updated formula
 * @throws    PHPExcel_Exception
 */

Upvotes: 0

Related Questions