OpenExcel method caused all formulae to stop working

Posted by: c2d3e4 on 8 September 2017, 1:18 pm EST

  • Posted 8 September 2017, 1:18 pm EST

    I have sheets(0) with a bunch of formulae that work fine.  However, I need to import an Excel page 1 to sheets(1) to do some data manipulation.


    I used the OpenExcel method to import the Excel file like this:


      FpSpread1.Sheet(1).OpenExcel("Testing.xls", 0)


    The import works fine.  But now all the formula on sheets(0) are no longer working!  This is a show-stopper for me.


    Did I miss something here?  Is there a work-around to make the formulae work again?


    I have attached a test project to demonstrate the behavior.  I am using the FpSpread version 4.0.2001.2005


    Another note: I also experienced some Excel macros that do not behave normally after using OpenExcel method when I do the Excel.Run("macro").  I would get Excel alerts that requires comfirmation that I would not normally get.  But those I can turn them off, fortunately.


    Thanks!


    Joe


     


    2008/06/Testing OpenExcel bug.zip
  • Replied 8 September 2017, 1:18 pm EST

    Joe -

    No patches.  Just ran your project as is.  You could try getting the maintenance release and see if it makes a difference, but I tried that and the behavior is the same.

  • Replied 8 September 2017, 1:18 pm EST

    I would not know why it works for you but not for me. Not matter what values I put in columns B or C, column A does not change.  And of course I moved off the cells to try to see the change.


    The Excel sheet formula works fine, but that's not really important here since there does not need to have any formula to get the behavior I am describing on the first sheet.


    What version are you running and patches if any?


     


     

  • Replied 8 September 2017, 1:18 pm EST

    Joe -

    It works for me.  Remember it won't change until you click off the cell.

  • Replied 8 September 2017, 1:18 pm EST

    After you clicked on the Open Excel button and the Excel sheet is imported, the formula on on column A on the first sheet stopped to work when you changed the values on columns B and C.  That is what I am seeing.  Are you seeing it differently?
  • Replied 8 September 2017, 1:18 pm EST

    Joe -

    What behavior are you seeing here?  All the formulas seem to be working for me on the first sheet, as they do on the imported sheet.

  • Replied 8 September 2017, 1:18 pm EST

    So it ran for you, that's the good news. The bad news is it still not working for me. Do you have other ideas to debug this issue?  I'll try to run this on other pc tomorrow to see if this makes a difference.  I am using .net framework 2 version.


    Here is a picture of my project references:



  • Replied 8 September 2017, 1:18 pm EST

    Joe -

    I have the same versions.  You're using VS2008?  Is there a particular cell you're changing the value in when it doesn't work?  We run into this sometimes with service packs, etc.  But it's rare.  What version was the excel file created in?

  • Replied 8 September 2017, 1:18 pm EST

    Scott and Bob,


    A co-worker helped me to capture the File Monitor (FileMon.exe) report when the test project is running. Attached is the filtered report on "File Not Found" and "Path Not Found".  Maybe this would help you to debug what is missing in my setup.  There is also a screen shot of Add and Remove screen from the Control Panel.


    I already tried it on a Vista pc as well, essentially with the same setup and it failed too. 


    Joe 


     


     


    2008/06/FileMonitor_Error.zip
  • Replied 8 September 2017, 1:18 pm EST

    Scott,


    I changed the sheetnames of the first sheet and/or the second sheet, before the import.  In any case, it still did not work.  Interestingly, the second sheet is always set to "sheet1" after the import, though you can change it to something else AFTER the import.

  • Replied 8 September 2017, 1:18 pm EST

    Bob,


    I am using VS2005.  I believe I loaded the version of the FpSpread for .net framework 2.  Can you give me the link to the maintenance release?  I may want to try that out. I am using Excel 2003.


    I tried changing all the referenced cell values of the formula after the import, but none of them has an effect on the calculated value on the first page.


    Joe

  • Replied 8 September 2017, 1:18 pm EST

    Joe,


    When loading the Excel file into Sheet1, the Excel sheet has a name of Sheet 1, which is the same name as the SheetName of Sheet0 in Spread. Thus, you now have two sheets with the same name and the CalcEngine will not work correctly. Either change the name of Sheet0 in Spread or the first sheet in Excel and then the formulas should all work correctly.

  • Replied 8 September 2017, 1:18 pm EST

    Here is the screen shot from the Add and Remove screen of the Control Panel on what .net frameworks are installed.

  • Replied 8 September 2017, 1:18 pm EST

    Scott,


    That works!  Thank you for all your effort.


    Joe.

  • Replied 8 September 2017, 1:18 pm EST

    Joe,


     I am able to reproduce this issue now and it looks to be a bug in Spread. I have reported this as bug #22626 for the development team to fix for the next maintenance release. For now, you can workaround the issue by setting the AutoCalculation property on Sheet(0) to True after loading the Excel file. The first thing I said about renaming the Sheet will not be needed since we store a pointer to the DataModel in the CalcEngine,

  • Replied 8 September 2017, 1:18 pm EST

    I compiled the test project and ran it through half a dozen pc in our department. All of them have at least the .net framework 2 and at least one has .net framework 3.5.  They all share the Far Point dll loaded from the GAC.  They all failed the test.  Here is what is in the GAC for Far Point (see screen shot attachment).


     Joe


     


     


     



Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels