connect05.gms : Simple Connect Example for Excel

Description

This model uses GAMS Connect to read and write to Excel.
On Windows with installed Excel the output sheets are merged
back into the input workbook.

Contributor: Michael Bussieck, September 2022


Category : GAMS Data Utilities library


Main file : connect05.gms   includes :  connect05.gms

$title 'Simple Connect Example for Excel' (CONNECT05,SEQ=148)

$ontext
This model uses GAMS Connect to read and write to Excel.
On Windows with installed Excel the output sheets are merged
back into the input workbook.

Contributor: Michael Bussieck, September 2022
$offtext

set i / i1*i3 /; alias (i,j,k);
table a(i,j) 'original matrix'
      i1     i2     i3
i1    1      2      3
i2    1      3      4
i3    1      4      3
;
$onEmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: a
- PandasExcelWriter:
    file: input.xlsx
    symbols:
      - name: a
$offEmbeddedCode      

$onMultiR
$clear i a 

$onEmbeddedCode Connect:
- PandasExcelReader:
    file: input.xlsx
    symbols:
      - name: a
        range: a!a1
- Projection:
    name: a(i,j)
    newName: i(i)
    asSet: True
- GAMSWriter:
    writeAll: True
$offEmbeddedCode i a

parameter
  inva(i,j) 'inverse of a'
  chk(i,j)  'check the product a * inva'
  ;

$libInclude linalg invert i a inva
abort$execerror 'Trouble with calculating inverse';

EmbeddedCode Connect:
- GAMSReader:
    symbols:
      - name: inva
- PandasExcelWriter:
    file: output.xlsx
    symbols:
      - name: inva
endEmbeddedCode

Scalar mergedRead /0/;
$ifThen not %system.filesys% == UNIX 
$  libInclude win32 -e msappavail Excel
   mergedRead$(errorLevel=0) = 1;
$endIf   

if (mergedRead,
$  if not %system.filesys% == UNIX 
$  libInclude win32 -e XLSMerge output.xlsx input.xlsx
    EmbeddedCode Connect:
    - PandasExcelReader:
        file: input.xlsx
        symbols:
          - name: a
            range: a!a1
          - name: inva
            range: inva!a1
    - GAMSWriter:
        writeAll: True
    endEmbeddedCode a inva
else
    EmbeddedCode Connect:
    - PandasExcelReader:
        file: input.xlsx
        symbols:
          - name: a
            range: a!a1
    - PandasExcelReader:
        file: output.xlsx
        symbols:
          - name: inva
            range: inva!a1
    - GAMSWriter:
        writeAll: True
    endEmbeddedCode a inva
);
 
chk(i,j) = sum{k, a(i,k)*inva(k,j)};
chk(i,j) = round(chk(i,j),15);
display a,inva,chk;
chk(i,i) = chk(i,i) - 1;
abort$[card(chk)] 'a * ainv <> identity';