excel 如何将ClosedXML中的货币格式化为数字

lp0sw83n  于 2022-12-14  发布在  其他
关注(0)|答案(4)|浏览(252)

我们使用ClosedXML将数据表对象转换为Excel电子表格以呈现给用户。DataTable对象的构建简单,只需将所有db值(来自NHibernate)赋给字符串,然后将它们格式化,如下所示:

//formatting
EstimatedCost = Currency.SafeToString(Currency.Create(n.EstimatedCost)),

然后,我们将列类型设置为属性类型,即在所有情况下都为String。
输出Excel工作表中会发生什么情况,因为列设置为货币,但数字为文本警告,那么它将无法正确排序。
我的问题是,由于我们将所有数据构建到DataTable中,所以我没有机会正确修饰ClosedXML列。有没有我没有想到的快速方法来完成此操作?

public const string ExcelDataType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml";

public static MemoryStream GenerateExcelFileFromData(IEnumerable<KeyValuePair<string, DataTable>> tabs, int colWidth = 100)
{
  var workbook = new XLWorkbook { ColumnWidth = colWidth };
  foreach (var enumerable in tabs)
  {
    workbook.Worksheets.Add(enumerable.Value, enumerable.Key);
  }

...

public static DataTable ConvertToDataTable<T>(IEnumerable<T> varlist, List<string> excludedColumns, bool titleizeColumn = false)
 {
   var dtReturn = new DataTable();

   // column names 
   PropertyInfo[] oProps = null;

   if (varlist == null) return dtReturn;

    foreach (T rec in varlist)
    {
     // Use reflection to get property names, to create table, Only first time, others will follow 
        if (oProps == null)
        {
           oProps = rec.GetType().GetProperties();
              foreach (PropertyInfo pi in oProps)
               {
                    if (excludedColumns.Contains(pi.Name))
                    {
                        continue;
                    }
                    var colType = pi.PropertyType;
                    dtReturn.Columns.Add(new DataColumn(GetColumnName(pi, titleizeColumn), colType));
                }
          }

        DataRow dr = dtReturn.NewRow();

        foreach (var pi in oProps.Where(pi => !excludedColumns.Contains(pi.Name)))
          {
             try
                {
                    dr[GetColumnName(pi, titleizeColumn)] = pi.GetValue(rec, null) ?? DBNull.Value;
                }
                catch (ArgumentException)
                {
                    dr[GetColumnName(pi, titleizeColumn)] = DBNull.Value;
                }
            }
            dtReturn.Rows.Add(dr);
        }
        return dtReturn;
kmpatx3s

kmpatx3s1#

您可以按以下方式设置货币值的格式:

worksheet.Cell(rowIndex, columnIndex).Style.NumberFormat.Format = "$0.00";
worksheet.Cell(rowIndex, columnIndex).DataType = XLCellValues.Number; // Use XLDataType.Number in 2018 and after
f0ofjuux

f0ofjuux2#

您可以获取所有已知为货币的列,并设置NumberFormat.Format。下面是一个示例,说明如何完成此操作。美元货币的正确格式为"[$$-en-US]#,##0.00_);[Red]([$$-en-US]#,##0.00)"。使用此格式时,如果您在Excel中打开电子表格并转到“单元格格式”,您将看到它设置为“货币”。

// Assume all columns that have "price" in the heading are currency types and format as currency.
    var priceColumns = table.Columns(c => c.FirstCell().Value.ToString().Contains("price", StringComparison.InvariantCultureIgnoreCase));
    foreach (var priceColumn in priceColumns)
    {
        try
        {
            // Get all the cells in this column, except for the header row
            var cells = priceColumn.Cells(2, rowCount);
            cells.Style.NumberFormat.Format = @"[$$-en-US]#,##0.00_);[Red]([$$-en-US]#,##0.00)";
        }
        catch { } // Exception here means not all of the cells have a numeric value.
    }
zfciruhq

zfciruhq3#

一个简单的方法来获得格式的权利是去excel本身,选择格式,你想你的单元格,然后右键单击单元格,并选择格式单元格,之后你可以复制格式代码从你选择的格式,你是好去。

// I had to escape double quotes though
string myFormat = "\"$\"#,##0;[RED]-\"$\"#,##0";
worksheet.Cell("A1").Style.NumberFormat.Format = myFormat;
aelbi1ox

aelbi1ox4#

ws.Cell(ro, co).Style.NumberFormat.Format = "[$$-en-US] #,##0.00";
ws.Cell(ro, co).DataType = XLDataType.Number;

相关问题