Wrong Date Custom Format (for Germany locale) gets returned

Aspose.Cells Product Family
1

Hello.
​
​We are running into an issue where Aspose.cells for Java is returning the wrong Date Culture Custom format to us. Please use the attached DateFormat_DE.xlsx workbook and the following code to print out the culture date formats.

You will see that the formats get returned is “TT.MM.JJJJ” while the expected format is “dd.MM.yyyy”.
​
​This issue is found in the version of 18.7 of Aspose.Cells for Java we are currently using as well as in the latest version of 18.11.

The same test case works fine in version of 17.6.3, where the date culture custom format “dd.MM.yyyy” is returned as expected.

We need this fixed in the Java version asap.

Thank you for your assistance.

public static void printDateCultureCustom() throws Exception{
    String dir = "C:/asposetest";
    Locale locale = new Locale("de");
    Workbook workBook = new Workbook(dir + "/DateFormat_DE.xlsx");
    workBook.getSettings().setLocale(locale);
    Worksheet sheet = workBook.getWorksheets().get(0);
    Cells cells = sheet.getCells();
    
    for(int i = 0; i < cells.getMaxDataRow()+1; i++){

        for(int j = 0; j < cells.getMaxDataColumn()+1; j++){
            Cell cell = cells.get(i, j);
            if(cell.getValue() != null) {
                System.out.println("Cell value: "+cell.getValue()+" Culture Custom: "+cell.getStyle().getCultureCustom());
            }
        }
    }
}

P.S. it doesn’t let me upload a file with an extension .xlsx. Is there an option to provide you a copy of the xlsx file that is used in the test code above?

DateFormat_DE.zip (6.3 KB)

2

@ibmlittleton,

Thanks for the sample code and details.

We need your template file to evaluate your issue precisely. Please zip your template file and attach it here, we will check it soon.

3

Hi Amjad,

Thanks for your prompt reply. I’ve attached the template file above in the post.

If anything else you need from my end, pls just let me know.

Thank you

4

@ibmlittleton,

I have evaluated your scenario/ case a bit. I set German (Germany) regional/locale settings using the control panel and then opened your template file into MS Excel manually. I check the format of the cell, it shows “TT.MM.JJJJ” as custom format, see the screenshot for your reference:

so, I think Aspose.Cells is giving expected results.

5

Hi @Amjad_Sahi,

Thanks for looking it into and get back to us.

The return of the format “TT.MM.JJJJ” breaks our existing date formatting logic.

In prior version e.g. version of 17.6.3 This custom format is returned as “dd.MM.YYYY” instead of “TT.MM.JJJJ”.

Can you please help in explaining why this change is introduced?

Thanks for your support.

7

@ibmlittleton,

For CultureCustom, culture means it represents the culture depended custom pattern for formattings. However, it is hard for us to support all regions at a time. And the culture date patterns for some locales such as Italy were not supported in older versions. In recent versions we have supported some other locales for CultureCustom according to some other users’ scenario and requirement. To get the standard formatting pattern, please use Style.Custom instead of CultureCustom.

8

Hello,

Given all the discussion above we’ve decided to rewrite the number formatting logic in our product to address this matter.

Before we can proceed Can you please help provide the details of followings?

How does Style.InvariantCustom differ from Style.Custom?

Are Style.InvariantCustom values a superset of Style.Custom values?

Will there be any changes to the values of Style.InvariantCustom and Style.Custom going forward?

Can you please advise? Thank you in advance.

9

@ibmlittleton,

We will get back to you soon with details/answers for your queries.

10

@ibmlittleton,

See the answers for your queries.

  1. Style.Custom only returns the custom pattern string specified by you. For built-in number formats, it will return null. This property can also be used to check whether the formatting has been set (Style.Number is not 0 or Style.Custom is not empty). Style.InvariantCustom will return the same value with Style.Custom when the custom has been specified by you (Style.Custom is not empty). But for built-in number formats, Style.InvariantCustom will return the corresponding pattern string according to Workbook’s region. Here the “Invariant” means the specifiers in the pattern string are invariant, such as, ‘y’ represents the year part, ‘m’ represents the month part, …etc. Please see the corresponding API doc for reference.

  2. Yes, according to our reply of question 1, values of Style.InvariantCustom contains all values of Style.Custom in one workbook.

  3. We will not change the returned value of Style.Custom in future except the situation that there is some bug for parsing the input custom string. For example, when loading template files, user specified custom pattern will be set as this property. If there are some bug for parsing the custom pattern, we may fix it in future and then for the same template file you may get different value of this property for the same cell’s style (the value of new version should be the correct one).
    For Style.InvariantCustom, it may be changed with more possibility. As we have said, we cannot support all built-in formats for all locales at a time. When we found some special locale should be supported with different formatting pattern from the standard one, we will make such kind of enhancement and then the returned value of Style.InvariantCustom will be changed.