I’m pondering about how to obtain complete information about numerical values in cells.
Let’s suppose a cell contains 12.34567 and in Excel I set its formatting properties to be an accounting type with 3 decimal places and $ as the currency symbol.
How do I:
Discover it is an accounting type cell? In Aspose, the style associated with the cell returns that it is a general cell rather than accounting.
Discover the number of decimal places?
Discover the currency symbol?
Get the number 12.346, correctly rounded to the number of decimal places set in the formatting?
A style can give a custom and culture custom string back which encodes some information about the format of numbers/dates.
What is the difference between custom and culture custom?
What are the exact syntax and semantics of these strings?
Well, MS Excel's Numbers formatting is a very complex feature on its own, it might involve multiple locales or other aspects, so it would be tough to determine your desired tasks/ queries etc. accurately. As your queries involve diverse scenarios, so you might not always get accurate results or expected results for certain scenarios.
For your queries/ tasks:
mrossi:
Discover it is an accounting type cell? In Aspose, the style associated with the cell returns that it is a general cell rather than accounting.
Well, you may try to use Style.Number and Style.Custom attributes. But, I am afraid there is no better way to determine whether one pattern used for formatting numbers is accounting or not. For Style.Number, it should be accounting when the number returned is 5,6,7,8,42 or 44, but for custom string, I am a afraid, you have to check whether it denotes accounting by yourself.
mrossi:
Discover the number of decimal places?
Discover the currency symbol?
Well, I think you can use DisplayStringValue attribute of Cell and then use your own Java code to find or determine the currency symbol and decimal places, there is no such APIs in Aspose.Cells component. Please note, generally, it can be used to determine to do the task but for some special cases, such as 1.2, if formatting pattern is "#.###", the resultant string is "1.2". So, we are not sure whether you want the decimal places should come as 1 or 3.
mrossi:
Get the number 12.346, correctly rounded to the number of decimal places set in the formatting?
You might try using Excel's Round formula or any other relevant formula/ function upon the value when extracting it from your desired cells.
mrossi:
What is the difference between custom and culture custom?
What are the exact syntax and semantics of these strings?
Well, Custom attribute denotes the formatting pattern in en-us locale, such as "$#,###.#". CultureCustom is the formatting pattern corresponding to one specified locale, such as for fr-FR locale, in MS Excel, you will see the pattern is like "$# ###,#" instead of "$#,###.#". CultureCustom is provided for the convenience of the users to get/set style as they are normally familiar with some special locales, however, it is not or cannot be fully supported for all locales and Style.Custom is always the recommended way to get/set formatting patterns.
PS. We also recommend you to kindly try our latest version/fix: Aspose.Cells for .NET v8.6.0, we made more enhancements regarding Numbers formattings.
Indeed, Excel’s number formatting is complex. In the meantime, I tried to understand what happens by a combination of working in Excel, reading what appears on the .xlsx file and what Aspose.Cells returns and I have now a clearer picture.
Unfortunately, for me this means to implement methods to parse the Culture/Custom strings to get the number of decimal places, the currency, etc. and/or to parse the displayString to get the rounded number and other information. Parsing strings is not what I expect from an object-oriented framework like Aspose.Cells. However, I also understand that if I’m the only customer with such needs, it is my duty to implement them for myself.
It seems your issue is resolved. Do you still need anything from Aspose.Cells behalf? If you have any questions, please feel free to ask us, we will look into it and help you asap.
well, technically speaking the issue is not resolved.
However, I don’t expect Aspose to resolve the issue any time soon. The ideal would be for Apose to provide a richer API to manipulate numerical values better, with more control.
1. Discover it is an accounting type cell 2. Discover the number of decimal places
As you know, ms excel’s formatting feature is very complex and it is not so easy to give distinct flag for some specific part. The flag may be distinct for some formatting pattern, but may be meaningless or even ambiguous for some other formatting patterns.
As a workaround, we think you can try the formula =CELL(“format”,refenceCell) where referenceCell is the cell you want to get its formatting. By the calculated result of this formula, you can determine formatting details easier than parsing the custom string. For CELL formula and meaning of its returned value, please see ms excel’s help document for more details.
3. Discover the currency symbol
If you got “C…” from CELL formula, then the formatting should have currency symbol. Then you can search the symbol from Style.Custom or Style.CultureCustom. For Style.CultureCustom, you can use it when Style.Custom is empty but Style.Number != 0.
4. Get the number 12.346, correctly rounded to the number of decimal places set in the formatting
You can just get the formatted result by Cell.StringValue.
mrossi: