Hi,
Refer the below code.
HTML
<div>
<asp:GridView ID="grdViewProducts" runat="server" AutoGenerateColumns="False" Width="100%"
AllowPaging="false" OnRowDataBound="grdViewProducts_RowDataBound" OnRowCreated="grdViewProducts_RowCreated">
<Columns>
<asp:BoundField DataField="Year" HeaderText="Year"></asp:BoundField>
<asp:BoundField DataField="Period" HeaderText="Period"></asp:BoundField>
<asp:BoundField DataField="AuditedBy" HeaderText="Audited By"></asp:BoundField>
<asp:BoundField DataField="DirectRevenue" HeaderText="Direct"></asp:BoundField>
<asp:BoundField DataField="ReferralRevenue" HeaderText="Referral"></asp:BoundField>
<asp:TemplateField HeaderText="Total">
<ItemTemplate>
<asp:Label runat="server" ID="lblTotalRevenue" Text="0" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
</asp:GridView>
<br />
<asp:Button Text="Export" runat="server" OnClick="btnExportExcel_Click" />
</div>
Code
string strPreviousRowID = string.Empty;
int intSubTotalIndex = 1;
double SubTotalDirectRevenue;
double SubTotalReferralRevenue;
double SubTotalTotalRevenue;
double GrandTotalDirectRevenue;
double GrandTotalReferralRevenue;
double GrandTotalTotalRevenue;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
BindGrid();
}
private void BindGrid()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[] {
new DataColumn("Year"), new DataColumn("Period"), new DataColumn("AuditedBy"),
new DataColumn("DirectRevenue"), new DataColumn("ReferralRevenue") });
dt.Rows.Add("2008", "Q1", "Maria Anders", "12500.00", "2500.00");
dt.Rows.Add("2008", "Q2", "Ana Trujillo", "21000.00", "8000.00");
dt.Rows.Add("2008", "Q3", "Antonio Moreno", "20000.00", "5000.00");
dt.Rows.Add("2008", "Q4", "Thomas Hardy", "25000.00", "1200.00");
dt.Rows.Add("2009", "Q1", "Christina Berglund", "72500.00", "5000.00");
dt.Rows.Add("2009", "Q2", "Hanna Moos", "15000.00", "6500.00");
dt.Rows.Add("2009", "Q3", "Thomas Hardy", "25000.00", "1520.00");
dt.Rows.Add("2009", "Q4", "Martín Sommer", "42000.00", "2580.00");
dt.Rows.Add("2010", "Q1", "Laurence Lebihan", "12500.00", "1500.00");
dt.Rows.Add("2010", "Q2", "Elizabeth Lincoln", "25000.00", "5500.00");
dt.Rows.Add("2010", "Q3", "Hanna Moos", "12000.00", "1800.00");
dt.Rows.Add("2010", "Q4", "Antonio Moreno", "10000.00", "1200.00");
grdViewProducts.DataSource = dt;
grdViewProducts.DataBind();
}
protected void grdViewProducts_RowCreated(object sender, GridViewRowEventArgs e)
{
bool IsSubTotalRowNeedToAdd = false;
bool IsGrandTotalRowNeedtoAdd = false;
if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Year") != null))
if (strPreviousRowID != DataBinder.Eval(e.Row.DataItem, "Year").ToString())
IsSubTotalRowNeedToAdd = true;
if ((strPreviousRowID != string.Empty) && (DataBinder.Eval(e.Row.DataItem, "Year") == null))
{
IsSubTotalRowNeedToAdd = true;
IsGrandTotalRowNeedtoAdd = true;
intSubTotalIndex = 0;
}
if (IsSubTotalRowNeedToAdd)
{
GridView grdViewProducts = (GridView)sender;
GridViewRow SubTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
TableCell HeaderCell = new TableCell();
HeaderCell.Text = "Sub Total";
HeaderCell.HorizontalAlign = HorizontalAlign.Left;
HeaderCell.ColumnSpan = 3;
HeaderCell.CssClass = "SubTotalRowStyle";
SubTotalRow.Cells.Add(HeaderCell);
HeaderCell = new TableCell();
HeaderCell.Text = string.Format("{0:0.00}", SubTotalDirectRevenue);
HeaderCell.HorizontalAlign = HorizontalAlign.Right;
HeaderCell.CssClass = "SubTotalRowStyle";
SubTotalRow.Cells.Add(HeaderCell);
HeaderCell = new TableCell();
HeaderCell.Text = string.Format("{0:0.00}", SubTotalReferralRevenue);
HeaderCell.HorizontalAlign = HorizontalAlign.Right;
HeaderCell.CssClass = "SubTotalRowStyle";
SubTotalRow.Cells.Add(HeaderCell);
HeaderCell = new TableCell();
HeaderCell.Text = string.Format("{0:0.00}", SubTotalTotalRevenue);
HeaderCell.HorizontalAlign = HorizontalAlign.Right;
HeaderCell.CssClass = "SubTotalRowStyle";
SubTotalRow.Cells.Add(HeaderCell);
grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex + intSubTotalIndex, SubTotalRow);
intSubTotalIndex++;
SubTotalDirectRevenue = 0;
SubTotalReferralRevenue = 0;
SubTotalTotalRevenue = 0;
}
if (IsGrandTotalRowNeedtoAdd)
{
GridView grdViewProducts = (GridView)sender;
GridViewRow GrandTotalRow = new GridViewRow(0, 0, DataControlRowType.DataRow, DataControlRowState.Insert);
TableCell HeaderCell = new TableCell();
HeaderCell.Text = "Grand Total";
HeaderCell.HorizontalAlign = HorizontalAlign.Left;
HeaderCell.ColumnSpan = 3;
HeaderCell.CssClass = "GrandTotalRowStyle";
GrandTotalRow.Cells.Add(HeaderCell);
HeaderCell = new TableCell();
HeaderCell.Text = string.Format("{0:0.00}", GrandTotalDirectRevenue);
HeaderCell.HorizontalAlign = HorizontalAlign.Right;
HeaderCell.CssClass = "GrandTotalRowStyle";
GrandTotalRow.Cells.Add(HeaderCell);
HeaderCell = new TableCell();
HeaderCell.Text = string.Format("{0:0.00}", GrandTotalReferralRevenue);
HeaderCell.HorizontalAlign = HorizontalAlign.Right;
HeaderCell.CssClass = "GrandTotalRowStyle";
GrandTotalRow.Cells.Add(HeaderCell);
HeaderCell = new TableCell();
HeaderCell.Text = string.Format("{0:0.00}", GrandTotalTotalRevenue);
HeaderCell.HorizontalAlign = HorizontalAlign.Right;
HeaderCell.CssClass = "GrandTotalRowStyle";
GrandTotalRow.Cells.Add(HeaderCell);
grdViewProducts.Controls[0].Controls.AddAt(e.Row.RowIndex, GrandTotalRow);
}
}
protected void grdViewProducts_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
strPreviousRowID = DataBinder.Eval(e.Row.DataItem, "Year").ToString();
double dblDirectRevenue = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "DirectRevenue").ToString());
double dblReferralRevenue = Convert.ToDouble(DataBinder.Eval(e.Row.DataItem, "ReferralRevenue").ToString());
Label lblTotalRevenue = ((Label)e.Row.FindControl("lblTotalRevenue"));
lblTotalRevenue.Text = string.Format("{0:0.00}", (dblDirectRevenue + dblReferralRevenue));
SubTotalDirectRevenue += dblDirectRevenue;
SubTotalReferralRevenue += dblReferralRevenue;
SubTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue);
GrandTotalDirectRevenue += dblDirectRevenue;
GrandTotalReferralRevenue += dblReferralRevenue;
GrandTotalTotalRevenue += (dblDirectRevenue + dblReferralRevenue);
}
}
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);
grdViewProducts.AllowPaging = false;
BindGrid();
grdViewProducts.HeaderRow.Style.Add("background-color", "#FFFFFF");
grdViewProducts.HeaderRow.Cells[0].Style.Add("background-color", "green");
grdViewProducts.HeaderRow.Cells[1].Style.Add("background-color", "green");
grdViewProducts.HeaderRow.Cells[2].Style.Add("background-color", "green");
grdViewProducts.HeaderRow.Cells[3].Style.Add("background-color", "green");
grdViewProducts.HeaderRow.Cells[4].Style.Add("background-color", "green");
grdViewProducts.HeaderRow.Cells[5].Style.Add("background-color", "green");
for (int i = 0; i < grdViewProducts.Rows.Count; i++)
{
GridViewRow row = grdViewProducts.Rows[i];
row.BackColor = System.Drawing.Color.White;
row.Attributes.Add("class", "textmode");
if (i % 2 != 0)
{
row.Cells[0].Style.Add("background-color", "#C2D69B");
row.Cells[1].Style.Add("background-color", "#C2D69B");
row.Cells[2].Style.Add("background-color", "#C2D69B");
row.Cells[3].Style.Add("background-color", "#C2D69B");
row.Cells[4].Style.Add("background-color", "#C2D69B");
row.Cells[5].Style.Add("background-color", "#C2D69B");
}
}
grdViewProducts.RenderControl(hw);
string style = @"<style> .textmode { mso-number-format:\@; } </style>";
Response.Write(style);
Response.Output.Write(sw.ToString());
Response.Flush();
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Verifies that the control is rendered */
}
Screenshot