Description
This test ensures the correctness of the Connect agent ExcelReader. Contributor: Clemens Westphal, December 2023
Small Model of Type : GAMS
Category : GAMS Test library
Main file : caxlsr.gms
$title 'Test Connect agent ExcelReader' (CAXLSR,SEQ=950)
$ontext
This test ensures the correctness of the Connect agent ExcelReader.
Contributor: Clemens Westphal, December 2023
$offtext
$log --- Using Python library %sysEnv.GMSPYTHONLIB%
$log test invalid input - index and symbols
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
index: Sheet1
symbols:
- name: p
range: scalar!A1
rowDimension: 0
columnDimension: 0
$offEmbeddedCode
$if errorfree $abort 'Expect failure when both index and symbols is specified'
$clearErrors
$log read set with cdim=0, ignoreText=False and range that does not contain expl text - expect error
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: rdim_only!B3:D5
rowDimension: 3
columnDimension: 0
type: set
ignoreText: False
$offEmbeddedCode
$if errorfree $abort 'Expect failure'
$clearErrors
$log read set with rdim=0, ignoreText=False and range that does not contain expl text - expect error
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
trace: 4
symbols:
- name: s
range: cdim_only!C2:E4
rowDimension: 0
columnDimension: 3
type: set
ignoreText: False
$offEmbeddedCode
$if errorfree $abort 'Expect failure'
$clearErrors
$log read from empty sheet with insufficient full range (rows) to get an error
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty_sheet!D10:N19
rowDimension: 10
columnDimension: 10
$offEmbeddedCode
$if errorfree $abort 'Expect failure for insufficient range'
$clearErrors
$log read from empty sheet with insufficient full range (columns) to get an error
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty_sheet!D10:M20
rowDimension: 10
columnDimension: 10
$offEmbeddedCode
$if errorfree $abort 'Expect failure for insufficient range'
$clearErrors
$log read more data than exists using ignoreRows and ignoreColumns to get an invalid range error when ignoring too many rows
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty!A1:J16
ignoreRows: [1,2,3,4,5,6,7,8,9,10,13,20]
ignoreColumns: [3,4,5,6,20]
rowDimension: 5
columnDimension: 5
$offEmbeddedCode
$if errorfree $abort 'Expect failure for insufficient range'
$clearErrors
$log read more data than exists using ignoreRows and ignoreColumns to get an invalid range error when ignoring too many columns
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty!A1:J16
ignoreRows: [2,3,4,5,6,7,8,9,10,13,20]
ignoreColumns: [2,3,4,5,6,20]
rowDimension: 5
columnDimension: 5
$offEmbeddedCode
$if errorfree $abort 'Expect failure for insufficient range'
$clearErrors
$log read a scalar using NW corner
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: scalar!A1
rowDimension: 0
columnDimension: 0
- PythonCode:
code: |
expected = 2
data = connect.container["p"].records.values
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read a scalar using full range and use valueSubstitutions
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: scalar!C4:C4
rowDimension: 0
columnDimension: 0
valueSubstitutions: {5: 6}
- PythonCode:
code: |
expected = 6
data = connect.container["p"].records.values
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read a single value as scalar from a larger set of data
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!C3
rowDimension: 0
columnDimension: 0
- PythonCode:
code: |
expected = 1
data = connect.container["p"].records.values
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim parameter from a full range (sparse)
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B2:G6
- PythonCode:
code: |
expected = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i2', 'j1', 6.],
['i3', 'j2', 12.],
['i4', 'j1', 16.],
['i4', 'j3', 18.],
['i4', 'j5', 20.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log partially read 2-dim parameter using NW corner with multiple symbols in spreadsheet
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B2
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
expected = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i2', 'j1', 6.],
['i3', 'j2', 12.],
['i4', 'j1', 16.],
['i4', 'j3', 18.],
['i4', 'j5', 20.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim parameter using NW corner with multiple symbols in spreadsheet using skipEmpty=2
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B2
rowDimension: 1
columnDimension: 1
skipEmpty: 2
- PythonCode:
code: |
expected = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i1', 'j6', 21.],
['i1', 'j8', 23.],
['i2', 'j1', 6.],
['i2', 'j9', 29.],
['i3', 'j2', 12.],
['i3', 'j7', 32.],
['i3', 'j10',35.],
['i4', 'j1', 16.],
['i4', 'j3', 18.],
['i4', 'j5', 20.],
['i4', 'j6', 36.],
['i4', 'j8', 38.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log partially read 2-dim parameter using full range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B2:D5
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
expected = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i2', 'j1', 6.],
['i3', 'j2', 12.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter from a full range - test case insensitivity of sheet names - handle blank row - handle merged cells
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: MuLtI2!B2:I6
columnDimension: 2
mergedCells: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 1.],
['i1', 'j1', 'k2', 2.],
['i1', 'j2', 'k1', 3.],
['i1', 'j3', 'k2', 5.],
['i1', 'j4', 'k1', 6.],
['i1', 'j5', 'k2', 7.],
['i2', 'j1', 'k1', 8.],
['i2', 'j4', 'k1', 13.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter using NW corner with multiple symbols in spreadsheet - handle blank row - handle merged cells
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi2!B2
columnDimension: 2
skipEmpty: 3
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 1.],
['i1', 'j1', 'k2', 2.],
['i1', 'j2', 'k1', 3.],
['i1', 'j3', 'k2', 5.],
['i1', 'j4', 'k1', 6.],
['i1', 'j5', 'k2', 7.],
['i1', 'j6', 'k1', 15.],
['i1', 'j6', 'k2', 16.],
['i1', 'j6', 'k3', 17.],
['i2', 'j1', 'k1', 8.],
['i2', 'j4', 'k1', 13.],
['i2', 'j6', 'k1', 18.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter using NW corner with skipEmpty=-1
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
mergedCells: True
symbols:
- name: p
range: multi2!B2
columnDimension: 2
ignoreRows: 4
skipEmpty: -1
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 1.],
['i1', 'j1', 'k2', 2.],
['i1', 'j2', 'k1', 3.],
['i1', 'j3', 'k2', 5.],
['i1', 'j4', 'k1', 6.],
['i1', 'j5', 'k2', 7.],
['i1', 'j6', 'k1', 15.],
['i1', 'j6', 'k2', 16.],
['i1', 'j6', 'k3', 17.],
['i1', 'j7', 'k1', 19.],
['i2', 'j1', 'k1', 8.],
['i2', 'j4', 'k1', 13.],
['i2', 'j6', 'k1', 18.],
['i2', 'j7', 'k1', 20.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim set from a full range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: True
symbols:
- name: s
range: multi3!A2:E5
rowDimension: 0
columnDimension: 2
type: set
- PythonCode:
code: |
expected = [
['i1', 'j1', 'text 1'],
['i2', 'j1', 'text 2'],
['i2', 'j2', 'text 3']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim set using NW corner with multiple symbols in spreadsheet
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: False
symbols:
- name: s
range: multi3!B2
rowDimension: 0
columnDimension: 2
type: set
skipEmpty: 2
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', ''],
['i1', 'j2', ''],
['i2', 'j1', ''],
['i2', 'j2', ''],
['i3', 'j1', ''],
['i3', 'j2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim set using NW corner with multiple symbols and autoMerge=False (default)
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: multi3!B2
rowDimension: 0
columnDimension: 2
type: set
skipEmpty: 2
- PythonCode:
code: |
expected = [
['i1', 'j1', ''],
['i2', 'j1', ''],
['i3', 'j1', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter without data from a full range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty!A1:E5
rowDimension: 1
columnDimension: 2
- PythonCode:
code: |
expected = None
data = connect.container["p"].records
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter without data using NW corner
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty!A1
rowDimension: 1
columnDimension: 2
- PythonCode:
code: |
expected = None
data = connect.container["p"].records
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim set without data from a full (partial) range and valueSubstitutions - using root parameters
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
type: set
rowDimension: 1
columnDimension: 2
valueSubstitutions: {.nan: ""}
autoMerge: True
symbols:
- name: s
range: empty!A1:D4
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ""],
['i1', 'j1', 'k2', ""],
['i1', 'j2', 'k1', ""],
['i2', 'j1', 'k1', ""],
['i2', 'j1', 'k2', ""],
['i2', 'j2', 'k1', ""]
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim set with set element text and change it with value substitutions
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
type: set
rowDimension: 0
columnDimension: 2
autoMerge: True
symbols:
- name: s
range: multi3!B2
ignoreText: False
valueSubstitutions: {
"text 1": "another text",
"text 2": "another text",
"text 3": "another text 2",
}
- PythonCode:
code: |
expected = [
['i1', 'j1', 'another text'],
['i2', 'j1', 'another text'],
['i2', 'j2', 'another text 2'],
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim parameter from a full range using valueSubstitutions
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B2:G6
rowDimension: 1
columnDimension: 1
valueSubstitutions: { .nan: eps }
- PythonCode:
code: |
expected = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i2', 'j1', 6.],
['i2', 'j2', -0.0 ],
['i2', 'j3', -0.0 ],
['i2', 'j4', -0.0 ],
['i2', 'j5', -0.0 ],
['i3', 'j1', -0.0 ],
['i3', 'j2', 12.],
['i3', 'j3', -0.0 ],
['i3', 'j4', -0.0 ],
['i3', 'j5', -0.0 ],
['i4', 'j1', 16.],
['i4', 'j2', -0.0 ],
['i4', 'j3', 18.],
['i4', 'j4', -0.0 ],
['i4', 'j5', 20.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim parameter with integer labels
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: int_labels!A1
- PythonCode:
code: |
expected = [
['1', '5', 1.],
['1', '6', 2.],
['2', '6', 5.],
['2', '7', 6.],
['3', '6', 8.],
['3', '7', 9.],
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter with rdim=dim
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: True
symbols:
- name: p
range: rdim_only!B3
rowDimension: 3
columnDimension: 0
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 0.1],
['i1', 'j1', 'k3', 0.3],
['i1', 'j2', 'k3', 0.6],
['i2', 'j1', 'k2', 0.8],
['i2', 'j2', 'k3', 1.3]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter with rdim=dim - autoMerge=False
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: rdim_only!B3
rowDimension: 3
columnDimension: 0
autoMerge: False
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 0.1]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter from a full range with rdim=dim
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: rdim_only!B3:E5
rowDimension: 3
columnDimension: 0
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 0.1],
['i1', 'j1', 'k3', 0.3]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter with cdim=dim
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: True
symbols:
- name: p
range: cdim_only!C2
rowDimension: 0
columnDimension: 3
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 0.1],
['i1', 'j1', 'k3', 0.3],
['i2', 'j2', 'k3', 0.6]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter with cdim=dim - autoMerge=False (default)
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: cdim_only!C2
rowDimension: 0
columnDimension: 3
autoMerge: False
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 0.1]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter from a full range with cdim=dim
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: cdim_only!C2:E6
rowDimension: 0
columnDimension: 3
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 0.1],
['i1', 'j1', 'k3', 0.3]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 1-dimensional parameter from 2-dimensional data (rdim)
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B3:C6
rowDimension: 1
columnDimension: 0
- PythonCode:
code: |
expected = [
['i1', 1.],
['i2', 6.],
['i4', 16.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 1-dimensional parameter from 2-dimensional data (cdim)
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!C2:G3
rowDimension: 0
columnDimension: 1
- PythonCode:
code: |
expected = [
['j1', 1.],
['j2', 2.],
['j3', 3.],
['j4', 4.],
['j5', 5.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim set from a data range with numerical values
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
type: set
symbols:
- name: s
range: rdim_only!B3:E5
rowDimension: 3
columnDimension: 0
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', '0.1'],
['i1', 'j1', 'k3', '0.3']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read scalar, 1-dim with rdim=0, and 1-dim with rdim=1 that are close together
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p0
range: many!B2
rowDimension: 0
columnDimension: 0
- name: p1
range: many!D2:E4
rowDimension: 1
columnDimension: 0
- name: p2
range: many!H1:J2
rowDimension: 0
columnDimension: 1
- PythonCode:
code: |
expected_p0 = [[3.14,]]
expected_p1 = [['i1', 1.0], ['i2', 2.0], ['i3', 3.0]]
expected_p2 = [['i1', 1.0], ['i2', 2.0], ['i3', 3.0]]
data = connect.container["p0"].records.values.tolist()
if data != expected_p0:
raise Exception("Unexpected Data p0")
data = connect.container["p1"].records.values.tolist()
if data != expected_p1:
raise Exception("Unexpected Data p1")
data = connect.container["p2"].records.values.tolist()
if data != expected_p2:
raise Exception("Unexpected Data p2")
$offEmbeddedCode
$log read scalar, 1-dim with rdim=0, and 1-dim with rdim=1 that are very close together - unprecise ranges
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p0
range: many!B6
rowDimension: 0
columnDimension: 0
- name: p1
range: many!C5:D8
rowDimension: 1
columnDimension: 0
- name: p2
range: many!E5:H6
rowDimension: 0
columnDimension: 1
- PythonCode:
code: |
expected_p0 = [[3.14,]]
expected_p1 = [['i1', 1.0], ['i2', 2.0], ['i3', 3.0]]
expected_p2 = [['i1', 1.0], ['i2', 2.0], ['i3', 3.0]]
data = connect.container["p0"].records.values.tolist()
if data != expected_p0:
raise Exception("Unexpected Data p0")
data = connect.container["p1"].records.values.tolist()
if data != expected_p1:
raise Exception("Unexpected Data p1")
data = connect.container["p2"].records.values.tolist()
if data != expected_p2:
raise Exception("Unexpected Data p1R")
$offEmbeddedCode
$log read parameter with NW:SE and garbage borders - ignoreRows
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p1
range: nwse!B2:E5
rowDimension: 1
columnDimension: 1
- name: p2
range: nwse!B9:C11
rowDimension: 1
columnDimension: 0
- name: p3
range: nwse!G8:I9
rowDimension: 0
columnDimension: 1
- name: p4
range: nwse!B14:D17
rowDimension: 2
columnDimension: 0
- name: p5
range: nwse!G15:J17
rowDimension: 3
columnDimension: 0
- name: p6
range: nwse!C21:E24
rowDimension: 0
columnDimension: 2
ignoreRows: 23
- name: p7
range: nwse!I21:K25
rowDimension: 0
columnDimension: 3
ignoreRows: 24
- name: p8
range: nwse!B28:F34
rowDimension: 2
columnDimension: 3
- PythonCode:
code: |
expected_p1 = [['i1', 'j1', 1.0], ['i1', 'j2', 2.0], ['i1', 'j3', 3.0],
['i2', 'j1', 4.0], ['i2', 'j2', 5.0], ['i2', 'j3', 6.0],
['i3', 'j1', 7.0], ['i3', 'j2', 8.0], ['i3', 'j3', 9.0]]
expected_p2 = [['i1', 1.0], ['i2', 4.0], ['i3', 7.0]]
expected_p3 = [['j1', 1.0], ['j2', 2.0], ['j3', 3.0]]
expected_p4 = [['i1', 'i1', 1.0], ['i2', 'i2', 4.0], ['i3', 'i3', 7.0]]
expected_p5 = [['i1', 'i1', 'i1', 1.0], ['i2', 'i2', 'i2', 4.0], ['i3', 'i3', 'i3', 7.0]]
expected_p6 = [['j1', 'j1', 1.0], ['j2', 'j2', 2.0], ['j3', 'j3', 3.0]]
expected_p7 = [['j1', 'j1', 'j1', 1.0], ['j2', 'j2', 'j2', 2.0], ['j3', 'j3', 'j3', 3.0]]
expected_p8 = [['i1', 'i1', 'j1', 'j1', 'j1', 1.0], ['i2', 'i2', 'j2', 'j2', 'j2', 2.0], ['i3', 'i3', 'j3', 'j3', 'j3', 3.0]]
data = connect.container["p1"].records.values.tolist()
if data != expected_p1:
raise Exception("Unexpected Data p1")
data = connect.container["p2"].records.values.tolist()
if data != expected_p2:
raise Exception("Unexpected Data p2")
data = connect.container["p3"].records.values.tolist()
if data != expected_p3:
raise Exception("Unexpected Data p3")
data = connect.container["p4"].records.values.tolist()
if data != expected_p4:
raise Exception("Unexpected Data p4")
data = connect.container["p5"].records.values.tolist()
if data != expected_p5:
raise Exception("Unexpected Data p5")
data = connect.container["p6"].records.values.tolist()
if data != expected_p6:
raise Exception("Unexpected Data p6")
data = connect.container["p7"].records.values.tolist()
if data != expected_p7:
raise Exception("Unexpected Data p7")
data = connect.container["p8"].records.values.tolist()
if data != expected_p8:
raise Exception("Unexpected Data p8")
$offEmbeddedCode
$log read parameter with some 0 dim with NW only and garbage border - ignoreColumns - ignoreRows
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p1
range: nw0cdim!B3
rowDimension: 1
columnDimension: 0
- name: p2
range: nw0rdim!C2
rowDimension: 0
columnDimension: 1
- name: p3
range: nw0cdim!F3
rowDimension: 2
columnDimension: 0
ignoreColumns: H
- name: p4
range: nw0cdim!L3
rowDimension: 3
columnDimension: 0
- name: p5
range: nw0rdim!C6
rowDimension: 0
columnDimension: 2
ignoreRows: 8
- name: p6
range: nw0rdim!C12
rowDimension: 0
columnDimension: 3
ignoreRows: 15
- PythonCode:
code: |
expected_p1 = [['i1', 1.0], ['i2', 4.0], ['i3', 7.0]]
expected_p2 = [['j1', 1.0], ['j2', 2.0], ['j3', 3.0]]
expected_p3 = [['i1', 'i1', 1.0], ['i2', 'i2', 4.0], ['i3', 'i3', 7.0]]
expected_p4 = [['i1', 'i1', 'i1', 1.0], ['i2', 'i2', 'i2', 4.0], ['i3', 'i3', 'i3', 7.0]]
expected_p5 = [['j1', 'j1', 1.0], ['j2', 'j2', 2.0], ['j3', 'j3', 3.0]]
expected_p6 = [['j1', 'j1', 'j1', 1.0], ['j2', 'j2', 'j2', 2.0], ['j3', 'j3', 'j3', 3.0]]
data = connect.container["p1"].records.values.tolist()
if data != expected_p1:
raise Exception("Unexpected Data p1")
data = connect.container["p2"].records.values.tolist()
if data != expected_p2:
raise Exception("Unexpected Data p2")
data = connect.container["p3"].records.values.tolist()
if data != expected_p3:
raise Exception("Unexpected Data p3")
data = connect.container["p4"].records.values.tolist()
if data != expected_p4:
raise Exception("Unexpected Data p4")
data = connect.container["p5"].records.values.tolist()
if data != expected_p5:
raise Exception("Unexpected Data p5")
data = connect.container["p6"].records.values.tolist()
if data != expected_p6:
raise Exception("Unexpected Data p6")
$offEmbeddedCode
$log read labels with trailing spaces
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: trailing_spaces!A1
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
expected = [
['i1', 'j6', 21.0],
['i1', 'j8', 23.0],
['i2', 'j9', 29.0],
['i3', 'j7', 32.0],
['i3', 'j10', 35.0],
['i4', 'j6', 36.0],
['i4', 'j8', 38.0]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read date columns
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: dates!A1
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
expected = [
['i1', 'j1', 43893],
['i1', 'j3', 43893],
['i2', 'j1', 43894],
['i2', 'j3', 43894],
['i3', 'j1', 43895],
['i3', 'j2', 5],
['i3', 'j3', 43895],
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read mixed columns - dates and float
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: dates!A7
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
expected = [
['i1', 'j1', 1],
['i1', 'j3', 43893],
['i2', 'j1', 43894],
['i2', 'j3', 43894],
['i3', 'j2', 5],
['i3', 'j3', 43895],
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim parameter from named range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: p
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
expected = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i2', 'j1', 6.],
['i3', 'j2', 12.],
['i4', 'j1', 16.],
['i4', 'j3', 18.],
['i4', 'j5', 20.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim parameter from named range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: p_2
rowDimension: 1
columnDimension: 2
mergedCells: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 1.],
['i1', 'j1', 'k2', 2.],
['i1', 'j2', 'k1', 3.],
['i1', 'j3', 'k2', 5.],
['i1', 'j4', 'k1', 6.],
['i1', 'j5', 'k2', 7.],
['i2', 'j1', 'k1', 8.],
['i2', 'j4', 'k1', 13.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 2-dim set from named range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: True
symbols:
- name: s
range: s
type: set
rowDimension: 0
columnDimension: 2
- PythonCode:
code: |
expected = [
['i1', 'j1', 'text 1'],
['i2', 'j1', 'text 2'],
['i2', 'j2', 'text 3']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read from index sheet
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
index: index!A1
- PythonCode:
code: |
expected_p1_to_p4 = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i2', 'j1', 6.],
['i3', 'j2', 12.],
['i4', 'j1', 16.],
['i4', 'j3', 18.],
['i4', 'j5', 20.]
]
for p in ['p1', 'p2', 'p3', 'p4']:
data = connect.container[p].records.values.tolist()
if data != expected_p1_to_p4:
raise Exception(f"Unexpected Data {p}")
expected_p5_6 = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i1', 'j6', 21.],
['i1', 'j8', 23.],
['i2', 'j1', 6.],
['i2', 'j9', 29.],
['i3', 'j2', 12.],
['i3', 'j7', 32.],
['i3', 'j10',35.],
['i4', 'j1', 16.],
['i4', 'j3', 18.],
['i4', 'j5', 20.],
['i4', 'j6', 36.],
['i4', 'j8', 38.]
]
data = connect.container['p5'].records.values.tolist()
if data != expected_p5_6:
raise Exception("Unexpected Data p5")
data = connect.container['p6'].records.values.tolist()
if data != expected_p5_6:
raise Exception("Unexpected Data p6")
expected_s1 = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j1', 'k3', '']
]
data = connect.container['s1'].records.values.tolist()
if data != expected_s1:
raise Exception("Unexpected Data s1")
expected_s2 = [
['i1', 'j1', 'k2', ''],
['i2', 'j1', 'k2', ''],
]
data = connect.container['s2'].records.values.tolist()
if data != expected_s2:
raise Exception("Unexpected Data s2")
expected_s3 = [
['i1', 'j1', 'k2', '2'],
['i1', 'j2', 'k2', '4'],
['i2', 'j2', 'k2', '8']
]
data = connect.container['s3'].records.values.tolist()
if data != expected_s3:
raise Exception("Unexpected Data s3")
expected_s4 = [
['i1', 'j2', 'k1', '3'],
['i1', 'j5', 'k2', '7']
]
data = connect.container['s4'].records.values.tolist()
if data != expected_s4:
raise Exception("Unexpected Data s4")
expected_s5 = [
['i1', 'j2', 'k1', '3'],
['i1', 'j5', 'k2', '7']
]
data = connect.container['s5'].records.values.tolist()
if data != expected_s5:
raise Exception("Unexpected Data s5")
expected_s6 = [
['i1', 'j2', 'k1', '3'],
['i1', 'j5', 'k2', '7']
]
data = connect.container['s6'].records.values.tolist()
if data != expected_s6:
raise Exception("Unexpected Data s6")
$offEmbeddedCode
$log read 2-dim set with ignoreText=True
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: True
symbols:
- name: s
range: multi3!A2:E5
rowDimension: 0
columnDimension: 2
type: set
ignoreText: True
- PythonCode:
code: |
expected = [
['i1', 'j1', ''],
['i1', 'j2', ''],
['i2', 'j1', ''],
['i2', 'j2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read 3-dim set with ignoreText=True
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
mergedCells: True
symbols:
- name: s
range: multi2!B2
rowDimension: 1
columnDimension: 2
type: set
ignoreText: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j2', 'k1', ''],
['i1', 'j3', 'k1', ''],
['i1', 'j3', 'k2', ''],
['i1', 'j4', 'k1', ''],
['i1', 'j5', 'k2', ''],
['i2', 'j1', 'k1', ''],
['i2', 'j1', 'k2', ''],
['i2', 'j2', 'k1', ''],
['i2', 'j3', 'k1', ''],
['i2', 'j3', 'k2', ''],
['i2', 'j4', 'k1', ''],
['i2', 'j5', 'k2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read empty 3-dim set with ignoreText=True and explicit range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: empty!A1:C4
rowDimension: 1
columnDimension: 2
type: set
ignoreText: True
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i2', 'j1', 'k1', ''],
['i2', 'j1', 'k2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with cdim=0, ignoreText=True and range that does not contain expl text
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: rdim_only!B3:D5
rowDimension: 3
columnDimension: 0
type: set
ignoreText: True
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j1', 'k3', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with rdim=0, ignoreText=True and range that does not contain expl text
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: cdim_only!C2:E4
rowDimension: 0
columnDimension: 3
type: set
ignoreText: True
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j1', 'k3', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with rdim=0, ignoreText=auto and range that does contain expl text
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
ignoreText: True
symbols:
- name: s
range: cdim_only!C2:I5
rowDimension: 0
columnDimension: 3
type: set
ignoreText: auto
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', '0.1'],
['i1', 'j1', 'k3', '0.3'],
['i2', 'j2', 'k3', '0.6']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with rdim=0, ignoreText=auto and range that does not contain expl text
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
ignoreText: False
symbols:
- name: s
range: cdim_only!C2:I4
rowDimension: 0
columnDimension: 3
type: set
ignoreText: auto
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j1', 'k3', ''],
['i2', 'j2', 'k1', ''],
['i2', 'j2', 'k2', ''],
['i2', 'j2', 'k3', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with rdim=0, ignoreText=auto and nw corner
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: cdim_only!C2
rowDimension: 0
columnDimension: 3
type: set
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j1', 'k3', ''],
['i2', 'j2', 'k1', ''],
['i2', 'j2', 'k2', ''],
['i2', 'j2', 'k3', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with cdim=0, ignoreText=auto and range that does contain expl text
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: rdim_only!B3:E5
rowDimension: 3
columnDimension: 0
type: set
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', '0.1'],
['i1', 'j1', 'k3', '0.3']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with cdim=0, ignoreText=auto and range that does not contain expl text
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: rdim_only!B3:D5
rowDimension: 3
columnDimension: 0
type: set
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j1', 'k3', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with cdim=0, ignoreText=auto and nw corner
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: rdim_only!B3
rowDimension: 3
columnDimension: 0
type: set
autoMerge: True
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', ''],
['i1', 'j1', 'k2', ''],
['i1', 'j1', 'k3', ''],
['i1', 'j2', 'k1', ''],
['i1', 'j2', 'k2', ''],
['i1', 'j2', 'k3', ''],
['i2', 'j1', 'k1', ''],
['i2', 'j1', 'k2', ''],
['i2', 'j1', 'k3', ''],
['i2', 'j2', 'k1', ''],
['i2', 'j2', 'k2', ''],
['i2', 'j2', 'k3', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with rdim!=0 and cdim!=0, ignoreText=auto and nw corner
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
mergedCells: True
symbols:
- name: s
range: multi2!B2
rowDimension: 1
columnDimension: 2
type: set
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', '1'],
['i1', 'j1', 'k2', '2'],
['i1', 'j2', 'k1', '3'],
['i1', 'j3', 'k2', '5'],
['i1', 'j4', 'k1', '6'],
['i1', 'j5', 'k2', '7'],
['i2', 'j1', 'k1', '8'],
['i2', 'j4', 'k1', '13']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read set with rdim!=0 and cdim!=0, ignoreText=auto and full range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
mergedCells: True
symbols:
- name: s
range: multi2!B2:D6
rowDimension: 1
columnDimension: 2
type: set
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', '1'],
['i1', 'j1', 'k2', '2'],
['i2', 'j1', 'k1', '8']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read with ignoreColumns, mergedCells=True and autoMerge=True
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
mergedCells: True
symbols:
- name: s
range: multi2!B2
columnDimension: 2
ignoreText: True
ignoreColumns: [C, 5, 6, H, I]
type: set
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k2', ''],
['i1', 'j3', 'k2', ''],
['i2', 'j1', 'k2', ''],
['i2', 'j3', 'k2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log read with ignoreRows, mergedCells=True and autoMerge=True
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
mergedCells: True
symbols:
- name: s
range: multi3!B9
rowDimension: 2
ignoreText: True
ignoreRows: [10, 13]
type: set
- PythonCode:
code: |
expected = [
['i1', 'j2', 'k1', ''],
['i1', 'j2', 'k2', ''],
['i2', 'j1', 'k1', ''],
['i2', 'j1', 'k2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test skipEmpty being ignored for full range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B2:N6
rowDimension: 1
columnDimension: 1
skipEmpty: 1
- PythonCode:
code: |
expected = [
['i1', 'j1', 1.],
['i1', 'j2', 2.],
['i1', 'j3', 3.],
['i1', 'j4', 4.],
['i1', 'j5', 5.],
['i1', 'j6', 21.],
['i1', 'j8', 23.],
['i2', 'j1', 6.],
['i2', 'j9', 29.],
['i3', 'j2', 12.],
['i3', 'j7', 32.],
['i3', 'j10',35.],
['i4', 'j1', 16.],
['i4', 'j3', 18.],
['i4', 'j5', 20.],
['i4', 'j6', 36.],
['i4', 'j8', 38.]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test default range
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
trace: 4
symbols:
- name: int_labels
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
expected = [
['1', '5', 1.],
['1', '6', 2.],
['2', '6', 5.],
['2', '7', 6.],
['3', '6', 8.],
['3', '7', 9.]
]
data = connect.container["int_labels"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test special values
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: sv!A1
rowDimension: 2
columnDimension: 1
- PythonCode:
code: |
import gams.transfer as gt
expected = [
['i1', 'j1', 'k1', gt.SpecialValues.UNDEF],
['i1', 'j1', 'k2', gt.SpecialValues.NA],
['i1', 'j1', 'k3', gt.SpecialValues.POSINF],
['i1', 'j2', 'k1', gt.SpecialValues.NEGINF],
['i1', 'j2', 'k2', gt.SpecialValues.EPS],
['i1', 'j2', 'k3', 6.0],
]
data = connect.container["p"].records.values.tolist()
if data[2:] != expected[2:]:
raise Exception("Unexpected Data")
if not gt.SpecialValues.isUndef(data[0][-1]): # check for UNDEF
raise Exception("Unexpected Data: UNDEF")
if not gt.SpecialValues.isNA(data[1][-1]): # check for NA
raise Exception("Unexpected Data: NA")
$offEmbeddedCode
$log test special values with valueSubstitutions
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi!B2:G4
rowDimension: 1
columnDimension: 1
valueSubstitutions: {
1: NA,
2: INF,
3: -INF,
4: EPS,
5: UNDEF
}
- PythonCode:
code: |
import gams.transfer as gt
expected = [
['i1', 'j1', gt.SpecialValues.NA],
['i1', 'j2', gt.SpecialValues.POSINF],
['i1', 'j3', gt.SpecialValues.NEGINF],
['i1', 'j4', gt.SpecialValues.EPS],
['i1', 'j5', gt.SpecialValues.UNDEF],
['i2', 'j1', 6.0],
]
data = connect.container["p"].records.values.tolist()
if data[1:-2] != expected[1:-2] or data[-1] != expected[-1]:
raise Exception("Unexpected Data")
if not gt.SpecialValues.isNA(data[0][-1]): # check for NA
raise Exception("Unexpected Data: NA")
if not gt.SpecialValues.isUndef(data[4][-1]): # check for Undef
raise Exception("Unexpected Data: Undef")
$offEmbeddedCode
$log test special values with valueSubstitutions
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: sv!A1
rowDimension: 2
columnDimension: 1
valueSubstitutions: {
UNDEF: NA,
NA: INF,
INF: -INF,
-INF: EPS,
EPS: UNDEF
}
- PythonCode:
code: |
import gams.transfer as gt
expected = [
['i1', 'j1', 'k1', gt.SpecialValues.NA],
['i1', 'j1', 'k2', gt.SpecialValues.POSINF],
['i1', 'j1', 'k3', gt.SpecialValues.NEGINF],
['i1', 'j2', 'k1', gt.SpecialValues.EPS],
['i1', 'j2', 'k2', gt.SpecialValues.UNDEF],
['i1', 'j2', 'k3', 6.0],
]
data = connect.container["p"].records.values.tolist()
if data[1:-2] != expected[1:-2] or data[-1] != expected[-1]:
raise Exception("Unexpected Data")
if not gt.SpecialValues.isNA(data[0][-1]): # check for NA
raise Exception("Unexpected Data: NA")
if not gt.SpecialValues.isUndef(data[4][-1]): # check for Undef
raise Exception("Unexpected Data: Undef")
$offEmbeddedCode
$log test indexSubstitutions nan->""
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: False
symbols:
- name: p
range: multi2!B2:F6
rowDimension: 1
columnDimension: 2
indexSubstitutions: {.nan: ""}
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 1],
['i1', '' , 'k2', 2],
['i1', 'j2', 'k1', 3],
['i2', 'j1', 'k1', 8],
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test indexSubstitutions with multiple substitutions
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: False
symbols:
- name: p
range: multi2!B2:F6
rowDimension: 1
columnDimension: 2
indexSubstitutions: {.nan: "X0", "i1": "X1", "i3":"X3", "k2": "X2", "X2": "X3"}
- PythonCode:
code: |
expected = [
['X1', 'j1', 'k1', 1],
['X1', 'X0', 'X2', 2],
['X1', 'j2', 'k1', 3],
['i2', 'j1', 'k1', 8],
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test indexSubstitutions with multiple substitutions on sets
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: False
ignoreText: True
symbols:
- name: s
type: set
range: rdim_only!B3:E7
rowDimension: 3
columnDimension: 0
indexSubstitutions: {.nan: "X0", "i1": "X1"}
- PythonCode:
code: |
expected = [
['X1', 'j1', 'k1', ''],
['X0', 'X0', 'k2', ''],
['X0', 'X0', 'k3', ''],
['X0', 'j2', 'k1', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test indexSubstitutions with PythonCode
$onEmbeddedCode Connect:
- PythonCode:
code: |
symbols = [ 'p0', 'p1', 'p2' ]
for s in symbols:
instructions.append(
{
'ExcelReader':
{
'file': 'caxlsr.xlsx',
'autoMerge': False,
'ignoreText': True,
'symbols': [{
'name': 's',
'type': 'set',
'range': 'rdim_only!B3:E7',
'rowDimension': 3,
'columnDimension': 0,
'indexSubstitutions': {None: "X0", "i1": "X1"}
}]
}
})
- PythonCode:
code: |
expected = [
['X1', 'j1', 'k1', ''],
['X0', 'X0', 'k2', ''],
['X0', 'X0', 'k3', ''],
['X0', 'j2', 'k1', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test mergedCells=True and autoMerge=False
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: False
mergedCells: True
symbols:
- name: p
range: multi2!L2
rowDimension: 1
columnDimension: 2
- PythonCode:
code: |
expected = [
['i1', 'j6', 'k1', 15],
['i1', 'j6', 'k2', 16],
['i1', 'j6', 'k3', 17],
['i2', 'j6', 'k1', 18]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test mergedCells=False (default) and autoMerge=False
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: False
symbols:
- name: p
range: multi2!L2
rowDimension: 1
columnDimension: 2
- PythonCode:
code: |
expected = [
['i1', 'j6', 'k1', 15],
['i2', 'j6', 'k1', 18]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test mergedCells=False (default) and autoMerge=False
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: False
symbols:
- name: p
range: multi3!B9
rowDimension: 2
columnDimension: 1
- PythonCode:
code: |
expected = [
['i1', 'j1', 'k1', 1],
['i1', 'j1', 'k2', 2],
['i2', 'j1', 'k1', 5],
['i2', 'j1', 'k2', 6]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test mergedCells=True and autoMerge=False with ignoreRows and ignoreColumns
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: multi3!B9
rowDimension: 2
columnDimension: 1
autoMerge: False
mergedCells: True
ignoreRows: [10,12]
ignoreColumns: E
- PythonCode:
code: |
expected = [
['i1', 'j2', 'k1', 3],
['i2', 'j2', 'k1', 7]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test ignoreRows, ignoreColumns not altering data if not in range
$onEmbeddedCode Connect:
# 1. read without ignoreRows, ignoreColumns
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p1
range: multi!B2
rowDimension: 1
columnDimension: 1
# 2. read with ignoreRows, ignoreColumns not part of data range using nw croner
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p2
ignoreColumns: [A,P]
ignoreRows: [1,7]
range: multi!B2
rowDimension: 1
columnDimension: 1
#3. read with ignoreRows, ignoreColumns not part of data range using full range
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p3
ignoreColumns: [A,P]
ignoreRows: [1,7]
range: multi!B2:G7
rowDimension: 1
columnDimension: 1
- PythonCode:
code: |
data_p1 = connect.container["p1"].records.values.tolist()
data_p2 = connect.container["p2"].records.values.tolist()
data_p3 = connect.container["p3"].records.values.tolist()
if not data_p1 == data_p2 == data_p3:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test mixed labels data type for the same label for sets
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
type: set
range: mixed!A1
rowDimension: 2
columnDimension: 2
ignoreText: True
- PythonCode:
code: |
expected = [
['i1', '1', 'j1', '2', ''],
['i1', '1', 'j2', '2', ''],
['i2', '1', 'j1', '2', ''],
['i2', '1', 'j2', '2', ''],
['i3', '1', 'j1', '2', ''],
['i3', '1', 'j2', '2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test mixed labels data type for the same label for parameters
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: mixed!A1
rowDimension: 2
columnDimension: 2
ignoreText: True
- PythonCode:
code: |
expected = [
['i1', '1', 'j1', '2', 1],
['i1', '1', 'j2', '2', 2],
['i2', '1', 'j1', '2', 3],
['i2', '1', 'j2', '2', 4],
['i3', '1', 'j1', '2', 5],
['i3', '1', 'j2', '2', 6]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
$log test range ending with "!" being resolve to "!A1"
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: mixed!
rowDimension: 2
columnDimension: 2
ignoreText: True
- PythonCode:
code: |
expected = [
['i1', '1', 'j1', '2', 1],
['i1', '1', 'j2', '2', 2],
['i2', '1', 'j1', '2', 3],
['i2', '1', 'j2', '2', 4],
['i3', '1', 'j1', '2', 5],
['i3', '1', 'j2', '2', 6]
]
data = connect.container["p"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode
* test reading duplicate records
embeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p1
range: duplicates!A1
rowDimension: 1
columnDimension: 1
- name: p2
range: duplicates!A6
rowDimension: 2
columnDimension: 1
- name: p3
range: duplicates!A11
rowDimension: 2
columnDimension: 2
- name: p4
range: duplicates!A17
rowDimension: 2
columnDimension: 2
- name: p5
range: duplicates!A23
rowDimension: 2
columnDimension: 2
- name: s1
range: duplicates!A17
type: set
rowDimension: 2
columnDimension: 2
- PythonCode:
code: |
expected_p1 = [
['i1', 'j1', 1],
['i1', 'j1', 2],
['i1', 'j1', 3],
['i1', 'j1', 4]
]
expected_p2 = [
['i1', 'j1', 'k1', 1],
['i1', 'j1', 'k1', 2],
['i1', 'j1', 'k1', 3],
['i1', 'j1', 'k1', 4]
]
expected_p3 = [
['i1', 'j1', 'k1', 'l1', 1],
['i1', 'j1', 'k1', 'l1', 2],
['i1', 'j1', 'k1', 'l1', 3],
['i1', 'j1', 'k1', 'l1', 4]
]
expected_p4 = [
['i1', 'i1', 'i1', 'i1', 1],
['i1', 'i1', 'i1', 'i1', 2],
['i1', 'i1', 'i1', 'i1', 3],
['i1', 'i1', 'i1', 'i1', 4]
]
expected_p5 = [
['1', '1', '1', '1', 1],
['1', '1', '1', '1', 2],
['1', '1', '1', '1', 3],
['1', '1', '1', '1', 4]
]
expected_s1 = [
['i1', 'i1', 'i1', 'i1', '1'],
['i1', 'i1', 'i1', 'i1', '2'],
['i1', 'i1', 'i1', 'i1', '3'],
['i1', 'i1', 'i1', 'i1', '4']
]
data_p1 = connect.container["p1"].records.values.tolist()
data_p2 = connect.container["p2"].records.values.tolist()
data_p3 = connect.container["p3"].records.values.tolist()
data_p4 = connect.container["p4"].records.values.tolist()
data_p5 = connect.container["p5"].records.values.tolist()
data_s1 = connect.container["s1"].records.values.tolist()
if expected_p1 != data_p1:
raise Exception("Unexpected data p1")
if expected_p2 != data_p2:
raise Exception("Unexpected data p2")
if expected_p3 != data_p3:
raise Exception("Unexpected data p3")
if expected_p4 != data_p4:
raise Exception("Unexpected data p4")
if expected_p5 != data_p5:
raise Exception("Unexpected data p5")
if expected_s1 != data_s1:
raise Exception("Unexpected data s1")
endEmbeddedCode
$log read from empty sheet without failing
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty_sheet!Z100
rowDimension: 10
columnDimension: 10
- PythonCode:
code: |
data = connect.container["p"].records
if data is not None:
raise Exception("Unexpected data")
$offEmbeddedCode
$log read from empty sheet using full range without failing
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty_sheet!D10:N20
rowDimension: 10
columnDimension: 10
- PythonCode:
code: |
data = connect.container["p"].records
if data is not None:
raise Exception("Unexpected data")
$offEmbeddedCode
$log read from empty sheet with ignoreRows without failing
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty_sheet!A1
ignoreRows: [1,2]
rowDimension: 10
columnDimension: 10
- PythonCode:
code: |
data = connect.container["p"].records
if data is not None:
raise Exception("Unexpected data")
$offEmbeddedCode
$log read more data than exists using full range without failing
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: s
range: empty!A1:K11
rowDimension: 10
columnDimension: 10
type: set
- PythonCode:
code: |
data = connect.container["s"].records
if data is not None:
raise Exception("Unexpected data")
$offEmbeddedCode
$log read more data than exists with ignoreRows without failing
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty!A1
ignoreRows: [1,2]
rowDimension: 10
columnDimension: 10
- PythonCode:
code: |
data = connect.container["p"].records
if data is not None:
raise Exception("Unexpected data")
$offEmbeddedCode
$log read more data than exists using ignoreRows and ignoreColumns
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
symbols:
- name: p
range: empty!A1:J16
ignoreRows: [2,3,4,5,6,7,8,9,10,13,20]
ignoreColumns: [3,4,5,6]
rowDimension: 5
columnDimension: 5
- PythonCode:
code: |
data = connect.container["p"].records
if data is not None:
raise Exception("Unexpected data")
$offEmbeddedCode
$log read 2-dim set with ignoreText being set to True automatically if ignoreRows makes range insufficient to contain text
$onEmbeddedCode Connect:
- ExcelReader:
file: caxlsr.xlsx
autoMerge: True
symbols:
- name: s
range: multi3!A2:E4
rowDimension: 0
columnDimension: 2
ignoreRows: 4
type: set
- PythonCode:
code: |
expected = [
['i1', 'j1', ''],
['i1', 'j2', ''],
['i2', 'j1', ''],
['i2', 'j2', '']
]
data = connect.container["s"].records.values.tolist()
if data != expected:
raise Exception("Unexpected Data")
$offEmbeddedCode