ASP.NET 4.0 Need help with a SQL table date converting to a 5 digit number when Exported to Excel

[leo57]

The date in the SQL table is DateAwarded(date, null).
I have and asp 4.0 page as follows. Can something be done in the Http: lines to make it a date and not a 5 digit number.
I tried several ways converting the date to text and from text to date and nothing makes it an actual date when it gets to Excel. I either get a 5 digit number or I get a date in text format. The user wants it to be a real Date not text.

    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
        Dim ExcelFilename As String = "Recognition"
        Dim tbl As DataTable
        ' 10-25-13 Added ePrizeID of user so it only exports their records
        ' it was showing all the records when exported
        'removed UniqueID, from SQL
        'Dim SQLString As String = "SELECT CommonName, EmployeeEpriseID, Location," & _
        '                        "VPName, Nominator, ReportToManager, Report1, Report2, Report3, " & _
        '                        "AwardType, CONVERT (nvarchar(12), DateAwarded, 101) AS DateAwarded, Event, " & _
        '                        "NominationSummary FROM Recognition " & _
        '                        "Where year(DateAwarded) = " & Me.ddlChooseYear.Text & " AND " & _
        '                        " VPName = '" & Me.ddlChooseVP.Text & "' And ePrizeID = '" & Session("ePrizeID") & "' " & _
        '                        "ORDER BY UniqueID DESC"

        ' 10-28-13 DBP changed the Enterprise ID to an integer with Convert
        Dim SQLString As String = "SELECT CommonName, CONVERT(bigint, EmployeeEpriseID, 101) AS EmployeeEpriseID, Location," & _
                                "VPName, Nominator, ReportToManager, Report1, Report2, Report3, " & _
                                "AwardType, CONVERT(Date, testdate, 101) AS DateAwarded, Event, Awarded, " & _
                                "NominationSummary FROM Recognition_test "
                                

        tbl = [highlight #FCAF3E]PopulateGridwithSQL[/highlight](SQLString)
        Dim pck As ExcelPackage = New ExcelPackage()

        Dim Tabname As String = "Recognition"
        'Create the worksheet
        Dim ws As ExcelWorksheet = pck.Workbook.Worksheets.Add(Tabname)

        'Load the datatable into the sheet, starting from cell A1. Print the column names on row 1
        ws.Cells("A1").LoadFromDataTable(tbl, True)

        'Write it back to the client
        HttpContext.Current.Response.Clear()
        HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;  filename=" + ExcelFilename + ".xlsx")
        HttpContext.Current.Response.BinaryWrite(pck.GetAsByteArray())
        HttpContext.Current.Response.End()
        HttpContext.Current.ApplicationInstance.CompleteRequest()

        pck = Nothing
    End Sub

 

[jbenson001]

Google is your friend..
you have to use mso-xxx formats for Excel:

http://cosicimiento.blogspot.com/2008/11/styling-excel-cells-with-mso-number.html

example how to use:

http://forums.asp.net/t/1888013.aspx