$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 try to read with type: dSet - expect error $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx symbols: - name: s range: cdim_only!C2:E4 rowDimension: 0 columnDimension: 3 type: dSet ignoreText: False $offEmbeddedCode $if errorfree $abort 'Expect failure for type dSet' $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 from named range to test for case-insensitive behaviour $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 string->nan $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx autoMerge: True symbols: - name: p range: multi2!B2:F6 rowDimension: 1 columnDimension: 2 indexSubstitutions: {k1: .nan} - PythonCode: code: | expected = [ ['i1', 'j1', 'k2', 2], ] data = connect.container["p"].records.values.tolist() if data != expected: raise Exception("Unexpected Data") $offEmbeddedCode $log test indexSubstitutions string->null $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx autoMerge: True symbols: - name: p range: multi2!B2:F6 rowDimension: 1 columnDimension: 2 indexSubstitutions: {k1: null} - PythonCode: code: | expected = [ ['i1', 'j1', 'k2', 2], ] 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 $log read with ignoreText=False and columnDimension=0 (sheet boundary) $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx symbols: - name: s range: s_rdim!A1 type: set columnDimension: 0 rowDimension: 2 ignoreText: False - PythonCode: code: | expected = [] data = connect.container["s"].records.values.tolist() if data != expected: raise Exception("Unexpected Data") $offEmbeddedCode $log read with ignoreText=False and columnDimension=0 and valueSubstitutions (sheet boundary) $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx symbols: - name: s range: s_rdim!A1 type: set columnDimension: 0 rowDimension: 2 ignoreText: False valueSubstitutions: {.nan: ""} - PythonCode: code: | expected = [ ['i1', 'j1', ''], ['i1', 'j2', ''], ] data = connect.container["s"].records.values.tolist() if data != expected: raise Exception("Unexpected Data") $offEmbeddedCode $log read with ignoreText=False and rowDimension=0 (sheet boundary) $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx symbols: - name: s range: s_cdim!A1 type: set columnDimension: 2 rowDimension: 0 ignoreText: False - PythonCode: code: | expected = [] data = connect.container["s"].records.values.tolist() if data != expected: raise Exception("Unexpected Data") $offEmbeddedCode $log read with ignoreText=False and rowDimension=0 and valueSubstitutions (sheet boundary) $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx symbols: - name: s range: s_cdim!A1 type: set columnDimension: 2 rowDimension: 0 ignoreText: False valueSubstitutions: {.nan: ""} - PythonCode: code: | expected = [ ['i1', 'j1', ''], ['i1', 'j2', ''], ] data = connect.container["s"].records.values.tolist() if data != expected: raise Exception("Unexpected Data") $offEmbeddedCode $log read empty symbol with rowDimension: 2 without error $onEmbeddedCode Connect: - ExcelReader: file: caxlsr.xlsx symbols: - name: s range: rdim_only!AQ10 type: set rowDimension: 2 - PythonCode: code: | expected = [] data = connect.container["s"].records.values.tolist() if data != expected: raise Exception("Unexpected Data") $offEmbeddedCode