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 B1

new 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

Thanks. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped.

You are using OLEDB to update an Excel Document which, I believe, has nothing to do with an actual in-memory 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.

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?

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

Thanks. I will try to credit the ones who helped but most important is we really do sincerely thanks to all who have helped.

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.

Member

738 Points

2365 Posts

## Need 1 more line of code to make the xls worksheet calculate

Dec 10, 2017 09:26 AM|hkbeer|LINK

I have this code to update the value of an Excel cell in A1:

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 B1

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

www.developerfusion.com/tools/convert/csharp-to-vb/

All-Star

37021 Points

14933 Posts

## Re: Need 1 more line of code to make the xls worksheet calculate

Dec 10, 2017 01:29 PM|mgebhard|LINK

You are using OLEDB to update an Excel Document which, I believe, has nothing to do with an actual in-memory 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.

Member

738 Points

2365 Posts

## Re: Need 1 more line of code to make the xls worksheet calculate

Dec 19, 2017 01:03 AM|hkbeer|LINK

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

www.developerfusion.com/tools/convert/csharp-to-vb/

All-Star

37021 Points

14933 Posts

## Re: Need 1 more line of code to make the xls worksheet calculate

Dec 19, 2017 10:54 AM|mgebhard|LINK

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?

Member

738 Points

2365 Posts

## Re: Need 1 more line of code to make the xls worksheet calculate

Dec 19, 2017 02:07 PM|hkbeer|LINK

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

www.developerfusion.com/tools/convert/csharp-to-vb/

All-Star

37021 Points

14933 Posts

## Re: Need 1 more line of code to make the xls worksheet calculate

Dec 19, 2017 02:42 PM|mgebhard|LINK

You are explaining how you think the problem should be solved not the actual problem.

AFAIK, your approach will not work.