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

Aspose.Cells Product Family
14

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

15

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)

16

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

17

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!

18

@ibmlittleton,

We are looking into it and will update you soon.

19

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

20

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?

21

@ibmlittleton,

Thanks for providing further details.

We have logged your findings against your issue into our database for investigation. We will look into it thoroughly and get back to you with more details once there is some new information/update to be shared.

22

@ibmlittleton,

You do not need to traverse all cells in the workbook. For MS Excel’s built-in number formats, the count of them is less than 60, so you just need to cache an array (whose length can be about 60 and index of it corresponds to built-in number) of the built-in formatting patterns corresponding one specific locale. The code like:

Workbook workbook = new Workbook();                
workbook.Settings.Region = CountryCode.USA;                
Style s = workbook.CreateStyle();                
string[] res = new string[60];                
for (int i = 1; i < res.Length; i++)
{                    
    s.Number = i;                    
    res[i] = s.InvariantCustom;                
}

Then, you can use this array to get the custom string for cells in your workbook by checking cell’s style.Number. Please note, if style.Number is 0, then the number format may has not been specified or has been set as custom string.

23

We would like to understand why the CultureCustom property was changed between versions 17.6.3 and 18.7. We would like Aspose to understand that this is a breaking change for us, and now requires us to write additional code and cache arrays and hope that there are no edge cases requiring the real CultureCustom. Why was this changed and where is the documentation to support this? Frankly, our upper management is questioning our use of Aspose because time and again we take in new jars and get breaking changes like this with no way of using older functions as replacements. This is not how new software should be released, by breaking consumers. Please give me the contact information of a Product Manager as per our enterprise license because we would like to escalate this issue further. Right now, we are highly dissatisfied with this solution and lack of reasoning.

24

@ibmlittleton,

We are analyzing your comments and will provide our feedback soon.

25

@ibmlittleton,

The reason is just as we have replied you here: Wrong Culture Custom Format gets returned (Germany, Spain, French, and Italy) in Java - #7 by amjad.sahi
There are too many different settings for different locales so we cannot support all of them at a time. For example, the formatting patterns for Italy, it was not supported in older versions. For unsupported locales, the CultureCustom will give the result same with en_US locale. It is a feature in evolution and will be enhanced by supporting more and more locales according to users’ requirements. And, it is just what happened for locales like Italy/German, the built-in formatting for this locale was required by our customers later, so we supported it in newer versions.

For such kind of change, we usually declare it in the release notes and the change is only attached to corresponding (specific) issue ids which may be posted as a bug (e.g users find the formatted results for the specific locale where their need is not the same with what is shown in MS Excel, then they will report it as a bug) or an enhancement (users inquire whether we can format some cells correctly for some specific locales and we find those locales have not been supported yet, then we will create one ticket as new feature or enhancement to support users’ requirements).

Thanks for your understanding!

26

Hi Amjad,
Please post a link to the documentation and release notes on this change so that my team can read it and fully understand it. Thank you.

27

@ibmlittleton,

We will check if we could find and provide you some issue ids/title in the release notes or thread urls (if these are not private). But as we told you that such a change is posted as a bug (e.g users find the formatted results for the specific locale where their need is not the same with what is shown in MS Excel, then they will report it as a bug) or an enhancement (users inquire whether we can format some cells correctly for some specific locales and we find those locales have not been supported yet, then we will create one ticket as new feature or enhancement to support users’ requirements).

28

We evaluated the option you suggested using Excel’s built-in number formats but this doesn’t work for us.

The entire number format code we currently provide to our customers in the product is based on the culture formats you provided in the version of 17.6.3 and prior,

For instance, following are the formats that are returned (for excel build-in number #38)

Locale code Culture formats returned in version of 17.6.3
IT #,##0_);Red
FR #,##0;[Red]-#,##0
DE #,##0;[Red]-#,##0
ES #,##0_);Red
JA #,##0;[Red]-#,##0
EN #,##0_);Red
…
formats_returned_in_17.6.3_we_use_in_the_product.png (3.6 KB)

As they are shown above, those formats vary according to locales, which our product has been written based on.

After we upgraded to the version of 18.7/18.10, the entire thing broke.There is no way for us to get these same formats back from new versions. It breaks all of our foreign language customers.

Is there any way you can bring back or retain the same functionalities in new versions of releases?

Will you please help us with this?

I appreciate your support.

29

@ibmlittleton,

Thanks for providing us further details with screenshot.

I have logged your details with screenshot against your issue into our database. We will evaluate your issue thoroughly and get back to you soon.

We are sorry for any inconvenience caused!

30

@ibmlittleton,

We are afraid, we cannot rollback any supported/improved features to unsupported ones. For your situation, if you persist the old formatting patterns(even though they are “incorrect”), we think you have to export all formatting patterns of those locales you provided to your customers with 17.6.3. Then you can use those exported content as static data for your software instead of fetching them from new versions of our component dynamically.

For your requirement on the announcements of such kind of changes, as an example, please see the post Display cell string value as Exel - #4 by amjad.sahi which requires the correct formatting for locale Italy by our customer. Also, a bug fix (attached with the ticket CELLSJAVA-42355) was listed in the release notes of 17.8 version.

31

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?

  1. How does Style.InvariantCustom differ from Style.Custom?

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

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

Can you please advise? Thank you in advance.

32

@ibmlittleton,

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

33

@ibmlittleton,

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.

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

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.