Description

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
. Solving the model trnsport
. Write solution to a GDX file
. Use GDX file to update spreadsheet

This model is referenced in "Reading Data from Spreadsheet and writing Data to
Spreadsheet after Solve" from the GDXXRW Documentation.

Category : GAMS Data Utilities library

Main file : GDXXRWExample14.gms   includes :  GDXXRWExample14.gms  TrnsportData.xlsx

$title Reading Data from Spreadsheet and writing Data to Spreadsheet after Solve (GDXXRWExample14,SEQ=026)

$onText
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
. Solving the model trnsport
. Write solution to a GDX file
. Use GDX file to update spreadsheet

This model is referenced in "Reading Data from Spreadsheet and writing Data to
Spreadsheet after Solve" from the GDXXRW Documentation.
$offText

$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"


$onEcho > Taskin.txt
dSet=i rng=A3:A4 rDim=1
dSet=j rng=B2:D2        cDim=1
par =d rng=A2    rDim=1 cDim=1
par =a rng=A8    rDim=1
par =b rng=A13   rDim=1
par =f rng=A19    dim=0
$offEcho

$call gdxxrw TrnsportData.xlsx @Taskin.txt trace=0
$gdxIn TrnsportData.gdx

Set
   i(*) 'canning plants'
   j(*) 'markets';

$load i j

display i, j;

Parameter
   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;

Variable
   x(i,j) 'shipment quantities in cases'
   z      'total transportation costs in thousands of dollars';

Positive Variable x;

Equation
   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 TrnsportData.gdx output=TrnsportData.xlsx squeeze=n var=x.l rng=sheet2!A1 trace=0';