EPPlus Format Cell as "Accounting" Number

Question

In Excel you can format numbers in the following format:

I'd like to format a cell with this format in EPPlus, but I don't see that property available:

I've looked a number of other formats, , but none of them are the same. I also tried to set the "BuildIn" number(it has no setter, this didn't work). I'm trying to use the system format, vs roll my own custom format. Is it possible to use the "Accounting" format with EPPlus?

Lockett

Why not make a dummy spreadsheet and set a cell to the format you want. Then read that cells formatting using EPP (or with VBA) and see what the actual format is. Then there should be no guessing. – Richard

Answer 1

EPPlus does not include the number formats built into Excel so you must set it manually.

All the built-in formats in Excel have an actual number format. For accounting, the format is:

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

The format above may differ depending on your region/settings. To see what the format is for any built-in formats:

1. Right-click a cell and select Format Cells

2. Click on your built-in format, in this case Accounting

3. Click on Custom to see the format of the previously selected format

Apply the format to your cells:

ws.Cells["A1:A5"].Style.Numberformat.Format = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-";

Blanton

Answer 2

I used this format is simulating same as "Accounting Number" in the Excel.

ws.Cells["A1:A5"].Style.Numberformat.Format = "_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"

Circumambulate

Answer 3

In my testing, none of the answers to date are exactly correct. The cell contents look fine, but Excel doesn't recognize them as Accounting, so they show up as "Custom" in the format drop-down list. The following format string is recognized by Excel as "Accounting" and not "Custom":

const string AccountingFormat = @"_(""$""* #,##0.00_);_(""$""* \(#,##0.00\);_(""$""* ""-""??_);_(@_)";

ws.Cells["A1:A25"].Style.Numberformat.Format = AccountingFormat

(The differences with the other answers are the dollar signs are quoted here, and the inner parentheses are escaped.)

Vendible

Answer 4

I don't know what an accounting number format looks like, but if you want the dollar sign behind the number, you can do this.

ws.Cells["A1:A25"].Style.Numberformat.Format = "#,##0.00 $";

Turgot

If you look at the question's image of the spreadsheet, the accounting format puts the dollar sign in front of the number, not behind it. Specifically, it places the dollar sign at the beginning of the cell. – Lapp