Topic
  • 9 replies
  • Latest Post - ‏2017-03-23T14:13:02Z by Hary18338
HuiD
HuiD
30 Posts

Pinned topic SheetWrite not working with oplrun

‏2013-10-03T10:27:18Z |

Hi,  I read input data from Excel file. SheetRead works fine. But recently I need to update the data in the same file. so I use SheetWrite. 

However, I find that it works in the IDE but not in the command line. When the problem becomes bigger, it's really not efficient with IDE. 

Does anyone know how to solve this problem?

Thanks,

Hui

  • AlexFleischer
    AlexFleischer
    2575 Posts

    Re: SheetWrite not working with oplrun

    ‏2013-10-03T12:16:48Z  

    hi,

    can you attach the model and data files ?

    regards

  • HuiD
    HuiD
    30 Posts

    Re: SheetWrite not working with oplrun

    ‏2013-10-03T13:05:39Z  

    hi,

    can you attach the model and data files ?

    regards

    in the .mod file:

    Time = 1..12;

    {link} topo = ...;

    {demand} traffic = ...;

    int previous[t in Time][l in topo] = ...;

    dvar boolean C[t in Time][d in traffic][l in topo];

    dexpr int exist[t in Time][l in topo] = sum(d in traffic)C[t][d][l]*d.rate;

     

    the .dat file

    SheetConnection input("data.xls");

    topo from SheetRead(input, "Sheet1!A1:C5");

    traffic from SheetRead(input, "Sheet2!A1:C3");

    previous from SheetRead(input, "Sheet3!A1:H5");

    exist to SheetWrite(input, "occupied");

     

    The "exist" matrix is used to update the "previous". Previously, I wrote A1:H5 in the data file and it won't work in either IDE or oplrun. Then I name the range A1:H5 in Sheet3 as "occupied" and it works in IDE, but not in oplrun.

     

  • Hary18338
    Hary18338
    12 Posts

    Re: SheetWrite not working with oplrun

    ‏2017-03-20T13:28:16Z  

    hi,

    can you attach the model and data files ?

    regards

    Hi,

     

    I believe I am having the same problem.

    I create a sheet connection and can read data in but not return it to the excel file.

     

    SheetConnection Connection1("Ueberleitung Data 1.xlsx");

    CurrentDist from SheetRead(Connection1, "Input!E13:G15");

    CurrentDist to SheetWrite(Connection1, "Output 2!E13:G15");

     

    Did anyone figure out why this doesnt work?

    I also tried creating a named range for these cells in Excel called "Output" but this does not work either and I dont believe we need to do this.

     

    Regards

  • AlexFleischer
    AlexFleischer
    2575 Posts

    Re: SheetWrite not working with oplrun

    ‏2017-03-20T14:13:03Z  

    Hi,

     

    I believe I am having the same problem.

    I create a sheet connection and can read data in but not return it to the excel file.

     

    SheetConnection Connection1("Ueberleitung Data 1.xlsx");

    CurrentDist from SheetRead(Connection1, "Input!E13:G15");

    CurrentDist to SheetWrite(Connection1, "Output 2!E13:G15");

     

    Did anyone figure out why this doesnt work?

    I also tried creating a named range for these cells in Excel called "Output" but this does not work either and I dont believe we need to do this.

     

    Regards

    Hi,

    can you attach your project ?

    regards

  • Hary18338
    Hary18338
    12 Posts

    Re: SheetWrite not working with oplrun

    ‏2017-03-21T09:37:36Z  

    Sure, the line which is not working is in the data file, I have highlighted it.

    Attachments

  • Hary18338
    Hary18338
    12 Posts

    Re: SheetWrite not working with oplrun

    ‏2017-03-22T09:30:33Z  

    Ok after a lot of faffing around I have a partial solution.

    It depends on whats in your run configuration. If I create "RunConfiguration 1" and add my smalleg.mod file and .dat file the SheetWrite works fine.

    If however I create "RunConfiguration 2" and add my main.mod file SheetWrite no longer works. One can only add main.mod to RunConfiguration 2 as one cannot have more than one .mod file per run configuration and adding the .dat file throws errors.

    Now the question is how to run my main.mod file and still get SheetWrite to work.

    Any ideas?

  • Hary18338
    Hary18338
    12 Posts

    Re: SheetWrite not working with oplrun

    ‏2017-03-22T11:13:41Z  

    Hi,

    can you attach your project ?

    regards

    The problem still persists. Which files should be in my run configuration and how do you get SheetWrite to work with just a main?

  • AlexFleischer
    AlexFleischer
    2575 Posts

    Re: SheetWrite not working with oplrun

    ‏2017-03-22T15:42:41Z  

    Ok after a lot of faffing around I have a partial solution.

    It depends on whats in your run configuration. If I create "RunConfiguration 1" and add my smalleg.mod file and .dat file the SheetWrite works fine.

    If however I create "RunConfiguration 2" and add my main.mod file SheetWrite no longer works. One can only add main.mod to RunConfiguration 2 as one cannot have more than one .mod file per run configuration and adding the .dat file throws errors.

    Now the question is how to run my main.mod file and still get SheetWrite to work.

    Any ideas?

    Hi,

    in your main.mod I see

    Model.generate();

    but I do not see

    Model.postProcess()

    which would call the SheetWrite part

    regards

  • Hary18338
    Hary18338
    12 Posts

    Re: SheetWrite not working with oplrun

    ‏2017-03-23T14:13:02Z  

    Hi,

    in your main.mod I see

    Model.generate();

    but I do not see

    Model.postProcess()

    which would call the SheetWrite part

    regards

    I was not aware you had to write Model.postProcess() to implement post processing - that answers two questions in one! Thanks