Gridview Export To Excel Formula not working if i filter in excel [Answered]RSS

6 replies

Last post Oct 07, 2019 08:46 AM by Yongqing Yu

‹ Previous Thread|Next Thread ›

Shortcuts

Related Links

Gopi.MCA

Member

219 Points

507 Posts

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

Oct 01, 2019 06:25 PM|Gopi.MCA|LINK

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

Yongqing Yu

Contributor

3660 Points

1042 Posts

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

Oct 02, 2019 07:11 AM|Yongqing Yu|LINK

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.

MSDN Community Support

Please remember to click "Mark as Answer" the responses that resolved your issue.

If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

- Reply
Andrey Belya...
None

0 Points

26 Posts

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

Oct 02, 2019 08:23 AM|Andrey Belyakov|LINK

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.
- Reply
Gopi.MCA
Member

219 Points

507 Posts
Re: Gridview Export To Excel Formula not working if i filter in excel

Oct 03, 2019 09:20 AM|Gopi.MCA|LINK
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
- Reply
Yongqing Yu
Contributor

3660 Points

1042 Posts
Re: Gridview Export To Excel Formula not working if i filter in excel

Oct 07, 2019 02:09 AM|Yongqing Yu|LINK
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.
MSDN Community Support

Please remember to click "Mark as Answer" the responses that resolved your issue.

If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
- Reply
Gopi.MCA
Member

219 Points

507 Posts
Re: Gridview Export To Excel Formula not working if i filter in excel

Oct 07, 2019 06:56 AM|Gopi.MCA|LINK
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

Yongqing Yu

Contributor

3660 Points

1042 Posts

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

Oct 07, 2019 08:46 AM|Yongqing Yu|LINK

Hi Gopi.MCA,

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.

‹ Previous Thread|Next Thread ›