This is a migrated thread and some comments may be shown as answers.

Export to Excel

1 Answer 27 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Randy
Top achievements
Rank 2
Randy asked on 26 Aug 2014, 02:43 PM
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

1 Answer, 1 is accepted

Sort by
0
Kostadin
Telerik team
answered on 29 Aug 2014, 08:32 AM
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
 

Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

 
Tags
Grid
Asked by
Randy
Top achievements
Rank 2
Answers by
Kostadin
Telerik team
Share this question
or