How can I set contents of a spreadsheet cell to Accounting format with EPPlus?

Question

I need to set the contents of certain columns to Accounting format.

This attempt:

public static readonly string NUMBER_FORMAT_ACCOUNTING = "$";
. . .
bidPriceCell.Style.Numberformat.Format = NUMBER_FORMAT_ACCOUNTING;

...simply gives "$" and "-$" as the values.

This attempt:

public static readonly string NUMBER_FORMAT_ACCOUNTING = "$0.00";
. . .
bidPriceCell.Style.Numberformat.Format = NUMBER_FORMAT_ACCOUNTING;

...gives me values such as, "$24.09" and "-$0.91"

What the user wants is space between the dollar sign and the value, and parens around negative values, such as "$ 24.09" and "$ (0.91)"

What string do I need to assign the Numberformat.Format property for this to work?

Answer 1

Due to I need the currency format for Euros and a Spanish locale (example 1.000.000,00€). I had to:

1. Unzip one excel file where I had apply that format to a cell.
2. Go to the xl folder and open the styles.xml file
3. Find the format searching for '€' symbol between the numFmts tags

styles.xml:

<numFmts count="2">
    <numFmt numFmtId="44" formatCode="_-* #,##0.00\ &quot;€&quot;_-;\-* #,##0.00\ &quot;€&quot;_-;_-* &quot;-&quot;??\ &quot;€&quot;_-;_-@_-"/>
</numFmts>

Therefore, you can use this format like this:

sheet.Cells["A1"].Style.Numberformat.Format = @"_-* #,##0.00\ ""€""_-;\-* #,##0.00\ ""€""_-;_-* ""-""??\ ""€""_-;_-@_-";

Answer 2

Found the answer from Wildpinkler here, which is:

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

...so that the following works:

public static readonly  String NUMBER_FORMAT_ACCOUNTING = @"_(""$""* #,##0.00_);_(""$""* \(#,##0.00\);_(""$""* ""-""??_);_(@_)";
. . .
bidPriceCell.Style.Numberformat.Format = RoboReporterConstsAndUtils.NUMBER_FORMAT_ACCOUNTING;