Building excel charts from AMPL via
built-in ODBC handler
2005-12-20
Here is a model consisting of N vertices on a flat space. The coordinates of the vertices are assigned randomly and there are links between the vertices if they are sufficiently close to each other.
reset;
option randseed 0;
param ver symbolic = "a22";
param N;
param xmax;
param ymax;
param r;
param x{1..N}=Uniform(0,xmax);
param y{1..N}=Uniform(0,xmax);
set LINKS dimen 2 =
union{i in 1..N, j in i+1..N:
(x[i]-x[j])^2+(y[i]-y[j])^2<=r^2}
{(i,j), (j,i)};
data;
param N := 42;
param xmax := 5;
param ymax := 5;
param r := 1;
model;
minimize Cost;
node Nodes{i in 1..N}: net_out = (if i=1 then
1) + (if i=N then -1);
arc Links{(i,j) in LINKS} >=0, <=1,
from
Nodes[i],
to
Nodes[j],
obj
Cost 1;
solve;
set FLOW dimen 2;
let FLOW :=
if
solve_result == "solved"
then {(i,j) in LINKS: Links[i,j]>0}
else {};
The first vertex sends one unit of flow to the last one and the objective is to minimize the cost (the length) of the flow. To the set FLOW we assign the links followed by the min-cost flow (if there is a solution; and an empty set if there is no solution).
Four parameters with suffixes ax, ay, bx and by are to store the coordinates of each link in sets LINKS and FLOW. They are associated with tables links and flow to be written in an excel file via AMPL’s built-in ODBC handler.
param linkax{(a,b) in LINKS} = x[a];
param linkay{(a,b) in LINKS} = y[a];
param linkbx{(a,b) in LINKS} = x[b];
param linkby{(a,b) in LINKS} = y[b];
param flowax{(a,b) in FLOW} = x[a];
param floway{(a,b) in FLOW} = y[a];
param flowbx{(a,b) in FLOW} = x[b];
param flowby{(a,b) in FLOW} = y[b];
table links OUT "ODBC" (ver &
".xls"):
[i,j],linkax,linkay,linkbx,linkby;
table flow OUT "ODBC" (ver &
".xls"):
[i,j],flowax,floway,flowbx,flowby;
shell ("copy " & ver &
"-0.xls " & ver & ".xls");
write table links;
write table flow;
shell ("ren " & ver &
".xls " & ver & "-" & (if solve_result =
"solved" then Cost else "no") & "-" &
$randseed & ".xls");
The original excel file does not contain any worksheets and namespaces corresponding to the tables links and flow, but it contains data structures preparing the visualization, which are referring to the worksheets named “links” and “flow”, to be created later by the AMPL “write table” command.
Before writing the tables, the script makes a new copy (the first shell command) and then after the writing, it renames the excel file adding the length of the shortest path solution and the random seed value to the file name.
Files:
- Examples with solutions
- Examples with no solution
The chart of the 9
leg shortest path:
Related links:
Download:
You need this zip file (31.7Kb) containing the template excel file and the AMPL script. Unzip them in a folder and run model a22.txt from the AMPL command line.
* * *
© 2005 www.switzernet.com