CODEWITHSUNDEEP
c#exceldatetimedatatablesclosedxml
Willy
Willy

Reputation: 10660

Exporting DateTime to Excel does not work using ClosedXML: dd/MM/yyyy HH:mm:ss is cut off into dd/MM/yyyy

I have an ASP.NET MVC Web app and I am trying to export a DataTable to Excel using ClosedXML v0.87.0.0 One of the DataTable columns is of type DateTime and it has the format dd/MM/yyyy HH:mm:ss. Once DataTable is exported correctly to Excel, I open it using Excel and I see that the DateTime column appears with the format dd/MM/yyyy. It seems like the time has been ignrored.

I need the DateTime field to be exported into Excel as dd/MM/yyyy HH:mm:ss. How can I do it? Why ClosedXML is ignoring the time part?

Below some code snippet (suppose DataTable 'dt' is already populated):

XLWorkbook workbook = new XLWorkbook();
IXLWorksheet workSheet = workbook.Worksheets.Add(dt, dt.TableName);

As seen above, I add the entire DataTable 'dt' to the Excel worksheet. I am not looping over all the DataTable rows and add them one by one.

I know I can loop over each DataTable row and add one by one to the Excel worksheet and applying the correct datetime format to DateTime column by doing something similar to below code snippet:

Using (XLWorkbook wb = new XLWorkbook())
{
    IXLWorksheet ws = wb.Worksheets.Add("worksheet");

    if (dt.Rows.Count > 0)
    {
        ws.Cell("A1").Value = dt.Columns[0].ColumnName;
        ws.Cell("B1").Value = dt.Columns[1].ColumnName;
        ws.Cell("C1").Value = dt.Columns[2].ColumnName;

        for (int i = 0; i < dt.Rows.Count - 1; i++)
        {
            ws.Cell(String.Format("A{0}", (i + 2))).Value = dt.Rows[i][0];
            ws.Cell(String.Format("B{0}", (i + 2))).Value = dt.Rows[i][1];
            ws.Cell(String.Format("C{0}", (i + 2))).Value = dt.Rows[i][2];
            ws.Cell(String.Format("C{0}", (i + 2))).Style.DateFormat.Format = "dd/MM/yyyy HH:mm:ss";
        }
    }
}

... but I want to avoid looping through all DataTable rows. I would like to add DataTable to worksheet at once but then how can I indicate the correct format for DateTime column to ClosedXML?

Upvotes: 0

Views: 1262

Answers (1)

Josue Barrios
Josue Barrios

Reputation: 502

You could use the Epplus package, I think it is easier to use. And the code you would use would be this: Answer - Export DataTable to Excel with EPPlus

Upvotes: 0

Related Questions