oleibman 39511e03b5 Ods Handling of Ceiling and Floor ...

Fix #407, which went stale over 7 years ago, and is now reopened. Ods handling of CEILING and FLOOR functions is, apparently by design, subject to interoperability problems with Excel. See [FLOOR](https://wiki.documentfoundation.org/Documentation/Calc_Functions/FLOOR) and [CEILING](https://wiki.documentfoundation.org/Documentation/Calc_Functions/CEILING). Ods implements its own `CEILING.XCL` and `FLOOR.XCL` functions for interoperability purposes. PhpSpreadsheet will implement those, and `CEILING.ODS` and `FLOOR.ODS` psuedo-functions as well; none of these four will be listed in the official Functions documentation.

In all of the descriptions below, FLOOR follows CEILING exactly.

Ods writes its functions to Xml as follows:
- CEILING.XCL becomes COM.MICROSOFT.CEILING
- CEILING.MATH becomes COM.MICROSOFT.CEILING.MATH
- CEILING.PRECISE becomes COM.MICROSOFT.CEILING.PRECISE
- CEILING remains unchanged

PhpSpreadsheet Ods Writer will do the same, plus:
- CEILING.ODS becomes CEILING

PhpSpreadsheet Ods Reader will act as follows:
- CEILING (not preceded by 'COM.MICROSOFT.') becomes CEILING.ODS
- COM.MICROSOFT.CEILING.MATH becomes CEILING.MATH
- COM.MICROSOFT.CEILING.PRECISE becomes CEILING.PRECISE
- COM.MICROSOFT.CEILING becomes CEILING

PhpSpreadsheet Xlsx Writer will act as follows:
- CEILING.ODS becomes CEILING.MATH
- CEILING.XCL becomes CEILING

PhpSpreadsheet Xls Writer will recognize only the CEILING and FLOOR functions.

Note that the only difference between Ods CEILING and CEILING.MATH is that CEILING will return an error if the number and significance operands have opposite signs. PhpSpreadsheet implements this as `#VALUE!`.

2025-05-10 21:40:28 -07:00

11 KiB

Raw Permalink History

View Raw