Detect datatable date field and force date format in EPPlus export

Question

I have a button on my page that when clicked exports data from a gridview to excel.

protected void btExport_Click(object sender, EventArgs e)
{
    Response.Clear();
    Response.Charset = "";
    Response.ContentEncoding = System.Text.Encoding.UTF8;
    Response.Cache.SetCacheability(HttpCacheability.NoCache);
    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    Response.AddHeader("content-disposition", "attachment;filename=Output.xlsx");

    DataTable dt= gvOutput.DataSource as DataTable;

    ExcelPackage pck = new ExcelPackage();
    using(pck)
    {
      ExcelWorksheet wsDt = pck.Workbook.Worksheets.Add("Sheet1");
      wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.None);
      wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();

      Response.BinaryWrite(pck.GetAsByteArray());

    }

    Response.Flush();
    Response.End();
}

This is working fine using the above code, however date fields are formatted as integers. I understand specific cells can be forced to have a certain format, but my issue is that the date columns will change as the dataset for the gridview is dynamic.

I need a way to identify on the fly which column is a date and to force the format of that column to be a date time.

I imagine it will involve an IF statement like this but I haven't got a clue what to put in it or where to put it!

if (dt.Columns[x].DataType == typeof(DateTime))
{ 
    //do something
}

Any ideas/help is greatly appreciated.

Solution 1

Loop through the DataTable columns and determine which ones are DateTime values. If you find one, then set the format for the column as shown below. I haven't tested the code, but it should be the general logic to follow.

...
wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.None);
int colNumber = 1;

foreach (DataColumn col in dt.Columns) 
{        
    if (col.DataType == typeof(DateTime))
    { 
         wsDt.Column(colNumber).Style.Numberformat.Format = "MM/dd/yyyy hh:mm:ss AM/PM";
    }    
    colNumber++;      
}

wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();
Response.BinaryWrite(pck.GetAsByteArray());

Solution 2

Correct me if i'm wrong, but I believe that @desiguy's and @Rick S's answers are not totally correct.

In @desiguy's answer, the statement colNumber++ is outside the foreach loop on the datatable columns.

Therefore, because the index increment is done outside the loop, the loop will always work on colNumber=1.

Regarding to @Rick S's solution; If I am not mistaken, in the EPPlus, the column indexes begin with 1 and not 0, so , the statement:

wsDt.Column(colNumber++).Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss AM/PM"

should be as follows:

wsDt.Column(++colNumber).Style.Numberformat.Format = "MM/dd/yyyy hh:mm:ss AM/PM"

while there are two differences:

1. The colNumber++ was changed to ++colNumber. In that case, the column will start with 1 and not with 0, so you won't get an error

2. The mm/dd/yyyy was changed to MM/dd/yyyy because the lower case mm represents minutes and not months, hence, MM is the correct syntax for month representation

Another simpler solution is only setting the int colNumber = 0; to int colNumber = 1; and you get the same result.

Solution 3

Slight modification to the above answer. Almost correct. I fixed it with below code.

wsDt.Cells["A1"].LoadFromDataTable(dt, true, TableStyles.None);

int colNumber = 0;
foreach (DataColumn col in dt.Columns) 
{
    colNumber++;
    if (col.DataType == typeof(DateTime))
    { 
       wsDt.Column(colNumber).Style.Numberformat.Format = "mm/dd/yyyy hh:mm:ss AM/PM"
    }          
}

wsDt.Cells[wsDt.Dimension.Address].AutoFitColumns();
Response.BinaryWrite(pck.GetAsByteArray());

Solution 4

Fully corrected (and tested) code:

        int colNumber = 0;

        foreach (DataColumn col in DataToExport.Columns)
        {
            colNumber++;
            if (col.DataType == typeof(DateTime))
            {
                wsDt.Column(colNumber).Style.Numberformat.Format = "MM/dd/yyyy hh:mm:ss AM/PM";
            }
        }

View more solutions