@OLE is an interface function for moving data back and forth from Excel using OLE based transfers. OLE transfers are direct memory transfers and do not make use of intermediate files. When using @OLE for exports, LINGO loads Excel, tells Excel to load the desired spreadsheet, and sends ranges of data containing solution values to the sheet. You must have Excel 5, or later, to use the @OLE function. @OLE can export one and two-dimensional ranges (rectangular ranges that lie on a single spreadsheet in Excel), but cannot export discontinuous and three-dimensional ranges (ranges which traverse more than one spreadsheet in Excel). In order to export solutions with @OLE, you place calls to @OLE in the data section of your model. These @OLE export instructions are executed each time your model is solved.

Syntax Form 1

The first form of syntax for using @OLE to export data is:

@OLE( ['spreadsheet_file’] [, range_name_list]) = object_list;

The object_list is a comma delimited list of sets, set attributes, and/or scalar variables to be exported.

The 'spreadsheet_file’is the name of the workbook file to export the values to. If the name is omitted, LINGO defaults to using whatever workbook is currently open in Excel.

The range_name_list is the list of named ranges in the sheet to export solution values to. The ranges must contain exactly one cell for each exported value for each object in the object_list. Primitive sets and set attributes export one value per element. Derived sets, on the other hand, export one value for each dimension of the set. Thus, a two-dimensional set exports two values per set member.

As an example, consider the following model and its solution:

SETS:

  S1: X;

  S2( S1, S1): Y;

ENDSETS

DATA:

  S1,X = M1,1  M2,2  M3,3;

  S2,Y = M1,M2,4  M3,M1,5;

ENDDATA

 

    Variable           Value

      X( M1)        1.000000

      X( M2)        2.000000

      X( M3)        3.000000

  Y( M1, M2)        4.000000

  Y( M3, M1)        5.000000

X and Y, both set attributes, export one numeric value per element. More specifically, X exports: 1,2,3, whileY exports: 4 and 5. S1, a primitive set, exports one text value per element, or the values: M1, M2, and M3. S2, on the other hand, is a two-dimensional derived set. Thus, it exports two text values per element. In this case, S2 has two members, so it exports the following four values: M1,M2,M3, M1.

There are three options available for how you specify the range names. First, you can explicitly specify one receiving range for each model object in object_list. Secondly, you can omit the range arguments entirely. In that case, LINGO supplies default values for the range names that are the same as the names of the model objects. Finally, you can specify a single range name to export all the solution values to. In this final case, all the variables in object_list must be defined on the same set, and LINGO exports the data in a tabular format. Examples of these three methods for using @OLE to export solutions follow:

Example 1:        @OLE( '\XLS\DEVELOP.XLS', 'BUILD_IT', 'HOW_BIG') = BUILD, SQ_FEET;

Here, an individual range for receiving each model object is specified. Thus, the values of BUILD will be placed in the range BUILD_IT and SQ_FEET in the range HOW_BIG. When specifying individual ranges, the model objects are not required to be defined on the same set.

Example 2:        @OLE( '\XLS\DEVELOP.XLS') = BUILD, SQ_FEET;

In this case, we omitted the range name argument. Thus, LINGO defaults to using the model object names for the range names. So, LINGO exports BUILD and SQ_FEET to ranges of the same name in the DEVELOP.XLS Excel sheet .

Example 3:        @OLE( '\XLS\DEVELOP.XLS', 'SOLUTION') = BUILD, SQ_FEET;

Here we have specified a single range, SOLUTION, to receive both model objects. Assuming that the receiving range has two columns and our model objects are both one-dimensional, the values of BUILD will be placed in column 1 and SQ_FEET in column 2. Note, in order to get this method to work, BUILD and SQ_FEET must be defined on the same set.

 

Note:  The major difference to notice between using @OLE for exports, as opposed to imports, is the side of the statement the @OLE function appears on. When the @OLE function appears on the left of the equals sign, you are exporting. When it appears on the right, you are importing. So, always remember:

 

         @OLE( … ) = object_list;        ↔        Export, and

         object_list = @OLE( … );        ↔        Import

 

Another way to remember this convention is that the left-hand side of the expression is receiving the data, while the right-hand side is the source. For those familiar with computer programming languages, this follows the convention used in assignment statements.

 

Syntax Form 2

As with @TEXT, you may also use the @WRITEFOR function in conjunction with @OLE to give you more control over the values that are exported, which brings us to our second form of syntax for exporting to Excel:

@OLE( ['spreadsheet_file’], range_name_list) = @WRITEFOR( setname

  [ ( set_index_list) [ | conditional_qualifier]] : output_obj_1[,…,output_obj_n]);

One thing to note that differs from the previous syntax is that the range name list is now required when exporting via the @WRITEFOR function.  The range name list can be a single-cell range, a single multiple-cell range, or a list of multiple-cell ranges.

In the case of a single cell range, the i-th output object will be written to the (i-1)-th column to the right of the named cell.  Note that single-cell ranges act dynamically in that all values will be written to the workbook even though, of course, they lie outside the single-cell range.  When all output is written, the original single-cell range will be at the upper left corner of the table of output values.

In the case of a single multiple-cell range, LINGO creates a table of all the output values, where output object i forms the i-th column of the table.  This table is then written to the output range.  Items are written from upper-left to lower-right.  In general, your output range will have one column for each output object.  If not, the columns will get scrambled on output.

If a list of multiple cell ranges is specified, then you must specify one range name for each output object.  Each output object will we written to its output range in upper-left to lower-right direction.

@WRITEFOR functions like any other set looping function in that, as a minimum, you will need to specify the set to loop over.  Optionally, you may also specify an explicit set index list and a conditional qualifier.  If a conditional qualifier is used, it is tested for each member of the looping set and output will not occur for any members that don't pass the test.  It's this feature of being able to base output on the results of a condition that distinguish this second style of syntax.

The list of output objects, of course, specifies what it is you want to output.  As with the first form of syntax, the output objects may be labels, set members and variable values.  However, you have additional latitude in that the output objects may now consist of complex expressions of the variable values (e.g., you could compute the ratio of two variables).  This is a useful feature when you need to report statistics and quantities derived from the variable values.  By placing these calculations in the data section, as opposed to the model section, you avoid adding unnecessary complications to the constraints of the model.

In general, you can do everything in the second form of syntax that you can do in the first, and more.  However, the first form has an advantage in that it can be very concise.

Some examples follow:

Example 1:        @OLE( 'RESULTS.XLS', 'A1') =

@WRITEFOR( DAYS( D) | START( DAYS) #GT# 0:

 DAYS( D), START( D));

Here, our target is the single cell A1.  Starting at A1 we will write two columns.  The first column will contain the names of the DAYS set for which the attribute START is nonzero.  The second column will contain the START values.  Assuming that there are five days that have nonzero values, then range A1:A5 will contain the names of the days and B1:B5 will contain the start values.

Example 2:        @OLE( 'RESULTS.XLS', 'SKED') =

@WRITEFOR( DAYS( D) | START( DAYS) #GT# 0:

 DAYS( D), START( D));

Here, our target is the multiple-cell range SKED.  Assuming SKED is a two column range, column one will receive the DAYS set members and column 2 will receive the START values.

Example 3:        @OLE( 'RESULTS.XLS', 'DAYS', 'START') =

@WRITEFOR( DAYS( D) | START( DAYS) #GT# 0:

 DAYS( D), START( D));

In this example, we specify one named range for each output object.  In which case, each output object will be written to its corresponding range.