caxlsr.gms : Test Connect agent ExcelReader

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 named range with invalid reference
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: p
        range: brokenRange
$offEmbeddedCode
$if errorfree $abort 'Expect failure for named range with invalid reference'
$clearErrors

$log test invalid ignoreColumns range format  (using non integer)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi2!B2
        ignoreColumns: [C, "3.5 : 7" ,  I]
$offEmbeddedCode
$if errorfree $abort 'Expect failure for range ignoreColumns with invalid format'
$clearErrors

$log test invalid ignoreColumns range format  (using more than one colon)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi2!B2
        ignoreColumns: "3 : 7 : 11"
$offEmbeddedCode
$if errorfree $abort 'Expect failure for range ignoreColumns with invalid format'
$clearErrors

$log test invalid ignoreRows range format  (first element greater then second one)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi!B2
        ignoreRows: [3, "5:3"]
$offEmbeddedCode
$if errorfree $abort 'Expect failure for range ignoreRows with invalid format'
$clearErrors

$log test invalid ignoreRows range format  (using non integer)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi!B2
        ignoreRows: "3.5 : E"
$offEmbeddedCode
$if errorfree $abort 'Expect failure for range ignoreRows with invalid format'
$clearErrors

$log test invalid ignoreRows range format  (using more than one colon)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi2!B2
        ignoreRows: "3 : 4 : 6"
$offEmbeddedCode
$if errorfree $abort 'Expect failure for range ignoreRows with invalid format'
$clearErrors

$log test using a name that already exists in the database
$onEmbeddedCode Connect:
- PythonCode:
    code: |
      connect.container.addParameter("a")
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: a
        range: scalar!A1
        rowDimension: 0
        columnDimension: 0
$offEmbeddedCode
$if errorfree $abort 'Expect failure due to using a name that already exists'
$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: |
      data = connect.container["p"].records
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'value']

      if data is None or not data.empty:
        raise Exception("Expected >p< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >p<.")
$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: |
      data = connect.container["p"].records
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'value']

      if data is None or not data.empty:
        raise Exception("Expected >p< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >p<.")
$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
    columnDimension: 0
    rowDimension: 0
- 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_p7_8 = [
        ['i1', 'j4', 4.0],
        ['i1', 'j5', 5.0],
        ['i4', 'j5', 20.0]
      ]
      
      data = connect.container['p7'].records.values.tolist()
      if data != expected_p7_8:
        raise Exception("Unexpected Data p7")

      data = connect.container['p8'].records.values.tolist()
      if data != expected_p7_8:
        raise Exception("Unexpected Data p8")
        
      expected_p9_10 = [
        ['i4', 'j5', 20.0],
        ['i4', 'j6', 36.0],
        ['i4', 'j8', 38.0]
      ]
      
      data = connect.container['p9'].records.values.tolist()
      if data != expected_p9_10:
        raise Exception("Unexpected Data p9")

      data = connect.container['p10'].records.values.tolist()
      if data != expected_p9_10:
        raise Exception("Unexpected Data p10")

      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_7 = [
        ['i1', 'j2', 'k1', '3'],
        ['i1', 'j5', 'k2', '7']
      ]
      data = connect.container['s5'].records.values.tolist()
      if data != expected_s5_7:
        raise Exception("Unexpected Data s5")

      expected_s6_8 = [
        ['i1', 'j2', 'k1', '3'],
        ['i1', 'j5', 'k2', '7']
      ]
      data = connect.container['s6'].records.values.tolist()
      if data != expected_s6_8:
        raise Exception("Unexpected Data s6")
        
      data = connect.container['s7'].records.values.tolist()
      if data != expected_s5_7:
        raise Exception("Unexpected Data s7")
        
      data = connect.container['s8'].records.values.tolist()
      if data != expected_s6_8:
        raise Exception("Unexpected Data s8")
$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=infer 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: infer
        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=infer 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: infer
        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=null 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=null 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=null 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=null 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=null 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=null 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: |
      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
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'uni_3', 'uni_4', 'uni_5', 'uni_6', 'uni_7',
       'uni_8', 'uni_9', 'uni_10', 'uni_11', 'uni_12', 'uni_13', 'uni_14',
       'uni_15', 'uni_16', 'uni_17', 'uni_18', 'uni_19', 'value']
      

      if data is None or not data.empty:
        raise Exception("Expected >p< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >p<.")
$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
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'uni_3', 'uni_4', 'uni_5', 'uni_6', 'uni_7',
       'uni_8', 'uni_9', 'uni_10', 'uni_11', 'uni_12', 'uni_13', 'uni_14',
       'uni_15', 'uni_16', 'uni_17', 'uni_18', 'uni_19', 'value']
      

      if data is None or not data.empty:
        raise Exception("Expected >p< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >p<.")
$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
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'uni_3', 'uni_4', 'uni_5', 'uni_6', 'uni_7',
       'uni_8', 'uni_9', 'uni_10', 'uni_11', 'uni_12', 'uni_13', 'uni_14',
       'uni_15', 'uni_16', 'uni_17', 'uni_18', 'uni_19', 'value']
      

      if data is None or not data.empty:
        raise Exception("Expected >p< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >p<.")
$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
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'uni_3', 'uni_4', 'uni_5', 'uni_6', 'uni_7',
       'uni_8', 'uni_9', 'uni_10', 'uni_11', 'uni_12', 'uni_13', 'uni_14',
       'uni_15', 'uni_16', 'uni_17', 'uni_18', 'uni_19', 'element_text']
      

      if data is None or not data.empty:
        raise Exception("Expected >s< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >s<.")
$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
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'uni_3', 'uni_4', 'uni_5', 'uni_6', 'uni_7',
       'uni_8', 'uni_9', 'uni_10', 'uni_11', 'uni_12', 'uni_13', 'uni_14',
       'uni_15', 'uni_16', 'uni_17', 'uni_18', 'uni_19', 'value']
      

      if data is None or not data.empty:
        raise Exception("Expected >p< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >p<.")
$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
      expected_cols = ['uni_0', 'uni_1', 'uni_2', 'uni_3', 'uni_4', 'uni_5', 'uni_6', 'uni_7',
       'uni_8', 'uni_9', 'value']
      

      if data is None or not data.empty:
        raise Exception("Expected >p< to have an empty DataFrame.")

      if (data.columns != expected_cols).any():
        raise Exception("Unexpected columns for >p<.")
$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

$log read with ignoreRows as a range
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi!B2
        ignoreRows: "3:5"
- PythonCode:
    code: |
      expected = [
        ['i4', 'j1', 16.0],
        ['i4', 'j3', 18.0],
        ['i4', 'j5', 20.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreRows as a list including a range
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi!B2
        ignoreRows: [3, " 3  : 4   ", 5]
- PythonCode:
    code: |
      expected = [
        ['i4', 'j1', 16.0],
        ['i4', 'j3', 18.0],
        ['i4', 'j5', 20.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreColumns as a range (letter:letter)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi2!B2
        columnDimension: 2
        ignoreColumns: "C : G"
- PythonCode:
    code: |
      expected = [
        ['i1', 'j4', 'k1', 6.0],
        ['i1', 'j5', 'k2', 7.0],
        ['i2', 'j4', 'k1', 13.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreColumns as a range (number:number)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi2!B2
        columnDimension: 2
        ignoreColumns: "3:7"
- PythonCode:
    code: |
      expected = [
        ['i1', 'j4', 'k1', 6.0],
        ['i1', 'j5', 'k2', 7.0],
        ['i2', 'j4', 'k1', 13.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreColumns as a range (letter:number)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi2!B2
        columnDimension: 2
        ignoreColumns: "C :7"
- PythonCode:
    code: |
      expected = [
        ['i1', 'j4', 'k1', 6.0],
        ['i1', 'j5', 'k2', 7.0],
        ['i2', 'j4', 'k1', 13.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreColumns as a range (number:letter)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    symbols:
      - name: s
        range: multi2!B2
        columnDimension: 2
        ignoreColumns: "3: G"
- PythonCode:
    code: |
      expected = [
        ['i1', 'j4', 'k1', 6.0],
        ['i1', 'j5', 'k2', 7.0],
        ['i2', 'j4', 'k1', 13.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreColumns as a list including a range (number:letter)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    mergedCells: True
    symbols:
      - name: s
        range: multi2!B2
        columnDimension: 2
        ignoreColumns: [C, "5 : G" ,  I]
- PythonCode:
    code: |
      expected = [
        ['i1', 'j1', 'k2', 2.0],
        ['i1', 'j4', 'k1', 6.0],
        ['i2', 'j4', 'k1', 13.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreColumns as a list including a range (letter:letter)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    mergedCells: True
    symbols:
      - name: s
        range: multi2!B2
        columnDimension: 2
        ignoreColumns: [C, "E : G" ,  I]
- PythonCode:
    code: |
      expected = [
        ['i1', 'j1', 'k2', 2.0],
        ['i1', 'j4', 'k1', 6.0],
        ['i2', 'j4', 'k1', 13.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode

$log read with ignoreColumns as a list including a range (number:number)
$onEmbeddedCode Connect:
- ExcelReader:
    file: caxlsr.xlsx
    mergedCells: True
    symbols:
      - name: s
        range: multi2!B2
        columnDimension: 2
        ignoreColumns: [C, "5 : 7" ,  I]
- PythonCode:
    code: |
      expected = [
        ['i1', 'j1', 'k2', 2.0],
        ['i1', 'j4', 'k1', 6.0],
        ['i2', 'j4', 'k1', 13.0]
      ]
      data = connect.container["s"].records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data")
$offEmbeddedCode