Excel_GAMS

Hi,
I want to test my code( about vehicle routing problem) by different problem instances. there are about 100 or more different sets of nodes with X and Y coordinates. I used Excel to calculate euclidean distances and generate matrice for each set. but it will be time consuming to calculate distances for each set by Excel in this way. is there a way to write a code for this calculation in GAMS and adding it to my own code? or is there an easier way for generating this matrix in excel and linking it to GAMS?


To unsubscribe from this group and stop receiving emails from it, send an email to gamsworld+unsubscribe@googlegroups.com.
To post to this group, send email to gamsworld@googlegroups.com.
Visit this group at http://groups.google.com/group/gamsworld?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.

\

Hi ???

If I understand your question correctly, you have the information on your networks in an excel sheet and want to calculate the distances for all networks.
Let’s assume that this information is in the sheets “network1”, “network2”, etc. in the excel file “mynetworks.xlsx”.

What you can do is write a batch file that reads for every network the data, does the calculations and writes the results back to a gdx file or to excel.
Here is some pseudo code that explains how to do this. You have to code it properly to make it run.

The batch file “network.bat” looks like this:
Call gams Euclid.gms --network=network1
Call gams Euclid.gms --network=network2

You can now use %network% in your gams file and it will be automatically replaced by network1 , network2 etc.
The file Euclid.gms has the following structure:

  • Read the sets and the coordinates from the excel sheet for the specific network
    Set i ‘nodes’;
    Alias(i,j)
    Parameter coordinates(i,j);

GDXXRW network.xlsx par=coordinates rng=%network%A1 Cdim=1 Rdim=1
GDXXRW network.xlsx Dset=i rng=%network%A1 Rdim=1

  • Do the calculations
    Loop(I,
    Loop(j,

    ););

  • Write the results to a gdx file
    Execute_unload “%network%.gdx”, results;


    You can now run the batch file in a dos-window.

Hope this helps. For the gdx utility see the documentation on using gdx and the mailing lists.

Cheers

Renger


From: gamsworld@googlegroups.com [mailto:gamsworld@googlegroups.com] On Behalf Of smy mrd
Sent: Saturday, February 09, 2013 9:13 PM
To: gamsworld@googlegroups.com
Subject: Excel_GAMS

Hi,
I want to test my code( about vehicle routing problem) by different problem instances. there are about 100 or more different sets of nodes with X and Y coordinates. I used Excel to calculate euclidean distances and generate matrice for each set. but it will be time consuming to calculate distances for each set by Excel in this way. is there a way to write a code for this calculation in GAMS and adding it to my own code? or is there an easier way for generating this matrix in excel and linking it to GAMS?

\