Custom format applied through Style.Custom is not always escaped properly

When a custom format is applied to the style through the .Custom property, the format is not always escaped properly. In Excel UI, all $ signs have an extra backslash prepended.

I found a potential workaround through SetCustom(), but the workaround cannot always be used due to another bug. I will report the other bug separately.

I have attached sample code and screenshots illustrating the difference.

CustomFormat.zip (26.9 KB)

@bingxie,

Thanks for the screenshots and sample.

I checked your code segment and you have not defined the custom format string properly. Please try using the following custom format string and it will work as expected:
e.g.
Sample code:

......
// Default Excel Accounting Format
static string CustomFormat = "_(\"$\"* #,##0.00_);_(\"$\"* \\(#,##0.00\\);_(\"$\"* \"-\"??_);_(@_)";

Hope, this helps a bit.

@Amjad_Sahi Thanks for your update.

However, the format string you provided is different from Excel UI.

This is the actual format string copied directly from the Excel UI:

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)

This is what you provided:

_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)

In the format string from the Excel UI, there is no extra double quotes around $.

I have attached a new screenshot showing that the CustomFormat I used was exactly the same as the Excel UI.

I know in the XML behind the scenes, Excel escapes it, but I think as a user we should be able to copy & paste formats from Excel without having to manually apply extra double quotes around dollar signs.

Excel UI.png (17.9 KB)

@bingxie,

I think MS Excel omits quotes around “$” (a kind of behavior of MS Excel), so you might not see it in the dialog of MS Excel. But you have to encode/escape quotes around “$” when defining the custom string to be applied for the cell.

@Amjad_Sahi Thanks again for your update.

However, style.SetCustom(format, true) works fine without manually escaping $ with double quotes.

Escaping $ with double quotes is only needed when I set the .Custom property.

I think this behavior difference is not mentioned in the documentation, and is confusing. I doubt most would realize that $ must be encoded in format strings through .Custom, but not in .SetCustom(format, true).

@bingxie,

We got to evaluate it in details and then update you. When the Boolean attribute builtinPreference in SetCustom(format, true) is true, Aspose.Cells would try to match the closest passible built-in numbers and implement it once found. Anyways, we will clarify and provide more details on it.

@bingxie,
For setting Style.Custom property, if you need the currency symbol changes according to the region, it should not be quoted.

For Style.SetCustom(format,true), we will try different built-in formats corresponding to current region settings of the Workbook to check whether the given format can match one of them. It cannot be ensured that the given format can be converted to a built-in format successfully, even though it is same with what you saw for one built-in format in ms excel. The main reason is that there are so many different regions and commonly there are some major or minor differences for the patterns of the same built-in format. We always try to make our code work for user’s requireed region, but it is hard for us to cover all situations.

For the currency symbol issue displayed in ms excel for the generated files, I think it is the same one with your another post: Style.SetCustom throws NullReferenceException - #12 by bingxie. And let’s continue the discussion there.

@johnson.shi Thanks for the update.

For setting Style.Custom property, if you need the currency symbol changes according to the region, it should not be quoted.

However, according to @Amjad_Sahi , if the dollar signs are not quoted, they will not be recognized in Excel correctly as a built-in format (also as shown in the screenshots in this thread).

For Style.SetCustom(format,true), we will try different built-in formats corresponding to current region settings of the Workbook to check whether the given format can match one of them.

How do I change the “current region settings” of a workbook? That’s exactly what I need because we need to support about 15 regions.

@bingxie,
For currency in Style.Custom, I got it mixed up with other separators. @Amjad_Sahi is right, ms excel can recognize it as built-in only when the currency symbol is quoted.

For performance consideration we cannot check user input custom string for such kind of situation, and in fact it is also hard for us to know all the rules and possibilities that ms excel uses to handle the recognization between custom and built-in. We just keep user’s input custom for the Style and save it to the resultant excel file finally.

To change the regional settings of a workbook, we think you have used those apis:
WorkbookSettings.Region and WorkbookSettings.CultureInfo
Any one of them can change the regional settings for the workbook and will update another accordingly. However, the rules ms excel uses to recognize custom as built-in are complicated and currently we cannot make all possible custom be recognized as built-in correctly like ms excel. For you situation, we think it will be better if you can just set Style.Number directly instead of using Style.Custom.

@johnson.shi Thanks for your update.

All formats we use must be recognizable as built-in in Excel. The problem is Style.Number does not have all built-in Accounting and Currency formats available in Excel. It lacks some basic ones as well as many region-specific formats.

@bingxie,
We agree. According the our discussion, it seems at least you need to specify different regions for those “built-in” formats for your current requirement. And with those special settings, in fact the required formats are not “built-in” anymore and cannot be represented by any built-in number.

For your requirement and situation, we are afraid you have to set the custom to the exact one which can be recognized by ms excel.

1 Like

Thank you. I think we will have to build our own reference table for region-specific “Excel built-in” formats.