.net 使用EPPlus时如何设置列类型

How to set Column Type when using EPPlus

提问by Michael

I'm using EPPlusto generate Excelfiles, in DAL I'm populating DataTable, filling data into table, and passing table to Presentation Layer. From there I'm using LoadFromDataTable()method to generate Excelfile.

我正在使用EPPlus生成Excel文件，在 DAL 中我正在DataTable填充，将数据填充到表中，并将表传递给表示层。从那里我使用LoadFromDataTable()方法来生成Excel文件。

Everything works fine, except that I want to set one of the column's type to Date. I tried to set Column type of my DataTabletoDateand than pass DataTableto Presentation Layer, but it seems EPPluseither, ignored it, or didn't recognize, because when I'm opening generated Excelfile, cell's type is Number.

一切正常，除了我想将列的类型之一设置为Date. 我试图将我的列类型设置DataTable为Date并传递DataTable给表示层，但似乎EPPlus要么忽略它，要么无法识别，因为当我打开生成的Excel文件时，单元格的类型是Number.

If I manually Format Cells and set Type to Date, Excelshows correct dates. So how can I achieve this ?

如果我手动设置单元格格式并将类型设置为Date，则Excel显示正确的日期。那么我怎样才能做到这一点？

回答by banging

You do need the DataTable column to have the right type but you also need to modify the column or cell's Style.Numberformat.Format property.

您确实需要 DataTable 列具有正确的类型，但您还需要修改列或单元格的 Style.Numberformat.Format 属性。

Say you have an ExcelWorksheetnamed ws:

假设你有一个ExcelWorksheet命名的ws：

ws.Column(1).Style.Numberformat.Format  = "yyyy-mm-dd"; 
//OR "yyyy-mm-dd h:mm" if you want to include the time!

回答by insilenzio

Based on this discussion (epplus.codeplex.com/discussions/349927) you can also set column format to date.

基于此讨论 ( epplus.codeplex.com/discussions/349927)，您还可以将列格式设置为日期。

worksheet_1.Cells[row, 3].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

worksheet_1.Cells[row, 3].Style.Numberformat.Format = DateTimeFormatInfo.CurrentInfo.ShortDatePattern;

回答by midohioboarder

If your columns are likely to move around (as we know end-users tend to be fickle) or you just have many date columns scattered across your spreadsheet, it would be helpful to write something a little more generic. Here is what I just wrote. It finds the position of all DateTime types in my POCO and creates a list that it then uses to set the column formatting. Remember data tables are zero based and Excel is not.

如果您的列可能会四处移动（我们知道最终用户往往是善变的）或者您的电子表格中只有许多日期列，那么编写一些更通用的内容会很有帮助。这是我刚刚写的。它在我的 POCO 中找到所有 DateTime 类型的位置，并创建一个列表，然后用于设置列格式。请记住，数据表是基于零的，而 Excel 不是。

        ws.Cells.LoadFromDataTable(tbl, true);
        var dPos = new List<int>();
        for (var i = 0; i < tbl.Columns.Count; i++)
            if (tbl.Columns[i].DataType.Name.Equals("DateTime"))
                dPos.Add(i);
        foreach (var pos in dPos)
        {
            ws.Column(pos+1).Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss AM/PM";
        }

If you are doing more than one datatable, you'll probably want to refactor it off into a function.

如果您正在处理多个数据表，您可能希望将其重构为一个函数。

And here is a freebie... I can't take credit for this code. It takes a POCO list and turns it into a data table. It has made my life easier on a number of occasions having it in my 'toolkit'. Enjoy.

这是一个免费赠品...我不能相信这段代码。它需要一个 POCO 列表并将其转换为数据表。在我的“工具包”中多次使用它使我的生活变得更轻松。享受。

        public DataTable ConvertToDataTable<T>(IList<T> data)
    {
        var properties =
           TypeDescriptor.GetProperties(typeof(T));
        var table = new DataTable();
        foreach (PropertyDescriptor prop in properties)
            table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
        foreach (T item in data)
        {
            var row = table.NewRow();
            foreach (PropertyDescriptor prop in properties)
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            table.Rows.Add(row);
        }
        return table;
    }

回答by Brennan Pope

Here's a nice C# extension method to help load from collection with headers and the proper date formatting:

这是一个很好的 C# 扩展方法，可帮助从带有标题和正确日期格式的集合中加载：

(Decorate your properties with Description attributes for the column headings)

（使用列标题的描述属性装饰您的属性）

public static class EpPlusExtensions
{
    public static void Load<T>(this ExcelWorksheet worksheet, IEnumerable<T> collection)
    {
        worksheet.Cells["A1"].LoadFromCollection(collection, true);

        var properties = typeof(T).GetProperties();

        for (var i = 0; i < properties.Length; i++)
        {
            if (new []{typeof(DateTime), typeof(DateTime?)}.Contains(properties[i].PropertyType)) 
            {
                worksheet.Column(i + 1).Style.Numberformat.Format = "m/d/yyyy";
            }
        }
    } 
}

回答by Micha? Brix

To use build in excel formats, you need to pass correct string to

要使用 excel 格式的构建，您需要将正确的字符串传递给

sheet.Cells[1, 1].Style.Numberformat.Format 

property.

财产。

Now, somewhere later during execution, probably during serialization, EPPlus will try to match this format property with current dictionary of styles in workbook. It may depend on exact library version, but for example for EPPlust 4.1.0.0 short date key is "mm-dd-yy".

现在，在稍后执行期间，可能在序列化期间，EPPlus 将尝试将此格式属性与工作簿中的当前样式字典进行匹配。它可能取决于确切的库版本，但例如 EPPlust 4.1.0.0 短日期键是“mm-dd-yy”。

For 4.1.0.0 you can find all hard-coded codes and keys to build in formats in:

对于 4.1.0.0，您可以在以下格式中找到所有硬编码代码和密钥：

1. ExcelNumberFormatXml.cs, internal static void AddBuildIn(XmlNamespaceManager NameSpaceManager, ExcelStyleCollection<ExcelNumberFormatXml> NumberFormats)- here all of those codes are actually included into workbook, all hard-coded
2. use debugger and check Workbook.Styles.NumberFormatsenumeration (as key use ExcelNumberFormatXml.Format)
3. use debugger and check Workbook.Styles.NumberFormats.(non public memeber)_dicfor exact keys.

1. ExcelNumberFormatXml.cs，internal static void AddBuildIn(XmlNamespaceManager NameSpaceManager, ExcelStyleCollection<ExcelNumberFormatXml> NumberFormats)-这里所有这些代码实际上都包含在工作簿中，都是硬编码的
2. 使用调试器并检查Workbook.Styles.NumberFormats枚举（作为关键使用ExcelNumberFormatXml.Format）
3. 使用调试器并检查Workbook.Styles.NumberFormats.（非公共成员）_dic的确切密钥。