Issues with German Custom Date

Aspose.Cells Product Family
1

We are receiving unexpected results when using a custom date format in German.

System setup:

  • Windows settings, go to Clock, Languages and Region and then Region
  • Formats Tab: Format is “German (Germany)”
  • Location Tab: Home location is “Germany”

Launch Excel

  • Select a cell and set formatting to Custom and Type is: TT.MM.JJ
  • Now in the cell type 20.12.2018

Note that after hitting enter it is correct in Excel

The following code shows the issue we are seeing.

    Workbook excel = new Workbook(C:\\aspose-test\\german_date.xlsx");
    try {
        Cells cells = excel.getWorksheets().get(0).getCells();
        Cell cell = cells.get(0, 0);
        System.out.println(cell.getDisplayStringValue());
        System.out.println(cell.getStringValue());
        System.out.println(cell.getStyle().getCustom());

…

The first two provide what I expect, but the last does not. The following is the resulting data that I receive:

20.10.19
20.10.19
dd/mm/yy

Please let me know if you feel I am doing something wrong or if there might be an issue. Thank you!

2

@ibmlittleton,
Could you please try getting the format with this code:

System.out.println(cell.getStyle().getCultureCustom());

Let us know your feedback.

3

Thank you for your reply. I agree that the getCultureCustom returns the expected value. Is getCustom expected to not return the same value? I am not exactly sure how it came up with a different format than was set in my example. I have read the API doc and am uncertain if this is a bug in getCustom and the work around it to use getCultureCustom or if it is expected that we need to check for a culture custom. I am not writing something simple and new. Unfortunately the existing code uses the getCustom and is deeply intertwined in our codebase, so I want to really understand what our options are here.

Thank you very much!

4

@ibmlittleton,
Thank you for your query. We are analyzing the issue and will share feedback soon.

5

@ibmlittleton,
We have logged the issue as “CELLSNET-47215” in our database for investigations. Once, we will have some news for you, we will update you in this topic.

6

@ibmlittleton,

We evaluated your issue further.
Well, it is the designed behavior for Style.Custom. As we declared in the API Docs, the returned value should be “InvariantCulture”. It also means that when user specifies custom format for the style, the input pattern string should be culture-independent for the specifiers. In short, this is not an issue.

7

Thank you very much. I appreciate your help and feedback on this!

8

@ibmlittleton,

You are welcome.

9

We have have attempted to resolve the issue using the getCultureCustom(). However, although getCultureCustom() returns the correct pattern, it does not use the standard specifiers. So we decided to use InvariantCustom, which returns the same pattern with standardized format specifiers.

Your documentation states that: the difference between[CultureCustom] and [InvariantCustom] is: the format specifiers are kept as standard, such as “y” will always be used as the “year” part no matter what other special character is used for the specific locale.
However, for our test case the following has occured
System.out.println(cell.getStyle().getCustom())// ==>>> dd/mm/yy
System.out.println(cell.getStyle().getCultureCustom());// >>>TT.mm.JJ
System.out.println(cell.getStyle().getInvariantCustom()); // >> dd/mm/yy
we can see that invariantCulture did not follow the same pattern as CultureCustom.
We expected the pattern to match cultureCustom with format specifiers are kept as standard
like so: dd.mm.yy

I am attaching the test case for your review.testcase.zip (566 Bytes)

10

@ibmlittleton,

Thanks for the sample code and further details.

I tested your issue (after setting up respective locale) with a sample file and sample code and I noticed the issue apparently regarding InvariantCustom attribute. I have reopened your issue and we will investigate and look into it further.

Once we have an have an update on it, we will let you know.

11

@ibmlittleton,

What you want to get from getInvariantCustom() and what’s wrong with the returned value “dd/mm/yy”? Just as the API document declares, here the specifiers are meant standard (y,m,d) and different from those in getCultureCustom() (T,m,J).

12

For Style.InvariantCustom property, in contrast to Style.CultureCustom property, the difference is whether the format specifiers in the returned pattern sequence is locale-dependent. For example, for some regions the year part should be represented as character other than “y”, such as “j”. Then the value of Style.CultureCustom is “…jj…” but the value of Style.InvariantCustom is “…yy…”. But for the pattern sequence, such as “m/d/y” for some regions and “d/m/y” for some others, the API should return it according to the used locale of the workbook for both Style.InvariantCustom and Style.CultureCustom.

This means that the pattern returned for getCultureCustom and getInvariantCustom should be the same. Therefore, our expectation for getInvariantCustom is for it to return the following pattern
dd.mm.yy

13

@ibmlittleton,

Thanks for providing us details.

We will evaluate it and get back to you soon.

14

@ibmlittleton,

Please note, in Aspose.Cells API document, we did not declare what kind of separator will be used for getInvariantCustom(). However, for MS Excel, the standard separator of DateTime pattern is ‘/’. So it is not an issue for the returned “dd/mm/yy” for this method. For your special scenario and requirements, we think you should parse the invariant custom pattern by yourself and replace the standard separator with the one you need to use.