Custom format

Aspose.Cells Product Family
1

Dear Laurence,

We have found a bug regarding custom formats in Aspose Cells for Java.

We are generating a report containing 40 different Styles that are reused for the different cells of the report. The Styles contains the following different custom formats:

Custom: null

Custom: #,##0

Custom: #,##0.0

Custom: #,##0.00

Custom: #,##0.000

Custom: #,##0.0000

Custom: #,##0.00000

Custom: #,##0.00000

Custom: #,##0.00000


However, during the write process… something happens with the custom formats in the cells of the excel file.
Just before calling write we print the custom format for a specific cell using:

System.out.println("Custom format: " + iWorkbook.getSheet(0).getCell(8, 0).getStyle().getCustom());
This prints out:
Custom format: #,##0


We then immediately open the generated file with Aspose again and print the custom format of the same cell again:
System.out.println("Custom format: " + iWorkbook.getSheet(0).getCell(8, 0).getStyle().getCustom());

This prints out:

Custom format: null

Somehow the format gets lost.
If we open the file in Excel that cell contains the custom format #,##00000. This is also the case when we open the file with a different commerical excel api.

I sent an example file that shows this behaviour directly to you Laurence, since it contains sensitive information. Unfortunately I haven’t been able to write a small example that reproduces this example. In this case we are not using any template (the file is created from scratch using Aspose Cells)

The problem does not happen for all of our reports but for complex reports with a lot of different formats.

Thanks and best regards,
Ulf

2

Hi,

Please try this fix.We have fixed the bugs of the custom format and the auto filter.

3

Dear Warren,

Thanks for the quick fix. The autofilter issue is completely solved and the custom formats seems to be correct when viewed with Excel. However, when loading the generated file with Aspose the custom format is still null. This is a minor issue but it would be great if this could be fixed as well.

Thanks and best regards,
Ulf

4

Hi,

Which custom formats are still null when loading the generated file with Aspose , all or some ?

5

I’m not sure. Perhaps it is just an optimization from your part. The format I noticed matches one of the builtin formats. Perhaps this explains it. So don’t put any work on this before I have verified that it actually is loosing formats.

Thanks,
Ulf

6

Hi Ulf,

There are two methods to get number format: Style.getCustom and Style.getNumber. When Style.getCustom is null, please check Style.getNumber. In your case, Style.getNumber must return some value other than zero.

If you see custom format string in MS Excel but get null using Style.getCustom method, generally it's caused by local settings. It's OK. You can get a value other than 0 from Style.getNumber method.

It's a bug only when you find Style.getCustom is null and Style.getNumber is 0 but the number format is not "General" in MS Excel.

7

Dear Laurence,

Thanks for your suggestions. I became a father for the second time last week so I have been out of the office and I will not return until next week. I’ll get back to you then.

Thanks and best regards,
Ulf

8

Congratulations!