Exporting Multiple GridViews To Excel SpreadSheet in ASPNet

In this article I will explain with an example, how to export multiple GridViews to Excel format in ASP.Net using C# and VB.Net.

Database

Here I am making use of Microsoft’s Northwind Database. You can download it from here.

Download and install Northwind Database

HTML Markup

The following HTML Markup consists of:

GridView – For displaying Customers data and Order details.

Columns

Both GridView consist of two BoundField columns.

Properties

AllowPaging – For enabling paging in the GridView control. Here it is set to true.

Events

Both GridView have been assigned with an OnPageIndexChanging event handler.

RadioButtonList:-

Two RadioButtonList controls will be used for getting following Information:

Paging will be enabled or not means export current page or export all pages of GridView.

Preference of export i.e. Vertical or Horizontal.

Button – For exporting GridView data to Excel format.

The Button has been assigned with an OnClick event handler

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPaging">

<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="Customer Id" />

<asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />

</Columns>

</asp:GridView>

<br />

<asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="OnPaging">

<asp:BoundField ItemStyle-Width="150px" DataField="OrderID" HeaderText="Order Id" />

<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="Customer Id" />

</Columns>

</asp:GridView>

<br />

Paging Enabled?

<asp:RadioButtonList ID="rbPaging" runat="server">

<asp:ListItem Text="Yes" Value="True" Selected="True"></asp:ListItem>

<asp:ListItem Text="No" Value="False"></asp:ListItem>

</asp:RadioButtonList>

<br />

Export Preference:

<br />

<asp:RadioButtonList ID="rbPreference" runat="server">

<asp:ListItem Text="Vertical" Value="1" Selected="True"></asp:ListItem>

<asp:ListItem Text="Horizontal" Value="2"></asp:ListItem>

</asp:RadioButtonList>

<br/ >

<asp:Button ID="btnExportExcel" runat="server" Text="Export To Excel" OnClick="ExportToExcel" />

Namespaces

You will need to import the following namespaces.

using System.IO;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

VB.Net

Imports System.IO

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Binding Multiple GridViews

Inside the Page_Load event handler, the GetData method is called which accepts SQL query as a parameter.

Inside the GetData method, using SQL query data is fetched from the Northwind database and stored in an object of the DataTable.

Finally, the object of the DataTable is returned.

protected void Page_Load(object sender, EventArgs e)

{

gvCustomers.DataSource = this.GetData("SELECT CustomerID, City FROM Customers");

gvCustomers.DataBind();

gvOrders.DataSource = this.GetData("SELECT OrderID, CustomerID FROM Orders");

gvOrders.DataBind();

}

private DataTable GetData(string query)

{

string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

using (SqlConnection con = new SqlConnection(constr))

{

using (SqlDataAdapter sda = new SqlDataAdapter(query, con))

{

using (DataTable dt = new DataTable())

{

sda.Fill(dt);

return dt;

}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load

gvCustomers.DataSource = Me.GetData("SELECT CustomerID, City FROM Customers")

gvCustomers.DataBind()

gvOrders.DataSource = Me.GetData("SELECT OrderID, CustomerID FROM Orders")

gvOrders.DataBind()

End Sub

Private Function GetData(ByVal query As String) As DataTable

Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString

Using con As SqlConnection = New SqlConnection(constr)

Using sda As SqlDataAdapter = New SqlDataAdapter(query, con)

Using dt As DataTable = New DataTable()

sda.Fill(dt)

Return dt

End Using

End Function

Exporting GridViews to Excel

When the Export Button is clicked, the Response class properties are set.

1. Content-Disposition – It is a response header indicating, the download file is an attachment and allows setting the file name.

Note: For more details on Content-Disposition header, please refer What is Content Disposition Header in ASP.Net.

2. ContentType – It informs the Browser about the file type. In this case it is Excel file.

Next, the StringWriter and HtmlTextWriter class objects are created and the PrepareForExport method is called which accepts ID of the GridView to be exported as parameter.

An object of Table class is created along with the one TableRow and three TableCell class objects to construct the HTML Table dynamically according to the export preference.

Then, the GridView is added as a cell in separate rows.

A check is performed to know the selected preference according to which the layout of the exported GridView in Excel file will be determined.

After that, CSS class named textmode is defined. This class makes sure that all the contents are rendered in Text format (mso number format).

Note: The mso number format style prevents large numbers from getting converted to exponential values.

Finally, StringWriter object is written to the Response which initiates the File download operation.

protected void ExportToExcel(object sender, EventArgs e)

{

Response.Clear();

Response.Buffer = true;

Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");

Response.Charset = "";

Response.ContentType = "application/vnd.ms-excel";

using (StringWriter sw = new StringWriter())

{

HtmlTextWriter hw = new HtmlTextWriter(sw);

this.PrepareForExport(gvCustomers);

this.PrepareForExport(gvOrders);

Table table = new Table();

TableRow row1 = new TableRow();

TableCell cell1 = new TableCell();

cell1.Controls.Add(gvCustomers);

row1.Cells.Add(cell1);

TableCell cell2 = new TableCell();

cell2.Text = " ";

TableCell cell3 = new TableCell();

cell3.Controls.Add(gvOrders);

if (rbPreference.SelectedValue == "2")

{

row1.Cells.Add(cell2);

row1.Cells.Add(cell3);

table.Rows.Add(row1);

}

else

{

TableRow tr2 = new TableRow();

tr2.Cells.Add(cell2);

TableRow tr3 = new TableRow();

tr3.Cells.Add(cell3);

table.Rows.Add(row1);

table.Rows.Add(tr2);

table.Rows.Add(tr3);

}

table.RenderControl(hw);

//style to format numbers to string.

string style = @"<style> .textmode { mso-number-format:\@; </style>";

Response.Write(style);

Response.Output.Write(sw.ToString());

Response.Flush();

Response.End();

}

VB.Net

Protected Sub ExportToExcel(ByVal sender As Object, ByVal 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 StringWriter = New StringWriter()

Dim hw As HtmlTextWriter = New HtmlTextWriter(sw)

Me.PrepareForExport(gvCustomers)

Me.PrepareForExport(gvOrders)

Dim table As Table = New Table()

Dim row1 As TableRow = New TableRow()

Dim cell1 As TableCell = New TableCell()

cell1.Controls.Add(gvCustomers)

row1.Cells.Add(cell1)

Dim cell2 As TableCell = New TableCell()

cell2.Text = " "

Dim cell3 As TableCell = New TableCell()

cell3.Controls.Add(gvOrders)

If rbPreference.SelectedValue = "2"Then

row1.Cells.Add(cell2)

row1.Cells.Add(cell3)

table.Rows.Add(row1)

Else

Dim tr2 As TableRow = New TableRow()

tr2.Cells.Add(cell2)

Dim tr3 As TableRow = New TableRow()

tr3.Cells.Add(cell3)

table.Rows.Add(row1)

table.Rows.Add(tr2)

table.Rows.Add(tr3)

End If

table.RenderControl(hw)

'style to format numbers to string.

Dim style As String = "<style> .textmode { mso-number-format:\@; </style>"

Response.Write(style)

Response.Output.Write(sw.ToString())

Response.Flush()

Response.[End]()

End Using

End Sub

PrepareForExport Method

Inside the PrepareForExport method, the AllowPaging property of the referenced GridView is set to the value selected in RadioButton.

Finally, a FOR EACH loop is executed over the GridView rows and textmode class is applied to each row.

protected void PrepareForExport(GridView gridView)

{

gridView.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value);

gridView.PagerSettings.Visible = false;

gridView.DataBind();

foreach (GridViewRow row in gridView.Rows)

{

//Apply text style to each Row.

row.Attributes.Add("class", "textmode");

}

VB.Net

Protected Sub PrepareForExport(ByVal gridView As GridView)

gridView.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value)

gridView.PagerSettings.Visible = False

gridView.DataBind()

For Each row As GridViewRow In gridView.Rows

'Apply text style to each Row.

row.Attributes.Add("class", "textmode")

End Sub

Implement Paging in GridView

Inside the OnPageIndexChanging event handler, the PageIndex property of the GridView is updated with the new Page Number which was clicked.

Finally, the GridView is populated using the BindGrid method which in-turn displays the new GridView page.

protected void OnPaging(object sender, GridViewPageEventArgs e)

{

GridView gridView = (GridView)sender;

gridView.PageIndex = e.NewPageIndex;

gridView.DataBind();

}

VB.Net

Protected Sub OnPaging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)

Dim gridView As GridView = CType(sender, GridView)

gridView.PageIndex = e.NewPageIndex

gridView.DataBind()

End Sub

Error

The following error occurs when you try to render a control such as GridView to HTML using the RenderControl method.

Server Error in '/ASP.Net' Application.

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

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Control ' gvCustomers ' of type 'GridView' must be placed inside a form tag with runat=server.

Solution

The solution to this problem is to override VerifyRenderingInServerForm event handler.

For more details please refer my article, Exception - Control GridView1 of type GridView must be placed inside a form tag with runat server.

Screenshots

The Form