Performance when loading Excel file

Posted by: zsurdel on 8 September 2017, 2:30 pm EST

  • Posted 8 September 2017, 2:30 pm EST

    Hello -


    We are using FarPoint to open an existing Excel file that contains several sheets each with rather involved calculations. The file is opened using OpenExcel(fileName) method. A new tab was introduced in our file that increased the time it takes to execute this method from less than a minute to over 9 minutes.


    When opened in a designer, the file opens with no noticable delay. This delay is unacceptable from the user perspective.


    I attempted to turn off automatic formula evaluation in the file itself - no help. We tried to rewrite some of the calculations. I tried using the designer to save the file as XML but that saved version could not be opened by the designer itself, let alone programatically. Nothing I tried helped speed up the process of loading the file into the spread. This is now a critical issue that we need to address ASAP.


    Any help would be greatly appreciated.


    Thank you

  • Replied 8 September 2017, 2:30 pm EST

    Hello,

    I opened the excel file using the following argument with OpenExcel () method and it opened up the file in a flash.

     fpSpread1.OpenExcel("D:\\AAV_TEST.xls", FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad  ); 

    I see that it is taking time in calculating all the formulas.Please try the same and let me know if that helps.

     

    Thanks,

     

     

  • Replied 8 September 2017, 2:30 pm EST

    You are right, this makes the opening of the file faster. Unfortunately, this does not solve my problem.


    After I load the file, I then populate it with some values and read out numbers that it produced.


    For the results to get calculated, I need to call LoadFormulas (results are not recalculated otherwise) - if I skip loading formulas originally, it is this method call that will now take 8 minutes.


    Is there any way to speed this up?


    Thank you

  • Replied 8 September 2017, 2:30 pm EST

    Hello,

    Loading the excel file is not taking time, it is the Formulas which are making the process really slow, which is proved by the code,

       FpSpread1.OpenExcel("D:\AAV_TEST.xls", FarPoint.Excel.ExcelOpenFlags.DoNotRecalculateAfterLoad)
    You may turn the AutoCalculation On later using :

     FpSpread1.ActiveSheet.AutoCalculation = True 

    I will further see if we can make the processing better with the formulas also.

     

    Thanks,

     

  • Replied 8 September 2017, 2:30 pm EST

    Hello,

    We would need to have a look at the excel file in order to replicate the issue at our end. Could you please post that excel file?

     

    Thanks,

     

  • Replied 8 September 2017, 2:30 pm EST

    I am attaching a stripped-down version of the file.


    Thanks


    2010/11/AAV_TEST.zip
  • Replied 8 September 2017, 2:30 pm EST

    Deepak,


    I agree - it is the formulae that cause the problem. The spreadsheet has no issues when opened in Excel or the designer of FarPoint grid.


    Using your approach (setting AutoCalculation to true after the file is loaded into grid) only delays the performance hit.


    Please let me know if there is anything else we can try.


    Thanks

  • Replied 8 September 2017, 2:30 pm EST

    Hello,

    The problem appears to be caused by many invalid arguments specified in the formulas.  The calc engine is throwing many InvalidCastExceptions and FormatExceptions during the recalculation, and that slows down the load significantly.

    The Excel file is setup such that it generates a large number of #VALUE! errors in the non-taken branches of the IF statements.  Since our calc engine evaluates the non-taken branches and since our calc engine uses exceptions to handle the #VALUE! errors, our calc engine hits a real performance bottle neck in this rare real world example.

     The long term fix would be to implement the IF/CHOOSE functions as branch statements and to stop using InvalidCastException for handling #VALUE! errors.

    I possible short term fix might be to modify the operators (e.g. +, -) to check for "" and return #VALUE! error prior to calling CalcConvert.ToXxx method.  This would avoid many of the InvalidCastException exceptions encountered in this specific Excel file.

     

    Thanks,

     

     

     

Need extra support?

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

Learn More

Forum Channels