Converting Excel cell to percentage using epplus

Question

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.

Declared

Answer 1

I found it!

I tried

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

Declared

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. – Ceil

Answer 2

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

Marriott

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. – Harvell

Answer 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

Hillary

Answer 4

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%";
}

Scheel

Answer 5

sheet.Cells[1, 1, row, col].Style.Numberformat.Format = "##0.00\\%;[Red]-##0.00\\%";

It's worked for me.

Appolonia