ExportToExcel中的数字格式根据区域性

本文关键字：区域性 格式 数字 ExportToExcel | 更新日期: 2023-09-27 18:15:43

我使用ExcelPackage(EP Plus)用于ExportTo Excel。这是我的代码…

 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();
    }

我无法获得正确的十进制格式。我想要印度尼西亚数字格式，但它仍然显示默认值

ExportToExcel中的数字格式根据区域性

我以前使用过EP Plus，但我不需要更改十进制格式。但是试试这个，我想它会起作用的

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

 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));
                    }
                }
            }
    }

创建一个转换数字格式的静态方法

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