I am trying to export a datagridview to excel and it works great. Only problem is the columns which have a date are showing up as number. For instance the date 9/17/1963 is showing up in the spreadsheet as 23271. The ideal date format I want to use is mm/dd/year. Here is my code:
Dim participants = GetParticipants()
Dim excel As ExcelPackage = New ExcelPackage
Dim workSheet = excel.Workbook.Worksheets.Add("Participants")
Dim totalCols = participants.Columns.Count
Dim totalRows = participants.Rows.Count
Dim col = 1
Do While (col <= totalCols)
workSheet.Cells(1, col).Value = participants.Columns((col - 1)).ColumnName
col = (col + 1)
Loop
Dim row = 1
Do While (row <= totalRows)
col = 0
Do While (col < totalCols)
workSheet.Cells((row + 1), (col + 1)).Value = participants.Rows((row - 1))(col)
col = (col + 1)
Loop
row = (row + 1)
Loop
Dim memoryStream = New MemoryStream
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
Response.AddHeader("content-disposition", "attachment; filename=participants-" & planID.ToString() & ".xlsx")
excel.SaveAs(memoryStream)
memoryStream.WriteTo(Response.OutputStream)
Response.Flush()
Response.End()