Gdxls: call only if needed

Here is a trick to call gdxls from GAMS only if the source file input.xls is newer than the destination file input.gdx:

$call test input.xls -nt input.gdx && gdxls -propertyfile properties.txt

This will cause that gdxls will only be called if input.xls has been changed since the last time this conversion was performed.

gdxls: write xls files on Linux

The gdxls tool is a Java based alternative for GDXXRW on Linux and other non-Windows operating systems.


Besides converting xls→gdx (as described in http://yetanothermathprogrammingconsultant.blogspot.com/2008/10/gdxls-read-xls-files-on-linux.html) we can also convert gdx→xls.

As simple example model is shown below:

$onecho > trnsport2.properties
o=trnsport2.xls
i=trnsport2.gdx
  
parameter=c
rng=a1
rdim=1
cdim=1
 
parameter.2=c
rng.2=f1
rdim.2=2
cdim.2=0
  
parameter.3=c
rng.3=sheet1!A10
rdim.3=0
cdim.3=2

$offecho
 
$call gamslib trnsport
$call gams trnsport lo=2 gdx=trnsport2
  
$call ./gdxls -propertyfile trnsport2.properties
 
$call oocalc trnsport2.xls

We run the trnsport model from the Model Library and save the results in a gdx file. We then extract the c(i,j) parameter from the gdx file and write it to the spreadsheet in different formats.

Note that we use a numbered suffix in the properties file to indicate which options belong to the same item. No suffix is identical to suffix ".1". So we have three items to process and they are numbered 1,2,3.

The result rendered by Open Office Calc looks like the picture shown below (click to enlarge).

gdxls: read xls files on Linux

The GAMS tool GDXXRW is not available under Linux, Unix and OS X.  As I needed to have this functionality in some projects, I developed a Java based alternative. Technobabble: The tool is using GCJ,GCC and CNI so it should be easy to port to any platform where the GNU compilers GCC and GCJ are available.

The tool gdxls can read xls files on Linux (and other Unix-based operating systems) to generate gdx files. Similar to GDXXRW it works as described below.

Consider the following spreadsheet (it can be an xls file from Excel or as shown from Open Office):



The GAMS model to import this file via a gdx file is:

$onecho > trnsport.properties
i=trnsport.xls
o=trnsport.gdx
 
parameter=c
rng=b4
rdim=1
cdim=1
$offecho
 
$call gdxls -propertyfile trnsport.properties
 
sets
  i 'canning plants' / seattle, san-diego /
  j 'markets'        / new-york, chicago, topeka /
;
 
parameter c(i,j);
$gdxin trnsport.gdx
$load c
 
display c;

The log will look like:

erwin@erwin-desktop:~/workspace/gdxls$ gams test1.gms
--- Job test1.gms Start 10/25/08 09:42:14
GAMS Rev 228  Copyright (C) 1987-2008 GAMS Development. All rights reserved
Licensee: GAMS Development Corporation, Washington, DC   G871201/0000CA-ANY
         Free Demo,  202-342-0180,  sales@gams.com,  www.gams.com   DC0000
--- Starting compilation
--- test1.gms(12) 2 Mb
--- call gdxls -propertyfile trnsport.properties
GDXLS V 0.1, Amsterdam Optimization (c) 2008
xls2gdx,input=trnsport.xls,output=trnsport.gdx
Parameter;name=c;range=B4:E6;rdim=1;cdim=1
xls2gdx done
--- test1.gms(20) 3 Mb
--- GDXin=/home/erwin/workspace/gdxls/trnsport.gdx
--- test1.gms(23) 3 Mb
--- Starting execution: elapsed 0:00:00.186
--- test1.gms(23) 4 Mb
*** Status: Normal completion
--- Job test1.gms Stop 10/25/08 09:42:14 elapsed 0:00:00.188
erwin@erwin-desktop:~/workspace/gdxls$

The result is:

----     23 PARAMETER c  gdxls range:B4:E6
 
            new-york     chicago      topeka
 
seattle         2.500       1.700       1.800
san-diego       2.500       1.800       1.400

Another posting will describe gdx to xls conversion.

Reading Excel XLS files on Linux

Using the code from http://poi.apache.org/ I can now read and process Excel XLS files on Linux (and Mac). 

Note: this code implements the Compound Document Format, so does not depend on Windows.