Export DataTable to Excel with Date Formatting using ClosedXML in ASP.Net

Last Reply 21 days ago By dharmendr

Posted 21 days ago

Greeting Everyone

I am trying to Export DataTable to excel using ClosedXML and one of the column in Microsoft SQL is date.

When date column is exported using (dd/mm/yyyy) format the excel does not pick as date until the cell is clicked.

Also the sql command and code used are

 SQL

Select FORMAT(DateOfBirth, 'dd/MM/yyyy') as 'DATE OF BIRTH' from User

 vb.net 

Using wb As New XLWorkbook()
Dim ws = wb.Worksheets.Add(dt, "Employee")
ws.Table(0).ShowAutoFilter = False ' Disable AutoFilter.
ws.Table(0).Theme = XLTableTheme.None ' Remove Theme.
ws.Columns().AdjustToContents() ' Resize all columns.
ws.Column("B").Style.DateFormat.Format = "dd/MMM/yyyy"
'' I have tried both (1 at a time) but same issue
ws.Column("B").Style.DateFormat.SetFormat("DD/MMM/YYYY")

 

You are viewing reply posted by: dharmendr 21 days ago.
Posted 21 days ago