Topic
  • 5 replies
  • Latest Post - ‏2015-01-14T21:50:39Z by IrvL
WaelNassief
WaelNassief
2 Posts

Pinned topic Importing multiple excel sheets one by one in CPLEX

‏2013-07-19T21:41:38Z | data excel instances multiple

For now, I can only import one excel sheet, at a time, into my CPLEX opl to solve my optimization problem.

How can I make this automated? I want to make my code run in a loop to access my excel sheet files one by one solving them and export the solution one by one as well.

Also, note that each excel sheet has a different cells range. Finally, all these excel sheets are in one excel file.

Any help, please?

  • AlexFleischer
    AlexFleischer
    2072 Posts

    Re: Importing multiple excel sheets one by one in CPLEX

    ‏2013-07-22T19:27:42Z  

    Hi,

     

    in SheetConnection you may use a string variable that you can change in the main part of the .mod

     

    regards

  • WaelNassief
    WaelNassief
    2 Posts

    Re: Importing multiple excel sheets one by one in CPLEX

    ‏2013-07-22T19:34:54Z  

    Hi,

     

    in SheetConnection you may use a string variable that you can change in the main part of the .mod

     

    regards

    Hi Alex,

    Thank you for your response. Do you know where I could find more information about using strings?

    Regards,

  • AlexFleischer
    AlexFleischer
    2072 Posts

    Re: Importing multiple excel sheets one by one in CPLEX

    ‏2013-07-23T06:59:07Z  

    Hi Alex,

    Thank you for your response. Do you know where I could find more information about using strings?

    Regards,

    Hi,

    in the documentation

    ==> string

    for example.

    If you take the example oilSheet in the OPL examples, you may write

     

    string fileName="oilSheet.xls"; in the .mod

     

    and then

    SheetConnection sheet(fileName); in the .dat

     

    regards

     

     

     

  • AlexFleischer
    AlexFleischer
    2072 Posts

    Re: Importing multiple excel sheets one by one in CPLEX

    ‏2014-12-21T09:09:19Z  

    Hi,

    in the documentation

    ==> string

    for example.

    If you take the example oilSheet in the OPL examples, you may write

     

    string fileName="oilSheet.xls"; in the .mod

     

    and then

    SheetConnection sheet(fileName); in the .dat

     

    regards

     

     

     

    Related to

    https://www.ibm.com/developerworks/community/forums/html/topic?id=092c58bb-7bc8-4121-b3d9-91c07424c586

  • IrvL
    IrvL
    12 Posts

    Re: Importing multiple excel sheets one by one in CPLEX

    ‏2015-01-14T21:50:39Z  

    I had done this some time back, but don't have the code any more.  So I wrote a simple example, and it is attached.

    There is a basic model file SimpleModel.mod .  It uses two kinds of data, one to specify the strings for the input to SheetRead statements, and the other to specify the SheetRead statements.  The file SheetLoop.mod then shows how to dynamically create the strings that have to be passed to the SheetRead statements.  The separation of the data for the strings and the data that specifies the SheetRead is important, as in the script, there are two data sources, one for the strings (which are dynamic and created in the loop), and the other for the SheetRead statements, which are unchanged each time.

    I modified this to also cause the results to be written back to the spreadsheet using SheetWrite statements.

    Hope this helps people.

       -Irv Lustig, PhD
       Optimization Principal
       Princeton Consultants

     

    Attachments

    Updated on 2015-01-21T22:32:29Z at 2015-01-21T22:32:29Z by IrvL