Wrong Culture Custom Format gets returned (Germany, Spain, French, and Italy) in Java

Hello.

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

You will see that the formats get returned is #.##0 ;Red-#.##0 _ while the expected format is #,##0);Red(#,##0).

​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 culture custom format #,##0_);Red(#,##0) is returned as expected.

We need this fixed in the Java version asap.

Thank you for your assistance.

public static void printCultureCustomFormats() throws Exception{
    String dir = "C:/asposetest";
    Locale locale = new Locale("it");
    Workbook workBook = new Workbook(dir + "/NumberFormat_IT.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?

NumberFormat_IT.zip (7.2 KB)

@ibmlittleton,

Thanks for the sample code and details.

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

Hi Amjad,

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

Please also note that the same issue is found with Italy as well as Germany, Spain, and French. There isn’t an issue back in the version 17.6.3.

Any additional information you need that i can provide, please just let me know.

Thanks

Anything else

@ibmlittleton,

Thanks for the template file and further details.

After an initial test, I am able to observe the issue as you mentioned by using your template file and sample code. I found that wrong culture custom format is returned for different locales (Germany, French, Italy and Spain). I have logged a ticket with an id “CELLSJAVA-42768” for your issue. We will look into it soon.

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

Hi Amjad,

Do you have any update on this issue?

Can you please upgrade this issue to move it to the Enterprise Support forum?

It is a regression in the version of 18.7 Aspose.Cells for Java and onwards. This impacts a number of our customers. Can you please help escalate this issue to enterprise and get us a fix as soon as possible.

Thanks for your support!

@ibmlittleton,
We are discussing this requirement and will update you soon.

@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.

Also, we have tested the display formatting in MS Excel manually for different locales. Please see the attached screenshots. Custom_IT.png is what is shown by MS Excel with the set region of Italy for one of our machine. And Custom_US.png is the result when change machine’s region to en-US. We think our component gives the correct results.
Custom_IT.png (10.2 KB)
Custom_US.png (12.2 KB)

Thanks for your response.

In this case, Style.Custom doesn’t return anything. We are blocked here.

Can you please advise?

Thanks for your support!

@ibmlittleton,

You are right, I can see Style.Custom attribute gives blank value in your case. We need to evaluate your issue thoroughly and quickly as you have now availed Enterprise support service for this issue. Hopefully we will figure your issue out soon.

@ibmlittleton,

The formatting of those cells was set as built-in and it is designed for Style.Custom that it will return empty for builtin number formats. For your requirement, we now plan to provide another property Style.InvariantCustom, which is corresponding to Style.CultureCustom but it will give the culture-independent custom pattern instead (including the pattern string for builtin number formats).

Hope, this will suit your needs.

@Amjad_Sahi.

That sounds good. I am looking forward to giving it a shot when the new jar is avalilable.

Thanks for your support!

@ibmlittleton,

Since you have availed Enterprise support service for this issue, so you should be getting the fix soon.

@ibmlittleton,

This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-42768”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

The issues you have found earlier (filed as ) have been fixed in Aspose.Cells for Java 18.12. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi

Hi Amjad_Sahi,

Thanks for the quick turnaround.

I downloaded the new version 18.12 and gave it a shot. Here are a few issues i found:

  1. Style.InvariantCustom property doesn’t return as expected
    for instance it returns #,##0 _¤;[Red]-#,##0 _¤ while the expected format is attached in this post.

  2. Cells.importArrayList(…) gives compiler error “Unhandled exception type Exception” but this isn’t documented in the releases notes.

Can you please advise?

Thanks for your support!
expected_InvariantCustom.PNG (728 Bytes)

@ibmlittleton,

  1. Please do not set the locale, see the following sample code which works as expected:
    e.g
    Sample code:

    String dir = “f:\files”;

         Workbook workBook = new Workbook(dir + "/NumberFormat_IT.xlsx");
         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()+" Invariant Culture Custom: "+cell.getStyle().getInvariantCustom());
                 }
             }
         }
    
  2. Please create either a new thread with all the details, sample code, template file(s) and screenshots to reproduce the issue or provide the required artifacts here, we will check it soon.

Hi Amjad_Sahi,

  1. “Not setting locale” is not acceptable. Locale setting is critical for the application to work properly. Unlike getCultureCustom() method, getInvariantCustom() should work regardless of Locale setting.

Can you please look it into?

Thanks for your support!

@ibmlittleton,

We are looking into it and will update you soon.

@ibmlittleton,

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, we return it according to the used locale of the workbook for both Style.InvariantCustom and Style.CultureCustom.

For parts of the pattern sequences other than format specifier, they depend on many other global caches of locale information. Rebuilding those caches every time when user is getting some property like Style.InvariantCustom is unacceptable for performance considerations. To get the built-in pattern sequence in completely “standard” format, you should change the workbook’s locale to en-US and gather Style.InvariantCustom values together.

Thanks for your understanding.

Hi Amjad_Sahi,

Thanks for your reply.

The application we build often deals with the workbook in which it contains number of cells up to 140K ~ 9000K. If we gather and cache Style.InvariantCustom values for each of the cell and do the matching for each of them later on, it’d result in huge performance degrade and high memory consumption.

So we are stuck here.

Anything you can do from your end to help us out on this?