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
- ExcelWriter:
    file: input.xlsx
    symbols:
      - name: a
$offEmbeddedCode      

$onMultiR
$clear i a 

$onEmbeddedCode Connect:
- ExcelReader:
    file: input.xlsx
    symbols:
      - name: a
- Projection:
    name: a(i,j)
    newName: i(i)
    asSet: True
- GAMSWriter:
    symbols: all
    duplicateRecords: first
$offEmbeddedCode i a

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

executeTool.checkErrorLevel 'linalg.invert i a inva';
* Symbol inva has been loaded implicitly by executeTool.checkErrorLevel. The compiler
* instruction in the next line supresses errors about presumably unassigned symbols
$onImplicitAssign

embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: inva
- ExcelWriter:
    file: output.xlsx
    symbols:
      - name: inva
endEmbeddedCode

Scalar mergedRead /0/;
executeTool 'win32.msappavail Excel';
mergedRead$(errorLevel=0) = 1;

if (mergedRead,
    executeTool.checkErrorLevel 'win32.excelMerge output.xlsx input.xlsx';
    embeddedCode Connect:
    - ExcelReader:
        file: input.xlsx
        symbols:
          - name: a
          - name: inva
    - GAMSWriter:
        symbols: all
    endEmbeddedCode a inva
else
    embeddedCode Connect:
    - ExcelReader:
        file: input.xlsx
        symbols:
          - name: a
    - ExcelReader:
        file: output.xlsx
        symbols:
          - name: inva
    - GAMSWriter:
        symbols: all
    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';