Reader: Any way to retrieve the Excel number format string or other form of number format?

[danatemple]

I've tried ingesting an Excel file with various number formattings (percentage, roundings, currency) and I am trying to do the equivalent of PHPSpreadsheet:

$cell->getStyle()->getNumberFormat()->getFormatCode();

I can see there are methods getFormat() and getStyleId() on Style, but these just return null and -1 respectively, independent of the actual formatting.

I guess #174 is also pointing to this.

Any plans to implement?

[Slamdunk]

Not from me, but any PR is welcome 💪

[danatemple]

Know anyone who could do the work? I don't have the skill set but would be willing to reimburse for effort spent!

[Slamdunk]

Can you give me a sample and the output you expect from the method? I'll see if I can implement what you are looking for, and I'll give you my quote

[danatemple]

Sure - I've attached an example file with a variety of formattings, and here is some code and the output:

use \PhpOffice\PhpSpreadsheet\IOFactory;

$ppsType = IOFactory::identify($filepath);

$reader = IOFactory::createReader($ppsType);
$spreadsheet = $reader->load($filepath);

$worksheet = $spreadsheet->getSheet(0);
$highestRow = $worksheet->getHighestRow();

$formatStr = "%3s %20s %15s %15s %35s\n ";

print(sprintf($formatStr, 'row#', 'getValue', 'type', 'formattedValue', 'formatCode'));

for ($row = 1; $row <= $highestRow; $row++) {
	$cell = $worksheet->getCellByColumnAndRow(1, $row);

	$value = $cell->getValue();
	$type = gettype($value);

	$formatCode = $cell->getStyle()->getNumberFormat()->getFormatCode();

	print(sprintf($formatStr,
				  $row,
				  $value,
				  $type,
				  $cell->getFormattedValue(),
				  $formatCode,
	));
}

Which outputs:

row#             getValue            type  formattedValue                          formatCode
   1                Value          string           Value                             General
   2                              boolean           FALSE                             General
   3                    1         boolean            TRUE                             General
   4                 0.05          double              5%                                  0%
   5                40909         integer          Jan-12                              mmm-yy
   6      41275.231944444          double   1/1/2013 5:34                       m/d/yyyy h:mm
   7               45/100          string          45/100                             General
   8                 3140         integer         3.14E+3                            0.00E+00
   9               234.55          double        £234.55    "£"#,##0.00;[Red]\-"£"#,##0.00
  10                12.44          double          $12.44                    [$$-409]#,##0.00
  11               124.55          double         $124.55                    [$$-409]#,##0.00
  12                45028         integer       4/12/2023                            m/d/yyyy
  13                16143         integer      1944-03-12                      yyyy\-mm\-dd;@
  14               0.4545          double          45.45%                               0.00%
  15               0.3221          double         32.210%                              0.000%
  16                0.123          double          12.30%                               0.00%
  17      44693.043055556          double  5/12/2022 1:02                       m/d/yyyy h:mm
  18             12343434         integer   12,343,434.00                            #,##0.00
  19            234235235         integer     234,235,235                               #,##0
  20               123456         integer      123,456.00                            #,##0.00
  21               1.2345          double               1                                   0
  22               1.4567          double            1.46                                0.00
  23             -0.11734          double         -11.73%                               0.00%
  24             -123.456          double        -123.456                             General
  25               -47.12          double         $-47.12                    [$$-409]#,##0.00
  26        -456789.33555          double     -456,789.34                            #,##0.00

There are probably more examples there than necessary :-)

Essentially what I'm looking for is the same ability to get both the raw (typed) value from getValue(), and the format code string from getFormatCode(). With that, anything is then possible in terms of interpretation.

getFormattedValue() would be a nice to have if it is simple, assuming the formatted value is also stored in the XML and not rendered by PHPSpreadsheet / Excel?

excel_formatting.xlsx

[Slamdunk]

As far as I can tell, the xlsx doesn't contain the formattedValue nor the formattedCode infos.

PhpSpredsheet had to hard-code them to have them available: https://github.com/PHPOffice/PhpSpreadsheet/blob/0c37ae2c30599819d7fab7d13ab5027ffd71f61a/src/PhpSpreadsheet/Style/NumberFormat.php#L301

It is not my intention to provide any data that's not already in the files, with this package.

[danatemple]

I agree entirely, there is no point in making that mapping - I would rather interpret directly from the raw numeric code in that case too, and I will update my PHPSpreadsheet-based code to use getBuiltInFormatCode() instead.