By Michael


2012-03-25 10:53:02 8 Comments

I'm using EPPlus to generate Excel files, 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 Excel file.

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 DataTable toDate and than pass DataTable to Presentation Layer, but it seems EPPlus either, ignored it, or didn't recognize, because when I'm opening generated Excel file, cell's type is Number.

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

5 comments

@Brennan Pope 2018-11-16 20:24:00

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

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

@MichaƂ Brix 2017-05-30 11:09:33

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

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".

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

  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.NumberFormats enumeration (as key use ExcelNumberFormatXml.Format)
  3. use debugger and check Workbook.Styles.NumberFormats.(non public memeber)_dic for exact keys.

@midohioboarder 2017-05-14 07:08:45

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.

        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.

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

@insilenzio 2015-03-04 13:04:43

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

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

@MatthewD 2015-08-06 20:32:37

FYI anyone, this comes from System.Globalization so you have to add that namespace "using System.Globalization".

@banging 2012-04-17 17:51:04

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.

Say you have an ExcelWorksheet named ws:

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

@user525192 2015-10-08 22:23:28

And will Excel change this format depending on the personal Excel settings? Think EU & US.

Related Questions

Sponsored Content

40 Answered Questions

61 Answered Questions

[SOLVED] In C#, how do I calculate someone's age based on a DateTime type birthday?

  • 2008-07-31 23:40:59
  • Jeff Atwood
  • 621405 View
  • 1890 Score
  • 61 Answer
  • Tags:   c# .net datetime

44 Answered Questions

[SOLVED] How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office?

  • 2008-09-29 22:30:28
  • mistrmark
  • 1113139 View
  • 1911 Score
  • 44 Answer
  • Tags:   c# .net excel file-io

29 Answered Questions

[SOLVED] How to enumerate an enum

3 Answered Questions

[SOLVED] Excel Date column returning INT using EPPlus

  • 2014-07-24 12:39:27
  • Tsukasa
  • 23476 View
  • 26 Score
  • 3 Answer
  • Tags:   c# excel epplus

1 Answered Questions

[SOLVED] Set Column Data Format using EPPLUS in C#

  • 2017-02-08 03:33:07
  • Chris K.
  • 6601 View
  • 2 Score
  • 1 Answer
  • Tags:   c# excel vba epplus

3 Answered Questions

[SOLVED] How To Set Cell Data Type

  • 2015-04-06 15:08:00
  • BorisP
  • 13916 View
  • 9 Score
  • 3 Answer
  • Tags:   types cell epplus

1 Answered Questions

How to set CSS style in EPPLus?

1 Answered Questions

[SOLVED] Formatting column in Excel using EPPlus Lib C#.Net

Sponsored Content