Avatar of schwientekd
schwientekd
Flag for United States of America asked on

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()
ASP.NETVisual Basic.NETMicrosoft Excel

Avatar of undefined
Last Comment
schwientekd

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Robberbaron (robr)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
schwientekd

ASKER
I haven't had a chance to continue this issue but I will do so in the next week.
schwientekd

ASKER
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
SOLUTION
schwientekd

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
schwientekd

ASKER
Awarded points to expert and flagged my post as a solution because it shows the result I was looking for.  Hopefully will help others.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23