$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: writeAll: True $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 %system.NullFile%';