ASP.NET Export Datagrid to Excel Date Format

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()
schwientekdIT DirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Robberbaron (robr)Commented:
you will need to set formatting on the desired columns because dates are actually stored as numbers in excel (and c#).

there are likely better examples but one on the ExcelPackage page is
worksheet.Column(1).Width = 15;

Open in new window

which sets the width of a column.  You want to set format of column to "dd/mm/yyyy"  

as an aside, 12/11/2000 is 12th November in many countries, including mine. So bets to use "dd/mmm/yyyy" to remove any confusion.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
schwientekdIT DirectorAuthor Commented:
I haven't had a chance to continue this issue but I will do so in the next week.
schwientekdIT DirectorAuthor Commented:
I see how to use the example you give above "worksheet.Column(1).Width = 15" but I can't figure out how to format as a date.  I'm getting errors like "type xxx cannot be converted to OfficeOpenXml.Style.ExcelNumberFormat".

I tried this but it doesn't work:

workSheet.Column(1).Style.Numberformat = DateFormat.ShortDate
schwientekdIT DirectorAuthor Commented:
I figured it out.

workSheet.Column(1).Style.Numberformat.Format = "mm/dd/yyyy"
schwientekdIT DirectorAuthor Commented:
Awarded points to expert and flagged my post as a solution because it shows the result I was looking for.  Hopefully will help others.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.