how to apply formula sum() to a cell

Question

1.80/5 (2 votes)

See more:

Hi friends,

I am using Vs 2008,C#,asp.net.
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

Posted 17-Jan-14 1:15am

george@84

Add a Solution

Comments

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.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Karthik_Mahalingam · Answer 1 · 2014-01-17T01:56:00

Solution 1

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..

http://epplus.codeplex.com/discussions/250690[^]

http://stackoverflow.com/questions/5512084/excel-interop-and-formula-range/5512369#5512369[^]

http://stackoverflow.com/questions/8878896/adding-formula-to-excel-with-c-sharp-making-the-formula-shown[^]

Posted 17-Jan-14 1:56am

Karthik_Mahalingam

Comments

[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.

Thanks

Karthik_Mahalingam 17-Jan-14 9:13am

Senthil,
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@84 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@84 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@84 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@84 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

Agree...

Senthilnathan Vaithi · Answer 2 · 2014-01-17T03:03:00

Set the ShowFooter="True" in GridView

C#

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.