Export to Excel

[Randy]

I have 4 grid controlss on 4 MultiPage controls and am exporting to a single Excel file.  The problem is that when I load the spreadsheet into Excel, It loads in Protected View. When I click the 'Enable Editing' button each of the data cells shows a comment that says," The number in this cell is formatted as text or preceded by an apostrophy." I then have to click the warning and convert 'each' cell to a number. Trying to select a whole column and format to number does not work.  I've looked at some of the articles on formatting cells when exporting but don't think the discussed events are firing.
Here is my exporting code:
#Region "exporting"

    Private struct As xls.ExportStructure = New xls.ExportStructure()
    Private tbl As xls.Table = New xls.Table()
    Private row As Integer = 1
    Private col As Integer = 1
    Private gridControlsFound As List(Of RadGrid) = New List(Of RadGrid)

    Private Sub btnexport_Click(sender As Object, e As EventArgs) Handles btnexport.Click
        FindGridsRecursive()
        ExportGrid()
    End Sub
    Private Sub FindGridsRecursive()
        Dim i = 1
        For Each pg As RadPageView In RadPageView1.MultiPage.PageViews
            Select Case i
                Case 1
                    Dim Grid As RadGrid = FindControl("Week1")
                    gridControlsFound.Add(DirectCast(Grid, RadGrid))
                    i += 1
                Case 2
                    Dim Grid As RadGrid = pg.FindControl("Week2")
                    gridControlsFound.Add(DirectCast(Grid, RadGrid))
                    i += 1
                Case 3
                    Dim Grid As RadGrid = pg.FindControl("PayPeriod")
                    gridControlsFound.Add(DirectCast(Grid, RadGrid))
                    i += 1
                Case 4
                    Dim Grid As RadGrid = pg.FindControl("ACDetail")
                    gridControlsFound.Add(DirectCast(Grid, RadGrid))
            End Select
        Next
    End Sub

    Private isFirstItem As Boolean = True
    Private Sub GenerateTable(grid As RadGrid, singleTable As xls.Table)
        '       If ExportingType.SelectedValue = "1" Then
        singleTable = New xls.Table(grid.ID)
        row = 1
        col = 1
        'Else
        'If Not isFirstItem Then
        'row += 1
        'Else
        'isFirstItem = False
        'End If
        'End If

        Dim headerItem As GridHeaderItem = TryCast(grid.MasterTableView.GetItems(GridItemType.Header)(0), GridHeaderItem)

        For i As Integer = 3 To headerItem.Cells.Count - 1
            singleTable.Cells(i - 1, row).Value = headerItem.Cells(i).Text
        Next

        row += 1

        For Each item As GridDataItem In grid.MasterTableView.Items
            For Each column As GridColumn In grid.Columns
                singleTable.Cells(col, row).Value = item(column.UniqueName).Text
                col += 1
            Next
            col = 1
            row += 1
        Next

        struct.Tables.Add(singleTable)
    End Sub

    Private Sub ExportGrid()
        For Each grid As RadGrid In gridControlsFound
            grid.AllowPaging = False
            grid.CurrentPageIndex = 0
            getlocadate()
            grid.Rebind()
            GenerateTable(grid, tbl)
        Next

        Dim renderer As New xls.XlsBiffRenderer(struct)
        Dim renderedBytes As Byte() = renderer.Render()
        Response.Clear()
        Dim filename As String = loca & "|" & startDate.ToShortDateString & "<>" & endDate2.ToString & ".xls"
        Response.AppendHeader("Content-Disposition:", "attachment; filename=" & filename)
        Response.ContentType = "application/vnd.ms-excel"
        Response.BinaryWrite(renderedBytes)
        Response.End()
    End Sub
#End Region

[Kostadin]

Hi Randy,

In order to format the cell which contains a numeric values you need to manually parse them to the appropriate numeric type. For instance, please check out the following code snippet which get the grid cell and try parsing the text as integer.

For Each item As GridDataItem In grid.MasterTableView.Items
    For Each column As GridColumn In grid.Columns
        Dim result As Integer

        If Integer.TryParse(item(column.UniqueName).Text, result) Then
            singleTable.Cells(col, row).Value = result
        End If
        col += 1
    Next
    col = 1
    row += 1
Next

Keep in mind that if the data of the column is stored as an integer in your database, this explicitly converting will not be necessary.

Regards,
Kostadin
Telerik