![]() ![]() $call =xls2gms I=C:\tmp\test.xls O=C:\tmp\data.inc R=B1:E10 The batch facility can be used from inside a GAMS model, e.g.: Table c(i,j) 'data from spreadsheet' / This call will perform its task without user intervention. An example is: C:\tmp>xls2gms "I=c:\My Documents\test.xls" O=test.inc When calling XLS2GMS directly from GAMS we want to specify all command and options directly from the command line or from a command file. The file may be located on a remote machine using the notation \machine\directory\file.xls. The browse button can be used to launch a file open dialog which makes it easier to specify a file. The file must be a valid MS Excel spreadsheet file (*.XLS). This is the combo box to specify the input file. The main screen contains a number of buttons and edit boxes, which are explained below. Using it this way, one can specify the spreadsheet file (.XLS file), the range and the final destination file (a GAMS include file) using the built-in interactive environment. When the tool is called without command line parameters, it will startup interactively. $call =xls2gms yieldtl(l,cl,ty) 'livestock "yield" time series (kg per head)' L 'livestock types' / sheep, goat, angora, cattle, buffalo, mule, poultry /Ĭl 'livestk comm' / meat, milk, wool, hide, egg / This way we can import this table as: Set A simple way is to insert a (narrow) column with a dot in each cell. To make this valid GAMS syntax we need to insert a dot between the index elements. In this spreadsheet the first two columns are index columns. ![]() The following picture shows a spreadsheet with two sets with elements organized vertically (A1:A3) and horizontally (B5:D5). Sets can be directly imported if they are organized vertically. This interface sheet can be filled either manually, with formulas that automatically update values, or by macro's (either recorded or programmed in VBA). It is convenient to add a sheet dedicated for this purpose to your workbook. In some cases the data will need to be copied and massaged to fit into the above format. Table d(i,j) 'distance in thousands of miles' * Workbook: D:\gams projects\xls2gms\ver2.0\Book3.xls * Erwin Kalvelagen, GAMS Development Corp. This table can be expressed comfortably in a spreadsheet as follows: In effect the spreadsheet is considered as an alternative editor for GAMS source code.Īs an example consider the GAMS table in the model which is part of the GAMS model library: Table d(i,j) 'distance in thousands of miles' Instead of defining a new format, we use the GAMS language syntax as the required representation. This tool will use the first approach: the modeler is required to lay-out the data in the spreadsheet in a well defined format. To export spreadsheet data to GAMS parameters, tools will either require a strict format to be used inside the spreadsheet or they will need to offer a complex specification step where the data representation in the spreadsheet is described so that it can be understood by the tool. In some cases the data is scattered around different sheets, and in a format that is not compatible with a more structured multi-dimensional parameter as are used in a GAMS model. Spreadsheet data are often differently organized than is suitable for import into a GAMS model. Microsoft Excel is included in the MS Office suite. XLS2GMS runs only on PC's running Windows (95/98/NT/XP) and with MS Excel installed. This allows tables to be exported directly to GAMS include files. The text is exported to a GAMS include file where some spacing is introduced to maintain cell boundaries. This text can contain GAMS statements, or parts of GAMS statements (e.g. The philosophy of the tool is to consider the content of a spreadsheet as Text. Alternatively XLS2GMS can be run in batch mode which is useful when calling it directly from a GAMS model using the $call command. When running the executable XLS2GMS.EXE without command line parameters the tool will run interactively with a built-in GUI interface. The source is a MS Excel spreadsheet file (*.XLS) and the target is a GAMS Include File. XLS2GMS is a tool to convert spreadsheet data from a Microsoft Excel spreadsheet into GAMS readable format. ![]() ![]() Therefore, we strongly recommend using GDXXRW for data exchange with Excel. While we continue to include all four tools in our distribution, only GDXXRW is under active development. Overview Note The Windows GAMS distribution contains several tools to exchange data with MS-Excel: GDX2XLS, GDXXRW, XLS2GMS, and XLSDUMP. Author Erwin Kalvelagen, GAMS Development Corp Version 2.4 Date May 2004 ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |