locked
Need 1 more line of code to make the xls worksheet calculate RRS feed

  • Question

  • User-775831949 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 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

    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 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. 

    Sunday, December 10, 2017 1:29 PM
  • User-775831949 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
  • User-775831949 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