I have already explained how to Export GridView to Excel in my following articles
Export GridView To Word/Excel/PDF/CSV in ASP.Net
Export GridView with Images from database to Word, Excel and PDF Formats
Export GridView with Images to Word, Excel and PDF Formats in ASP.Net
But this is something different and is hitting the forums quite frequently.
1. How to Export Multiple GridViews to Excel?
2. How to align GridViews in Excel?
And the article below is the working answer for the above questions.
GridViews
Below is the HTML markup of the two GridViews
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true"
OnPageIndexChanging = "OnPaging">
<Columns>
<asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"
HeaderText = "CustomerID" />
<asp:BoundField ItemStyle-Width = "150px" DataField = "City"
HeaderText = "City"/>
</Columns>
</asp:GridView>
<br /><br />
<asp:GridView ID="GridView2" runat="server"
AutoGenerateColumns = "false" Font-Names = "Arial"
Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
HeaderStyle-BackColor = "green" AllowPaging ="true"
OnPageIndexChanging = "OnPaging" >
<Columns>
<asp:BoundField ItemStyle-Width = "150px" DataField = "OrderID"
HeaderText = "Order ID" />
<asp:BoundField ItemStyle-Width = "150px" DataField = "CustomerID"
HeaderText = "Customer ID"/>
</Columns>
</asp:GridView>
Namespaces
You will need to import the following namespaces
C#
using System.IO;
using System.Text;
using System.Configuration;
VB.Net
Imports System.IO
Imports System.Text
Imports System.Configuration
Connection String
Below is the connection string used for this tutorial. As you will notice I am using the NorthWind SQL Server Data base which you can download by clicking here
<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQLEXPRESS;
database=Northwind;Integrated Security=true" />
</connectionStrings>
DataBinding GridViews
Below is the how I bind the two GridViews in the Page Load Event of the ASP.Net Web Page. You will notice I am running a simple Select Query and binding the data to both the GridViews using the GetData function
C#
protected void Page_Load(object sender, EventArgs e)
{
string strQuery = "select CustomerID,City,Country from customers";
SqlCommand cmd = new SqlCommand(strQuery);
DataTable dt = GetData(cmd);
GridView1.DataSource = dt;
GridView1.DataBind();
strQuery = "select OrderID, CustomerID, EmployeeID from Orders";
cmd = new SqlCommand(strQuery);
dt = GetData(cmd);
GridView2.DataSource = dt;
GridView2.DataBind();
}
private DataTable GetData(SqlCommand cmd)
{
DataTable dt = new DataTable();
String strConnString = ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlDataAdapter sda = new SqlDataAdapter();
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
sda.SelectCommand = cmd;
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
sda.Dispose();
con.Dispose();
}
}
VB.Net
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
Dim strQuery As String = "select CustomerID,City,Country from customers"
Dim cmd As New SqlCommand(strQuery)
Dim dt As DataTable = GetData(cmd)
GridView1.DataSource = dt
GridView1.DataBind()
strQuery = "select OrderID, CustomerID, EmployeeID from Orders"
cmd = New SqlCommand(strQuery)
dt = GetData(cmd)
GridView2.DataSource = dt
GridView2.DataBind()
End Sub
Private Function GetData(ByVal cmd As SqlCommand) As DataTable
Dim dt As New DataTable()
Dim strConnString As String = ConfigurationManager _
.ConnectionStrings("conString").ConnectionString()
Dim con As New SqlConnection(strConnString)
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
The figure below displays the two GridViews we just populated using the NorthWind Database.
![Multiple GridViews displayed on ASP.Net Webpage]()
Concept
Now in order to export multiple GridViews you will need to wrap the GridViews in a Control that will act as a container and then export that control instead of the GridViews. Hence here I am creating a fake dynamic table and exporting the table instead of GridViews.
Option of Paging Enable Disable and GridView Alignment
Here I provided the user facility whether user wants complete records or only the records for the current page. Secondly I also provided the choice whether the two GridViews should be aligned horizontally next to each other or vertically one after another using the two RadioButtonLists
<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
<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>
In the front end they look like below. As you can see user can choose how he wants the output in the Exported Excel file.
![Option to Enable Disable Paging and also the Orientation while exporting]()
Exporting the Multiple GridViews
Now comes the final part that is exporting the two GridViews to Excel on the click of the Export Button
Refer the code below
C#
protected void btnExportExcel_Click(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";
StringWriter sw = new StringWriter();
HtmlTextWriter hw = new HtmlTextWriter(sw);
PrepareForExport(GridView1);
PrepareForExport(GridView2);
Table tb = new Table();
TableRow tr1 = new TableRow();
TableCell cell1 = new TableCell();
cell1.Controls.Add(GridView1);
tr1.Cells.Add(cell1);
TableCell cell3 = new TableCell();
cell3.Controls.Add(GridView2);
TableCell cell2 = new TableCell();
cell2.Text = " ";
if (rbPreference.SelectedValue == "2")
{
tr1.Cells.Add(cell2);
tr1.Cells.Add(cell3);
tb.Rows.Add(tr1);
}
else
{
TableRow tr2 = new TableRow();
tr2.Cells.Add(cell2);
TableRow tr3 = new TableRow();
tr3.Cells.Add(cell3);
tb.Rows.Add(tr1);
tb.Rows.Add(tr2);
tb.Rows.Add(tr3);
}
tb.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 btnExportExcel_Click(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"
Dim sw As New StringWriter()
Dim hw As New HtmlTextWriter(sw)
PrepareForExport(GridView1)
PrepareForExport(GridView2)
Dim tb As New Table()
Dim tr1 As New TableRow()
Dim cell1 As New TableCell()
cell1.Controls.Add(GridView1)
tr1.Cells.Add(cell1)
Dim cell3 As New TableCell()
cell3.Controls.Add(GridView2)
Dim cell2 As New TableCell()
cell2.Text = " "
If rbPreference.SelectedValue = "2" Then
tr1.Cells.Add(cell2)
tr1.Cells.Add(cell3)
tb.Rows.Add(tr1)
Else
Dim tr2 As New TableRow()
tr2.Cells.Add(cell2)
Dim tr3 As New TableRow()
tr3.Cells.Add(cell3)
tb.Rows.Add(tr1)
tb.Rows.Add(tr2)
tb.Rows.Add(tr3)
End If
tb.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 Sub
As you will notice I am creating a Dynamic Table and adding the GridViews to the Dynamic Table Cells and then rendering the Table instead of the GridViews this is is the trick that makes it possible to export two or multiple GridViews to a Single Excel Sheet
You will notice the PrepareForExport Function which basically gives some formatting and styling to the GridView refer below.
C#
protected void PrepareForExport(GridView Gridview)
{
Gridview.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value);
Gridview.DataBind();
//Change the Header Row back to white color
Gridview.HeaderRow.Style.Add("background-color", "#FFFFFF");
//Apply style to Individual Cells
for (int k = 0; k < Gridview.HeaderRow.Cells.Count; k++)
{
Gridview.HeaderRow.Cells[k].Style.Add("background-color", "green");
}
for (int i = 0; i < Gridview.Rows.Count; i++)
{
GridViewRow row = Gridview.Rows[i];
//Change Color back to white
row.BackColor = System.Drawing.Color.White;
//Apply text style to each Row
row.Attributes.Add("class", "textmode");
//Apply style to Individual Cells of Alternating Row
if (i % 2 != 0)
{
for (int j = 0; j < Gridview.Rows[i].Cells.Count; j++)
{
row.Cells[j].Style.Add("background-color", "#C2D69B");
}
}
}
}
VB.Net
Protected Sub PrepareForExport(ByVal Gridview As GridView)
Gridview.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value)
Gridview.DataBind()
'Change the Header Row back to white color
Gridview.HeaderRow.Style.Add("background-color", "#FFFFFF")
'Apply style to Individual Cells
For k As Integer = 0 To Gridview.HeaderRow.Cells.Count - 1
Gridview.HeaderRow.Cells(k).Style.Add("background-color", "green")
Next
For i As Integer = 0 To Gridview.Rows.Count - 1
Dim row As GridViewRow = Gridview.Rows(i)
'Change Color back to white
row.BackColor = System.Drawing.Color.White
'Apply text style to each Row
row.Attributes.Add("class", "textmode")
'Apply style to Individual Cells of Alternating Row
If i Mod 2 <> 0 Then
For j As Integer = 0 To Gridview.Rows(i).Cells.Count - 1
row.Cells(j).Style.Add("background-color", "#C2D69B")
Next
End If
Next
End Sub
The Screenshots below display the GridViews in the Horizontal and the Vertical Alignments
Vertical Alignment
![Multiple GridViews aligned Vertically in the exported Excel File]()
Horizontal Alignment
![Multiple GridViews aligned Horizontally in the exported Excel File]()
You might get the following errors. Click on the links below for the solution to resolve them
RegisterForEventValidation can only be called during Render()
Control 'GridView1' of type 'GridView' must be placed inside a form tag with runat=server
To View the Live demo Click here
You can download the complete source code in VB.Net and C# using the link below.
ExportMultipleGridViewsToExcel.zip (6.07 kb)
