Description
This test ensures the correctness of the Connect agent RawExcelReader. Contributor: Michael Bussieck, March 2022
Small Model of Type : GAMS
Category : GAMS Test library
Main file : carxr.gms
$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