PHPExcel and subtotal
I am using PHPExcel to parse XLSX files. I found that SUBTOTAL () members are not ignored when combining values with functions like SUM (). Is there a setting or something that needs to be set to force PHPExcel to ignore cells with a SUBTOTAL formula when summing over it? Maybe a workaround if this is not possible?
source to share
In the end, I wrote a function similar to toArray () to parse the Excel sheet and return the previously cached values (stored in the "v" tag in the xml sheet). This prevents PHPExcel from trying to calculate cell values. PHPExcel did not find such a function. Note that it uses the calculated value in the event that the cached value is not available. Practically speaking, the cached value should always be available as the sheet will be generated by Excel which populates the cached value field.
function getCachedSheetData($sheet)
{
/* Usage
sheet: $excel_obj->getActiveSheet()
*/
// This function is similar to the toArray function, but returns previously cached values to prevent PHPExcel from calculating any formulas
$toreturn = array();
foreach ($sheet->getRowIterator() as $row)
{
$toreturn[] = array();
$cellIterator = $row->getCellIterator();
$cellIterator->setIterateOnlyExistingCells(false); // This loops all cells, even if it is not set. By default, only cells that are set will be iterated.
foreach ($cellIterator as $cell)
{
if ($cell->getOldCalculatedValue() === null)
{
$toreturn[$cell->getRow()][$cell->getColumn()] = $cell->getValue();
}
else
{
$toreturn[$cell->getRow()][$cell->getColumn()] = $cell->getOldCalculatedValue();
}
}
}
return $toreturn;
}
source to share