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
formulawhen 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.
Member
294 Points
679 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
my code behind
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
Contributor
3710 Points
1043 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.
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.
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.
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.
Member
294 Points
679 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
how to change this for any no of records
Thanking You
Contributor
3710 Points
1043 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,
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:
Best Regards,
YongQing.
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.
Member
294 Points
679 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
Need Your Help
Contributor
3710 Points
1043 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,
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.
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.