Calculate Sum of the cells in Excel using C#

Question

Hi, I am using windows application,in that windows application i have exported grid view to excel successfully. now i want to calculate sum of cells in the excel sheet in a particular cell... i tried it but it goes exception... can anyone tell me what i did wrong? How to solve that problem? or tell me any other solution for do this..

Thanks in advance...

My code is:

wksheet.get_Range(Type.Missing, strSumOfTargetCell + RowCount).Formula = "=SUM("+strRowValue+":"+strColumnRangeValue+")";

Answer 1

First, read my comment. Second, if you still calculate cells from excel, in my opinion you need to write a code, which read .xlsx file and calculates some values. Here is the sample code can read excel doc, and binds to DataTable.

your code will be like this. after reading whole excel cells, you got all cells values. (when you creating object[] row = new obejct[] )

using (OpenFileDialog openFileFromDialog = new OpenFileDialog())
        {
            openFileFromDialog.Filter = "Excel Document|*.xls";
            openFileFromDialog.Title = "Select FIle";
            openFileFromDialog.ShowDialog();
            if (String.IsNullOrEmpty(openFileFromDialog.FileName))
                return;

            using (OleDbConnection connection = new OleDbConnection { ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; 
                        Data Source={0};Extended Properties=Excel 12.0;", openFileFromDialog.FileName) })
            {
                    using (DbCommand command = connection.CreateCommand())
                    {
                        command.CommandText = String.Format("SELECT * FROM [{0}]", sheetName);
                        if (connection.State == ConnectionState.Closed)
                        {
                            connection.Open();
                        }
                        using (DbDataReader dr = command.ExecuteReader())
                        {

                            while (dr.Read())
                            {

                                object[] row = new object[]
                                        { 
                                        dr[1],
                                        dr[2],
                                        dr[3]
                                        };
                                YOURDATATABLE.Rows.Add(row);
                                }
                        }
                    }
                  }
            }

at the part of yourdatatable.Rows.Add(), you can make calculations instead of making new dataTable such as.

decimal Sum = 0;
while(dr.Read())
{
Sum += (ConvertToDecimal(dr[1]) + ConvertToDecimal(dr[2])) * ConvertToDecimal(dr[3])
}