CODEWITHSUNDEEP
phpnumbersphpexcelxls
ChAnger
ChAnger

Reputation: 45

PHPExcel parsing .XLS file with "numbers stored as text" warning returns #VALUE

My code uses the getFormattedValue() method to pull the data into PHP.

Here is the problem part of .xls with "numbers stored as text" at column D:

Excel cells contains next values:   D7 -> 0,43;  E7 -> =D7*1,2;  F7 -> =E7*11,2;
getFormattedValue() returns:          0,43;        #VALUE!;          #VALUE!;
getCalculatedValue() also returns     0,43;        #VALUE!;          #VALUE!;
getValue() returns                    0,43;        D7*1.2;           E7*11.2;

If I fix it in excel, as it propose, to number. then getFormattedValue() work just fine. But I can't tell to client that your xls is wrong...

So question, is it possible get PHPExcel to act like Excel? Or maybe some other method to make this work.

Thank you for your help and time!

--- EDIT 1 ---

Basicly it's a comment to Mark Baker's answer.

I would like to find a solution that doesn't include any changes to .xls files. I don't know what structure it would be, where is the problem cells, etc.

Is there some way to make PHPExcel recognize that cells? Maybe how MS Excel do that? Or find out our method to do that.

I think about trying to divide cell value by itself and if we get 1 so that's a numeric cell. AND\OR find cells that are included in mathematical calculations. To better understand what i mean - cell E7 contains D7*1,2. So, we can check - is D7 numeric? Something like that.

At this time I don't have any solution to this problem... So any help would be great.

Upvotes: 3

Views: 2566

Answers (1)

Mark Baker
Mark Baker

Reputation: 212412

You would need to convert those string values in column D to a valid numeric (using a decimal point rather than a comma) before getting the values from cells in columns E and F. If the problematic cells are always in the same column (or at least predictable) you could use a cell binder to handle this conversion when the workbook is loaded.

EDIT

You'd need to create a binder something like:

class PHPExcel_Cell_AdvancedValueBinder 
    extends PHPExcel_Cell_DefaultValueBinder 
    implements PHPExcel_Cell_IValueBinder
{
    public function bindValue(PHPExcel_Cell $cell, $value = null)
    {

        // sanitize UTF-8 strings
        if (is_string($value)) {
            $value = PHPExcel_Shared_String::SanitizeUTF8($value);
        }

        // Find out data type
        $dataType = parent::dataTypeForValue($value);

        if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && 
            !$value instanceof PHPExcel_RichText) {
            if (preg_match('/^\d+,\d+$/', $value)) {
                list($w, $d) = explode(',', $value);
                $value = (float) $w . '.' . $d;
                $cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
                return true;
            }
        }

        // Not bound yet? Use parent...
        return parent::bindValue($cell, $value);
    }
}

Upvotes: 2

Related Questions