getFormattedValue(): invalid format value in some cases

[biyun233]

This is:

- [x] a bug report
- [ ] a feature request
- [ ] **not** a usage question (ask them on https://stackoverflow.com/questions/tagged/phpspreadsheet or https://gitter.im/PHPOffice/PhpSpreadsheet)

What is the expected behavior?

value : 1 format code : 0/0 formatted value : 1/1 (as displayed in Excel)

What is the current behavior?

value : 1 format code : 0/0 formatted value : 1 (string)

What are the steps to reproduce?

Please provide a Minimal, Complete, and Verifiable example of code that exhibits the issue without relying on an external Excel file or a web server:

test file : https://docs.google.com/spreadsheets/d/1OJAqntQX5S6EjcgFbY7iNEzwDZXKumeiSCyOhbPisU0/edit#gid=0

<?php

require __DIR__ . '/vendor/autoload.php';

// Create new Spreadsheet object

$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load($filename)->getActiveSheet();

echo $spreadsheet->getCell('A1')->getValue();
// 1
echo $spreadsheet->getCell('A1')->getStyle()->getNumberFormat()->getFormatCode();
// 0/0
echo $spreadsheet->getCell('A1')->getFormattedValue();
// 1

Which versions of PhpSpreadsheet and PHP are affected?

PHP : 8.0.2 PhpSpreadscheet : 1.29.0

[oleibman]

Confirmed. Interestingly, if the format is 0 0/0, Excel's formatted value will be 1 0/1; but, if the format is ? ??/???, which certainly seems similar, Excel's formatted value will be 1. PhpSpreadsheet's test suite tests for the latter, but not for the former nor your test case. Format ??/??? behaves similarly to your 0/0. It could be tricky trying to figure exactly when Excel does and does not display a fraction for integers.

[oleibman]

Lots of minor discrepancies between Excel and PhpSpreadsheet for the fraction formatting test. This could take a while.

[biyun233]

Thanks for your reply :)