$title 'Test Connect agent RawExcelReader' (CARXR,SEQ=893)

$onText
This test ensures the correctness of the Connect agent RawExcelReader.

Contributor: Michael Bussieck, March 2022
$offText

* On the major platforms (Windows, Linux, Mac), GMSPYTHONLIB gets automatically set 
* to use the internal Python installation in sysdir/GMSPython.
$if not setEnv GMSPYTHONLIB $abort.noError Embedded code Python not ready to be used
$log --- Using Python library %sysEnv.GMSPYTHONLIB%

$onEchoV > t.gms
Set s(*) Workbook sheets / '%S%1' Sheet1, '%S%2' Sheet2 /;
Set w(*) Workbook sheets by name / 'Sheet1', 'Sheet2' /;
Set ws(*,*) Workbook map / '%S%1'.'Sheet1', '%S%2'.'Sheet2' /;
Set c(*) Columns / %C%1*%C%3 /;
Set r(*) Rows / %R%1*%R%14 /;

$if not set MERGEF $set MERGEF ""
$if not set MERGES $set MERGES ""
$onUNDF
Parameter vf(*,*,*) Cells with numerical value /
%S%1.%R%1 .%C%1 1, 
%S%1.%R%1 .%C%2 1, 
%S%1.%R%1%MERGEF%.%C%3 1, 
%S%1.%R%2 .%C%2 2, 
%S%1.%R%3 .%C%1 39448, 
%S%1.%R%3 .%C%2 3, 
%S%1.%R%4 .%C%1 +Inf, 
%S%1.%R%4 .%C%2 4, 
%S%1.%R%5 .%C%1 -Inf, 
%S%1.%R%5 .%C%2 5, 
%S%1.%R%6 .%C%1 NA, 
%S%1.%R%6 .%C%2 6, 
%S%1.%R%7 .%C%1 NA, 
%S%1.%R%7 .%C%2 7, 
%S%1.%R%8 .%C%1 Eps, 
%S%1.%R%8 .%C%2 8, 
%S%1.%R%9 .%C%1 Eps, 
%S%1.%R%9 .%C%2 9, 
%S%1.%R%10.%C%1 Undf, 
%S%1.%R%10.%C%2 10, 
%S%1.%R%11.%C%1 39448, 
%S%1.%R%11.%C%2 11 /;
$offUNDF

Set vs(*,*,*) Cells with explanatory text /
%S%1. %R%4.%C%1 inf, 
%S%1. %R%5.%C%1 -inf, 
%S%1. %R%6.%C%1 na, 
%S%1. %R%7.%C%1 nan, 
%S%1. %R%8.%C%1 eps, 
%S%1. %R%9.%C%1 Eps, 
%S%1.%R%10.%C%1 undef, 
%S%2. %R%1.%C%1 short, 
%S%2. %R%2.%C%1 a23456789012345678901234567890123456789012345678901234567890123, 
%S%2. %R%3.%C%1 a234567890123456789012345678901234567890123456789012345678901234, 
%S%2. %R%4.%C%1 a234567890123456789012345678901234567890123456789012345678901~1, 
%S%2. %R%5.%C%1 a2345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234
%S%2. %R%6%MERGES%.%C%1 merge
%S%2. %R%11.%C%1 "This is some label that can not be represented in GAMS since it is too long"
%S%2. %R%12.%C%1 "This is some label that can not be represented in GAMS since it is ALSO too long"
%S%2. %R%13.%C%1 "This is ANOTHER label that can not be represented in GAMS since it is too long"
%S%2. %R%14.%C%1 "This is A THIRD label that can not be represented in GAMS since it is too long"
/;

Set %vu%(*,*,*,*) Cells with potential GAMS label /
%S%1.%R%1.%C%1.'True' True, 
%S%1.%R%1.%C%2.'1' 1, 
%S%1.%R%1%MERGEF%.%C%3.'1' 1, 
%S%1.%R%2.%C%1.'False' False, 
%S%1.%R%2.%C%2.'2' 2, 
%S%1.%R%3.%C%1.'2008-01-01 00:00:00' '2008-01-01 00:00:00', 
%S%1.%R%3.%C%2.'3' 3, 
%S%1.%R%4.%C%1.'inf' inf, 
%S%1.%R%4.%C%2.'4' 4, 
%S%1.%R%5.%C%1.'-inf' -inf, 
%S%1.%R%5.%C%2.'5' 5, 
%S%1.%R%6.%C%1.'na' na, 
%S%1.%R%6.%C%2.'6' 6, 
%S%1.%R%7.%C%1.'nan' nan, 
%S%1.%R%7.%C%2.'7' 7, 
%S%1.%R%8.%C%1.'eps' eps, 
%S%1.%R%8.%C%2.'8' 8, 
%S%1.%R%9.%C%1.'eps' Eps, 
%S%1.%R%9.%C%2.'9' 9, 
%S%1.%R%10.%C%1.'undef' undef, 
%S%1.%R%10.%C%2.'10' 10, 
%S%1.%R%11.%C%1.'39448' 39448, 
%S%1.%R%11.%C%2.'11' 11, 
%S%2.%R%1.%C%1.'short' short, 
%S%2.%R%2.%C%1.'a23456789012345678901234567890123456789012345678901234567890123' a23456789012345678901234567890123456789012345678901234567890123, 
%S%2.%R%3.%C%1.'a234567890123456789012345678901234567890123456789012345678901~1' a234567890123456789012345678901234567890123456789012345678901234, 
%S%2.%R%4.%C%1.'a234567890123456789012345678901234567890123456789012345678901~2' a234567890123456789012345678901234567890123456789012345678901~1, 
%S%2.%R%5.%C%1.'a234567890123456789012345678901234567890123456789012345678901~3' a2345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234
%S%2.%R%6%MERGES%.%C%1.'merge' merge
%S%2.%R%11.%C%1.'This is some label that can not be represented in GAMS since ~1' "This is some label that can not be represented in GAMS since it is too long"
%S%2.%R%12.%C%1.'This is some label that can not be represented in GAMS since ~2' "This is some label that can not be represented in GAMS since it is ALSO too long"
%S%2.%R%13.%C%1.'This is ANOTHER label that can not be represented in GAMS sin~1' "This is ANOTHER label that can not be represented in GAMS since it is too long"
%S%2.%R%14.%C%1.'This is A THIRD label that can not be represented in GAMS sin~1' "This is A THIRD label that can not be represented in GAMS since it is too long"
/;
$offEcho

*** test expected errors ***
$log Test raising an exception for existing symbol name
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addParameter("vf")
- RawExcelReader:
    file: carxr.xlsx
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

$log Test raising an exception for existing symbol name (empty file)
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addSet("r")

      import openpyxl
      wb = openpyxl.Workbook()
      wb.save("carxr_empty.xlsx")
- RawExcelReader:
    file: carxr_empty.xlsx
$offEmbeddedCode
$if errorFree $abort 'Expect errors'
$clearErrors

*********************************************************

$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx refnomerge.gdx --vu=vu --S=S --R=R --C=C
$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx refmerge.gdx --vu=vu --S=S --R=R --C=C --MERGEF="*R11" --MERGES="*R10"
$onEmbeddedCode Connect:
- RawExcelReader:
    file: carxr.xlsx
- GDXWriter:
    file: ctxrxoutnomerge.gdx
$offEmbeddedCode 
$onEmbeddedCode Connect:
- RawExcelReader:
    file: carxr.xlsx
    mergedCells: True
- GDXWriter:
    file: ctxrxoutmerge.gdx
$offEmbeddedCode 

$call.checkErrorLevel gdxdiff refnomerge.gdx ctxrxoutnomerge.gdx > %system.NullFile%
$call.checkErrorLevel gdxdiff refmerge.gdx ctxrxoutmerge.gdx > %system.NullFile%

$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx refnomerge.gdx --vu=vux --S=sxx --R=rxx --C=cxx
$call.checkErrorLevel gams t.gms lo=%gams.lo% gdx refmerge.gdx --vu=vux --S=sxx --R=rxx --C=cxx --MERGEF="*rxx11" --MERGES="*rxx10"
$onEmbeddedCode Connect:
- RawExcelReader:
    file: carxr.xlsx
    sheetLabel: sxx
    rowLabel: rxx
    columnLabel: cxx
    vuName: vux
- GDXWriter:
    file: ctxrxoutnomerge.gdx
$offEmbeddedCode 
$onEmbeddedCode Connect:
- RawExcelReader:
    file: carxr.xlsx
    mergedCells: True
    sheetLabel: sxx
    rowLabel: rxx
    columnLabel: cxx
    vuName: vux
- GDXWriter:
    file: ctxrxoutmerge.gdx
$offEmbeddedCode 

$call.checkErrorLevel gdxdiff refnomerge.gdx ctxrxoutnomerge.gdx > %system.NullFile%
$call.checkErrorLevel gdxdiff refmerge.gdx ctxrxoutmerge.gdx > %system.NullFile%

$onEcho > uelcheck.gms
set test(*) / i1*i5 'test' /;
alias (*,S,R,C,U);
set vu(S,R,C,U), vuX(S,R,C);
$onEmbeddedCode Connect:
- GAMSReader:
   symbols:
    - name: test
- PythonCode:
   code: |
     import pandas as pd
     df = connect.container['test'].records
     df.iloc[0] = {'uni_0': 'i1', 'element_text': '"test"'}
     df.iloc[1] = {'uni_0': 'i2', 'element_text': "'test'"}
     df.iloc[2] = {'uni_0': 'i3', 'element_text': 't\'e"s\'t'}
     df.iloc[3] = {'uni_0': 'i4', 'element_text': 'te\tst'}
     df.iloc[4] = {'uni_0': 'i5', 'element_text': 'te\nst'}
     df.to_excel('x.xlsx')
- RawExcelReader:
   file: x.xlsx
- GAMSWriter:
   symbols:
     - name: vu
$offEmbeddedCode vu
option vux<vu;
$eolCom !
abort$(not vux('S1','R2','C3')) 'expect uel'; ! "test"
abort$(not vux('S1','R3','C3')) 'expect uel'; ! 'test'
abort$vux('S1','R4','C3') 'expect no uel'; ! t'e"s't
abort$vux('S1','R5','C3') 'expect no uel'; ! te TAB st
abort$vux('S1','R6','C3') 'expect no uel'; ! te NEWLINE st
$offEcho
$call.checkErrorLevel gams uelcheck.gms lo=2

$log Test agent with empty file (Some symbols will have empty DataFrames)
$onEmbeddedCode Connect:
- RawExcelReader:
    file: carxr_empty.xlsx
    cName: col
    rName: row
    sName: sht
- PythonCode:
    code: |
      data_vf = connect.container['vf'].records
      data_vs = connect.container['vs'].records
      data_vu = connect.container['vu'].records

      expected_vf = ['sht', 'row', 'col', 'value']
      expected_vs = ['sht', 'row', 'col', 'element_text']
      expected_vu = ['sht', 'row', 'col', 'uni', 'element_text']

      if data_vf is None or not data_vf.empty:
        raise Exception("Expected >vf< with empty DataFrame.")

      if data_vs is None or  not data_vs.empty:
        raise Exception("Expected >vs< with empty DataFrame.")

      if data_vu is None or not data_vu.empty:
        raise Exception("Expected >vu< with empty DataFrame.")

      if (data_vf.columns != expected_vf).any() :
        raise Exception("Unexpected columns for DataFrame >vf<")

      if (data_vs.columns != expected_vs).any() :
        raise Exception("Unexpected columns for DataFrame >vs<")

      if (data_vu.columns != expected_vu).any() :
        raise Exception("Unexpected columns for DataFrame >vu<")
$offEmbeddedCode