Solved

AXLL Library: Keeping Workbook Open for Multiple Procedures

  • 7 October 2022
  • 1 reply
  • 39 views

Userlevel 1
Badge +3

Hello all!

I am working with a large Excel file that is used across multiple procedures. Loading/opening the Excel file into memory using axll::OpenWorkbook takes a very long time as a result of its size (think almost a minute long), and if at all possible, I would like to keep it open even after one procedure is done running such that the next procedure does not need to spend the time re-opening the workbook again.

My observation is that AIMMS automatically closes a workbook once a procedure is done running. Is there a way/option somewhere to keep the book loaded into memory? It would save our users quite a bit of time!

Thank you in advance!

icon

Best answer by Chris Kuip 9 October 2022, 11:52

View original

1 reply

Userlevel 5
Badge +7

Hi Matt,

 

Thank you for the clear description of your request. 

I have tried to reproduce the behavior you described, but failed. See the enclosed project. It is operated as follows:

  1. Unzip the enclosed prj1336.zip file and pen the AIMMS project in it. 
  2. Run the procedure pr_fillWorkbook once.  This procedure creates a 21 Mb workbook file in about 40 seconds on my laptop. The workbook is placed in the subfolder data.
  3. Close the app.
  4. Open the app again, and open the WebUI page. Note that opening the WebUI page takes roughly 20 seconds, because there is a call to axll::OpenWorkBook in it.
  5. Click the archery button in the right lower once. The WebUI page should now look as follows:
    Getting an arbitrary value out of a 21 Mb Excel workbook​​​​

     

  6. Click the archery button a couple of times more. Please note that the resulting value is returned almost instantaneously; no 20 sec waiting time.

  7. Note that clicking the archery button invokes the procedure pr_getAValue.  The AIMMS Profiler gives me the following timings for that procedure:

     

 

In view of the above failure to reproduce the behavior you described; I have the following questions:

  1. What am I missing in my example?
  2. Can you share a reproducible example?
    1. If this example necessarily contains confidential materials, please contact AIMMS User Support at: support@aimms.com They will provide a secure method for sharing confidential materials.  As you may know, e-mail is not a secure method for sharing confidential materials.
  3. Are you also writing to the workbook?
    1. If so, are you using One-Drive or another file system that secures your data?
  4. Is the behavior you described also observed on the laptops of your colleagues?
    1. If not, 
      1. when was the last time you rebooted your computer?
      2. how does your laptop setup differ from that of your colleagues?

 

With kind regards,

 

Chris

Reply


Didn't find what you were looking for? Try searching on our documentation pages:

AIMMS Developer & PRO | AIMMS How-To | AIMMS SC Navigator