Description
This test performs the basic read and write operations of the Connect agents ExcelReader and ExcelWriter. Contributor: Clemens Westphal, January 2024
Small Model of Type : GAMS
Category : GAMS Test library
Main file : caxlsrw.gms
$title 'Test Connect agents ExcelReader and ExcelWriter' (CAXLSRW,SEQ=951)
$onText
This test performs the basic read and write operations
of the Connect agents ExcelReader and ExcelWriter.
Contributor: Clemens Westphal, January 2024
$offText
$log --- Using Python library %sysEnv.GMSPYTHONLIB%
set i / i4, i3, i2, i1 /;
set j / j1*j4 /;
set k / k4, k3, k2, k1 /;
set l / l1*l4 /;
set m / m1*m1048576 /;
set n / n1*n16381 /;
set s1(i) / i1, i3 "line 3" /;
set s4(i,j,k,l) /
#i.#j.#k.l1 "line 1"
#i.#j.#k.l2
#i.#j.#k.l3 "line 3"
#i.#j.#k.l4 "line 4"
/;
s4(i,j,k,l)$(uniform(0,1)>0.1) = no;
parameter p0 / 3.14 /;
parameter p1(i);
parameter p4(i,j,k,l);
parameter p5(m,i);
parameter p6(i,n);
p1(i)$(uniform(0,1)<0.5) = uniform(0,1);
p4(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
p5(m,i) = uniform(0,1);
p6(i,n) = uniform(0,1);
variable v0 / l 3.14 /;
variable v1(i);
variable v4(i,j,k,l);
v1.l(i)$(uniform(0,1)<0.5) = uniform(0,1);
v1.m(i)$(uniform(0,1)<0.5) = uniform(0,1);
v4.l(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
v4.m(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
* ExcelWriter
embeddedCode Connect:
- GAMSReader:
readAll: True
- PythonCode:
code: |
import os
for merged_cells in [True, False]:
symbols = \
[
('p0', 'p0_{0}{1}.xlsx'),
('p1', 'p1_{0}{1}.xlsx'),
('p4', 'p4_{0}{1}.xlsx'),
('s1', 's1_{0}{1}.xlsx'),
('s4', 's4_{0}{1}.xlsx'),
]
for s,f in symbols:
for cdim in range(0, self._cdb.container.data[s].dimension+1):
file = f.format(cdim, "_mc" if merged_cells else "")
if os.path.isfile(file):
os.remove(file)
instructions.append( \
{
'ExcelWriter':
{
'file': file,
'mergedCells': merged_cells,
'symbols': [{'name': s, 'columnDimension': cdim, 'range': s+'!B3'}]
}
})
- Projection:
name: v0.all
newName: pv0
- Projection:
name: v1.all(i)
newName: pv1(i)
- Projection:
name: v4.all(i,j,k,l)
newName: pv4(i,j,k,l)
- Projection:
name: v0.lo
newName: v0_lower
- Projection:
name: v1.m(i)
newName: v1_marginal(i)
- Projection:
name: v4.l(i,j,k,l)
newName: v4_level(i,j,k,l)
- PythonCode:
code: |
import os
symbols = \
[
('pv0', 'v0_{0}.xlsx'),
('pv1', 'v1_{0}.xlsx'),
('pv4', 'v4_{0}.xlsx'),
('v0_lower', 'vs0_{0}.xlsx'),
('v1_marginal', 'vs1_{0}.xlsx'),
('v4_level', 'vs4_{0}.xlsx'),
]
for s,f in symbols:
for cdim in range(0, self._cdb.container.data[s].dimension+1):
if os.path.isfile(f.format(cdim)):
os.remove(f.format(cdim))
instructions.append( \
{
'ExcelWriter':
{
'file': f.format(cdim),
'symbols': [{'name': s, 'columnDimension': cdim, 'range': s+'!B3'}]
}
})
endEmbeddedCode
* ExcelReader
$onEchoV > read.gms
set i, j, k, l, m, n, o, q, r, t;
set s4(n<,o<,q<,r<)
set s1(t<);
scalar p0;
parameter p1(m<);
parameter p4(i<,j<,k<,l<);
$if not set vsK $set vsK "''"
$if not set vsV $set vsV "''"
$onEmbeddedCode Connect:
- ExcelReader:
file: %sym%_%cdim%.xlsx
ignoreText: False
symbols:
- name: %sym%
type: %type%
rowDimension: %rdim%
columnDimension: %cdim%
range: %sym%!B3
valueSubstitutions: { %vsK%: %vsV% }
- GAMSWriter:
symbols:
- name: %sym%
$offEmbeddedCode
$offEcho
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=4 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=3 --rdim=1 --vsK='Y' gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=2 --rdim=2 --vsK='Y' gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=1 --rdim=3 --vsK='Y' gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=0 --rdim=4 --vsK=.nan gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s1 --type=set --cdim=1 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s1 --type=set --cdim=0 --rdim=1 --vsK=.nan gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=4 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=3 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=2 --rdim=2 gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=1 --rdim=3 gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=0 --rdim=4 gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p1 --type=par --cdim=1 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p1 --type=par --cdim=0 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p0 --type=par --cdim=0 --rdim=0 gdx=0.gdx > %system.NullFile%";
scalar p0_x; execute_load '0.gdx', p0_x=p0; abort$(abs(p0_x-3.14)>1e-6) 'wrong p0', p0_x;
* ExcelReader to verify that merged cells are written correctly
$onEchoV > read_mc.gms
set i, j, k, l, m, n, o, q, r, t;
set s4(n<,o<,q<,r<)
set s1(t<);
scalar p0;
parameter p1(m<);
parameter p4(i<,j<,k<,l<);
$if not set vsK $set vsK "''"
$if not set vsV $set vsV "''"
$onEmbeddedCode Connect:
- ExcelReader:
file: %sym%_%cdim%_mc.xlsx
ignoreText: False
autoMerge: False
mergedCells: True
symbols:
- name: %sym%
type: %type%
rowDimension: %rdim%
columnDimension: %cdim%
range: %sym%!B3
valueSubstitutions: { %vsK%: %vsV% }
- GAMSWriter:
symbols:
- name: %sym%
$offEmbeddedCode
$offEcho
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=4 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=3 --rdim=1 --vsK='Y' gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=2 --rdim=2 --vsK='Y' gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=1 --rdim=3 --vsK='Y' gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=0 --rdim=4 --vsK=.nan gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s1 --type=set --cdim=1 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s1 --type=set --cdim=0 --rdim=1 --vsK=.nan gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=4 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=3 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=2 --rdim=2 gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=1 --rdim=3 gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=0 --rdim=4 gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p1 --type=par --cdim=1 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p1 --type=par --cdim=0 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p0 --type=par --cdim=0 --rdim=0 gdx=0.gdx > %system.NullFile%";
scalar p0_x; execute_load '0.gdx', p0_x=p0; abort$(abs(p0_x-3.14)>1e-6) 'wrong p0', p0_x;
parameter p1_empty(*);
* Test correct (cDim) order of sparse parameter written by ExcelWriter
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p1_empty
- name: p4
- ExcelWriter:
file: p4_order.xlsx
symbols:
# do not throw an exception if there is no data
- name: p1_empty
- name: p4
endEmbeddedCode
embeddedCode Connect:
- ExcelReader:
file: p4_order.xlsx
ignoreText: False
symbols:
- name: p4
rowDimension: 3
columnDimension: 1
range: p4!A1
- PythonCode:
code: |
# we expect the original UEL order: l1, l2, l3, l4
expected = [['i4', 'j1', 'k3', 'l4', 0.414599358],
['i4', 'j2', 'k4', 'l3', 0.3741985],
['i4', 'j2', 'k2', 'l1', 0.480878863],
['i4', 'j2', 'k2', 'l4', 0.681039283],
['i4', 'j3', 'k3', 'l1', 0.57775716],
['i4', 'j4', 'k4', 'l3', 0.839795967],
['i4', 'j4', 'k1', 'l2', 0.010171892],
['i3', 'j1', 'k4', 'l4', 0.55303282],
['i3', 'j1', 'k2', 'l1', 0.430502537],
['i3', 'j2', 'k3', 'l4', 0.31014418],
['i3', 'j2', 'k2', 'l1', 0.82116568],
['i3', 'j3', 'k3', 'l1', 0.6863915740000001],
['i3', 'j4', 'k4', 'l1', 0.806006615],
['i3', 'j4', 'k4', 'l3', 0.5721642610000001],
['i3', 'j4', 'k4', 'l4', 0.7420390320000001],
['i2', 'j1', 'k2', 'l3', 0.8109937880000001],
['i2', 'j3', 'k4', 'l2', 0.866509716],
['i2', 'j3', 'k4', 'l3', 0.428284381],
['i2', 'j3', 'k1', 'l4', 0.503866822],
['i2', 'j4', 'k2', 'l1', 0.377722234],
['i1', 'j1', 'k4', 'l1', 0.5039530520000001],
['i1', 'j1', 'k4', 'l2', 0.521365665],
['i1', 'j1', 'k4', 'l4', 0.76064865],
['i1', 'j1', 'k2', 'l2', 0.9094419050000001],
['i1', 'j1', 'k2', 'l3', 0.820356749],
['i1', 'j2', 'k3', 'l3', 0.8145443840000001],
['i1', 'j4', 'k2', 'l2', 0.925196663],
['i1', 'j4', 'k2', 'l3', 0.618022658],
['i1', 'j4', 'k2', 'l4', 0.405575083],
['i1', 'j4', 'k1', 'l4', 0.045967406]]
sym = connect.container.data["p4"]
sym.records.sort_values(sym.records.columns[:-1].tolist(), inplace=True)
data = sym.records.values.tolist()
if data != expected:
raise Exception("Unexpected Data p4")
endEmbeddedCode
* test index parameter
embeddedCode Connect:
- GAMSReader:
readAll: True
- ExcelWriter:
file: caxlsrw.xlsx
index: index!A1
tableOfContents: True
- ExcelReader:
file: caxlsrw.xlsx
symbols:
- name: p0_1
range: p0!A1
rowDimension: 0
columnDimension: 0
- name: p1_1
range: p1!A1
rowDimension: 0
columnDimension: 1
- name: p4_1
range: p4_1!A1
rowDimension: 2
columnDimension: 2
- name: p4_2
range: p4_2!B2
rowDimension: 1
columnDimension: 3
- name: p4_3
mergedCells: True
range: p4_3!B2
rowDimension: 3
columnDimension: 1
- name: s1_1
type: set
ignoreText: False
valueSubstitutions: {.nan: ""}
range: s1!A1
rowDimension: 1
columnDimension: 0
- name: s4_1
type: set
mergedCells: True
valueSubstitutions: {"Y": ""}
range: s4!A1
rowDimension: 3
columnDimension: 1
- name: toc
type: set
range: "Table Of Contents!A1"
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
p0_expected = connect.container['p0'].records.values.tolist()
p0_1 = connect.container['p0_1'].records.values.tolist()
if p0_expected != p0_1:
raise Exception("Unexpected Data p0_1.")
p1_expected = connect.container['p1'].records.values.tolist()
p1_expected = [row[:-1] + [round(row[-1], 16)] for row in p1_expected]
p1_1 = connect.container['p1_1'].records.values.tolist()
if p1_expected != p1_1:
raise Exception("Unexpected Data p1_1.")
p4_expected = connect.container['p4'].records.values.tolist()
p4_expected = [row[:-1] + [round(row[-1], 16)] for row in p4_expected]
p4_1 = connect.container['p4_1'].records.values.tolist()
p4_1 = [row[:-1] + [round(row[-1], 16)] for row in p4_1]
if p4_expected != p4_1:
raise Exception("Unexpected Data p4_1.")
p4_2 = connect.container['p4_2']
p4_2.reorderUELs(uels=['l1', 'l2', 'l3', 'l4'], dimensions=3)
p4_2 = p4_2.records.sort_values(p4_2.records.columns[:-1].tolist()).values.tolist()
p4_2 = [row[:-1] + [round(row[-1], 16)] for row in p4_2]
if p4_expected != p4_2:
raise Exception("Unexpected Data p4_2.")
p4_3 = connect.container['p4_3'].records.values.tolist()
p4_3 = [row[:-1] + [round(row[-1], 16)] for row in p4_3]
if p4_expected != p4_3:
raise Exception("Unexpected Data p4_3.")
s1_expected = connect.container['s1'].records.values.tolist()
s1_1 = connect.container['s1_1'].records.values.tolist()
if s1_expected != s1_1:
raise Exception("Unexpected Data s1_1.")
s4_expected = connect.container['s4'].records.values.tolist()
s4_1 = connect.container['s4_1']
s4_1.reorderUELs(uels=['j1', 'j2', 'j3', 'j4'], dimensions=1)
s4_1.reorderUELs(uels=['k4', 'k3', 'k2', 'k1'], dimensions=2)
s4_1 = s4_1.records.sort_values(s4_1.records.columns[:-1].tolist()).values.tolist()
if s4_expected != s4_1:
raise Exception("Unexpected Data s4_1.")
toc_expected = [
['p0', 'Type', 'Parameter'],
['p0', 'Dimension', '0'],
['p0', 'Record Count', '1'],
['p1', 'Type', 'Parameter'],
['p1', 'Dimension', '1'],
['p1', 'Record Count', '3'],
['p4', 'Type', 'Parameter'],
['p4', 'Dimension', '4'],
['p4', 'Record Count', '30'],
['p4', 'Type', 'Parameter'],
['p4', 'Dimension', '4'],
['p4', 'Record Count', '30'],
['p4', 'Type', 'Parameter'],
['p4', 'Dimension', '4'],
['p4', 'Record Count', '30'],
['s1', 'Type', 'Set'],
['s1', 'Dimension', '1'],
['s1', 'Record Count', '2'],
['s4', 'Type', 'Set'],
['s4', 'Dimension', '4'],
['s4', 'Record Count', '28']
]
toc = connect.container['toc'].records.values.tolist()
if toc_expected != toc:
raise Exception("Unexpected Data toc.")
endEmbeddedCode
* Test case insensitive symbol names and append content to Excel file
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- ExcelWriter:
file: p4_cicap.xlsx
symbols:
- name: P4
- ExcelWriter:
file: p4_cicap.xlsx
symbols:
- name: p4
range: append!A1
endEmbeddedCode
* Make parameter p4 dense for the following tests
p4(i,j,k,l) = uniform(0,1);
* Test range validation 1
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- ExcelWriter:
file: p4_range.xlsx
symbols:
- name: p4
columnDimension: 2
range: Sheet1!A1:R18
endEmbeddedCode
abort$[execerror <> 0] 'No errors expected';
* Test range validation (failure)
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- ExcelWriter:
file: p4_range.xlsx
symbols:
- name: p4
columnDimension: 2
range: Sheet1!A1:R17
endEmbeddedCode
abort$[execerror = 0] 'Expected range validation to fail';
execerror = 0;
* Test range validation (failure)
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- ExcelWriter:
file: p4_range.xlsx
symbols:
- name: p4
columnDimension: 2
range: Sheet1!A1:Q18
endEmbeddedCode
abort$[execerror = 0] 'Expected range validation to fail';
execerror = 0;
* Failing ExcelWriter tests due to Excel row/column limits
* Row Limit with writeAll
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p5
- ExcelWriter:
file: p5_excel_row_lim.xlsx
writeAll: True
endEmbeddedCode
abort$[execerror = 0] 'Expected row limit validation to fail';
execerror = 0;
* Column Limit with symbol and range specified
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p6
- ExcelWriter:
file: p6_excel_col_lim.xlsx
symbols:
- name: p6
range: sheet1!D1
endEmbeddedCode
abort$[execerror = 0] 'Expected column limit validation to fail';
execerror = 0;
* Test range validation for scalar
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p0
- ExcelWriter:
file: p0_range.xlsx
symbols:
- name: p0
range: Sheet1!A1:A1
endEmbeddedCode
* Test ExcelReader create categorical but never sort and ExcelWriter always sort
EmbeddedCode Connect:
- PythonCode:
code: |
import openpyxl
data = [
["i" , "j" , "value", "", "i" , "j" , "k1", "k2"],
["i1", "j2", 1 , "", "i1", "j2", "" , 1 ],
["i1", "j1", 2 , "", "i1", "j1", 2 , 3 ],
["i2", "j1", 4 , "", "i2", "j1", 6 , "" ],
["i2", "j2", 3 , "", "i2", "j2", 4 , 5 ],
]
wb = openpyxl.Workbook()
sheet = wb.create_sheet("order")
for row in data:
sheet.append(row)
wb.save("order.xlsx")
- ExcelReader:
file: order.xlsx
symbols:
- name: ij
range: order!A2:C5
rowDimension: 2
columnDimension: 0
- name: ijk
range: order!E1
rowDimension: 2
- PythonCode:
code: |
# checks that categoricals are correct
ij = connect.container.data["ij"]
ij_data_sorted = ij.records.sort_values(ij.records.columns[:-1].tolist()).values.tolist()
expected = [['i1', 'j2', 1.0],
['i1', 'j1', 2.0],
['i2', 'j2', 3.0],
['i2', 'j1', 4.0]]
if ij_data_sorted != expected:
raise Exception("Unexpected Data ij.")
# reorder UELs to check that ExcelWriter sorts
ij.reorderUELs(uels=['j1', 'j2'], dimensions=1)
# checks that categoricals are correct
ijk = connect.container.data["ijk"]
ijk_data_sorted = ijk.records.sort_values(ijk.records.columns[:-1].tolist()).values.tolist()
expected = [['i1', 'j2', 'k2', 1.0],
['i1', 'j1', 'k1', 2.0],
['i1', 'j1', 'k2', 3.0],
['i2', 'j2', 'k1', 4.0],
['i2', 'j2', 'k2', 5.0],
['i2', 'j1', 'k1', 6.0]]
if ijk_data_sorted != expected:
raise Exception("Unexpected Data ijk.")
# reorder UELs to check that ExcelWriter sorts
ijk.reorderUELs(uels=['i2', 'i1'], dimensions=0)
ijk.reorderUELs(uels=['j1', 'j2'], dimensions=1)
ijk.reorderUELs(uels=['k2', 'k1'], dimensions=2)
- ExcelWriter:
file: test_order.xlsx
symbols:
- name: ij
columnDimension: 0
- name: ijk
- ExcelReader:
file: test_order.xlsx
symbols:
- name: ij_new
range: ij!A1
rowDimension: 2
columnDimension: 0
- name: ijk_new
range: ijk!A1
rowDimension: 2
- PythonCode:
code: |
# checks that ExcelWriter sorts
ij = connect.container.data["ij_new"]
ij_data_sorted = ij.records.sort_values(ij.records.columns[:-1].tolist()).values.tolist()
expected = [['i1', 'j1', 2.0],
['i1', 'j2', 1.0],
['i2', 'j1', 4.0],
['i2', 'j2', 3.0]]
if ij_data_sorted != expected:
raise Exception("Unexpected Data ij_new.")
# checks that ExcelWriter sorts
ijk = connect.container.data["ijk_new"]
ijk_data_sorted = ijk.records.sort_values(ijk.records.columns[:-1].tolist()).values.tolist()
expected = [['i2', 'j1', 'k1', 6.0],
['i2', 'j2', 'k2', 5.0],
['i2', 'j2', 'k1', 4.0],
['i1', 'j1', 'k2', 3.0],
['i1', 'j1', 'k1', 2.0],
['i1', 'j2', 'k2', 1.0]]
if ijk_data_sorted != expected:
raise Exception("Unexpected Data ijk_new.")
endEmbeddedCode
* Test write multiple symbols into one sheet
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- ExcelWriter:
file: p4_multi.xlsx
columnDimension: 2
symbols:
- name: p4
range: Sheet1!A1
- name: p4
range: Sheet1!A20
- ExcelReader:
file: p4_multi.xlsx
rowDimension: 2
columnDimension: 2
symbols:
- name: p4_1
range: Sheet1!A1
- name: p4_2
range: Sheet1!A20
- GDXWriter:
file: multi1.gdx
symbols:
- name: p4
- GDXWriter:
file: multi2.gdx
symbols:
- name: p4_1
newName: p4
- GDXWriter:
file: multi3.gdx
symbols:
- name: p4_2
newName: p4
endEmbeddedCode
execute.checkErrorLevel "gdxdiff multi1.gdx multi2.gdx RelEps=0.0000000000000001 > %system.NullFile%";
execute.checkErrorLevel "gdxdiff multi1.gdx multi3.gdx RelEps=0.0000000000000001 > %system.NullFile%";
* Test columnDimension=auto (default)
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- name: p1
- ExcelWriter:
file: p4_cdim_auto.xlsx
columnDimension: 2
symbols:
- name: p4
columnDimension: auto
- ExcelReader:
file: p4_cdim_auto.xlsx
rowDimension: 3
columnDimension: 1
symbols:
- name: p4_new
range: p4!A1
- ExcelWriter:
file: p1_cdim_auto.xlsx
symbols:
- name: p1
- ExcelReader:
file: p1_cdim_auto.xlsx
rowDimension: 0
columnDimension: 1
symbols:
- name: p1_new
range: p1!A1
- GDXWriter:
file: p4_cdim_auto_ref.gdx
symbols:
- name: p4
- GDXWriter:
file: p4_cdim_auto.gdx
symbols:
- name: p4_new
newName: p4
- GDXWriter:
file: p1_cdim_auto_ref.gdx
symbols:
- name: p1
- GDXWriter:
file: p1_cdim_auto.gdx
symbols:
- name: p1_new
newName: p1
endEmbeddedCode
execute.checkErrorLevel "gdxdiff p1_cdim_auto_ref.gdx p1_cdim_auto.gdx RelEps=0.0000000000000001 > %system.NullFile%";
execute.checkErrorLevel "gdxdiff p4_cdim_auto_ref.gdx p4_cdim_auto.gdx RelEps=0.0000000000000001 > %system.NullFile%";
* Test range ending with "!"" being resolved as "!A1"
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- ExcelWriter:
trace: 4
file: p4_default_a1.xlsx
symbols:
- name: p4
columnDimension: 2
range: p4!
- ExcelReader:
file: p4_default_a1.xlsx
symbols:
- name: p4_new
columnDimension: 2
rowDimension: 2
range: p4!
- PythonCode:
code: |
data_p4 = connect.container["p4"].records.values.tolist()
data_p4_new = connect.container["p4_new"].records.values.tolist()
data_p4 = [row[:-1] + [round(row[-1], 16)] for row in data_p4]
data_p4_new = [row[:-1] + [round(row[-1], 16)] for row in data_p4_new]
if data_p4 != data_p4_new:
raise Exception("Unexpected Data.")
endEmbeddedCode
* Test writeAll=True
embeddedCode Connect:
- GAMSReader:
symbols:
- name: i
- name: j
- name: k
- name: l
- name: s1
- name: s4
- name: p0
- name: p1
- name: p4
- name: p0_x
- ExcelWriter:
file: write_all.xlsx
writeAll: True
- PythonCode:
code: |
import openpyxl
wb = openpyxl.load_workbook("write_all.xlsx")
if len(wb.sheetnames) != 10:
raise Exception("Unexpected number of sheets in workbook.")
expected_sheets = ["i", "j", "k", "l", "s1", "s4", "p0", "p1", "p4", "p0_x"]
for sheet in expected_sheets:
if sheet not in wb.sheetnames:
raise Exception(f"Expected sheet '{sheet}' to be contained in workbook.")
- ExcelReader:
file: write_all.xlsx
symbols:
- name: p1_new
range: p1!
rowDimension: 0
columnDimension: 1
- name: p4_new
range: p4!
rowDimension: 3
columnDimension: 1
- PythonCode:
code: |
data_p1 = connect.container['p1'].records.values.tolist()
data_p1 = [row[:-1] + [round(row[-1], 16)] for row in data_p1]
data_p1_new = connect.container['p1_new'].records.values.tolist()
data_p1_new = [row[:-1] + [round(row[-1], 16)] for row in data_p1_new]
if data_p1 != data_p1_new:
raise Exception("Unexpected Data.")
data_p4 = connect.container['p4'].records.values.tolist()
data_p4 = [row[:-1] + [round(row[-1], 16)] for row in data_p4]
data_p4_new = connect.container['p4_new'].records.values.tolist()
data_p4_new = [row[:-1] + [round(row[-1], 16)] for row in data_p4_new]
if data_p4 != data_p4_new:
raise Exception("Unexpected Data.")
endEmbeddedCode
* test writing of special values as strings (default)
embeddedCode Connect:
- PythonCode:
code: |
import pandas as pd
data = [
['i1', "EPS"],
['i2', "-INF"],
['i3', "INF"],
['i4', "UNDEF"],
['i5', "NA"],
['i6', 0],
['i7', 1]
]
df = pd.DataFrame(data)
connect.container.addParameter('p', ['*'], records=df)
- ExcelWriter:
file: sv.xlsx
writeAll: True
- ExcelReader:
file: sv.xlsx
symbols:
- name: p_new
range: p!
rowDimension: 0
- PythonCode:
code: |
import numpy as np
import gams.transfer as gt
data_p = connect.container["p"].records.values.tolist()
data_p_new = connect.container["p_new"].records.values.tolist()
if not np.array_equal(data_p, data_p_new):
raise Exception("Unexpected data.")
if not gt.SpecialValues.isEps(data_p[0][-1]) or not gt.SpecialValues.isEps(data_p_new[0][-1]):
raise Exception("Unexpected data EPS.")
if not gt.SpecialValues.isNegInf(data_p[1][-1]) or not gt.SpecialValues.isNegInf(data_p_new[1][-1]):
raise Exception("Unexpected data -INF.")
if not gt.SpecialValues.isPosInf(data_p[2][-1]) or not gt.SpecialValues.isPosInf(data_p_new[2][-1]):
raise Exception("Unexpected data INF.")
if not gt.SpecialValues.isUndef(data_p[3][-1]) or not gt.SpecialValues.isUndef(data_p_new[3][-1]):
raise Exception("Unexpected data UNDEF.")
if not gt.SpecialValues.isNA(data_p[4][-1]) or not gt.SpecialValues.isNA(data_p_new[4][-1]):
raise Exception("Unexpected data NA.")
if data_p[5][-1] != 0 or data_p_new[5][-1] != 0:
raise Exception("Unexpected data 0.")
if data_p[6][-1] != 1 or data_p_new[6][-1] != 1:
raise Exception("Unexpected data 1.")
endEmbeddedCode
* test value substitutions for special values
embeddedCode Connect:
- PythonCode:
code: |
import pandas as pd
data = [
['i1', "EPS"],
['i2', "-INF"],
['i3', "INF"],
['i4', "UNDEF"],
['i5', "NA"],
['i6', 0],
['i7', 1]
]
df = pd.DataFrame(data)
connect.container.addParameter('p', ['*'], records=df)
- ExcelWriter:
file: sv2.xlsx
writeAll: True
valueSubstitutions: {
"EPS": 0,
"-INF": 1,
"INF": 2,
"UNDEF": 3,
"NA": 4
}
- ExcelReader:
file: sv2.xlsx
symbols:
- name: p_new
range: p!
rowDimension: 0
- PythonCode:
code: |
expected = [
['i1', 0.],
['i2', 1.],
['i3', 2.],
['i4', 3.],
['i5', 4.],
['i6', 0.],
['i7', 1.]
]
data_p_new = connect.container["p_new"].records.values.tolist()
if expected != data_p_new:
raise Exception("Unexpected data")
endEmbeddedCode
* test value substitutions for special values not being transitive
embeddedCode Connect:
- PythonCode:
code: |
import pandas as pd
data = [
['i1', "EPS"],
['i2', "-INF"],
['i3', "INF"],
['i4', "UNDEF"],
['i5', "NA"],
['i6', 0],
['i7', 1]
]
df = pd.DataFrame(data)
connect.container.addParameter('p', ['*'], records=df)
- ExcelWriter:
file: sv2.xlsx
writeAll: True
valueSubstitutions: {
"EPS": .nan, # gets dropped - .nan is gt.SpecialValues.Undef and should not be replaced by the UNDEF:1 value substitution
"-INF": 0,
"INF": .nan, # gets dropped
"UNDEF": 1,
"NA": 2
}
- ExcelReader:
file: sv2.xlsx
symbols:
- name: p_new
range: p!
rowDimension: 0
- PythonCode:
code: |
expected = [
['i2', 0.],
['i4', 1.],
['i5', 2.],
['i6', 0.],
['i7', 1.]
]
data_p_new = connect.container["p_new"].records.values.tolist()
if expected != data_p_new:
raise Exception("Unexpected data")
endEmbeddedCode
* test ExcelWriter skipping symbol with empty DataFrame without raising an Exception and skipping file creation
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- Filter:
name: p4
newName: p4new
labelFilters:
- column: 1
keep: ["x"]
- ExcelWriter:
file: p4_empty.xlsx
symbols:
- name: p4new
- PythonCode:
code: |
import os
if os.path.exists("p4_empty.xlsx"):
raise Exception("Do not expect empty workbook to be written")
endEmbeddedCode
* test ExcelWriter skipping symbol with empty DataFrame without raising an Exception
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- name: p1
- Filter:
name: p4
newName: p4new
labelFilters:
- column: 1
keep: ["x"]
- ExcelWriter:
file: empty_sheet.xlsx
symbols:
- name: p4new
- name: p1
- PythonCode:
code: |
import os
if not os.path.exists("empty_sheet.xlsx"):
raise Exception("Do expect workbook to be written")
endEmbeddedCode
* test ExcelWriter writing toc (default)
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- name: p1
- name: s1
- name: s4
- ExcelWriter:
file: toc_default.xlsx
tableOfContents: True
- ExcelReader:
file: toc_default.xlsx
symbols:
- name: toc
type: set
range: Table Of Contents!A1
rowDimension: 1
columnDimension: 1
valueSubstitutions: {.nan: ""}
- PythonCode:
code: |
expected = [
['s1', 'Type', 'Set'],
['s1', 'Dimension', '1'],
['s1', 'Record Count', '2'],
['s1', 'Explanatory text', ''],
['s4', 'Type', 'Set'],
['s4', 'Dimension', '4'],
['s4', 'Record Count', '28'],
['s4', 'Explanatory text', ''],
['p1', 'Type', 'Parameter'],
['p1', 'Dimension', '1'],
['p1', 'Record Count', '3'],
['p1', 'Explanatory text', ''],
['p4', 'Type', 'Parameter'],
['p4', 'Dimension', '4'],
['p4', 'Record Count', '256'],
['p4', 'Explanatory text', '']
]
if expected != connect.container['toc'].records.values.tolist():
raise Exception("Unexpected data")
endEmbeddedCode
* test ExcelWriter writing toc (sorting, custom sheet, empty symbols)
option clear=p1;
embeddedCode Connect:
- GAMSReader:
symbols:
- name: p4
- name: p1
- name: s1
- name: s4
- ExcelWriter:
file: toc_custom.xlsx
tableOfContents:
sheetName: "TOC"
emptySymbols: True
sort: True
- ExcelReader:
file: toc_custom.xlsx
symbols:
- name: toc
type: set
range: TOC!A1
rowDimension: 1
columnDimension: 1
valueSubstitutions: {.nan: ""}
- PythonCode:
code: |
expected = [
['p1', 'Type', 'Parameter'],
['p1', 'Dimension', '1'],
['p1', 'Record Count', '0'],
['p1', 'Explanatory text', ''],
['p4', 'Type', 'Parameter'],
['p4', 'Dimension', '4'],
['p4', 'Record Count', '256'],
['p4', 'Explanatory text', ''],
['s1', 'Type', 'Set'],
['s1', 'Dimension', '1'],
['s1', 'Record Count', '2'],
['s1', 'Explanatory text', ''],
['s4', 'Type', 'Set'],
['s4', 'Dimension', '4'],
['s4', 'Record Count', '28'],
['s4', 'Explanatory text', '']
]
if expected != connect.container['toc'].records.values.tolist():
raise Exception("Unexpected data")
endEmbeddedCode
* test ExcelWriter value substitutions for sets
embeddedCode Connect:
- GAMSReader:
symbols:
- name: s1
- ExcelWriter:
file: set_valuesub.xlsx
valueSubstitutions: {"line 3": "new text"}
symbols:
- name: s1
- ExcelReader:
file: set_valuesub.xlsx
symbols:
- name: s1_new
range: s1!
rowDimension: 0
type: set
ignoreText: False
valueSubstitutions: {.nan: ""}
- PythonCode:
code: |
expected = [
['i3', 'new text'],
['i1', '']
]
data_s1_new = connect.container["s1_new"].records.values.tolist()
if expected != data_s1_new:
raise Exception("Unexpected data")
endEmbeddedCode