GDXXRW

GDXXRW is a utility to read and write Excel spreadsheet data. GDXXRW can read multiple ranges in a spreadsheet and write the data to a 'GDX' file, or read from a 'GDX' file, and write the data to different ranges in a spreadsheet.

Usage

gdxxrw Inputfile {Outputfile} {Options} [Symbols]

Parameters can also be read from a text file; the use of a file for parameters is indicated by preceding the file name with a @ (At sign.). When reading parameters from a text file, lines starting with an asterisk (*) will be ignored and act as a comment.

Parameters can also be read from an area in a spreadsheet; see Index below.

Files without a full path name are assumed to be in the current directory when using a DOS command prompt. When using the GAMS IDE, these files are assumed to be in the current project directory. The use of file names with embedded blanks is allowed as long as the file name is enclosed in double-quotes (").

Note: A small utility program is available to see if Excel is installed, to close an Excel file etc. See XLSTALK for more details. To read data from an Excel file without Excel installed see XLSDump.

Parameters and options

Describing the actions to be taken by gdxxrw requires passing a number parameters and options to the program. The ability of gdxxrw to process multiple actions in a single call makes the parameter passing and interpretation more complex.

There are four kind of parameters:

  1. Immediate Immediate parameters are recognized and processed before any other actions are taken and can only be specified once. Examples are:

    input= output= trace=

  2. Global Global parameters are interpreted from left to right and affect every action that follows. The same parameter can be used multiple times to affect the actions that follow. Examples are:

    SkipEmpty= EpsOut=

  3. Symbol A symbol definition introduces a new action for reading or writing a symbol. Examples are:

    par= set= dset=

  4. Symbol attributes Attributes specify additional information for the last symbol defined

    dim= cdim= merge clear etc

Options (Immediate)

Immediate options are command line options recognized independent of their position on the command line. These options are global and they can only be specified once.

Inputfile (Required parameter)

FileName: Is the first parameter on the command line
Or
Input = FileName
or I = FileName

The file extension of the input file is required and determines the action taken by the program.

The extension '.gdx' for the input file will read data from a 'GDX' file and write data to a spreadsheet. The extension '.xls' or '.xlsx' for the input file will read a spreadsheet and write the data to a '.gdx' file. In addition to the '.xls' input file extension, the following file extensions are also valid for spreadsheet input files: '.wk1', .'wk2', '.wk3' and '.dbf'.

A file sharing conflict will arise when writing to a spreadsheet with the target file open in Excel. Either close the file in Excel before executing GDXXRW, or mark the spreadsheet as a shared workbook in Excel. To change the shared status of a workbook, use the Excel commands available under: Tools|Share Workbook.

Writing to a shared workbook can be painfully slow; simply closing the file and reopen the file after GDXXRW is finished is often a better option.

Outputfile (Optional parameter)

Output = FileName
or
O = FileName

When an output file is not specified, the output file will be derived from the input file by changing the file extension of the input file and removing any path information. The file type, file extension, depends on the installed version of Excel. Versions prior to Excel 2007 use the .xls file extension, later version use .xlsx. Excel 2007 can write .xls files, but in that case the output file has to be specified with an .xls file extension.

Logfile (Optional parameter)

Log = FileName
or
LogAppend = FileName

Specifies the filename of the log file. When omitted, log information will be written to standard output. When using GDXXRW in a GAMS model that is started from the GAMSIDE, the output will be written to the IDE process window. Using LogAppend will add the log information to the end of the file.

Delay after opening a spreadsheet

RWait = integer (default = 0)

Introduce a delay after opening a spreadsheet before accessing the data. This parameter can be used to work around an issue we encountered that Excel indicated it was not ready.

Check if any works needs to be done

CheckDate

CheckDate

When specified, no data will be written if the output file exists and the file date for the output file is more recent than the file date for the input file. Provides a simple check to update the output file only if the input file has changed.

Use RC notation to specify cells and ranges

UseRC

Specify that all cell and range references use RC notation. So instead of specifying the range Sheet1!A1:D6 one specifies Sheet1!R1C1:R6C4 When tracing is enabled ranges will be reported in RC notation. This is a global option and applies to all cell references.

Trace and debug output

Trace = integer (default = 1)

Sets the amount of information written to the log. Higher values will generate more output. Valid range is 0..3.

Maximum number of duplicate records allowed for a symbol

MaxDupeErrors = integer (default = 0)

Sets the maximum number of duplicate records that is allowed when reading a spreadsheet and writing to a gdx file. The duplicate records will be reported in the logfile, and if their number does not exceed the maximum specified using this option, the gdx file will not be deleted. This is a global option and applies to each symbol read from the spreadsheet.

Updating of cells that refer to other spreadsheets

UpdLinks = integer (default = 0)

Specifies how links in a spreadsheet should be updated. The valid range is 0..3.

0   Doesn't update any references
1   Updates external references but not remote references
2   Updates remote references but not external references
3   Updates both remote and external references

Execution of Excel Auto macros

RunMacros = integer (default = 0)

This option controls the execution of the 'Auto_open' and the 'Auto_close' macros when opening or closing a spreadsheet. Valid values are 0..3.

0   Doesn't execute any macros
1   Executes Auto_open macro
2   Executes Auto_close macro
3   Executes Auto_open and Auto_close macro

Symbols

To write data to a spreadsheet or to a GDX file, one or more symbols and their associated Excel range need to be specified. See also Excel Ranges.

The general syntax for a Symbol specification is:

DataType=SymbolName {DataRange} {Dimensions} {SymbolOptions}

(Also see the Text directive below to write text to a spreadsheet)

DataType

Par = GAMS_Parameter

Specify a GAMS parameter to be read from a GDX file and written to spreadsheet, or to be read from a spreadsheet and written to a GDX file.

When writing to a spreadsheet, special values such as Eps, NA and Inf will be written in ASCII. When reading data from a spreadsheet, the ASCII strings will be used to write the corresponding special values to the GDX file.

Equ = GAMS_Equation
Var = GAMS_Variable

A sub-field of a variable or equation can be written to a spreadsheet and should be specified as part of the SymbolName. The fields recognized are .L (level), .M (marginal), .Lo (lower bound), .Up (upper bound), .Prior (priority), and .Scale (scale) The sub-field names are not case sensitive.

A sub-field of a variable or equation cannot be read from a spreadsheet and written to a GDX file.

Set = GAMS_Set [Values = ValueType]

In GAMS we can define a set by specifying all its elements. In addition, each tuple can have an associated text. To read a set from a spreadsheet, the values option is used to indicate if there is any data, and if there is, if the data should be interpreted as associated text or as an indicator whether the tuple should be included in the set or not.

ValueType Interpretation
Auto Based on the range, row and column dimensions for the set, the program decides on the value type to be used. This is the default for Values
NoData There is no data range for the set; all tuples will be included
YN Only those tuples will be included that have a data cell that is not empty and does not contain '0', 'N' or 'No'
Sparse Only those tuples will be included that have a data cell that is not empty. The string in the data cell will be used as the associated text for the tuple
Dense All tuples will be included. A string in the data cell will be used as the associated text for the tuple

Due to backward compatibility ValueType=String or All are also recognized and are synonyms for ValueType=Dense. The following table summarizes which ValueType will be used when reading a Set if a value type was not specified:

Range specification Rdim = 0 Or Cdim = 0 Rdim > 0 And Cdim > 0
Top left corner only Dense YN
A block, but the data range is empty Dense YN
A block, and there is a data range Dense YN

When writing to a spreadsheet, the entire set is written to the spreadsheet and the writing of the accociated text is governed by the values option:

ValueType Interpretation
Auto If Rdim = 0 or Cdim = 0, Auto means String, otherwise Auto means YN
NoData Neither associated text nor 'Y' is written for a set element
YN A 'Y' is written for a set element
String The associated text is written for a set element. If no text is stored with set element the cell will be empty

Due to backward compatibility ValueType=Dense, Sparse or All are also recognized and are synonyms for ValueType=String.

DSet = GAMS_Set

A domain set is used to read the domain of a set from a spreadsheet row or column. Either the row or the column dimension (Rdim or Cdim) should be set to '1' to specify a row or column for the set, resulting in a one-dimensional set. Duplicate labels in the range specified do not generate an error message.

Text = "String of characters" {DataRange}
TextID = Identifier {DataRange}

Write the text to the cell specified in the DataRange. In addition, TextID will write the explanatory text of the Identifier in the cell to the right of the DataRange.

A Text directive can be followed by a Link=Address or LinkID=identifier directive. Using Link will create a hyperlink to an external page or to a cell in the spreadsheet. LinkID will create a hyperlink to the top left corner of the symbol specified.

HText = "String of characters" {DataRange}
VText = "String of characters" {DataRange}

Write a string of characters in the horizontal direction for HText or vertical direction for VText. Text for the next cell is indicated by a comma. In order to write a comma as part of the text, the comma needs to be preceeded by a backslash.

The following options apply to the symbol preceding the option, and only affect that symbol:

DataRange (Optional)

Rng = Excel Range

The Excel Range for the data for the symbol. Note that an empty range is equivalent to the first cell of the first sheet.

Dimensions (Optional)

Dim = integer

The total dimension for the symbol

Cdim = Integer

Column dimension: the number of rows in the data range that will be used to define the labels for the columns. The first Cdim rows of the data range will be used for labels.

Rdim = Integer

Row dimension: the number of columns in the data range that will be used to define the labels for the rows. The first Rdim columns of the data range will be used for the labels.

More about dimensions:

When reading data from a GDX file and writing to a spreadsheet, the dimension of the symbol is known. When reading a spreadsheet and writing to a GDX file, the dimension is not known.

The sum of Cdim and Rdim determine the dimension of the symbol. This dimension is used when writing data to a GDX file, and is used to verify the dimension of a symbol when reading from a GDX file.

When reading a GDX file, the dimension of a symbol is known, and therefore the Cdim or Rdim parameter can be omitted. If both Cdim and Rdim are omitted, the program assumes that Cdim = 1 and Rdim= dimension - 1.

ColMerge = Integer

The number of columns that will use a previous value in that column if the cell is empty. Can only be used when reading from a spreadsheet. See ColMerge example.

Symbol Options

The options below are only valid when reading a GDX file and writing to a spreadsheet.

By default, writing data to a spreadsheet will include the row and column labels in addition to the data. The row and column labels will appear in the same order as they appear in the GDX file.

Merge

Using the 'Merge' option assumes that the row and column labels are in the spreadsheet already. For each value read from the GDX file, the location of the row and column labels is used to update the spreadsheet. Using the merge option will force the data to be presented in a given order using the row and column labels. Spreadsheet cells for which there is no matching row/column pair will not be changed. The matching of labels is not case sensitive.

Warning: The Merge or Clear option will clear the Excel formulas in the rectangle used, even if the cells do not have matching row / column headings in the GDX file. Cells containing strings or numbers are not affected.

Clear

The clear option is similar as the Merge option, except that the data range will be cleared before any data is written.

ReCalc = Flag (default = N)

Enable or disable the recalculations of cells inside Excel after writing to the spreadsheet. If there are many formulas in the spreadsheet the recalculation of cells can become very expensive and slowing down the writing process. By default, the recalculation is disabled and can be enabled via this option.

IntAsText = Flag (default = Y)

Unique elements that are a proper integer can be written as text or as an integer value. The default is N, which will write the unique element as a string. Note that this impacts the sorting order and can be used when using an Excel filter on a data range.

Index = Excel Range

The Index option is used to obtain the parameters by reading from the spreadsheet directly. The parameters are read using the specified range, and treated as if they were specified directly on the command line. The first three columns of the range have a fixed interpretation: DataType, Symbol identifier and Data range. The fourth and following columns can be used for additional parameters. The column header contains the keyword when necessary, and the Cell content is used as the option value.

Ignoring Rows and Columns when reading from a spreadsheet

IgnoreRows=rownr, rownr, rownr:rownr
IgnoreColumns=colnr, colnr, colnr:colnr

Row numbers are represented by integers. Column numbers are represented by Excel column numbers , like A, CD, IV etc, or by integers.

Notes:

  • Ignoring rows or columns is only allowed when reading a spreadsheet
  • The specification of ignored rows or columns follows the symbol specification and only applies to that symbol.
  • When ignoring a column that would be part of an index if the column was not ignored, the range for the index will be extended for each column that is ignored. The same holds for ignored rows that are part of an index.

See Ignore Rows and Columns Example.

Options

The following options affect the symbols that follow the option. They remain in effect unless they are used again for another symbol.

Acronyms = integer (default = 0)

A non-zero value indicates that acronyms can be expected and should be processed.

If no acronym processing takes place, reading an identifier in the data section of a sheet will generate an error. Writing an acronym to a sheet will write the internal numerical representation of the acronym.

Processing acronyms:

When reading a spreadsheet, an identifier in the data section of the sheet will be interpreted as an acronym and will be written to the gdx file.

When writing to a spreadsheet, a data tuple containing an acronym will be stored using the corresponding identifier of the acronym.

CMerge = integer (default = 0)

Option indicating how to read an empty cell that is part of a merged Excel range. See CMerge example. Possible values and their interpretation are:

0   Leave the cell empty
1   Use merged value in row and column headers only
2   Use merged value in all cells

EpsOut = istring (default = Eps)

String to be used when writing the value for 'Epsilon'.

Filter = integer (default = 0)

Set the Excel filter for symbols written to Excel. Using this option when reading an Excel file will result in an error. Specifying filter=1 will set an Excel filter for the row of labels labels that are closest to the data values. When there are multiple rows in a column header (CDIM > 1) we can specify a filter=2 indicating to use a row away from the data values. See filter example.

IncRC = flag (default = N)

Valid only when reading a spreadsheet.

Include Excel row and column indices when a symbol is written to the gdx file. For example, when we write a parameter P with indices I and J, without this option it will be written a P(I, J). When IncRC is enabled, the parameter will be written as P(Excel_Rows, I, Excel_Columns, J). Note that the sets Excel_Rows and Excel_Columns will be added the gdx file automatically.

MinfOut = string (default = -Inf)

String to be used when writing the value for 'Negative infinity'.

NaIn = string

String to be used when reading a value for 'Not available'; this string is used in addition to the string 'NA' and is not case sensitive.

NameConv = flag
or
NC = flag

The naming convention parameter is used to change the interpretation of an Excel range that does not contain an '!' (exclamation mark). For details see Ranges below. The default value is false.

NaOut = string (default = NA)

String to be used when writing the value for 'Not available'.

PinfOut = string (default = +Inf)

String to be used when writing the value for 'Positive infinity'.

ResetOut

Reset the output strings for special values to their defaults.

Squeeze = flag (default = Y)
or
SQ = flag

Writing to a spreadsheet:

The squeeze option affects the writing of sub-fields of variables and equations. A value for the field that is the default value for that type of variable or equation will not be written to the spreadsheet. For example, the default for .L (Level value) is 0.0, and therefore zero will not be written to the spreadsheet. When we set SQ=N, all values will be written to the spreadsheet.

Reading a spreadsheet:

When the squeeze option is enabled, zero values for parameters will not be written to the GDX file. When the squeeze option is disabled, zero values will be written to the GDX file. In either case, empty cells, or cells containing blanks only, will never be written to the GDX file.

SkipEmpty = integer (default = 1)
or
SE = integer

The SkipEmpty option can be used when reading a spreadsheet, and the range is specified using the top left corner instead of a block range. The value defines the number of empty row or column cells signal the end of a block. Valid values are 0..n.

UndfOut = string (default = Undf)

String to be used when writing the value for 'Undefined'.

AllUELs = flag (default = Y)

Valid only when reading a spreadsheet.

When enabled, all unique elements found in a range will be entered in the GDX file. When disabled, only those unique elements that are used in conjunction with a value will be entered in the GDX file.

ZeroOut = string (default = 0)

String to be used when writing the value for 'Zero'; by default this is '0'.

Syntax elements

Element Description
integer An unsigned integer
string A string of characters; a string can be quoted with single or double quotation marks.
flag True values: 1, Y or Yes
False values: 0, N or No
(not case sensitive)

Ranges

An Excel Range is specified using the standard Excel notation: SheetName!CellRange.

When the 'SheetName!' is omitted, the first sheet will be used. A CellRange is specified by using the TopLeft:BottomRight cell notation like A1:C12. When ':BottomRight' is omitted, the program will extend the range as far down and to the right as possible. (Using '..' in stead of ':' is supported.)

Excel also allows for named ranges; a named range includes a sheet name and a cell range. Before interpreting a range parameter, the string will be used to search for a pre-defined Excel range with that name.

When writing to a spreadsheet and a sheet name has been specified that does not exist, a new sheet will be added to the workbook with that name. Reading a spreadsheet and using an unknown range or sheet name will result in an error.

The following table summarizes all possible input combinations and their interpretation:

Input Sheet used Cell(s) used
First sheet A1
! First sheet A1
Name First sheet Name When nc=0
Name Name A1 When nc=1
Name! Name A1
!Name First sheet Name
Name1!Name2 Name1 Name2

Return Codes

On success, GDXXRW will return 0 as exit code. However, there might be an error which will be signaled with a specific return code in addition to an error message.

Return Code Interpretation
0 No error
1 Cannot write log
2 GDX error
3 No input file
4 Input file notfound
5 Bad parameter
6 Read error
7 Problem loading GDX DLL
8 Symbol not found
9 Dimension different
10 Types different
11 Bad uels
12 Bad output file
13 Problem opening Excel
14 Problem writing to Excel
15 Problem reading from Excel
16 Duplicate entry
17 Cannot add sheet
18 Bad cell value
19 Dimension conflict
20 Data exceeds range
21 Data exceeds range
22 Deprecated
23 Progrma aborted
24 Merge range empty
25 Too many columns skipped
26 Too many rows skipped

Warning

Warning: When executing gdxxrw.exe twice and redirecting output to the same log file may result in a fatal error.

For example:

Gdxxrw step1 parameters > logfile
Gdxxrw step2 parameters > logfile

The execution of step2 may fail, because Excel will close the logfile in step1 in a delayed fashion, but return control to gdxxrw.exe immediately. Using the 'Log' or 'LogAppend' parameter will avoid this problem.

Examples

Read spreadsheet: Example 5

Assuming we want to read parameter Data1 from the file test1.xls and write the data to test1.gdx. The sheet name in a range can be omitted when it refers to the first sheet.

clip0001.gif


GDXXRW test1.xls par=Data1 rng=A1:D3 Cdim=1 Rdim=1

Read spreadsheet: Example 6

The same data as in the previous example, but organized differently. We use the Dset option to read set I (in column A) and set A (in column B).

clip0002.gif
GDXXRW test1.xls par=Data2 rng=EX2!A1 Rdim=2 Dset=I rng=EX2!A1 Rdim=1 Dset=A rng=EX2!B1 Rdim=1

When using a few symbols, the command line can become too long to be practical. In such case, use a text file to hold the parameters. A parameter file can contain multiple lines to increase readability and a line starting with a '*' will be ignored.

*file example6.txt
par =Data2 rng=EX2!A1 RDim=2
Dset=I     rng=EX2!A1 Rdim=1
Dset=A     rng=EX2!B1 Rdim=1
GDXXRW test1.xls @example6.txt

Note:

A parameter file can also be written during the execution of a GAMS model using the GAMS PUT facility.

Read spreadsheet: Example 7

This example illustrates how a four dimensional parameter can be specified:

clip0003.gif
GDXXRW test1.xls par=Data3 rng=EX3!A1:F6 Rdim=2 Cdim=2

When we specify the range as a block, an empty row or column will be ignored. When we specify the top left cell only, the SkipEmpty option can be used to ignore one or more empty rows or columns. When we specify SkipEmpty=0, and cells A7, B7, G1 and G2 are empty, the range can be specified with a top left cell only:

GDXXRW test1.xls par=Data3 rng=EX3!A1 Rdim=2 Cdim=2

Read spreadsheet: Example 8

Special values can be read and written; the division by zero error in the spreadsheet will be written as 'Undefined'.

clip0004.gif
GDXXRW test1.xls par=Data4 rng=EX4!A1:F2 Cdim=1

Read spreadsheet: Example 9

Example of reading a set; the result will only contain the element 's1'.

clip0005.gif
GDXXRW test1.xls Set=SET1 values=yn rng=Ex5!A2:B6 Rdim=1

Read spreadsheet: Example 10

The Index option is used to read a number of parameters and sets based on information stored in the spreadsheet itself. The first row of the range is used for column headings indicating additional parameters.

clip0005.gif
GDXXRW test1.xls Index=Index!a1

Write to spreadsheet: Example 11

First, we create a GDX file using the GDX parameter in the GAMS call:

*file makedata.gms
set i /i1*i4/
    j /j1*j4/
    k /k1*k4/;

parameter v(i,j,k);
v(i,j,k)$(uniform(0,1) < 0.30) = uniform(0,1);

When we run this GAMS model, the file test2.gdx will be created at the end of the run.

GAMS makedata gdx=test2

Using the file test2.gdx, we can write to a spreadsheet:

Write parameter V to the first cell in the first sheet; because we only specify the top left corner of the sheet, the complete sheet can be used to store the data. We do not specify the row and column dimension, so they will default to rdim=2 and cdim=1. (See dimensions)

Before executing this example, open the Excel file (test2.xls) and use the Excel Tools menu to make this a shared notebook. After writing to the spreadsheet, use the Excel "File Save" command to verify the changes made.

GDXXRW test2.gdx par=V rng=a1

The steps above can be combined in a single GAMS model using the Execute_Unload and Execute statements as follows:

set i /i1*i4/
    j /j1*j4/
    k /k1*k4/;

parameter v(i,j,k);
v(i,j,k)$(uniform(0,1) < 0.30) = uniform(0,1);
Execute_Unload "test2.gdx",I,J,K,V;
Execute 'GDXXRW.EXE test2.gdx par=V rng=a1';

Write to spreadsheet: Example 12

The second sheet of this spreadsheet contains a number of labels to illustrate the use of the merge option. Note that the values written are no longer in the same cells because they have been matched with the column and row labels. Cells that were not changed by the merge option still contain 'xxx'.

GDXXRW test2.gdx par=V rng=sheet2!a1 merge

In the previous example, the cells that were not changed still contained 'xxx'. We can clear the data range before a merge by using the 'Clear' option.

GDXXRW test2.gdx par=V rng=sheet2!a1 clear

Read and Write spreadsheet: Example 13

In the following example, we read data from a spreadsheet and save the data in a GDX file. Using the $GDXIN and $LOAD GAMS directives, we read data from the GDX file into GAMS. The GAMS program modifies the data and at the end of the run the data is saved in a new GDX file (tmp.gdx). The last step updates the spreadsheet with the modified parameter.

The data in spreadsheet test1.xls:

clip0001.gif
$CALL GDXXRW test1.xls Set=I rng=A2:A3 Rdim=1 Set=A rng=B1:D1 Cdim=1 Par=X rng=A1:D3 Rdim=1 Cdim=1
$GDXIN test1.gdx
Set I(*),A(*);
$LOAD I A
Parameter X(I,A);
$LOAD X
Display I,A,X;
$GDXIN
X(I,A) = - X(I,A);
Execute_Unload 'tmp.gdx',I,A,X;
Execute 'GDXXRW.EXE tmp.gdx O=test1.xls par=X rng=EX6!A1:D3 rdim=1 cdim=1';

Read and Write spreadsheet: Example 14

In this example we use a modified version of the trnsport.gms model from the GAMS model library. This example illustrates:

  • Compilation phase
  • Read data from a spreadsheet and create a gdx file
  • Reading sets from the gdx file
  • Using the sets as a domain for additional declarations
  • Reading additional data elements
  • Execution phase
  • Solve the model
  • Write solution to a gdx file
  • Use gdx file to update spreadsheet
clip0009.gif
Data for trnsport model
$onecho > taskin.txt
dset=i rng=a3:a4 rdim=1
dset=j rng=b2:d2 cdim=1
par=d rng=A2 Cdim=1 Rdim=1
par=a rng=a8 Rdim=1
par=b rng=a13 Rdim=1
par=f rng=a19 Dim=0
$offecho

$call gdxxrw.exe trnsportdata.xls @taskin.txt

$gdxin trnsportdata.gdx

sets
   i(*) canning plants
   j(*) markets;

$load i j

display i,j;

Parameters
   a(i)  capacity of plant i in cases
   b(j)  demand at market j in cases
   d(i,j)  distance in thousands of miles
scalar f freight in dollars per case per thousand miles
$load d a b f
$gdxin

Parameter c(i,j)  transport cost in thousands of dollars per case ;
   c(i,j) = f * d(i,j) / 1000 ;


VARIABLES
         x(i,j) shipment quantities in cases
         z      total trnasportation costs in thousands of dollars ;

POSITIVE VARIABLE x ;

EQUATIONS
         cost            define objective function
         supply(i)       observe supply limit at plant i
         demand(j)       satisfy demand at market j ;

cost ..          z =e= sum((i,j),c(i,j)*x(i,j));
supply(i)..      sum(j,x(i,j)) =l= a(i);
demand(j)..      sum(i,x(i,j)) =g= b(j);

MODEL transport /all/ ;

SOLVE transport using lp minimizing z ;

DISPLAY x.l, x.m ;

execute_unload 'trnsportdata.gdx', x;
execute 'gdxxrw.exe trnsportdata.gdx var=x.l rng=sheet2!a1' ;
clip0010.gif
Solution written to sheet2

Write spreadsheet using a filter: Example 15

The following example creates a small gdx file; the gdx file is used to write the symbol A to a spreadsheet with the filter enabled.

set i /i1*i2/
    j /j1*j2/
    k /k1*k2/;
parameter A(i,j,k);
A(i,j,k)=uniform(0,1);
execute_unload 'test.gdx', A;
execute 'gdxxrw.exe test.gdx filter=1 par=A rdim=1 cdim=2 rng=sheet1!a1';
clip0011.gif

The screenshot above shows the filter in Excel. When we specify filter=2 in this example with two dimensions for the column header, the row with the filter moves away from the data range as illustrated below.

filter2.png

Write spreadsheet using text and hyperlinks

The following example illustrates the use of the Text directive.

First we write some data to a gdx file and we use text directive to write text to various cells; some of the cells are hyperlinks to other locations.

$onecho > task.txt
text="Link to data" rng=Index!A2 linkid=A
text="Below the data for symbol A" rng=data!c2
par=A rng=data!c4
text="Back to index" rng=data!a1 link=Index!A1
text="For more information visit GAMS" rng=data!c1 link=http://www.gams.com
$offecho

set i /i1*i9/
   j /j1*j9/;
parameter A(i,j);
A(i, j) = 10 * Ord(i) + Ord(j);
execute_unload "pv.gdx";
execute 'gdxxrw pv.gdx o=pv.xls @task.txt'

Below a screen shot showing the sheet 'data' created by the commands above:

clip0012.jpg

ColMerge Example

Using ColMerge to read from a spreadsheet:

colmerge.png

Reading the above spreadsheet using the following GAMS statement:

$call gdxxrw file.xls par=A rng=B2 rdim=3 cdim=1
colmerge2.png

Results in empty cells B4, B5, C4 and D5. Using the ColMerge parameter, we use the non-empty content of the previous cell in the same column as the content for the empty cell. Specifying ColMerge=2, will do this for the first two columns.

$call gdxxrw file.xls par=A rng=B2 rdim=3 cdim=1 ColMerge=2
colmerge3.png

CMerge Example

Using CMerge to read merged column headers:

cmerge1.png
Spreadsheet with merged cells

Note that the label 'red' is centered over columns B, C and D and label 'green' over columns E and F. We can read this spreadsheet using the following call:

$CALL gdxxrw file.xls cmerge=1 par=A rng=A1 rdim=1 cdim=2

And display the file in the GAMSIDE:

cmerge2.png
Symbol A displayed in GAMSIDE

Ignoring Rows and Columns Example

We can use the options IgnoreColumns and IgnoreRows to ignore columns and or rows when reading data for a symbol. The following sheet uses the red colored columns and rows we want to ignore:

ignore1.png
Red columns and rows to be ignored

We can read this spreadsheet and ignore the red colored columns and rows with the following call to gdxxrw:

$call gdxxrw test3.xlsx par=A rng=C1 Cdim=2 Rdim=2 IgnoreRows=2,6 IgnoreColumns=D,G

After opening the test3.gdx file in the gamside and rearranging the display, we see:

ignore2.png
gdx file displayed in the gamside

In the example above we ignored column D which would have been part of the index for the rows. So the range for the row index was extended with column 'E'. The 'E' column is no longer part of the data range. The treatment of the column index is similar. Row '2' would have been part of the column index, and now that the row is ignored, the next row becomes part of the column index and row '3' is no longer part of the data range.

Changes in the Set Values parameter

The following documents some changes that were made when reading a Set using the Values=Strings option. Reading a Domain or a parameter was not affected by these changes.

To illustrate the various behaviors in different versions of GDXXRW, we are using the spreadsheet data as shown below, using the following call:

$call gdxxrw.exe test.xlsx set=one rng=b2 rdim=1 values=string set=two rng=b1 rdim=1 cdim=1 values=string
setvalues1.png
Spreadsheet data to illustrate Set Values

We read the one dimensional set in column B by specifying the top-left corner of the data (cell B2) or the full range (B2..B5). A two dimensional set is read using the top-left corner of the data (cell B1) or the full range (B1..E5). Variations are introduced by specifying options for Values to be String or YN or NoData. The option value All is only available in later versions of GDXXRW and was used to introduce the same behavior as Strings in earlier versions.

Chronological description of the changes made to the Values option:

  • GAMS versions prior to version 24.3:

    Values=String results in reading the data dense. The contents of a cell is used for the set associated text and an element is included even if the data cell is empty.

    clip0001a.png
    clip0001b.png

  • GAMS version 24.3.1

    We changed the interpretation of String to mean that the set element was only to be included when the string data was not empty. Note below that element a4 is missing from the one-dimensional set and so are a1.b1, a2.b2 etc from the two-dimensional set. For the two-dimensional case this looked more or less how the GAMS compiler interprets a table statement. Unfortunately, the interpretation of empty data cells was also applied to one-dimensional sets leading to undesired results.

    clip0002a.png
    clip0002b.png

  • GAMS version 24.4.1

    Recognizing that reading a set dense was no longer available, we introduced a new option Values=All. This allowed us to read Excel data the same way as was possible before version 24.3.1 using the Values=String i.e. reading the data dense and including all cells whether the data cell is empty or not and use the content of the data cell for the set associated texts.

  • GAMS version 24.4.6 (Current status)

    We decided to remove some confusion with the interpretation of the Values option by introducing the options Dense and Sparse and flagging the options Strings and All as deprecated. Both Strings and All are replaced with Dense.

Backward compatibility issues.

With these changes we broke our in house rule not to introduce changes that break backward compatibility. Because of this, the user needs to change the parameters for the GDXXRW call or change the workbook data. The parameters for the call should be changed from Values=String to Values=Dense for one-dimensional sets where we specify the top-left corner only. In case the data in the workbook needs to be changed, inserting a string to in the data cell will address the issue. In the example on the top of the page, inserting a 'Y' in cell C5.