Click here to Skip to main content
14,775,221 members
Please Sign up or sign in to vote.
1.80/5 (2 votes)
See more:
Hi friends,

I am using Vs 2008,C#,
I have a datagrid and i am exporting to excel.
I want to apply Sum(D1:D5)formula to a cell using C#

how to do this?

Thanks in advance
george n t
ZurdoDev 17-Jan-14 7:42am
You can do it manually after the fact or include it in your SQL so that when it gets exported it will be there.

You need to use Microsoft.Office.Interop.Excel.dll or EPPlus for performing this..

have a look on these links you will get some idea.
i had the same requirement some month before.. i had used EPPlus.. it works fine..[^][^][^]
[no name] 17-Jan-14 8:58am
Better calculate the total and showit in the gridview footer. then it is easy to export to the excel
Karthik_Mahalingam 17-Jan-14 9:03am
ya, but he needs the formula to be applied on the excel CELL ...
[no name] 17-Jan-14 9:08am
We have already calculated the Sum. I hope we dont need of the formulae. correct me if i'm wrong.

Karthik_Mahalingam 17-Jan-14 9:13am
If the data is static your logic is fine...
suppose the user changes the values on the excel file ( after downloading), then the sum has to be calculated dynamically. in this case the formula has to be applied on the CELL...
I Hope you understood :)
george@12 17-Jan-14 22:58pm
you are right ,Karthik. that is the requirement.Formula should be applied through web application,not desktop application
Karthik_Mahalingam 17-Jan-14 23:02pm
try with the links which i hve posted..
you have to a big google research to achieve it.. :)

if any issue, ping me.. :)
george@12 24-Jan-14 13:53pm
Karthik, Is it posible to export gridview data to excel 2003 (.xls file) .Also the cells should be aligned right and fomulas(sum()) must work.
Karthik_Mahalingam 24-Jan-14 20:32pm
yes, you can.
export the gridveiw data to excel and save as temp file.
open the temp with Microsoft.Excel.Interop object or EPPlus and apply the cells format to it.
similary the formula too. and save it..
then you try to export it...
george@12 26-Jan-14 7:50am
Karthik, whether we want to save the temp file in server.will it degrade performance?I want export to excel feature in all reports.pls advise me
Karthik_Mahalingam 26-Jan-14 8:02am
do u have teamviewer ?
Karthik_Mahalingam 26-Jan-14 8:02am
yes,u can save the temp file.. and after downloading you can delete the file..
george@12 30-Jan-14 9:55am
Hi Karthik, I did export to excel 2003 and applyung sum formula using NPOI dll.
Is it possible to display a number with thousand separator and decimal separator in Indian style( 12, 12,34,567.89)in the exported excel
Karthik_Mahalingam 30-Jan-14 10:03am
You can set "Cell Format" to the column , I am not sure that how much is customisable...
[no name] 20-Jan-14 0:12am
Set the ShowFooter="True" in GridView

double dTotal = new double[1, dsTest.Tables[0].Columns.Count];
       for (int p = 0; p <= dsTest.Tables[0].Rows.Count - 1; p++)
           for (int c = 1; c <= dsTest.Tables[0].Columns.Count - 2; c++)
               dTotal[0, c] = dTotal[0, c] + Convert.ToDouble(dsTest.Tables[0].Rows[p][c]);
               GridView.FooterRow.Cells[(c+1)].Text = dTotal[0, c].ToString())
        GridView.FooterRow.Cells[1].Text = "Total ";

it's the sample code you can modify it with foreach statements for better performance.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900