troubleshooting Question

ASP.NET Export Datagrid to Excel Date Format

Avatar of schwientekd
schwientekdFlag for United States of America asked on
ASP.NETVisual Basic.NETMicrosoft Excel
5 Comments2 Solutions174 ViewsLast Modified:
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()
ASKER CERTIFIED SOLUTION
Robberbaron (robr)

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros