Xlexport, Xldump, Xlimport

Tom Rutherford  and associates at the University of Colorado developed a set of workbook data exchange utilities in the late 1990's.  These were redeveloped in 2002 in cooperation with the GAMS Corporation to utilize GDX files (internally they employ the Gdxxrw procedure that I discuss below).  Briefly, the interface routines do the following:

 Xlimport reads data from spreadsheets at compile time.  It can be used to retrieve preexisting data from an Excel workbook.

 Xldump writes data and element labels into an existing Excel workbook.  Row and column order will follow the internal GAMS order as discussed in the Rules for Item Capitalization and Ordering chapter.

 Xlexport writes data into spreadsheets according to the row and column labels in the specified target range.  The program will transfer data only for the labels are present in the spreadsheet.

The basic usage of the routines involves use of a libinclude command as follows

$LIBInclude Xlimport Gamsitem Workbookfile Sheetname!Range

$LIBInclude Xlexport Gamsitem Workbookfile Sheetname!Range /m

$LIBInclude Xldump Gamsitem Workbookfile Sheetname!Range

where

Xlexport, Xlexport and Xldump are names of GMS files resident in the inclib directory of the GAMS system directory (which are automatically placed there when a GAMS release after version 21.0 is installed).

Gamsitem is the name of a parameter, set, variable or equation in the source GAMS program.  Attributes are typically used with variables and equations as discussed in the Variables, Equations, Models and Solves chapter.

Workbookfile is the name of an Excel workbook file nominally without the XLS extension where one needs to use quotes for file names and paths with spaces in them as discussed above.

Sheetname!Range is the standard Excel terminology for specification of a set of cells in a workbook.

/m identifies when Xlexport is being used whether data put into the spreadsheet are to be merged or replaced.  If /m is specified then the program does not erase existing entries and where a zero is present in an array to be placed in the spreadsheet it will not overwrite an existing nonzero.  On the other hand if /m is not specified (left off) then the entire writing range will be cleared in the spreadsheet before any data are written.