【免费】GAMS与EXCEL互动的方法【免费】-经管之家官网！

无论读取还是保存，都需要使用gdx文件作为中介。

读取时，先将excel的数据转换到gdx中，然后读取数据至内存。

eg：

set

i/usa,eu,row/

t/2005*2010/

;

parameters

LandRate(i,t)

CapitalRate(i,t)

SkLabRate(i,t)

UnskLabRate(i,t)

NatResRate(i,t)

test(i)

;

$call "gdxxrw ../data/trend.xlsx output=../data/trend.gdx par=NatResRate rng=NatRes! par=LandRate rng=Land! par=CapitalRate rng=Capital!par=SkLabRate rng=SkLab! par=UnskLabRate rng=UnskLab!"

$GDXIN ../data/trend.gdx

$load LandRate CapitalRate SkLabRate UnskLabRate NatResRate

$GDXIN

display

LandRate

CapitalRate

SkLabRate

UnskLabRate

NatResRate;

将数据保存至excel时，首先要将内存中的变量或者参数保存成gdx形式，如后转到到excel。

eg：

execute_unload "../data/ReTest.gdx" NatResRate

execute 'gdxxrw.exe ../data/ReTest.gdx output=../data/ReTest.xlsx par=NatResRate rng=sheet1!'

注意：当data目录下已经存在ReTest.xlsx文件时，再次保存相同的文件名，并不会覆盖原来的文件，而是在这个文件上继续添加数据。

IntroductionThis section gives a brief overview on how to use the GDX facilities in GAMS to read data from Excel and to write data to Excel. For more detailed information see the Appendix "GDX Facilities and Tools" of the GAMS User's Guide.Sample model to write an Excel file: gms2xls.gmsSample model to read from an Excel file: xls2gms.gmsSystem Requirements: A GAMS system Distribution 21.0 or later is required.
IntroductionGAMS communicates with Excel via GDX (GAMS Data Exchange) files.A GDX file is a file that stores the values of one or more GAMS symbols such as sets, parameters variables and equations. GDX files can be used to prepare data for a GAMS model, present results of a GAMS model, store results of the same model using different parameters etc. A GDX file does not store a model formulation or executable statements.
GDX files are binary files that are portable between different platforms. They are written using the byte ordering native to the hardware platform they are created on, but can be read on a platform using a different byte ordering.
In order to write data from GAMS to Excel, the user writes a GDX file and then writes the Excel file from the GDX file.
GAMS ----> GDX ----> Excel This is practically seamless for the user and requires few commands. The process to import data from an Excel file to GAMS is similar: Excel ----> GDX ----> GAMS Example: GAMS to ExcelWe will build on the simple transportation model from the GAMS Model library and write the solution x and the marginals of x to an Excel file.After the solve statement, we unload the data (x.L and x.M) to a GDX file using the execute_unload command:
execute_unload "results.gdx" x.L x.M Note that the execute_unload command is executed during the actual execution phase (not during compilation time as $ control options) and creates a GDX file called results.gdx.Now let us write the data from the GDX file to an Excel file called results.xls. We do this using the GDXXRW utility
execute 'gdxxrw.exe results.gdx var=x.L' execute 'gdxxrw.exe results.gdx var=x.M rng=NewSheet!f1:i4' For the first call for x.L, there is no range specified and the data is written in cell A1 and beyond in the first available sheet. For the marginals x.M data will be written to cells F1:I4 in the sheet NewSheet. Note that we specified var=x.L and var=x.M. If the user wishes to write parameters to the Excel file, the relevant command is par. See the GDX facilities in GAMS document for further details.The complete GAMS model: gms2xls.gmsThe resulting GDX file: results.gdxThe resulting Excel file: results.xlsExample: Excel to GAMSAgain, we will use the transportation model and make use of the results.xls file created by the previous model. First we will create the GDX file from the Excel file. We will make use of the GDXXRW utility: $CALL GDXXRW.EXE results.xls par=Level rng=A1:D3 Note that since we are using the $CALL command, this occurs during the compilation phase and not during execution time. We specify that the data in the range A1:D3 is read in as a parameter called Level. The resulting GDX file will be called results.gdxBefore we can read in the data, we must define a parameter called Level over the appropriate sets:
Parameter Level(i,j); $GDXIN results.gdx $LOAD Level $GDXIN The GDXIN results.gdx command specifies that data will be read in from the appropriate GDX file. We then import data structures values using the $LOAD command. When we are finished, we terminate with $GDXIN.In the example below, we then fix the level values of the variable x to the parameter Level so that solving results in a trivial fixed model.The complete GAMS model: xls2gms.gms