Calculate Sum of the cells in Excel using C#

Question

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+")";

If you exported your gridView succesfully to excel, why still trying to read from excel instead of reading from gridview? — Serkan Hekimoglu

Serkan Hekimoglu Serkan Hekimoglu · Accepted Answer · 2010-10-04T07:44:21

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])
}

Calculate Sum of the cells in Excel using C#

1 Answers