Multiple GridView that contain multiple header row export data to excel(csharp)
Result:
Design Code:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="gridview.WebForm2"
EnableEventValidation="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Font-Names="Arial"
Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green"
AllowPaging="True" OnPageIndexChanging="OnPaging" OnRowCreated="GridView1_RowCreated">
<Columns>
<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
<asp:BoundField ItemStyle-Width="150px" DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
<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" />
<asp:BoundField ItemStyle-Width="150px" DataField="EmployeeID" HeaderText="Employee ID" />
</Columns>
</asp:GridView>
<br />
Paging Enabled?
<asp:RadioButtonList ID="RadioButtonList1" runat="server">
<asp:ListItem Selected="True" Value="True">Yes</asp:ListItem>
<asp:ListItem Value="False">No</asp:ListItem>
</asp:RadioButtonList>
<br />
Export Preference
<asp:RadioButtonList ID="RadioButtonList2" runat="server">
<asp:ListItem Selected="True" Value="1">Vertical</asp:ListItem>
<asp:ListItem Value="2">Horizontal</asp:ListItem>
</asp:RadioButtonList>
<asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />
</div>
</form>
</body>
</html>
EnableEventValidation="false" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" Font-Names="Arial"
Font-Size="11pt" AlternatingRowStyle-BackColor="#C2D69B" HeaderStyle-BackColor="green"
AllowPaging="True" OnPageIndexChanging="OnPaging" OnRowCreated="GridView1_RowCreated">
<Columns>
<asp:BoundField ItemStyle-Width="150px" DataField="CustomerID" HeaderText="CustomerID" />
<asp:BoundField ItemStyle-Width="150px" DataField="City" HeaderText="City" />
<asp:BoundField ItemStyle-Width="150px" DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
<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" />
<asp:BoundField ItemStyle-Width="150px" DataField="EmployeeID" HeaderText="Employee ID" />
</Columns>
</asp:GridView>
<br />
Paging Enabled?
<asp:RadioButtonList ID="RadioButtonList1" runat="server">
<asp:ListItem Selected="True" Value="True">Yes</asp:ListItem>
<asp:ListItem Value="False">No</asp:ListItem>
</asp:RadioButtonList>
<br />
Export Preference
<asp:RadioButtonList ID="RadioButtonList2" runat="server">
<asp:ListItem Selected="True" Value="1">Vertical</asp:ListItem>
<asp:ListItem Value="2">Horizontal</asp:ListItem>
</asp:RadioButtonList>
<asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />
</div>
</form>
</body>
</html>
CodeBehind:
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
namespace gridview
{
public partial class WebForm2 : System.Web.UI.Page
{
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["testString"].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();
}
}
protected void PrepareForExport(GridView Gridview)
{
Gridview.AllowPaging = Convert.ToBoolean(RadioButtonList1.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");
}
}
}
}
protected void Button1_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 (RadioButtonList2.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 { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
this.GridView1.PageIndex = e.NewPageIndex;
this.GridView1.DataBind();
this.GridView2.PageIndex = e.NewPageIndex;
this.GridView2.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)
{
//if (!control.GetType().Name.Equals("DataControlPagerLinkButton"))
//{
// base.VerifyRenderingInServerForm(control);
//}
}
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
switch (e.Row.RowType)
{
case DataControlRowType.Header:
TableCellCollection tcHeader = e.Row.Cells;
tcHeader.Clear();
//first header row
tcHeader.Add(new TableCell());
tcHeader[0].Attributes.Add("bgcolor", "DarkSeaBlue");
tcHeader[0].Attributes.Add("colspan", "3");
tcHeader[0].Attributes.Add("align", "center");
tcHeader[0].Text = "Customers Info</th></tr><tr>";
//second header row
tcHeader.Add(new TableCell());
tcHeader[1].Attributes.Add("bgcolor", "green");
tcHeader[1].Attributes.Add("colspan", "2");
tcHeader[1].Attributes.Add("align", "center");
tcHeader[1].Text = "Basic Info";
tcHeader.Add(new TableCell());
tcHeader[2].Attributes.Add("bgcolor", "green");
tcHeader[2].Attributes.Add("align", "center");
tcHeader[2].Text = "Extended Info</th></tr><tr>";
//third header row
tcHeader.Add(new TableCell());
tcHeader[3].Attributes.Add("bgcolor", "Khaki");
tcHeader[3].Text = "CustomerID";
tcHeader.Add(new TableCell());
tcHeader[4].Attributes.Add("bgcolor", "Khaki");
tcHeader[4].Text = "City";
tcHeader.Add(new TableCell());
tcHeader[5].Attributes.Add("bgcolor", "Khaki");
tcHeader[5].Text = "Country</th></tr><tr>";
break;
default:
break;
}
}
}
}
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.IO;
namespace gridview
{
public partial class WebForm2 : System.Web.UI.Page
{
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["testString"].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();
}
}
protected void PrepareForExport(GridView Gridview)
{
Gridview.AllowPaging = Convert.ToBoolean(RadioButtonList1.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");
}
}
}
}
protected void Button1_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 (RadioButtonList2.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 { } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
this.GridView1.PageIndex = e.NewPageIndex;
this.GridView1.DataBind();
this.GridView2.PageIndex = e.NewPageIndex;
this.GridView2.DataBind();
}
public override void VerifyRenderingInServerForm(Control control)
{
//if (!control.GetType().Name.Equals("DataControlPagerLinkButton"))
//{
// base.VerifyRenderingInServerForm(control);
//}
}
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
switch (e.Row.RowType)
{
case DataControlRowType.Header:
TableCellCollection tcHeader = e.Row.Cells;
tcHeader.Clear();
//first header row
tcHeader.Add(new TableCell());
tcHeader[0].Attributes.Add("bgcolor", "DarkSeaBlue");
tcHeader[0].Attributes.Add("colspan", "3");
tcHeader[0].Attributes.Add("align", "center");
tcHeader[0].Text = "Customers Info</th></tr><tr>";
//second header row
tcHeader.Add(new TableCell());
tcHeader[1].Attributes.Add("bgcolor", "green");
tcHeader[1].Attributes.Add("colspan", "2");
tcHeader[1].Attributes.Add("align", "center");
tcHeader[1].Text = "Basic Info";
tcHeader.Add(new TableCell());
tcHeader[2].Attributes.Add("bgcolor", "green");
tcHeader[2].Attributes.Add("align", "center");
tcHeader[2].Text = "Extended Info</th></tr><tr>";
//third header row
tcHeader.Add(new TableCell());
tcHeader[3].Attributes.Add("bgcolor", "Khaki");
tcHeader[3].Text = "CustomerID";
tcHeader.Add(new TableCell());
tcHeader[4].Attributes.Add("bgcolor", "Khaki");
tcHeader[4].Text = "City";
tcHeader.Add(new TableCell());
tcHeader[5].Attributes.Add("bgcolor", "Khaki");
tcHeader[5].Text = "Country</th></tr><tr>";
break;
default:
break;
}
}
}
}
