Zero value is returned as an empty string

Topics: Developer Forum
Jul 12, 2011 at 1:48 PM

In an .xls file, I need to make the distinction between a '0' (zero) value and an empty string when parsing the file with PHPExcel. However, if the value is '0' (number, text, decimal, whatever) the getValue() method always returns an empty string. When a cell is empty but has some (invisible) styling applied to it, you also end up with an empty string. Like that I don't see a way to detect a real '0' value.

PHPExcel_Reader_Excel2007 has a setReadDataOnly() method, but the default PHPExcel object hasn't, so that seems no solution either.

Anyone with a suggestion?

Feb 14, 2012 at 3:02 PM

definitely need to be sure of this too

Feb 14, 2012 at 10:21 PM
Edited Feb 14, 2012 at 10:22 PM

The PHPExcel object doesn't have a readDataOnly setting because it's meaningless: readDataOnly identifies to the reader whether the workbook should be loaded with data formatting, or without formatting, but datatypes still apply.

The cell getValue() method returns the raw data from a cell; getCalculatedValue() returns the calculated value if the cell contains a formula, or the raw data if it isn't a formula cell; getFormattedValue() returns the calculated data according to the cell formatting if the workbook was loaded with readDataOnly(false), or the calculated data if readDataOnly(true) when the workbook was loaded.

getValue() also returns data in the appropriate PHP type. If the cell contains a 0, then a 0 will be returned; an Excel numeric will be returned as a PHP Integer or float (as appropriate); if it contains an empty string, then an empty string will be returned; if it contains a NULL, then a NULL will be returned; while an Excel boolean will be returned as a PHP Boolean.

If this isn't the case, then can you please provide an example workbook that demonstrates where this behaviour is incorrect.

Feb 27, 2012 at 9:19 PM

How does the fromArray() function work with zeros?  I'm exporting an array where some attendance data is zero, but on the sheet, those cells are blank.

Feb 29, 2012 at 5:57 PM

fromArray() should store a any numeric value as a numeric value using the standard DefaultValueBinder:

  if (is_null($pValue)) {
   return PHPExcel_Cell_DataType::TYPE_NULL;
  } elseif ($pValue === '') {
   return PHPExcel_Cell_DataType::TYPE_STRING;
  } elseif ($pValue instanceof PHPExcel_RichText) {
   return PHPExcel_Cell_DataType::TYPE_STRING;
  } elseif ($pValue{0} === '=' && strlen($pValue) > 1) {
   return PHPExcel_Cell_DataType::TYPE_FORMULA;
  } elseif (is_bool($pValue)) {
   return PHPExcel_Cell_DataType::TYPE_BOOL;
  } elseif (is_float($pValue) || is_int($pValue)) {
   return PHPExcel_Cell_DataType::TYPE_NUMERIC;
  } elseif (preg_match('/^\-?([0-9]+\\.?[0-9]*|[0-9]*\\.?[0-9]+)$/', $pValue)) {
   return PHPExcel_Cell_DataType::TYPE_NUMERIC;
  } elseif (is_string($pValue) && array_key_exists($pValue, PHPExcel_Cell_DataType::getErrorCodes())) {
   return PHPExcel_Cell_DataType::TYPE_ERROR;
  } else {
   return PHPExcel_Cell_DataType::TYPE_STRING;