How to force update dependent cell when updated any excel cell using c# code and oledb?

Question

How to force update dependent cell when updated any excel cell using c# code and oledb? My site is hosted in cloud environment. User click on button and at that time my code get required values from database and update the few excel cells and prompt user to download this excel sheet. lots of calucation depends on this updated excel cells.

When I mannually update this cell all calculations works fine but when updating using c# code it is not working.

Pleasse check my Code here

            String connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0 XML;HDR=NO\";";

            System.Data.OleDb.OleDbConnection MyConnection;

            System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();

            string sql = null;

            MyConnection = new System.Data.OleDb.OleDbConnection(connstring);

            MyConnection.Open();

            myCommand.Connection = MyConnection;

            //term in months
            sql = "UPDATE [InputSheet$C7:C7] SET F1 = " + model.Lender.ApprovedTerm;
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();

            //intrest rate
            sql = "UPDATE [InputSheet$C8:C8] SET F1 = " + model.Lender.ContractRate;
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();

            //loan amount
            sql = "UPDATE [InputSheet$C9:C9] SET F1 = " + model.Lender.ApprovedLoanAmt;
            myCommand.CommandText = sql;
            myCommand.ExecuteNonQuery();

            MyConnection.Close();

I don't want to use Open XML. Is there any other way to force update dependent cell?

Answer 1

What you are asking, is how to force recalculation of a formula when you change an Excel sheet's values. You can't do when you use the OLE DB provider, since it treats the Excel sheet as just another database. Only Excel itself is able to execute the formulas and recalculate results.

If your code uses a template Excel file, you can change open the file in Excel, the file's properties to recalculate formulas by changing the Calculation options in the formula menu.

A better option though is to use Open XML instead of JET to set the values and change the file's calculation options to recalculate on load, as shown in this SO question

spreadSheet.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
spreadSheet.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;

Even that won't recalculate the formulas though, it will change the calculation properties to recalculate each time you open the file in Excel.

A better option is to use a library like EPPlus, which can calculate formulas. The library is a lot easier to use than the Open XML SDK too:

using(var package = new ExcelPackage(new FileInfo(@"c:\temp\tmp.xlsx")))
{
   var sheet=package.Workbook.Worksheets["my sheet"];
   var lender=model.Lender;
   sheet.Cells["C7"].Value=lender.ApprovedTerm;
   sheet.Cells["C8"].Value=lender.ContractRate;
   sheet.Cells["C9"].Value=lender.ApprovedLoanAmt;
   // calculate all formulas in the sheet
   sheet.Calculate();
}

The Open XML SDK does not require installation of Office or an SDK on the server - it's not Office Interop, it's a library that allows direct manipulation of the XLSX files. It also doesn't require installation of any drivers, like OLEDB does.

EPPlus makes working with XLSX files even easier, adding easier formatting, manipulation, LINQ queries and even loading directly from DataTables or collections. You only have to add the library's NuGet package to a project.