Asked by:
Need 1 more line of code to make the xls worksheet calculate
Question

User775831949 posted
I have this code to update the value of an Excel cell in A1:
objConn.Open(); OleDbCommand objCmdUpdate = new OleDbCommand("UPDATE[Sheet1$A1:A1] SET F1 = 2000", objConn); objCmdUpdate.ExecuteNonQuery();
It is working and update cell A1 to 2000. However, my goal is to get the calculated value of cell B1 which has formula =20*A1
Now I use this line to get cell B1new OleDbCommand("SELECT * from [Sheet1$B1:B1]", objConn2);
It is getting a value but it is getting cell value of B1 BEFORE using new A1 to calculate B1 cell value.
What is the code above to ensure the worksheet formula to calculate ?
Thanks
Sunday, December 10, 2017 9:26 AM
All replies

User475983607 posted
You are using OLEDB to update an Excel Document which, I believe, has nothing to do with an actual inmemory Excel WorkBook. As far as I know, Excel must be running in order to get to get the Excel functions working as expected.
Otherwise, look into MS Office interop which is not recommended for use on a web server due to concurrency. There is also Office Developer Tools in Visual Studio 2017 for use on MS Office platforms.
Can you explain why you are trying to use Excel's calculator rather than writing a C# function? Writing a function with inputs and outputs is, by far, a better approach as it allows for unit testing where the current approach does not.
Sunday, December 10, 2017 1:29 PM 
User775831949 posted
The reason is it is not a straight cell formula
User may make use of a lot of other worksheet cells in the same workbook in that formula in cell B1 and the formula keeps changing.
So I cannot hard code the calculation function in my code.
Pls help... Thanks
Tuesday, December 19, 2017 1:03 AM 
User475983607 posted
The reason is it is not a straight cell formula
User may make use of a lot of other worksheet cells in the same workbook in that formula in cell B1 and the formula keeps changing.
So I cannot hard code the calculation function in my code.
Pls help... Thanks
As far as I know the approach will not work as OLEDB to updates cells by writing to the file which as Excel open on the desktop. Can you explain the problem you are trying to solve? Are you creating an Excel report on the server then downloading the Excel Workbook?
Tuesday, December 19, 2017 10:54 AM 
User775831949 posted
What I want to achieve is:
Step1: User change a value on web page (eg using slide bar).
Step2: this value is saved to cell A1 in Excel
Step3: Excel calculate cell B1 based on the Excel formula in cell B1 using cell A1 value.
Step4: Web page display value of Calculated cell B1 value.Thanks
Tuesday, December 19, 2017 2:07 PM 
User475983607 posted
What I want to achieve is:
Step1: User change a value on web page (eg using slide bar).
Step2: this value is saved to cell A1 in Excel
Step3: Excel calculate cell B1 based on the Excel formula in cell B1 using cell A1 value.
Step4: Web page display value of Calculated cell B1 value.Thanks
You are explaining how you think the problem should be solved not the actual problem.
AFAIK, your approach will not work.
Tuesday, December 19, 2017 2:42 PM