Originally posted by: Marko_Obert

Hi,

I'm using SheetRead to load parameters from an excel file to a cplex model.

Unfortunately, I got recently a strange issue with SheetRead, because changing any value in the excel file causes that cplex cannot load data from this file anymore. Also, there is no output in log window, which could point me to the right direction. Just nothing happens. In the Windows Task Manager an Excel Instance appears, which runs with some load of 15%.

It does not matter if I change a value from a labelled cell or any other cell.

I tried:

• Restore a previous version of this excel file. The restored file worked until I change any value…
• Create a new excel file and transferred data from the old to the new file.Works only once until I change a value.
• Tried to run cplex in another windows user account.
• Tried another computer.
• Reinstalled Office
• Tried oplrun.exe -verbose, but no additional output appeared.
• Reinstall CPLEX
• Cleared temporary windows files.

Kind regards,

Marko

Is the Excel file open while CPLEX attempts to access it? If so, does it help to close it?

Originally posted by: Marko_Obert

Hi Daniel,

thank you, for the hint.

I restarted my computer and directly started cplex and ran it, but this does not help either.

Kind regards,

Marko

Originally posted by: Marko_Obert

Well, it looks like SheetRead cannot read reliably data from cells with formulas ...

If formulas are not allowed, why no simply use CSV Files as input files.

Hi

you could do that

https://www.ibm.com/developerworks/community/forums/html/topic?id=476469ad-eec4-46f5-93a5-7b04e87b2836&ps=25

regards

Originally posted by: FredericDelhoume

I tested the included sample oil file oilSheet.xls, changing "oil data"/C2 to a formula (=40+5) and it worked.

And "oil data"/B2 to =2500*E3 and it worked.

Introducing an error in a cell gives :

Description    Resource    Path    Location    Type
Excel: Column: 3 Row: 4, this cell contains an error.    oilSheet.dat    /oil    21:43-44 C:\Users\IBM_ADMIN\AppData\Roaming\IBM\ILOG\CPLEX_Studio128\workspace\oil\oilSheet.dat    OPL Problem Marker

So for me formulas work in dat files.

We do not recompute calculations (formulas) so they must be enabled as "automatic" I guess in Excel.

Maybe you changed something else ?

Originally posted by: Marko_Obert

Hello Frederic,

SheetReed works well with simple or few formulas, but my input sheet had at some point 52000 cells with if-condition and I think this caused issues to SheetReed. After I replaced those cells with actual values, everything works as expected. 

It would be great if this could be fixed, because using formulas makes Excel sheets actually very handy as input data format. 

Kind regards,

Marko

Originally posted by: FredericDelhoume

We have COM C++ code for reading Excel file, that code is not making complicated calls, so I did not expect such behavior with a large (or any) number of formulas.

Did you notice a threshold on the formula number  that made the Excel file unreadable ?

As the CPU load is on the Excel process I would think it is Excel that struggles with the formulas, not directly OPL.

The symptom you describe is 15% CPU usage, that is usually a single thread at 100% on an 8-core machine, so maybe it is slow and will eventually complete ?

Does loading the file in Excel is also slow ?

Originally posted by: Marko_Obert

Hi Fredereric,

I waited 15 minutes to load the excel into the cplex model until I killed the excel process or restarted the operation system.

The input file loads with and without formulas quick in Excel.

Kind regards,

Marko

Originally posted by: FredericDelhoume

I guess the way to progress now is that you give us if possible the minimal mod + dat + xls that reproduce the issue, so that we can try on our dev machines.

The mod would contain just the structure needed to describe the data loaded from Excel

The dat would contain just the connection and loading from Excel

The xls would contain the formulas

Please do not send your whole project as we have now to deal with RGPD regulation.