Fixing the roundtrip of Cell FormatCode (implementation-defined) for Time/Calendar type

[svanteschubert]

A FormatCode is being used in ODF for Cells, which is implementation-defined by the ODF applications:

from https://docs.oasis-open.org/office/OpenDocument/v1.4/OpenDocument-v1.4-part4-formula.html#TEXT

For instance,

1. Excel defines it here: https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5 and their example of a FormatCode
   "[Blue]#,##0.00_);Red;0.00;"sales "@"
   will result in a positive integer being blue 1234.5 will be displayed as 1.234.50 in blue, while the same as a negative number with brackets in red.
   Any string data will receive a sales prefix.
   I was able to add this string in LibreOffice 24.8.6.2 in the GUI for custom formatting of a cell, and it worked!

2. LibreOffice defines it here: https://help.libreoffice.org/24.8/en-GB/text/shared/01/05020301.html?&DbPAR=CALC&System=WIN

Reading the above documentation, there is no difference between yyyy and YYYY or dd and DD - all calendar characters have been provided in capital letters in the documentation of LibreOffice, which we might want to align by default as we are part of the same provider ( TDF)!
Please correct me if I misinterpreted this.

In our test class TableCellTest.java, the test testSetDefaultCellStyle() was disabled as it was not being round-tripped correctly.

In addition, the implementation of DD and dd is currently different in ODFDOM, while DD is the day of the month, dd is the day of the year, which should be fixed to the day of the month!

Please consider that the FormatCode has been changed, and the latest version of LibreOffice should be used for updating/validating the test documents.

[svanteschubert]

The format codes are being transformed to ODF XML nodes, and when reading them, we might want to return all calendar symbols as capital letters and accept both kinds.

[xzel23]

I don't think it is even possible to always accept both forms and knowing what is meant.

For some fields, upper- and lowercase symbols are used but with different meanings, like 'h' and 'H', 'd' and 'D', 'y' and 'Y'. While for 'm' and 'M' this can be derived from context, the other cases cannot really be distinguished.

And when a user wants to only display only the month of a datetime field, how would you do that? Maybe "MMT", but I think that's fragile and error prone. (I have seen that in Timesheet for tracking working hours)

But I know where you are coming from. In LO, only uppercase letters are presented to the user. We could use the strict parsing first and then try a more lenient approach when that fails.

Forget the above suggestion. It seems for compatibility we should do this the other way around: add additional methods that use the ISO format or convert to and from the ISO format. The description of what LibreOffice expects is quite good. I have cloned your PR and can prepare something, but I will probably need some days as my day job is calling.

[dennisroczek]

Am 20.05.25 um 07:12 schrieb Axel Howind:

But I know where you are coming from. In LO, only uppercase letters are presented to the user. We could use the strict parsing first and then try a more lenient approach when that fails.

That is wrong. In LibreOffice Calc you can use the letters with the correct caps in the format cell dialog.

[xzel23]

That is wrong. In LibreOffice Calc you can use the letters with the
correct caps in the format cell dialog.

I didn't say you cannot use lowercase letters (I don't know if you can), I said it "presents" you the format string in all uppercase. At least in my version of the program, the format strings of all predefined formats only use uppercase letters, for example ISO 8601 is shown as JJJJ-MM-TT"T"HH:MM:SS,000 (I obviously use the german locale), which results in a ISO 8601 conformant output but is not a valid ISO 8601 compliant format string (even if you replace J with D and so on).