none
Problem with Reading Excel after updating excel columns using oledb provider RRS feed

  • Question

  • Hi,

         I am using oledb provider to update some columns in sheet1 of excel workbook. In another sheet suppose sheet2, some of the columns are referring to sheet1 columns. When I am reading the sheet2 using oledb, I am not getting the values unless I physically save the Excel work book.


    Thanks in advance
    Anil
    Tuesday, February 2, 2010 9:53 AM

Answers

  • You can use Visual Studio Tools for Office (VSTO) which uses Excel DOM API and allows to open Excel and work with it from the code. You could recalculate the formulas this way. But it would require Excel application installed on any PC that uses this API. It just automates Excel. After the file is saved OLEDB should see the values, since they are calculated after re-save. I do not know any another way how formulas could be recalculated from the code.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, February 4, 2010 11:15 AM
    Moderator

All replies

  • That is expected result, since provider does not implement any Excel functionality to recalculate the formulas. All the formulas recalculated only when Excel application starts or the action explicitly called by the user from inside of the Excel. The provider just reads the values that are inside of the file.
    Val Mazur (MVP) http://www.xporttools.net
    Wednesday, February 3, 2010 11:19 AM
    Moderator
  • Hi Mazur,

        When i am opening the sheet2 physically, i am able to see the updated values. But it is giving problem only when i am reading them through oledb. Is there any other way to recalculate excel through program (ASP.Net C#).


    Thanks 
    Anil
    Thursday, February 4, 2010 4:35 AM
  • You can use Visual Studio Tools for Office (VSTO) which uses Excel DOM API and allows to open Excel and work with it from the code. You could recalculate the formulas this way. But it would require Excel application installed on any PC that uses this API. It just automates Excel. After the file is saved OLEDB should see the values, since they are calculated after re-save. I do not know any another way how formulas could be recalculated from the code.
    Val Mazur (MVP) http://www.xporttools.net
    Thursday, February 4, 2010 11:15 AM
    Moderator