Link to home
Create AccountLog in
Avatar of schwientekd
schwientekdFlag 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()
ASKER CERTIFIED SOLUTION
Avatar of Robberbaron (robr)
Robberbaron (robr)
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of schwientekd

ASKER

I haven't had a chance to continue this issue but I will do so in the next week.
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
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Awarded points to expert and flagged my post as a solution because it shows the result I was looking for.  Hopefully will help others.