13

I would like to convert the value in to 2 decimal places. I am using EPPlus if the value is 66.6666667 and I would like to show it as 66.66% I tried the following code but its not working. 

   foreach (var dc in dateColumns)
   {
       sheet.Cells[2, dc, rowCount + 1, dc].Style.Numberformat.Format = "###,##%";
   }

Please help.

ejo
  • 446
  • 1
  • 9
  • 23
rach
  • 669
  • 2
  • 8
  • 31

5 Answers5

23

I found it!

I tried 

 foreach (var dc in dateColumns)
  {
    sheet.Cells[2, dc, rowCount + 1, dc].Style.Numberformat.Format ="#0\\.00%";
   }
rach
  • 669
  • 2
  • 8
  • 31
  • 4
    it works when its only "0.00%". so without the '#' and without the '\'. Otherwise the value gets divided by hundred, such that the value stored and the value shown is not the same. – MovGP0 Mar 11 '19 at 11:57
19

The correct formula is as follows:

 foreach (var dc in dateColumns)
  {
    sheet.Cells[2, dc, rowCount + 1, dc].Style.Numberformat.Format ="#0.00%";
  }

The Double slash in "#0\\.00%" leads to very unusual numbers when you try to expand the decimal places later

TsTeaTime
  • 881
  • 1
  • 13
  • 34
  • 2
    The only thing is if you already has the value as percentage, you need to divide it by 100 so that its displayed correctly when you open the sheet in excel. – PBo Mar 12 '19 at 10:07
3

As far as I checked format you set via epp is just a common Excel cell format.

In my case this was really helpful

Michael Brennt
  • 731
  • 9
  • 20
0

If you want to do it on a cells range. You can use

using (ExcelRange Rng = wsDashboard.Cells["B6:J6"])
{
    Rng.Style.Numberformat.Format = "#0\\.00%";
}
Joe Mayo
  • 7,501
  • 7
  • 41
  • 60
Rene Riveron Bacallao
  • 61
  • 4
0
sheet.Cells[1, 1, row, col].Style.Numberformat.Format = "##0.00\\%;[Red]-##0.00\\%";

It's worked for me.

St Al
  • 1
  • 1