Click here to Skip to main content
14,331,758 members
Rate this:
Please Sign up or sign in to vote.
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
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.
Rate this:
Please Sign up or sign in to vote.

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[^]
   
Comments
Senthilnathan Vaithi 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 ...
Senthilnathan Vaithi 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@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...
Senthilnathan Vaithi 20-Jan-14 0:12am
   
Agree...
Rate this:
Please Sign up or sign in to vote.

Solution 2

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

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100