Exporting multiple gridviews into Excel

I have multiple gridviews to be exported to excel. 1 gridview per worksheet.

How to do that ? ( or in general how to write an html string to a worksheet )

This is to be done in asp.net , server side.

I can create multiple worksheets via epplus.codeplex.com nicely, but it works on cell level. I could export DataTable, but how 开发者_开发知识库to export Gridview's HTML ?

I didn´t find where and how was "clist" defined

For Each gView As GridView In cList
    CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
Next

I'm not sure if this works with GridView, but it's worth a try:

http://www.codeproject.com/KB/office/ExcelDataTable.aspx

Edit: it still works, tested with the following code:

        Dim data As New DataTable("Sample-Data")
        data.Columns.Add("ID", GetType(Int32)).AutoIncrement = True
        data.Columns.Add("Text", GetType(String))
        For i As Int32 = 1 To 100
            Dim newRow As DataRow = data.NewRow
            newRow("Text") = i & ". Row"
            data.Rows.Add(newRow)
        Next

        Dim grid As New System.Web.UI.WebControls.GridView
        grid.HeaderStyle.Font.Bold = True
        grid.DataSource = data
        grid.DataMember = data.TableName
        grid.DataBind()

        ' render the GridView control to a file '
        Using sw As New IO.StreamWriter("c:\\Temp\test.xls")
            Using hw As New HtmlTextWriter(sw)
                grid.RenderControl(hw)
            End Using
        End Using

The trick is that excel can display a HTML-Table and a GridView is rendered as a Table.

However this does not work if you want to generate multiple WorkSheets.

Excel perfectly works with XML.

Save some workbook from Excel as XML table and open it in notepad to see how it works.

I've just done exactly the same thing. One excel worksheet for each database talbe/gridview

You'll find everything in this blog post. Let me know if you need more help.

Public Shared Sub CreateWorkBook(ByVal cList As Object, ByVal wbName As String, ByVal CellWidth As Integer)
    Dim attachment As String = "attachment; filename=""" & wbName & ".xml"""

    HttpContext.Current.Response.ClearContent()

    HttpContext.Current.Response.AddHeader("content-disposition", attachment)
    HttpContext.Current.Response.ContentType = "application/ms-excel"

    Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
    sw.WriteLine("<?xml version=""1.0""?>")

    sw.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
    sw.WriteLine("<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""")

    sw.WriteLine("xmlns:o=""urn:schemas-microsoft-com:office:office""")
    sw.WriteLine("xmlns:x=""urn:schemas-microsoft-com:office:excel""")

    sw.WriteLine("xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""")
    sw.WriteLine("xmlns:html=""http://www.w3.org/TR/REC-html40"">")

    sw.WriteLine("<DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">")
    sw.WriteLine("<LastAuthor>Try Not Catch</LastAuthor>")

    sw.WriteLine("<Created>2010-05-15T19:14:19Z</Created>")
    sw.WriteLine("<Version>11.9999</Version>")

    sw.WriteLine("</DocumentProperties>")
    sw.WriteLine("<ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">")

    sw.WriteLine("<WindowHeight>9210</WindowHeight>")
    sw.WriteLine("<WindowWidth>19035</WindowWidth>")

    sw.WriteLine("<WindowTopX>0</WindowTopX>")
    sw.WriteLine("<WindowTopY>90</WindowTopY>")

    sw.WriteLine("<ProtectStructure>False</ProtectStructure>")
    sw.WriteLine("<ProtectWindows>False</ProtectWindows>")

    sw.WriteLine("</ExcelWorkbook>")
    sw.WriteLine("<Styles>")

    sw.WriteLine("<Style ss:ID=""Default"" ss:Name=""Normal"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom""/>")

    sw.WriteLine("<Borders/>")
    sw.WriteLine("<Font/>")

    sw.WriteLine("<Interior/>")
    sw.WriteLine("<NumberFormat/>")

    sw.WriteLine("<Protection/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s22"">")
    sw.WriteLine("<Alignment ss:Horizontal=""Center"" ss:Vertical=""Center"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("<Font ss:Bold=""1""/>")
    sw.WriteLine("</Style>")

    sw.WriteLine("<Style ss:ID=""s23"">")
    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")

    sw.WriteLine("<Borders>")
    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")

    sw.WriteLine("ss:Color=""#000000""/>")
    sw.WriteLine("</Borders>")

    sw.WriteLine("</Style>")
    sw.WriteLine("<Style ss:ID=""s24"">")

    sw.WriteLine("<Alignment ss:Vertical=""Bottom"" ss:WrapText=""1""/>")
    sw.WriteLine("<Borders>")

    sw.WriteLine("<Border ss:Position=""Bottom"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Left"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Right"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("<Border ss:Position=""Top"" ss:LineStyle=""Continuous"" ss:Weight=""1""")
    sw.WriteLine("ss:Color=""#000000""/>")

    sw.WriteLine("</Borders>")
    sw.WriteLine("<Font ss:Color=""#FFFFFF""/>")

    sw.WriteLine("<Interior ss:Color=""#FF6A6A"" ss:Pattern=""Solid""/>") 'set header colour here
    sw.WriteLine("</Style>")

    sw.WriteLine("</Styles>")
    For Each gView As GridView In cList
        CreateWorkSheet(gView.ID.ToString, sw, gView, CellWidth)
    Next
    sw.WriteLine("</Workbook>")
    HttpContext.Current.Response.Write(sw.ToString())
    HttpContext.Current.Response.End()

End Sub
Private Shared Sub CreateWorkSheet(ByVal wsName As String, ByVal sw As System.IO.StringWriter, ByVal gv As GridView, ByVal cellwidth As Integer)
    If IsNothing(gv.HeaderRow) = False Then
        sw.WriteLine("<Worksheet ss:Name=""" & wsName & """>")
        Dim cCount As Integer = gv.HeaderRow.Cells.Count
        Dim rCount As Long = gv.Rows.Count + 1
        sw.WriteLine("<Table ss:ExpandedColumnCount=""" & cCount & """ ss:ExpandedRowCount=""" & rCount & """ x:FullColumns=""1""")
        sw.WriteLine("x:FullRows=""1"">")
        For i As Integer = (cCount - cCount) To (cCount - 1)
            sw.WriteLine("<Column ss:AutoFitWidth=""1"" ss:Width=""" & cellwidth & """/>")
        Next

        GridRowIterate(gv, sw)
        sw.WriteLine("</Table>")
        sw.WriteLine("<WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">")

        sw.WriteLine("<Selected/>")
        sw.WriteLine("<DoNotDisplayGridlines/>")

        sw.WriteLine("<ProtectObjects>False</ProtectObjects>")
        sw.WriteLine("<ProtectScenarios>False</ProtectScenarios>")

        sw.WriteLine("</WorksheetOptions>")
        sw.WriteLine("</Worksheet>")
    End If
End Sub
Private Shared Sub GridRowIterate(ByVal gv As GridView, ByVal sw As System.IO.StringWriter)
    sw.WriteLine("<Row>")

    For Each tc As TableCell In gv.HeaderRow.Cells
        Dim tcText As String = tc.Text

        Dim tcWidth As String = gv.Width.Value
        Dim dType As String = "String"

        If IsNumeric(tcText) = True Then

            dType = "Number"

        End If
        sw.WriteLine("<Cell ss:StyleID=""s24""><Data ss:Type=""String"">" & tcText & "</Data></Cell>")

    Next
    sw.WriteLine("</Row>")

    For Each gr As GridViewRow In gv.Rows
        sw.WriteLine("<Row>")

        For Each gc As TableCell In gr.Cells
            Dim gcText As String = gc.Text
            Dim dType As String = "String"

            If IsNumeric(gcText) = True Then

                dType = "Number"
                gcText = CDbl(gcText)

            End If
            sw.WriteLine("<Cell ss:StyleID=""s23""><Data ss:Type=""" & dType & """>" & gcText & "</Data></Cell>")

        Next
        sw.WriteLine("</Row>")
    Next

End Sub