Set Date Format while exporting DataTable to Excel using ClosedXML in ASP.Net

Last Reply 5 months ago By pandeyism

Posted 5 months ago

I am using closed XML. My datatable has data in format " MMM YYYY" (MARCH 2019)

Now i am inserting same data in excel cell value but its showing data as "01/03/2019" which is incorrect.

How to solve such error?

My code is-

ws.Cell("A3").Value = dt.Rows[0][0].ToString() ;   

 

You are viewing reply posted by: pandeyism 5 months ago.
Posted 5 months ago Modified on 5 months ago

Hi Gayatri,

Refer below sample.

HTML

<asp:Button Text="Export" runat="server" OnClick="Export" />

Namespaces

C#

using System.Data;
using ClosedXML.Excel;
using System.IO;

VB.Net

Imports System.Data
Imports ClosedXML.Excel
Imports System.IO

Code

C#

protected void Export(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("Id", typeof(int)), 
    new DataColumn("Name", typeof(string)), 
    new DataColumn("Date", typeof(string)) });
    dt.Rows.Add(1, "Mudassar Khan", "MARCH 2019");
    dt.Rows.Add(2, "John Hammod", "APRIL 2019");
    dt.Rows.Add(3, "Robert Schidner", "MAY 2019");
    dt.Rows.Add(4, "Suzanne Mathews", "JUNE 2019");
    using (XLWorkbook wb = new XLWorkbook())
    {
        var ws = wb.Worksheets.Add("worksheet");
        if (dt.Rows.Count > 0)
        {
            // Adding HeaderRow.
            ws.Cell("A1").Value = dt.Columns[0].ColumnName;
            ws.Cell("B1").Value = dt.Columns[1].ColumnName;
            ws.Cell("C1").Value = dt.Columns[2].ColumnName;

            // Adding DataRows.
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ws.Cell("A" + (i + 2)).Value = dt.Rows[i][0];
                ws.Cell("B" + (i + 2)).Value = dt.Rows[i][1];
                ws.Cell("c" + (i + 2)).Value = dt.Rows[i][2];
                // Formating cell value.
                ws.Cell("C" + (i + 2)).Style.DateFormat.Format = "MMMM yyyy";
            }
        }
        ws.Column(2).AdjustToContents();
        ws.Column(3).AdjustToContents();
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;filename=" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xlsx");
        using (MemoryStream MyMemoryStream = new MemoryStream())
        {
            wb.SaveAs(MyMemoryStream);
            MyMemoryStream.WriteTo(Response.OutputStream);
            Response.Flush();
            Response.End();
        }
    }
}

VB.Net

Protected Sub Export(ByVal sender As Object, ByVal e As EventArgs)
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Date", GetType(String))})
    dt.Rows.Add(1, "Mudassar Khan", "MARCH 2019")
    dt.Rows.Add(2, "John Hammod", "APRIL 2019")
    dt.Rows.Add(3, "Robert Schidner", "MAY 2019")
    dt.Rows.Add(4, "Suzanne Mathews", "JUNE 2019")

    Using wb As XLWorkbook = New XLWorkbook()
        Dim ws = wb.Worksheets.Add("worksheet")

        If dt.Rows.Count > 0 Then
            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 i As Integer = 0 To dt.Rows.Count - 1
                ws.Cell("A" & (i + 2)).Value = dt.Rows(i)(0)
                ws.Cell("B" & (i + 2)).Value = dt.Rows(i)(1)
                ws.Cell("c" & (i + 2)).Value = dt.Rows(i)(2)
                ws.Cell("C" & (i + 2)).Style.DateFormat.Format = "MMMM yyyy"
            Next
        End If

        ws.Column(2).AdjustToContents()
        ws.Column(3).AdjustToContents()
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        Response.AddHeader("content-disposition", "attachment;filename=" & DateTime.Now.ToString("dd-MMM-yyyy") & ".xlsx")

        Using MyMemoryStream As MemoryStream = New MemoryStream()
            wb.SaveAs(MyMemoryStream)
            MyMemoryStream.WriteTo(Response.OutputStream)
            Response.Flush()
            Response.End()
        End Using
    End Using
End Sub

Screenshot