NumberFormat in ExportToExcel according to culture

Question

I am using ExcelPackage(EP Plus) for ExportTo Excel.Here is My code...

 public static void ExportDataSetToExcel(DataSet ds, string FileNameWithExtension)
    {
        //Using EPPLUS to export Spreadsheets
        ExcelPackage pck = new ExcelPackage();

        foreach (System.Data.DataTable table in ds.Tables)
        {
            var ws = pck.Workbook.Worksheets.Add(table.TableName);
            ws.Cells["A1"].LoadFromDataTable(table, true);
            ws.Cells["A1:CC1"].Style.Font.Bold = true;


            for (int iCount = 0; iCount < table.Columns.Count; iCount++)
            {
                if (table.Columns[iCount].DataType == typeof(decimal))
                {
                     CultureInfo cultureInfo =  Thread.CurrentThread.CurrentCulture;
             string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator);            
             string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.NumberDecimalSeparator, cultureInfo.NumberFormat.NumberGroupSeparator);

                      ws.Column(iCount + 1).Style.Numberformat.Format = Pattern2;
                }
                if (table.Columns[iCount].DataType == typeof(int))
                {
                    CultureInfo cultureInfo = Thread.CurrentThread.CurrentCulture;
                    string Pattern1 = string.Format("0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator);
                    string Pattern2 = string.Format("#{1}##0{0}00", cultureInfo.NumberFormat.CurrencyDecimalSeparator, cultureInfo.NumberFormat.CurrencyGroupSeparator);

                    ws.Column(iCount + 1).Style.Numberformat.Format = "0";
                }
                if (table.Columns[iCount].DataType == typeof(DateTime))
                {
                    ws.Column(iCount + 1).Style.Numberformat.Format = "dd-mm-yyyy";
                }
            }
        }

        pck.SaveAs(System.Web.HttpContext.Current.Response.OutputStream);
        System.Web.HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        System.Web.HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" + FileNameWithExtension);
        System.Web.HttpContext.Current.Response.End();
    }

I am unable to get the proper decimal format.I want indonesia number format but still it is showing the default.

I'd try this in Excel. The number formats you're creating aren't right. Comma is *always* the thousands separator, and dot is *always* the decimal separator. What is displayed depends on your system regional settings or [what is overriden in Excel](http://www.howtogeek.com/245510/how-to-change-excels-decimal-separators-from-periods-to-commas/). — Charles Mager, Oct 31 '16 at 13:16
Possible duplicate of [EPPlus number format](http://stackoverflow.com/questions/40209636/epplus-number-format) — VDWWD, Oct 31 '16 at 13:23
But in Indonesia NumberFormat Dot is always the thousands separator, and Comma is always the decimal separator. @Charles Mager — Jagannath Mahapatra, Nov 01 '16 at 07:06
@JagannathMahapatra in Excel? I'd be surprised. Are you confusing the format syntax with how you want it displayed? These are two different things. — Charles Mager, Nov 01 '16 at 07:54
I want to display the formatted number in Excel. eg. 1234.56 to 1.234,56@ Charles Mager — Jagannath Mahapatra, Nov 01 '16 at 09:48
@Charles Mager how do you get the original cell value regardless how is it formatted? If I get an Excel file generated with CultureInfo("ES_es") I have to parse it assuming decimal commas — Matias Masso, Sep 25 '19 at 13:03

score 1 · Answer 1 · answered Oct 31 '16 at 13:15

1

I Used EP Plus before but I didn't had to change the decimal format. But try this I think it will work

Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("id-ID");

answered Oct 31 '16 at 13:15

Ahmed

1,542
2
13
21

score 0 · Answer 2 · answered Nov 02 '16 at 06:24

 private static void AddDataRows(Excel.Worksheet sheet, DataTable table, object[,] tempArray)
    {

            var range = sheet.Range(sheet.Cells[2, 1], sheet.Cells[(table.Rows.Count), (table.Columns.Count)]);
            range.Value = tempArray;
            sheet.Name = table.TableName;
            for (int i = 0; i < table.Rows.Count; i++)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Columns[j].DataType == typeof(decimal))
                    {
                        sheet.Cells[i+2, j+1].Value = ConvertNumberFormat.ConvertToDecimal(Convert.ToDecimal( sheet.Cells[i+2 , j+1 ].Value));
                    }
                }
            }

    }

create one static method for converting number format.

score 0 · Answer 3 · edited Apr 19 '20 at 19:14

0

CultureInfo c;
c = new CultureInfo("en-EN");
var Test= worksheet.Cells[row, 1].Value != null ? 
Convert.ToDouble(worksheet.Cells[row, 1].Value, c) : 0,

edited Apr 19 '20 at 19:14

Felix Castor

1,598
1
18
39

answered Apr 19 '20 at 17:31

gökhan övsene

11
2

3

Please do not post only code as an answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually of higher quality, and are more likely to attract upvotes. Remember to format code properly. – Boken Apr 20 '20 at 01:07

NumberFormat in ExportToExcel according to culture

3 Answers3