Export 2 gridview to 2 worksheets in single Excel with formatting

I want to export 2 gridviews to 2 worksheets in single excel file. and also don't want to use any ot开发者_StackOverflowher dll or utility or Interop assemblies.

I've just done that a few days a ago.

Imports System.Data.SqlClient

Public Class Export

Inherits System.Web.UI.Page

Dim myGridViews(2) As Object 'number of gridviews -1 

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim myGrid As New GridView
    Dim myN As Integer = 0
    Dim myID As String
    Dim strq As String
    Dim cmd As New SqlCommand
    Dim dt As DataTable

    'load the first gridview
    myGrid = OneWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =0
    myN += 1

    'load the second gridview
    myGrid = TwoWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =1 
    myN += 1

    'load the third gridview
    myGrid = ThreeWorkBook
    myID = myGrid.ID
    strq = "SELECT * FROM " + myID
    cmd = New SqlCommand(strq)
    dt = Getdata(cmd)
    myGrid.DataSource = dt
    myGrid.DataBind()
    myGridViews(myN) = myGrid 'add this gridview to myGridViews, myN =2 
    myN += 1

End Sub

Protected Sub btnExport_Click(sender As Object, e As EventArgs) Handles btnExport.Click
    CreateWorkBook(myGridViews, "ExportToExcel", 80)
End Sub
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
Private Function Getdata(ByVal cmd As SqlCommand) As DataTable
    Dim dt As New DataTable()
    Dim strcon As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
    Dim con As New SqlConnection(strcon)
    Dim sda As New SqlDataAdapter
    cmd.CommandType = CommandType.Text
    cmd.Connection = con
    Try
        con.Open()
        sda.SelectCommand = cmd
        sda.Fill(dt)
        Return dt
    Catch ex As Exception
        Throw (ex)
    Finally
        con.Close()
        sda.Dispose()
        con.Dispose()
    End Try

End Function

End Class