How to format excel file when exporting gridview to excel vb

Question

User1717218719 posted

I have the following code which works great except it displayes the gridview in the style I have it in my asp.net code. I would like to display it in a plain way with cells all around the data so it looks like I have just copied and pasted the data into the file. Any help with how to format would be great many thanks.

Protected Sub expexcel_Click(sender As Object, e As EventArgs) Handles expexcel.Click

        Dim sw As New StringWriter()
        Dim hw As New System.Web.UI.HtmlTextWriter(sw)
        Dim frm As HtmlForm = New HtmlForm()

        Page.Response.AddHeader("content-disposition", "attachment;filename=results.xls")
        Page.Response.ContentType = "application/vnd.ms-excel"
        Page.Response.Charset = ""
        Page.EnableViewState = True

        frm.Attributes("runat") = "server"
        Controls.Add(frm)
        frm.Controls.Add(GridView1)
        frm.RenderControl(hw)
        Response.Write(sw.ToString())
        Response.End()

    End Sub

Answer 1

User-775646050 posted

You can use the code below to export to Excel. It has some basic formatting but you can modify to make it look how you want when exported.

Protected Sub Button1_OnClick(sender As Object, e As EventArgs)
    Response.Clear()
    Response.Buffer = True
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls")
    Response.Charset = ""
    Response.ContentType = "application/vnd.ms-excel"
    Using sw As New StringWriter()
      Dim htw As New HtmlTextWriter(sw)

      GridView1.AllowPaging = False
      BindGrid()

      GridView1.HeaderRow.BackColor = Color.White
      GridView1.HeaderRow.ForeColor = Color.Black
      For Each cell As TableCell In GridView1.HeaderRow.Cells
        cell.BackColor = Color.White
        cell.BorderStyle = BorderStyle.Solid
      Next

      For Each row As GridViewRow In GridView1.Rows
        row.BackColor = Color.White
      Next

      GridView1.RenderControl(htw)

      Dim style As String = "<style> .textmode { } </style>"
      Response.Write(style)
      Response.Output.Write(sw.ToString())
      Response.Flush()
      Response.[End]()
    End Using

  End Sub

Answer 2

User-719153870 posted

Hi E.RU,

I would like to display it in a plain way with cells all around the data so it looks like I have just copied and pasted the data into the file.

Not sure if i understand it correctly, are you trying to remove all its style before you export the gridview to Excel?

If so, it should be the datasource of the gridview instead of the gridview itself that should be exported.

Please check the GemBox sample, add it via Nuget Package Management and import GemBox.Spreadsheet and you can export the datatable to Excel without any style now.

Protected Sub Button2_Click(sender As Object, e As EventArgs)
        ' If using Professional version, put your serial key below.
        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")

        Dim workbook = New ExcelFile
        Dim worksheet = workbook.Worksheets.Add("DataTable to Sheet")

        Dim dataTable = New DataTable

        dataTable.Columns.Add("ID", Type.GetType("System.Int32"))
        dataTable.Columns.Add("FirstName", Type.GetType("System.String"))
        dataTable.Columns.Add("LastName", Type.GetType("System.String"))

        dataTable.Rows.Add(New Object() {100, "John", "Doe"})
        dataTable.Rows.Add(New Object() {101, "Fred", "Nurk"})
        dataTable.Rows.Add(New Object() {103, "Hans", "Meier"})
        dataTable.Rows.Add(New Object() {104, "Ivan", "Horvat"})
        dataTable.Rows.Add(New Object() {105, "Jean", "Dupont"})
        dataTable.Rows.Add(New Object() {106, "Mario", "Rossi"})

        worksheet.Cells(0, 0).Value = "DataTable insert example:"

        ' Insert DataTable to an Excel worksheet.
        worksheet.InsertDataTable(dataTable,
            New InsertDataTableOptions() With
            {
                .ColumnHeaders = True,
                .StartRow = 2
            })

        workbook.Save("D:\Test\aaa.xlsx")
    End Sub

Best Regard,

Yang Shen

Answer 3

User1717218719 posted

Hi Smtaz,

I have tried your code but I recieve the error "Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server"

Do you know why this might be happening ?

Thanks

Answer 4

User1717218719 posted

I also have a column which contains leading zeros. how can I format it that they dont disapear when exported to excel?

Thanks

Answer 5

User475983607 posted

I also have a column which contains leading zeros. how can I format it that they dont disapear when exported to excel?

The standard approach is using an Excel library to generate an accrual excel file rather than sending the browser HTML and letting Excel try to figure out your intent.

Answer 6

User2103319870 posted

"Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server"

Try adding the below lines of code in your code behind page and that will resolve the issue

Public Overloads Overrides Sub VerifyRenderingInServerForm ByVal control As Control)

        ' Verifies that the control is rendered

End Sub