The procedure Xldump can be used export data from a GAMS program to a spreadsheet. It writes data and labels to a specified range overwriting what ever is there. GAMS internal rules controls the order of the items in the output as discussed in the chapter on Rules for Item Capitalization and Ordering.
Example:
Solve tranport using lp minimizing totalcost ;
$libinclude xldump transport.l myspread.xls output2!a1
$libinclude xldump transport.l myspread.xls output!a1..d4
$libinclude xldump transport.l myspread.xls output4!a1
This copies the data for the solution levels (.l) of the variable named transport into the spreadsheet called myspread.xls in the ranges specified. The first command exports the data after the solve statement into the sheet called output2 in the range starting with a1 and clears the rest of that sheet. The second exports the data after the solve statement into the sheet called output in the range a1 to d4 leaving the rest of the sheet alone. The third exports the data after the solve statement into the sheet called output4 and will create that sheet if it does not already exist.
The output2 sheet before the import looks like
and the output2 sheet afterward looks like
while the sheet named output looks like
Also the sheet output4 is created, as it did not previously exist.
Notes:
The order of the results is controlled by internal rules in GAMS as discussed in the chapter on Rules for Item Capitalization and Ordering.
| • | When the range is specified just as the upper left corner any entries below and right will be cleared. You must use a fully specified range if you wish to avoid this. |
| • | The use of Xldump either requires the spreadsheet to be closed or it to be shared through the Excel Tools Share Workbook option. However, if shared, the data put by GAMS will only be reflected in the workbook if you do a file save and the procedure may be slow. Often the best option is to close the workbook. |
| • | The use of Xldump permits simplicity of data transfer but is not as versatile as the use of Gdxxrw as described below. |
| • | Xldump places the whole data parameter into the spreadsheet. |
| • | Xldump sends whatever data is current for an item at the stage of the program where the libinclude occurs and reflects any previous calculations and model solutions. |