Originally posted by: SystemAdmin

[dmitry said:]

Hello,

I'm trying to understand how to use OPL SheetRead/SheetWrite in the case when the number of entries in a spreadsheet is unknown in advance. In examples I see the data range is always "hardcoded" into a dat file, f.e. "gas!A2:A4", but what to do in the case when I've added a new value in A5 cell? Are there any other way to read/write from/to  Excel spreadsheet without knowing exactly the size of the range?

Thanks in advance
Dmitry
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

[oussedik said:]

Hi,

You can use dynamic named ranges in Excel, which means that the cells the named range refers to will change based on the amount of data. In OPL you'll have "gas!MyData" instead of "gas!A2:A4", MyData should be defined as a dynamic named range in Excel, then, when you have data in A1:A4, the named range MyData will "select" A1:A4. If you add data to A5, then MyData will select A1:A5.

Sofiane
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

[dmitry said:]

Hello again,

Thank you for your answer. I can see now how to provide the name for region for the read function, for example, using OFFSET(Sheet1!$A$1;;;COUNTA(Sheet1!$A:$A),num_of_columns)

But this code does not work for the write function, as the data are not poped in yet and COUNTA would not work. Are there any other ways of doing dynamic range naming?

Thanks again

Dmitry
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

[oussedik said:]

Hi,

You can't use SheetWrite() on an open spreadsheet (Excel locks the workbook to prevent simultaneous changes), but you can workaround that by enabling workbook sharing in Excel (Select Tools -> Share Workbook, and check the box Allow changes). An addtional step is to save your workbook, then your results will be displayed.

You can also use an intermediate excel spreadsheet (that will be always closed), write the results in it and update the results on your principal spreadsheet while refering to the intermediate one.

Hope this helps,

Sofiane


#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: SystemAdmin

[dgravot@noos.fr said:]

I found some workaround to write in excel with dynamic ranges, e.g those like A1..XY, where X is a column and Y is a row whose value are parameters-dependant (for instance, X is the number of periods and Y is the number of persons that are typically dynamic inputs)

I simply define a string in my opl model . This string will represent the excel range that I 'm going to write in in excel. Then, this string is set up in a script like the following one :

string outputCommande ;
{
var col1 = String.fromCharCode(65+NbPeriods+1);//Alphabet
outputCommande = "Command!C2:"+col1+(NbAllItems+1);
}

allcommands to SheetWrite(sheet2,outputCommand);//in the dat file

Hope this helps, but it is limited to the first 26 columns and is not able to compute directly column names like AA, AB...


David Gravot
ROSTUDEL - Operations Research
57, rue d'Alleray 75015 Paris - France
(33)1 45 31 19 06 / (33) 6 13 46 07 63
dgravot@rostudel.com
www.rostudel.com 
#DecisionOptimization
#OPLusingCPLEXOptimizer

Originally posted by: Hary18338

When using SheetWrite to write to named ranges, the size of the range does not have to match the size of the data you are writing to Excel. If the set is smaller, then only the first cells will be filled so you could also just define a very large named range.

#DecisionOptimization
#OPLusingCPLEXOptimizer