Currency display style number

Aspose.Cells Product Family
1

In Excel, I applied the following currency format for a cell

Decimal places:0
Currency Symbol : $
Negative numbers : -$1,234

I expected to get the style number for that cell as 5 using Style object. ( “5 Currency $#,##0;$-#,##0” provided in documentation under aspose built-in formats) . But I got 0 (General). But I get 5 when I apply ($1,234) format for Negative numbers. Why is that so ?
2

Hi,

Well, I think you are referring to the table in the document here:

Data Formatting

Here if you could see the 6th record, it is as following:

5 Currency $#,##0;$-#,##0

I am afraid, your desired formatting you specified at the top is not this one, it is actually “$#,##0” and no entry in the table matches this for Number’s value. So, you are getting 0 (General) for Style.getNumber() method. FYI, the table does not cover all the possible constants for Number attribute, it covers a few formattings only.

We recommend you kindly use Style.getCustom() to get your desired formatting.

For confirmation what I have told you above, I have attached a template file and here is my sample code to evaluate it:

e.g

Sample code:

Workbook workbook = new Workbook(“Bk_Currency1.xlsx”);

System.out.println(workbook.getWorksheets().get(0).getCells().get(“A1”).getStyle().getCustom()); //“$#,##0” - Ok

Thank you.

3

Thank you for the clarification. I tried the following case.

For the following custom format applied I get style number 5,
$#,##0_);($#,##0)

But I get 0 for the following format,
$#,##0;$-#,##0 (given in the table)

Can you please clarify this part ?




4

Hi,


Thanks for providing us further details.

I observed the issue as you mentioned. By setting the custom
format string “$#,##0;$-#,##0” to a cell does not return correct
Built-in Numbers format (via using Style.getNumber()) as you pointed out. I have attached the input Excel file and here is my sample
code to show the issue:


e.g


Sample code:


Workbook workbook = new Workbook("Bk_Currency1.xlsx");
System.out.println(workbook.getWorksheets().get(0).getCells().get("B1").getStyle().getNumber());
System.out.println(workbook.getWorksheets().get(0).getCells().get("B1").getStyle().getCustom());

I got the output as following:
0
"$"#,##0;"$"\-#,##0

I have logged a ticket with an id "CELLSJAVA-41230" for your issue. We will look into it soon.

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

Thank you.
5

Hi,


We have evaluated your issue further.
Well, for setting numbers format by Style.Custom/CultureCustom, we do not check whether a user’s input format pattern matches one of those present in built-in formattings list. It is mainly due to performance considerations. For built-in formattings, the format pattern string varies for different regions as you know that. To check the matching one, we have to check all the built-in formattings corresponding to specific region, it is surely a time-consumed operation, especially when users set style in this way many times. On the other hand, there is no difference for you to get the formatted cell value (in MS Excel or by program with Aspose.Cells APIs) when setting the custom format or built-in format to a style object. So we do not think we should identify user’s input pattern string to corresponding built-in number automatically with the degradation of performance (which is expensive you know).

Thanks for your understanding!
6

Thanks for the detailed explanation.

I have to verify if a cell contains a currency value. If the style number equals any of the built-in formats then further analysis is not required. Also $#,##0;$-#,##0 is a commonly used format.Recognising it will be of great use. All the other built-in formats are working perfectly fine for me. If a fix is possible, please let me know.
Thanks in advance.
7

Hi,


We will check if we could fix the issue regarding your particular formatting only.
If there is some further update on it, we will let you know here.

Thank you.
8

Hi,


We have evaluated your issue further.
After investigation, we found “$#,##0;$-#,##0” does not match any built-in number formatting of MS Excel. The similar pattern corresponding to built-in number format (en_US region) is “$#,##0_);($#,##0)”. We may consider to provide an API for you to set the style as built-in when you set the custom formatting as “$#,##0_);($#,##0)” if it fits your requirements. Or, if you are sure that with your specific region settings MS Excel does take “$#,##0;$-#,##0” as built-in number formatting, please let us know your region/locale settings used in MS Excel and send us one Excel template file with this built-in number formatting, so we could make further investigations.

Thank you.