I am attempting to use C# and ClosedXML
to enter data into an excel sheet. I have found most of the things I am looking for, however I am having some issues getting ClosedXML
to take care of the number of decimal places as well as rounding the number.
I am currently using cell.Style.NumberFormat.NumberFormatId = 10;
to get the cell to show a Percentage with 2 decimal places, I would however like to show only 1 decimal place, and the wiki for ClosedXML
only shows percent with 0 or 2 decimal places.
Next when I do not use ClosedXML
and add the data directly Excel will round the decimal places up, but when I use ClosedXML
to enter the data the cells do not round up, is there a way to force ClosedXML
to round for me, or do I need to do that manually?
Looks like you might have to write your own custom NumberFormatId
according to this SO Answer: https://stackoverflow.com/a/7900397/541208, alternatively, in the ClosedXML
docs, it says to add a new Style
:
var workbook = new XLWorkbook();
var ws = workbook.Worksheets.Add("Style NumberFormat");
var co = 2;
var ro = 1;
// Using a custom format
ws.Cell(++ro, co).Value = "123456.789";
ws.Cell(ro, co).Style.NumberFormat.Format = "$ #,##0.00";
ws.Cell(++ro, co).Value = "12.345";
ws.Cell(ro, co).Style.NumberFormat.Format = "0000";
// Using a OpenXML's predefined formats
ws.Cell(++ro, co).Value = "12.345";
ws.Cell(ro, co).Style.NumberFormat.NumberFormatId = 3;
ws.Column(co).AdjustToContents();
workbook.SaveAs("StylesNumberFormat.xlsx");
So your custom NumberFormat
would be something like 0.0%
based on the default formats listed here:
ws.Cell(ro, co).Style.NumberFormat.Format = "0.0%";