[Help-glpk] Excel connection

[CHRISTINA CIGNARALE (RIT Student)]

Hi,

 

I am using the current version of GLPK and have been referencing the sodoku_odbc.mod example for my own project. I recently changed my setup of reading each set of data from a different excel workbook to one workbook where each set has a sheet. The sheets are populated from a template sheet. I wrote some macros to transfer the data from the template to the appropriate sheet. I now have a problem with the ODBC handler. It will not accept the *.xlsm extension. Does anyone have any suggestions for fixing this? I get an error from the first table statement.

Thank you for your help.

 

Christina Cignarale

Industrial & Systems Engineer

Rochester Institute of Technology

 

set P;                                                                                     #set of all patients

set PA within P;                                                                #subset of patients admitted until the day before

set PN within P;                                                                #subset of incoming patients requiring admission in the unit

set ISOLATION;                                                                   #set of isolation needs

set GENDER;                                                                      #set of genders                                                                                                                                                              

set R;                                                                                    #set of available rooms

 

param filepath symbolic := 'C:\glpk445\examples\RGHPatient'; # text-parameter used to represent the path where the table files are stored

param B {j in R};                                                                                                # number of beds available in each room j in R  

param G {i in P};# in GENDER ;                                                                    # gender of each patient i in P

param I {i in P};# in ISOLATION;                                                                  # isolation requirement of patient i in P

param c {i in P};                                                                                                 # current relative criticality of patient i in P compared with all other

                                                                                                                                #  patients in the unit

param y {i in P, j in R} binary, default 0;                                                   # binary parameter that is 1 if patient i was in room j the day before, and 0 o.w.

param flag {i in P} binary;                                                                              # binary parameter that is 1 if patient i cannot be moved from its room, and 0 o.w.

 

 

table patient IN "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm'

                'SELECT * FROM [patient$]':

                P <- [P], G, I, c, flag;

table pa IN "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm'

                'SELECT * FROM [patest$]':

                PA <- [PA];

table pn IN "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm'

                'SELECT * FROM [pntest$]':

                PN <- [PN];

table iso IN "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm'

                'SELECT * FROM [iso$]':

                ISOLATION <- [iso];

table gender IN "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm'

                'SELECT * FROM [gender$]':

                GENDER <- [gender];

table rooms IN "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm'

                'SELECT * FROM [rooms$]':

                 R <-[R],B;

table yparam IN "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)}; dbq=RGHtemplate.xlsm'

                'SELECT * FROM [y$]':

                [P, R], y;

 

var x        {i in P, j in R} binary;                                                                                      # binary variable: 1 if patient i is moved to room j, and 0 o.w.

var delta {g in GENDER, j in R: B[j] <> 1} binary;                                                   # binary variable : 1 if there is at least one patient with gender g in room j, and 0 o.w.

var gamma {i in ISOLATION, j in R: B[j] <> 1} binary;                                                          # binary variable: 1 if there is at least one patient with isolation i in room j, and 0 o.w.

 

 

var splus{ i in P, j in R} >=0;

var sminus{i in P, j in R} >=0;

 

 

 

#minimize PatientMoves1: sum {i in PA, j in R} c[i]*(x[i,j]-y[i,j])  - sum { i in PN,j in R:B[j] <> 0 and B[j] <> 99 and B[j] <>1 } c[i]*x[i,j];

 

minimize PatientMoves2: sum {i in P, j in R} c[i]*(splus[i,j]+sminus[i,j]);

 

subject to const0 {i in P, j in R}:x[i,j]-y[i,j] = splus[i,j]-sminus[i,j];

 

subject to const1{i in P}: sum {j in R} x[i,j] =1;

 

subject to const2 {i in P, j in R: B[j] <> 0 and B[j] <> 99 and B[j] <>1}: x[i,j] <= delta[G[i],j];

 

subject to const3 {i in P, j in R: B[j] <> 0 and B[j] <> 99 and B[j] <>1}: x[i,j] <= gamma[I[i],j];

 

subject to const4{j in R: B[j] <> 0 and B[j] <> 99 and B[j] <>1}: sum {g in GENDER} delta[g,j] <=1;     

 

subject to const5 {j in R: B[j] <> 0 and B[j] <> 99 and B[j] <>1}: sum {i in ISOLATION} gamma[i,j] <=1;

               

subject to const6 {j in R:B[j] <> 0 and B[j] <> 99 }: sum {i in P} x[i,j] <= B[j];                                           

 

subject to const7 {i in PA,j in R: B[j]=0}: x[i,j] = 0;

 

subject to const8 {i in PN,j in R: B[j]= 99 }: x[i,j] =0;

 

subject to const9 {i in P, j1 in R: B[j1]=1 and y[i, j1]=1}:sum {j2 in R: B[j2] = 1 and j1 <>j2} x[i,j2]=0;

 

subject to const10 {i in P}: sum{j in R: B[j] <> 0 and B[j] <> 99} x[i,j] <= 1 - flag[i];

 

 

solve;

 

display x, y;

 

table results {i in P, j in R: x[i,j]=1} OUT "ODBC"

                'DRIVER={Microsoft Excel Driver (*.xlsm)};READONLY=FALSE; dbq=RGHtemplate.xlsm'

                'UPDATE [result$] set P = '''';'

                'UPDATE [result$] set R = '''';'

                'UPDATE [result$] set y = '''';'

                'UPDATE [result$] set x = '''';'

                'INSERT INTO [result$]'

                '(P, R, y,x)'

                'VALUES(?,?,?,?);':

                i ~ P, j ~ R, y[i,j]~Y, x[i,j]~X ;         # outputting results to table

 

printf "%16s%16s%16s%16s\n", "PATIENT","STATUS", "INITIAL_ROOM", "NEW_ROOM" > "RGHPatient_results.txt";                      # printing results to a text file

 

for {i in PA}{

                printf "%16i%16s", i, "PA">>"RGHPatient_results.txt";

                printf{r in R: y[i,r]=1} "%16s", r >> filepath &"RGHPatient_results.txt";

                printf{r in R:x[i,r]=1}"%16s\n", r >> filepath &"RGHPatient_results.txt";

}

for {i in PN}{

                printf "%16i%16s", i, "PN">>"RGHPatient_results.txt";

                printf{r in R: y[i,r]=1} "%16s", r >>filepath &"RGHPatient_results.txt";

                printf{r in R: x[i,r]=1}"%16s\n", r >> filepath &"RGHPatient_results.txt";

}

 

 

end;