4

How to format a cell as Number Accounting category using ClosedXML?

Accounting format

HackSlash
  • 4,944
  • 2
  • 18
  • 44
user1517433
  • 41
  • 1
  • 4

2 Answers2

2

The number format is:

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

And set the NumberFormatId to 43.

You can get the number format by using Excel. Style the cell as you want it and then go to Custom number format. The number format will display there.

In ClosedXML, you can use it like this (be careful to escape the quotation marks):

using (var wb = new XLWorkbook())
{
    var ws = wb.AddWorksheet("Sheet1");
    var cell = ws.FirstCell();

    cell.Value = 0.0;
    cell.DataType = XLDataType.Number;
    cell.Style.NumberFormat.Format = "_ * # ##0.00_ ;_ * -# ##0.00_ ;_ * \"-\"??_ ;_ @_ ";
    cell.Style.NumberFormat.SetNumberFormatId(43);

    wb.SaveAs("test.xlsx");
}
Francois Botha
  • 4,520
  • 1
  • 34
  • 46
  • Hello Francois, While using the above format, see the cell formatting. it is showing as Custom format instead of Accounting Number format. Could you please tell how to achieve the desired result. Thanks &Regards, Koti. – user1517433 Nov 22 '17 at 07:23
  • How did you come up with this `NumberFormatId` value (i.e., 43)? Is it documented somewhere? I couldn't find it anywhere, including [ClosedXML's docs](https://github.com/ClosedXML/ClosedXML/wiki/NumberFormatId-Lookup-Table). – 41686d6564 stands w. Palestine Jan 31 '21 at 02:20
  • 1
    Easiest is to create a little sample file in Excel with the formatting that you want and then read it using ClosedXML, which will give you the required numberformatid or custom format. – Francois Botha Jan 31 '21 at 06:36
  • In version 0.96.0 this creates a corrupt workbook. You can't use a `NumberFormatId` that is unsupported by the library. Those are shortcuts for setting known good formats. If I check the `NumberFormatId` returned when reading a sheet setup by Excel I get `-1` returned because it's not found in the enum provided by the library – HackSlash Sep 22 '22 at 17:11
0

If we do as @FrancoisBotha suggests; set a cell to the default Accounting format in Excel and then read the style in, we learn that there is no corresponding NumberFormatId for Accounting in ClosedXML as of version 0.101.0

What you do get back is this NumberFormat string:

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

// Setting the format handles the data type magically
cell.Style.NumberFormat.SetFormat(ExcelAccountingFormat);
HackSlash
  • 4,944
  • 2
  • 18
  • 44