How to remove decimal separator if the decimal part is zero?

Question

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

enter image description here

@Jason I use `0.##%`, not `#.##%` as your variant would not print zero at all; compare `0,5%` and `,5%`. And the prolblem remains, as it also prints the decimal separator — horgh, Aug 30 '13 at 01:45
Then I would suggest using conditional formatting. The format string doesn't seem to support what you want to achieve. Not that I can see. http://closedxml.codeplex.com/wikipage?title=Conditional%20Formatting&referringTitle=Documentation — Jason, Aug 30 '13 at 01:49
Best source I can locate on this is http://www.ozgrid.com/Excel/CustomFormats.htm — Jason, Aug 30 '13 at 01:58

horgh · Accepted Answer · 2013-08-30T03:32:29.283

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:

cell.AddConditionalFormat()
    .WhenEquals(
        string.Format(
            "=TRUNC(${0}${1})", 
            cell.WorksheetColumn().ColumnLetter(),
            cell.WorksheetRow().RowNumber()))
    .NumberFormat
    .Format = "general\"%\"";

I gave up using percent format specifier, as it makes me divide by 100. From Excel Custom Number Formats:

% Percentage. Microsoft Excel multiplies by 100 and adds the % character.

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.

How to remove decimal separator if the decimal part is zero?

1 Answers1