Gridview Export To Excel Formula not working if i filter in excel

Question

User-807418713 posted

Hello

This is my aspx page

<%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="TESTFile.aspx.cs" Inherits="TESTFile" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:GridView ID="GridView1" runat="server" Font-Bold="False" 
                    Font-Names="Calibri" Font-Size="12pt" OnRowDataBound="GridView1_RowDataBound" AutoGenerateColumns="False" ShowFooter="True">
                    
                    <Columns>
                    
                      <asp:BoundField DataField="ID"   HeaderText="ID" SortExpression="ID">
                     <ItemStyle HorizontalAlign="Left" ForeColor="Black" Wrap="True" Width="100px" />  
                </asp:BoundField>
                
                
                 <asp:BoundField DataField="Name"   HeaderText="Name" SortExpression="Name">
                     <ItemStyle HorizontalAlign="Left" ForeColor="Black" Wrap="True" Width="100px" />  
                </asp:BoundField>
                    
                    
                        <asp:TemplateField HeaderText="Qty" >
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Qty") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="L1" runat="server" Text='<%# Bind("Qty") %>'></asp:Label>
                    </ItemTemplate>
                      <FooterTemplate>
                        <asp:Label ID="FL1" runat="server"></asp:Label>
                    </FooterTemplate>
                    <ItemStyle ForeColor="Black" HorizontalAlign="Right" Width="100px" Wrap="True" />
                    <FooterStyle HorizontalAlign="Right" ForeColor="Black" Font-Size="Larger" />
                    
                </asp:TemplateField>
                
                
                <asp:TemplateField HeaderText="Rate" >
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Rate") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="L2" runat="server" Text='<%# Bind("Rate") %>'></asp:Label>
                    </ItemTemplate>
                      <FooterTemplate>
                        <asp:Label ID="FL2" runat="server"></asp:Label>
                    </FooterTemplate>
                    <ItemStyle ForeColor="Black" HorizontalAlign="Right" Width="100px" Wrap="True" />
                    <FooterStyle HorizontalAlign="Right" ForeColor="Black" Font-Size="Larger" />
                    
                </asp:TemplateField>
                
                
                 <asp:TemplateField HeaderText="Total" >
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Total") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="L3" runat="server" Text='<%# Bind("Total") %>'></asp:Label>
                    </ItemTemplate>
                      <FooterTemplate>
                        <asp:Label ID="FL3" runat="server"></asp:Label>
                    </FooterTemplate>
                    <ItemStyle ForeColor="Black" HorizontalAlign="Right" Width="100px" Wrap="True" />
                    <FooterStyle HorizontalAlign="Right" ForeColor="Black" Font-Size="Larger" />
                    
                </asp:TemplateField>
                
                
                    </Columns>
                    </asp:GridView>
    <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export Gridview To Excel" />

 
</asp:Content>

my code behind

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections.Specialized;
using System.Text;
using System.Drawing;
using System.IO;
using System.Net;
using System.Net.Mail;
using System.Net.Configuration;
public partial class TESTFile : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[5] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Qty"), new DataColumn("Rate"), new DataColumn("Total") });
            dt.Rows.Add(1, "AA",  10, 1, 10);
            dt.Rows.Add(2, "BB", 20, 2, 40);
            dt.Rows.Add(3, "CC", 5, 5, 25);
            dt.Rows.Add(4, "DD",  10, 5, 50);
            GridView1.DataSource = dt;
            GridView1.DataBind();

            Session["NCWK"] = dt;
            DataView dView = new DataView(dt);
            Session["EE"] = dView;
        }
    }

    decimal Pieces = 0;
    decimal Sqft = 0;
    decimal Total = 0;

    protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.DataRow)
        {

            Label LblPieces = (Label)e.Row.FindControl("L1");
            decimal PiecesSum = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Qty"));

            Label LblSqft = (Label)e.Row.FindControl("L2");
            decimal SqftSum = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Rate"));

            Label LblIssuedAvg = (Label)e.Row.FindControl("L3");
            decimal IssuedAvgSum = Convert.ToDecimal(DataBinder.Eval(e.Row.DataItem, "Total"));

            Pieces += PiecesSum;
            Sqft += SqftSum;

            Total = Sqft * Pieces;
             
        }

        if (e.Row.RowType == DataControlRowType.Footer)
        {

            Label LblPieces = (Label)e.Row.FindControl("FL1");
            LblPieces.Text = Pieces.ToString();

            Label LblSqft = (Label)e.Row.FindControl("FL2");
            LblSqft.Text = Sqft.ToString();

            Label LblIssuedAvg = (Label)e.Row.FindControl("FL3");
            LblIssuedAvg.Text = Total.ToString();

        }
    }


    public string gridviewData(GridView grid)
    {
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        GridView1.RenderControl(htw);

        return sb.ToString();
    }

    protected void ExportToExcel()
    {
        GridView1.ShowHeader = true;
        GridView1.HeaderStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#F2BB66");
        GridView1.HeaderStyle.ForeColor = System.Drawing.ColorTranslator.FromHtml("#FFFFFF");

        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customer_Costing_" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xls"));
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        using (StringWriter sw = new StringWriter())
        {
            HtmlTextWriter hw = new HtmlTextWriter(sw);
 

            DataView dt = (DataView)Session["EE"];
            GridView1.DataSource = dt;
            GridView1.DataBind();
            // }

            GridView1.RenderControl(hw);

            //style to format numbers to string
            string style =
                @"<style>  
        .grid-sltrow {
            background: #ddd;
            font-weight: bold;
        }

        .SubTotalRowStyle {
            border: solid 1px Black;
            background-color: #D8D8D8;
            font-weight: bold;
        }

       .item {
           text-align:left;
        }

        .GrandTotalRowStyle {
            border: solid 1px Gray;
            background-color: #000000;
            color: #ffffff;
            font-weight: bold;
        }

        .GroupHeaderStyle {
            border: solid 1px Black;
            background-color: #4682B4;
            color: #ffffff;
            font-weight: bold;
        }

        .serh-grid {
            width: 85%;
            border: 1px solid #6AB5FF;
            background: #fff;
            line-height: 14px;
            font-size: 11px;
            font-family: Verdana;
        } </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 */
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        ExportToExcel();
    }
}

My resultset

Now i export this above resultset in excel it look like this in excel as below

now i use filter in excel i select BB & CC i want total also to be showing automatically in excel like below

what to add in my code?

Thanking You

Answer 1

User665608656 posted

Hi Gopi.MCA,

According to your description, If you want the sum of the last line to change with the filter, I recommend that you change the content of the last line to a formula when exporting GridView to excel.

You don't need to use footer to show the line. You can add the last line to the datatable when binding data to ensure the correctness of the data source.

  <asp:GridView ID="GridView1" runat="server" Font-Bold="False"
            Font-Names="Calibri" Font-Size="12pt" AutoGenerateColumns="False" >
            <Columns>
                <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID">
                    <ItemStyle HorizontalAlign="Left" ForeColor="Black" Wrap="True" Width="100px" />
                </asp:BoundField>
                <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name">
                    <ItemStyle HorizontalAlign="Left" ForeColor="Black" Wrap="True" Width="100px" />
                </asp:BoundField>
                <asp:TemplateField HeaderText="Qty">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Qty") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="L1" runat="server" Text='<%# Bind("Qty") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle ForeColor="Black" HorizontalAlign="Right" Width="100px" Wrap="True" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Rate">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Rate") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="L2" runat="server" Text='<%# Bind("Rate") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle ForeColor="Black" HorizontalAlign="Right" Width="100px" Wrap="True" />
                </asp:TemplateField>
                <asp:TemplateField HeaderText="Total">
                    <EditItemTemplate>
                        <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Total") %>'></asp:TextBox>
                    </EditItemTemplate>
                    <ItemTemplate>
                        <asp:Label ID="L3" runat="server" Text='<%# Bind("Total") %>'></asp:Label>
                    </ItemTemplate>
                    <ItemStyle ForeColor="Black" HorizontalAlign="Right" Width="100px" Wrap="True" />
                </asp:TemplateField>
            </Columns>
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Export Gridview To Excel" />

 protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[5] { new DataColumn("ID"), new DataColumn("Name"), new DataColumn("Qty"), new DataColumn("Rate"), new DataColumn("Total") });
                dt.Rows.Add(1, "AA", 10, 1, 10);
                dt.Rows.Add(2, "BB", 20, 2, 40);
                dt.Rows.Add(3, "CC", 5, 5, 25);
                dt.Rows.Add(4, "DD", 10, 5, 50);

                decimal Pieces = 0;
                decimal Sqft = 0;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    Pieces += Convert.ToDecimal(dt.Rows[i]["Qty"]);
                    Sqft += Convert.ToDecimal(dt.Rows[i]["Rate"]);
                }
                DataRow dr = dt.NewRow();
                dr["Qty"] = Pieces;
                dr["Rate"] = Sqft;
                dr["Total"] = Pieces * Sqft;
                dt.Rows.Add(dr);
                GridView1.DataSource = dt;
                GridView1.DataBind();
                Session["NCWK"] = dt;
                DataView dView = new DataView(dt);
                Session["EE"] = dView;
            }
        }

        public string gridviewData(GridView grid)
        {
            StringBuilder sb = new StringBuilder();
            StringWriter sw = new StringWriter(sb);
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            GridView1.RenderControl(htw);

            return sb.ToString();
        }
        protected void ExportToExcel()
        {
            GridView1.ShowHeader = true;
            GridView1.HeaderStyle.BackColor = System.Drawing.ColorTranslator.FromHtml("#F2BB66");
            GridView1.HeaderStyle.ForeColor = System.Drawing.ColorTranslator.FromHtml("#FFFFFF");

            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customer_Costing_" + DateTime.Now.ToString("dd-MMM-yyyy") + ".xls"));
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            using (StringWriter sw = new StringWriter())
            {
                HtmlTextWriter hw = new HtmlTextWriter(sw);
                DataTable dt = (DataTable)Session["NCWK"];
                // DataTable dt1 = (DataTable)Session["EE"];
                GridView1.DataSource = dt;
                GridView1.DataBind();
                int k = 0;
                foreach (GridViewRow row in GridView1.Rows)
                {
                    if (k == dt.Rows.Count - 1)
                    {
                        (row.FindControl("L1") as Label).Text = "=SUBTOTAL(9,C2:C5)";
                        (row.FindControl("L2") as Label).Text = "=SUBTOTAL(9,D2:D5)";
                        (row.FindControl("L3") as Label).Text = "=PRODUCT(C6:D6)";
                    }
                    k++;
                }

                GridView1.RenderControl(hw);

                //style to format numbers to string
                string style =
                    @"<style>  
        .grid-sltrow {
            background: #ddd;
            font-weight: bold;
        }

        .SubTotalRowStyle {
            border: solid 1px Black;
            background-color: #D8D8D8;
            font-weight: bold;
        }

       .item {
           text-align:left;
        }

        .GrandTotalRowStyle {
            border: solid 1px Gray;
            background-color: #000000;
            color: #ffffff;
            font-weight: bold;
        }

        .GroupHeaderStyle {
            border: solid 1px Black;
            background-color: #4682B4;
            color: #ffffff;
            font-weight: bold;
        }

        .serh-grid {
            width: 85%;
            border: 1px solid #6AB5FF;
            background: #fff;
            line-height: 14px;
            font-size: 11px;
            font-family: Verdana;
        } </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 */
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportToExcel();
        }

You can also refer to this example : Insert formula while exporting ASP.Net GridView to Excel using C# and VB.Net

When you change the code, open Excel to filter the Name field, and you can see that the data in the last line will change accordingly.

For details, you can refer to the following screenshot:

Best Regards,

YongQing.

Answer 2

User-511826081 posted

Hello,

Sorry, didn't find in your code a part which put a last (summary) line into the grid.

If you need Excel to recalculate some fields - you need to put FORMULA into excel document.

Simplest way - create a single sheet document in Excel, put a formula into it and use as a template for generated result. 

Answer 3

User-807418713 posted

Hi

Thank You For Your Reply Yongqing Yu

The above what i shown is sample data so we cant fix may be in excel 100 rows or 500 rows we cant particualrly ser c2:c5

(row.FindControl("L1") as Label).Text = "=SUBTOTAL(9,C2:C5)";
                        (row.FindControl("L2") as Label).Text = "=SUBTOTAL(9,D2:D5)";

how to change this for any no of records

Thanking You

Answer 4

User665608656 posted

Hi Gopi.MCA,

Gopi.MCA

The above what i shown is sample data so we cant fix may be in excel 100 rows or 500 rows we cant particualrly ser c2:c5

According to your description, your table rows count is dynamic, right?

If the column is fixed, you can set the cell's value as a dynamic variable based on the count of rows table has.

You can modify the code to set the formula as follows：

(row.FindControl("L1") as Label).Text = "=SUBTOTAL(9,C2:C"+ dt.Rows.Count.ToString()+")";
(row.FindControl("L2") as Label).Text = "=SUBTOTAL(9,D2:D"+ dt.Rows.Count.ToString()+")";
(row.FindControl("L3") as Label).Text = "=PRODUCT(C"+ (dt.Rows.Count+1).ToString() +":D"+ (dt.Rows.Count+1).ToString()+")";

Best Regards,

YongQing.

Answer 5

User-807418713 posted

Hello

how to give forecolor for this

(row.FindControl("L3") as Label).Text = "=PRODUCT(C"+ (dt.Rows.Count+1).ToString() +":D"+ (dt.Rows.Count+1).ToString()+")";

Need Your Help

Answer 6

User665608656 posted

Hi Gopi.MCA,

how to give forecolor for this

(row.FindControl("L3") as Label).Text = "=PRODUCT(C"+ (dt.Rows.Count+1).ToString() +":D"+ (dt.Rows.Count+1).ToString()+")";

Need Your Help

Just add this statement when you loop the gridview rows:

foreach (GridViewRow row in GridView1.Rows)
                {
                    if (k == dt.Rows.Count - 1)
                    {
                        (row.FindControl("L1") as Label).Text = "=SUBTOTAL(9,C2:C"+ dt.Rows.Count.ToString()+")";
                        (row.FindControl("L2") as Label).Text = "=SUBTOTAL(9,D2:D"+ dt.Rows.Count.ToString()+")";
                        (row.FindControl("L3") as Label).Text = "=PRODUCT(C"+ (dt.Rows.Count+1).ToString() +":D"+ (dt.Rows.Count+1).ToString()+")";
                        (row.FindControl("L3") as Label).Style.Add("color", "red");
                    }
                    k++;
                }

Here is the result:

Best Regards,

YongQing.