ASP.NET Export Datagrid to Excel Date Format

schwientekd
schwientekd used Ask the Experts™
on
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()
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
schwientekdIT Director

Author

Commented:
I haven't had a chance to continue this issue but I will do so in the next week.
schwientekdIT Director

Author

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 Director
Commented:
I figured it out.

workSheet.Column(1).Style.Numberformat.Format = "mm/dd/yyyy"
schwientekdIT Director

Author

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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial