$title Complex Connect Example with Spreadsheets (CONNECT01,SEQ=144)

$onText
This model uses GAMS Connect to read and write Excel workbooks. It also
performs some label manipulation using Connect agend PythonCode and
compares different ways to read Excel tables (RawExcelReader vs ExcelReader).

Contributor: Michael Bussieck, March 2022
$offText

Set code, labId, cut, rr(code<,labId);
parameter labdata(code,labid,cut);
$onEcho > getdata.gms
* Symbols for RawExcelReader
alias (u,*); Set s,w,r,c,ws(s,w),vs(s,r,c),vu(s,r,c,u); Parameter vf(s,r,c);
$onEmbeddedCode Connect:
- RawExcelReader:
    file: labdata.xlsx
- GAMSWriter:
    symbols: all
$offEmbeddedCode

* Symbols to be filled
alias (*,code,labId,cut); Parameter raw(code,labId,cut); Set cutId, rr(code,labId)
Set cX(c,cut) 'column index', rX(r,code,labId) 'row index';
Singleton set cLast(c); Scalar lastPos;
loop(ws(s,'ZAg'),
  lastPos = smax(vu(s,r,c,u), c.pos); cLast(c) = c.pos=lastPos;
  loop(r$(ord(r)>4),
    rX(r,code,labId) $= vu(s,r,'C1',code) and vu(s,r,cLast,labId));
  loop(c$(ord(c)>1 and not cLast(c)),
    cX(c,cut) $= vu(s,'R4',c,cut));
  loop((rX(r,code,labId),cX(c,cut)),
    raw(code,labId,cut) = vf(s,r,c))
  loop(cX(c,cut),
    cutId(cut) = yes)
);
option rr<rX;
$offEcho
$call.checkErrorLevel gams getdata.gms lo=%gams.lo% gdx=getdata.gdx
$onEmbeddedCode Connect:
- GDXReader:
    file: getdata.gdx
    symbols: [ {name: rr}, {name: raw}, {name: cutId, newName: cut} ]
- Projection:
    name: rr(code,labid)
    newName: labid(labid)
- PythonCode:
    code: |
      labid_records = sorted([ 'L'+t[0] for t in connect.container['labid'].records.values ], key=lambda t: int(t[1:]))
      rr_records = sorted([ (t[0],
                               'L'+t[1]) for t in connect.container['rr'].records.values ], key=lambda t: int(t[0]))
      # Trim elements of set cut to two decimal places
      cut_records = sorted([ '{:.2f}'.format(float(t[0])) for t in connect.container['cut'].records.values ], key=float)
      labdata_records = [ (t[0],
                             'L'+t[1],
                             '{:.2f}'.format(float(t[2])),
                             t[-1]) for t in connect.container['raw'].records.values ]
                         
      connect.container.addSet('labid_mod', ['*'], records=labid_records)
      connect.container.addSet('rr_mod', ['*']*2, records=rr_records)
      connect.container.addSet('cut_mod', ['*'], records=cut_records)
      connect.container.addParameter('labdata', ['*']*3, records=labdata_records)
- GAMSWriter:
    symbols: [ {name: labid_mod, newName: labid}, {name: rr_mod, newName: rr}, {name: cut_mod, newName: cut}, {name: labdata} ]
    duplicateRecords: first
$offEmbeddedCode
execute_unload 'labdata.gdx', labdata, cut, rr;

* Reintroduce 0 (zeros)
labdata(rr,cut) = labdata(rr,cut) + eps;

execute 'rm -f labdatanew.xlsx';
* Write new workbook with good table
embeddedCode Connect:
- GAMSReader:
    symbols: [ {name: labdata} ]
- ExcelWriter:
    file: labdatanew.xlsx
    valueSubstitutions: {EPS: 0}
    symbols:
      - name: labdata
        range: ZAg!A4
endEmbeddedCode
option clear=rr, clear=labdata;
        
embeddedCode Connect:
- ExcelReader:
    file: labdatanew.xlsx
    symbols:
      - name: labdata
        rowDimension: 2
        range: ZAg!A4
- Projection:
    name: labdata(code,labid,cut)
    newName: rr(code,labid)
    asSet: True
- GAMSWriter:
    symbols: all
    duplicateRecords: first
endEmbeddedCode     
execute_unload 'labdatanew.gdx', labdata, cut, rr;
execute.checkErrorLevel 'gdxdiff labdata.gdx labdatanew.gdx > %system.NullFile%';