How to set Column Type when using EPPlus

.net excel epplus
Michael picture Michael · Mar 25, 2012 · Viewed 61.7k times · Source

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 ?

Answer

banging picture banging · Apr 17, 2012

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!

Related questions

EPPlus number format

I have an Excel sheet generated with Epplus, I am experiencing some pain points and I wish to be directed by someone who have solved a similar challenge. I need to apply number formatting to a double value and I …

Read More
Trying to read an Excel file with EPPlus works on the server, but not through a browser

When I published my project and ran it on the server, it worked. EPPlus found all 4 worksheets, iterated through them, and uploaded my data to SQL. But when I run it through my browser, or my coworkers browser, it shows 0 …

Read More
Make column or cells readonly with EPPlus

Is there a way to make a column or group of cells locked or read only using EPPlus? I've tried the code below both separate and together however neither seems to have the desired effect. Either the entire worksheet is …

Read More