I'm using ClosedXML to create a excel spreadsheet. The issue takes place while formatting cells, containg percentages.
The format I came up to is 0.##%
.
It works well when the decimal part is not zero, it shows: 1,15%
; but when it's integral-valued, it leaves the decimal separator visible, while hiding zeroes, for example: 5,%
.
How can I make it hide the decimal separator as well?
Here is a small program, demonstrating the issue:
XLWorkbook wb = new XLWorkbook();
var ws = wb.AddWorksheet("test");
string format = "0.##%";
var cell = ws.Cell(1, 1);
cell.SetValue(5.2M / 100);
cell.Style.NumberFormat.Format = format;
cell = ws.Cell(1, 2);
cell.SetValue(5M / 100);
cell.Style.NumberFormat.Format = format;
wb.SaveAs("test.xlsx");
and the output is
Many thanks to Jason for the link to CLOSED XML: Conditional Formatting!
Thanks to this I managed to find the way to apply conditional formatting I needed:
I gave up using percent format specifier, as it makes me divide by 100. From Excel Custom Number Formats:
Now I simply add
%
as a string, not as a format specifier.So the main number format is
0.##\"%\"
, but when the value's decimal part is zero, I substitute the number format to the general one with%
in the end using conditional formatting.