PHPExcel - Existing array functions get converted into normal functions?

Question

Greetings all,

I'm trying to write a script that loads an existing spreadsheet containing a number of array formulas, add data to a worksheet and save it. When opening the file after the script runs, the spreadsheet's formulas are no longer array formulas.

Below is the stripped down version of what I'm attempting:

$excelFile = new PHPExcel();
$fileName = 'blah.xlsx';

$excelReader = PHPExcel_IOFactory::createReader('Excel2007');
$excelFile = $excelReader->load($fileName);

//first sheet contains formulas to process the resulting dump
$excelFile->setActiveSheetIndex(1);

// just to illustrate what's used when retrieving data
...
while($record =  db_fetch_object($queryResult)) {
  $excelFile->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $record->field);
}

$excelWriter = PHPExcel_IOFactory::createWriter($excelFile, 'Excel2007');
$excelWriter->save($fileName);

After the script runs, a formula that once appeared as:

{=SUM(A1:C6)}

Now appears as:

=SUM(A1:C6)

Thanks in advance for your insight and input

Tony

Answer 1

Unfortunately, the PHPExcel readers and writers don't yet support array formulas. I believed that the Excel2007 reader/writer did, but your experience suggests otherwise.

Answer 2

It seems that the PHPExcel Cell object does not handle a formula element's attributes, so things like "t=array" would be lost by the time you get to createWriter.

To resolve this issue, we've made modifications to the cell and excel2007 reader and writer classes.

In cell.php:

private $_formulaAttributes;

// getter and setter functions

In reader/excel2007.php:

line 769 - after $this->castToFormula...

if(isset($c->f['t'])){
  $attributes = array();
  $attributes = $c->f;
  $docSheet->getCell($r)->setFormulaAttributes($attributes);
}

In writer/excel2007/worksheet.php:

line 1042 - after case 'f':

$attributes = $pCell->getFormulaAttributes();
if($attributes['t'] == 'array') {
  $objWriter->startElement('f');
  $objWriter->writeAttribute('t', 'array');
  $objWriter->writeAttribute('ref', $pCell->getCoordinate());
  $objWriter->writeAttribute('aca', '1');
  $objWriter->writeAttribute('ca', '1');
  $objWriter->text(substr($pCell->getValue(), 1));
  $objWriter->endElement();
} else {
  $objWriter->writeElement('f', substr($pCell->getValue(), 1));
}

hope this helps someone...