getCalculatedValue returning period instead of number value

Topics: User Forum
Dec 23, 2013 at 7:15 PM
I'm using PHPExcel 1.7.9 with PHP 5.3.5 and I'm encountering something odd when iterating an Excel file created with Excel 2010. I have a column of data that contains simple whole numbers (formatted as general) but the returned value by calling $cell->getCalculatedValue() gives me a period instead of the number.

I've tried formatting the cells differently (text and number) and get the same result. Other cells come through just fine. I've confirmed that I'm indeed looking at the correct column with $cell->getColumn() as well.

Has anyone ever seen this before? I can share a version of my spreadsheet and code if that helps.
Dec 23, 2013 at 8:42 PM
Edited Dec 23, 2013 at 8:48 PM
If you want to retrieve the value as it appears when viewing it in Excel then you need to use getFormattedValue() instead of getCalculatedValue().
getCalculatedValue() ignores any styling/formatting code the spreadsheet has.

However, this doesn't explain why it is returning a '.' value. That might be a different issue.

-- Christopher Mullins
Dec 26, 2013 at 3:05 PM
Thank you for the suggestion. I tried getFormattedValue() and it returns a period as well. I've put together a much simpler spreadsheet with two columns that illustrate the error. Row 1 has header labels. Column A has numeric values (1-5) and column B has a reference formula to column A of =IF(A2>0,".",""). Nothing too fancy, but the reference in column B is what seems to be doing this.

I believe the error comes about as I'm trying to structure each row of data into an array that is indexed to the header row 1. In other words, I want something that starts with:
      A       B
1     foo     bar
2     4       =IF(A2>0,".","")
3     0       =IF(A2>0,".","")
To look like the following structure on a per row basis (starting at row #2)
Array
(
    [foo] => 4
    [bar] => .
)
I didn't see a code sample or handy method to do this, so I wrote up the following myself to iterate the cells and create a $header_row array:
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load($_FILES["data_file"]["tmp_name"]);

$header_row = array();
$dates = array('dob', 'dop', 'dot', 'letter date', 'allocdate', 'deadline', 'resent date');
$worksheet = $objPHPExcel->getSheetByName('DistDataTemplate');

foreach ($worksheet->getRowIterator() as $row) 
{
    $line = array();

    $cellIterator = $row->getCellIterator();
    $cellIterator->setIterateOnlyExistingCells(false); // Loop all cells, even if it is not set
    foreach ($cellIterator as $cell) 
    {
        if (!is_null($cell)) 
        {
            // set up header row
            if ($row->getRowIndex() == 1)
            {
                $header_row[$cell->getColumn()] = strtolower($cell->getFormattedValue());  // [A] => ssn
            } else
            {
                $cell_value = $cell->getCalculatedValue();

                // convert Excel dates into Unix dates
                if (in_array($header_row[$cell->getColumn()], $dates) && PHPExcel_Shared_Date::isDateTime($cell))
                     $cell_value = PHPExcel_Shared_Date::ExcelToPHP($cell->getCalculatedValue());

                $line[$header_row[$cell->getColumn()]] = $cell_value;
            }
        }
    }

    // submit line of data to the API
    if ($row->getRowIndex() > 1 && $row->getRowIndex() < 11)
    {
        // do stuff here...
    }
}
The above seems to correctly work, but I believe the problem is that once the reference is made to A2 in the formula in B2, the cell coordinate gets set to A2. I turned on debugging output in Cell.php (along with my printing of the $line array) and get this:
Worksheet - DistDataTemplate
Row number - 1
Cell A1 value is a s with a value of foo Cell value for A1 is not a formula: Returning data value of foo
Cell B1 value is a s with a value of bar Cell value for B1 is not a formula: Returning data value of bar
Cell C1 value is a null with a value of Cell value for C1 is not a formula: Returning data value of
Row number - 2
Cell A2 value is a n with a value of 4 Cell value for A2 is not a formula: Returning data value of 4
Cell B2 value is a f with a value of =IF(A2>0,".","") Cell value for B2 is a formula: Calculating value Cell A2 value is a n with a value of 4 Cell value for A2 is not a formula: Returning data value of 4
A2 calculation result is . Returning calculated value of . for cell A2 Cell C2 value is a null with a value of Cell value for C2 is not a formula: Returning data value of

Array
(
    [foo] => .
    [] => 
)
Notice the Returning calculated value of . for cell A2 phrase above near the end of the output? I would expect it to be for B2, not A2. Maybe it is my code, but it looks like a bug. I'm happy to help in any way to sort it out.