Phpspreadsheet: Format date not displayed in ods file

[jeanmich82]

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?

I'm trying to format a datetime in a cell. It doesn't work with ods file but same code with Xlsx writer works perfectly.
Cell with
11/09/2018 17:05

What is the current behavior?

43354,7118055556

What are the steps to reproduce?

<?php

$sheet->setCellValueByColumnAndRow(4, $ligne, \PhpOffice\PhpSpreadsheet\Shared\Date::PHPToExcel($myobject->getDate()));
$sheet->getStyleByColumnAndRow(4, $ligne)->getNumberFormat()->setFormatCode('dd/mm/yyyy hh:mm');

$writer = IOFactory::createWriter($spreadsheet, 'Ods');
$fileName = 'myfile.ods';
$temp_file = tempnam(sys_get_temp_dir(), $fileName);
$writer->save($temp_file);

But it works with
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$fileName = 'myfile.xlsx';

Which versions of PhpSpreadsheet and PHP are affected?

PhpSpreadsheet 1.4.0
PHP 5.6

[julienfastre]

I encounter the same issue with PHP 7.2

phpoffice/phpspreadsheet 1.4.0
PHP 7.2.11

[julienfastre]

This issue persists with version 1.5.0.

[vince2corte]

Same issue and number format doesn't work either (FORMAT_PERCENTAGE for instance). It seems that ods format shows only the internal representation of numbers (including dates), but not the formated value.
On the other way, xlsx, xls and csv writers work perfectly.

phpoffice/phpspreadsheet 1.6.0
PHP 7.2.10

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[ChristophWurst]

This is still an issue in the latest version.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs.
If this is still an issue for you, please try to help by debugging it further and sharing your results.
Thank you for your contributions.

[jeanmich82]

Still issue

[NiavlysB]

I encountered this issue as well (PhpSpreadsheet 1.16.0, PHP 7.4), with the percentage format. It works for the Xls writer as a format, but I guess Ods works differently.

For a cell with the value "53.57%", the AdvancedValueBinder changes it to 0.5357, and sets a number format in the styles.

In the case of Ods, it should generate an XML node like this:

<table:table-cell table:style-name="ce4"
  office:value-type="percentage"
  office:value="0.5357"
  calcext:value-type="percentage">
    <text:p>53,57 %</text:p>
</table:table-cell>

Is there a fundamental issue with these points?

• the number format is not defined as a style, but on the cells themselves (office:value-type and calcext:value-type)
• the original value (before the AdvancedValueBinder modified it, divided it by 100% in the case of percentages) must be retained and outputted in the cell
• the modified value (0.5357) must also be outputted, this time in the office:value attribute