How to identify that cell has Currency format?

Aspose.Cells Product Family
1

I’m writing Excel file reader. It should extract currency information from sheet.
How to detect that cell is cell with currency value?

I’ve tried to use Cell.Type but Enum CellValueType (result of Cell.Type) does not contain currency type.
Cell.Style doesn’t return useful information also. I suspected that cell with Currency category in Excel would have style.Number = 5, but instead it is 0 and cell has setup Custom format “[$$-409]# ##0,00” that is not useful for me because source excel file can be with any culture and custom format can vary greatly.

Also, it would be nice to receive valid currency symbol from cell’s value.

2

@23W,

Thanks for the query.

Well, Display Number formats in MS Excel are divided into two categories (see the document for your reference).

1 - Built-in Formats (Style.Number will be >0 and may approx. upto 60)
2 - Custom Format (Style.Number will be 0)

Please note if Style.Number is 0 for certain cell, then it means, you should be dealing with Custom Format (the cell is custom formatted in the sheet ), in this case, you will use Style.Custom property.

For exercise, you can always create Dates & Number formats in Microsoft Excel and then load them and check the Style.Number and Style.Custom properties in Debug or Quick Watch.

Please see the following sample code. It should help you in understanding the difference related to Dates & Number formats. You can try using with your template file:
e.g
Sample code:

[C#]
Workbook wb = new Workbook("sample.xlsx");

Worksheet ws = wb.Worksheets[0];

Cells cells = ws.Cells;
//checking first column cells
for (int i = 0; i < 10; i++)
{
    Cell c = cells[i, 0];

    //Read cell number format
    Style st = c.GetStyle();

    if (st.Number == 0)
    {
        Debug.WriteLine(c.Name + "----" + st.Custom);
    }
    else
    {
        Debug.WriteLine(c.Name + "----" + st.Number);
    }

}

wb.Save("output.xlsx");

Moreover, you may also make use of Style.InvariantCustom and Style.CultureCustom property. This will give you the difference whether the format specifiers in the returned pattern sequence is locale-dependent or not.

Hope, this helps a bit.

3

Thank you for answer, but can you explain this behavior:
Let look at cell Format Example:
e1.png (23.8 KB)
It’s very similar to build-in format #5 (Currency $#,##0;$-#,##0) from the doc, but Aspose doesn’t detect it correctly and suggest custom (culture invariant) format:
a1.png (7.6 KB)

Maybe I’ve missed up some important information. Could you explain, are your build-in currency formats culture invariant or not? I mean, does Aspose generate format string depending on current culture or not? Because format string suggested by Excel for default currency representation is very similar to default UA culture format for currency. I thought, Aspose build-in format #5 generated in the same way.

UPDATED:

Further investigation:
I’ve setup custom format in Excel exactly like your build-in format #5:
e2.png (15.8 KB)
But Aspose still can’t identify it correctly and returns cell’s style with custom format:
a2.png (4.6 KB)

4

@23W,

What’s wrong with it. When you set custom format to a cell (Format Cells —> specify string using “Custom” category under Number tab), it will give you as custom format.

Please note the difference b/w Style.Custom and Style.InvariantCustom and Style.CultureCustom attributes:

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

  2. Moreover, Style.InvariantCustom values can be a superset of Style.Custom values. Style.InvariantCustom contains all values of Style.Custom in one workbook.

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

5

@23W,
Here is some additional information about your questions:

For the number formats read from a template file, whether it is built-in or custom depends on what’s saved in the template file. If the style’s custom string has been specified in the template file, then the number format is custom. Otherwise, if the number format has been specified with a non-zero number, then it will be taken as built-in.

For the number formats set by Aspose.Cells, if they are set by Style.Number, then it will be taken as built-in. If they are set by Style.Custom/CultureCustom, then they will be taken as custom, no matter whether the custom pattern is same with one of those built-in ones. If you prefer built-in even by specifying pattern string, you may try the method:

Style.SetCustom(string custom, bool builtinPreference)

This method will check all built-in formats according to the current culture of the Workbook object, if there is matched one, then the number format will be set as the matched built-in. However, please note, this method will cause much more time cost when comparing with Style.Number and Style.Custom so it is not the proper way to set number formats for lots of objects.

1 Like