Saturday, October 25, 2008

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.

6 comments:

  1. Hi, this looks interesting. Any chance you'll make the code available?

    ReplyDelete
  2. How much does it cost?

    ReplyDelete
  3. This would be useful for many people. Indeed, with windows and mac and unix versions compiled and available, I would buy it for the cross-platform problems it would solve.

    ReplyDelete
  4. Hi there
    I am actually very interested in this little program. Would it be possible to get it - I am using it for University purposes and would certainly refer to you..
    I contacted GAMS but they do not provide anything for Linux/ubuntu. This would really be very very helpful -

    Thank you for a response

    ReplyDelete
  5. Okay, so how does one obtain a license for this program, and how much does it cost? Thanks.

    ReplyDelete