Calculate cell value issue with read filter

Topics: Developer Forum, User Forum
Mar 10, 2010 at 2:37 PM

Hi,

I've got an xls file which I need to process that contains some cells which take values from other cells. I created a filter because I don't need all rows and columns of the xls file. Problems are occurring when a cell I want to include is supposed to read its value from a cell I excluded. The value '0' will simply be retrieved with getValue() because of it.

 

Example:

 

$objReadFilter = new clsXlsReadFilter();
$objReader = PHPExcel_IOFactory::createReaderForFile($strXlsFileName);
$objReader->setReadDataOnly(true);
$objReader->setReadFilter($objReadFilter);
$objPHPExcel = $objReader->load($strXlsFileName);

$objActiveSheet = $objPHPExcel->getActiveSheet();
echo $objActiveSheet->getCell('D2')->getCalculatedValue();
 

 

With readFilter class:

 

class clsXlsReadFilter implements PHPExcel_Reader_IReadFilter {
    private $aIncludedColumns = array('D', 'E', 'F');
    private $aExcludedRows = array(1);

    public function readCell($strColumn, $iRow, $strWorksheet = '') {
        if (in_array($strColumn, $this->aIncludedColumns) && !in_array($iRow, $this->aExcludedRows)) {
            return true;
        }

        return false;
    }
}

If cell 'D2' has formula '=A2' this example will output '0' instead of the actual value of cell 'A2'.

 

I don't actually need to keep the formulas since I'm not storing a new xls file after working with it, I'm only interested in reading the data. Is there a way to force all values to be calculated before the read filter is being processed? Or can something like an output filter be applied instead of a read filter so that all required data is available for output? The latter would allow me to discard all data I don´t need just before using e.g. toArray().

 

Developer
Mar 16, 2010 at 6:06 AM

In the Excel file, in each cell with a formula, the old calculated value from latest calculation (if there has been any?) is stored along with the formula.

You can retrieve it like this:

echo $objActiveSheet->getCell('D2')->getOldCalculatedValue();