ClosedXML: Working with percents, 1 decimal place and rounding?

Question

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?

Hut

Answer 1

Looks like you might have to write your own custom NumberFormatId according to this SO Answer: https://mcmap.net/q/514713/-applying-number-format-to-a-cell-value-using-openxml, 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%"; 

Kobayashi

I missed this as the obvious answer but after looking at it I have changed my format to 'cell.Style.NumberFormat.Format = "0.0%";' and the single decimal place is taken care of. Thank you. Now to figure out the rounding puzzle. – Hut

I take it back, now with 1 decimal place the number are rounding properly. Thank you again. – Hut