Search code examples
c#eppluscultureinfo

EPPlus Number Format according to different Culture

This the Excel Screenshot link to show example In a particular cell, It consists double value but showing rounded value in the cell.

like If in the cell the value is 553236 but the original value is 553236.456001

and can only see this value on top bar when click on that particular cell.

But my question is I have converted the original value into Italian currency, How to make it do the same as above

  • In Italian Culture 553,236.456 value will be converted as 553.236,456
  • Have to show only 553.236 and when Click on Particular cell On top bar value should be 553,236.456

For converting the value into Italian culture I used the code as below

Convert.ToDouble(cellValue).ToString("C", CultureInfo.CreateSpecificCulture("it-IT")).Split(' ')[0]

used split to not show currency symbol

Solution

  • After many trials like 

    cell.Value = Convert.ToDouble(cellValue,new CultureInfo("it-IT").NumberFormat);

    and

    cell.Value = Convert.ToDouble(cellValue).ToString("N",System.Globalization.CultureInfo.CreateSpecificCulture("it-IT")).Split(',')[0];

    I understood that number format or number according to culture is dependent on your system's region. If numbers are getting converted to different culture and were not showing or showing wrong format, that is cause of local system's region. If u change ur region to that particular culture. You'll get the perfect answer. I just did as per my country format in code like using

    cell.Style.Numberformat.Format = "#,##0";

    it worked fine with this. After committing code to svn, when I tried the Export of Excel from main website it was working beautifully according to system's region. If I change to Italian, Excel output numbers were in italian format and when I change to Indian , the numbers were in Indian number format.

    So Don't worry about whatever culture u want, do write according to ur culture all work's fine. Over this if u still want to get number according to particular culture use the above second code, but problem is u will get numbers stored as text. that u can't add or do any valuations.