$title 'Test Connect agents ExcelReader and ExcelWriter' (CAXLSRW,SEQ=951)

$onText
This test performs the basic read and write operations
of the Connect agents ExcelReader and ExcelWriter.

Contributor: Clemens Westphal, January 2024
$offText


$log --- Using Python library %sysEnv.GMSPYTHONLIB%


set i / i4, i3, i2, i1 /;
set j / j1*j4 /;
set k / k4, k3, k2, k1 /;
set l / l1*l4 /;
set m / m1*m1048576 /;
set n / n1*n16381 /;

set s1(i) / i1, i3 "line 3" /;
set s4(i,j,k,l) /
#i.#j.#k.l1 "line 1"
#i.#j.#k.l2
#i.#j.#k.l3 "line 3"
#i.#j.#k.l4 "line 4"
/;

s4(i,j,k,l)$(uniform(0,1)>0.1) = no;

parameter p0 / 3.14 /;
parameter p1(i);
parameter p4(i,j,k,l);
parameter p5(m,i);
parameter p6(i,n);

p1(i)$(uniform(0,1)<0.5) = uniform(0,1);
p4(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
p5(m,i) = uniform(0,1);
p6(i,n) = uniform(0,1);

variable v0 / l 3.14 /;
variable v1(i);
variable v4(i,j,k,l);

v1.l(i)$(uniform(0,1)<0.5) = uniform(0,1);
v1.m(i)$(uniform(0,1)<0.5) = uniform(0,1);
v4.l(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);
v4.m(i,j,k,l)$(uniform(0,1)<0.1) = uniform(0,1);

* ExcelWriter
embeddedCode Connect:
- GAMSReader:
    symbols: all
      
- PythonCode:
    code: |
      import os
      for merged_cells in [True, False]:
        
        symbols = \
        [
          ('p0', 'p0_{0}{1}.xlsx'),
          ('p1', 'p1_{0}{1}.xlsx'),
          ('p4', 'p4_{0}{1}.xlsx'),
          ('s1', 's1_{0}{1}.xlsx'),
          ('s4', 's4_{0}{1}.xlsx'),
        ]
        for s,f in symbols:
          for cdim in range(0, self._cdb.container.data[s].dimension+1):
            file = f.format(cdim, "_mc" if merged_cells else "")
            if os.path.isfile(file):
              os.remove(file)
            instructions.append( \
            {
              'ExcelWriter':
              {
                'file': file,
                'mergedCells': merged_cells,
                'symbols': [{'name': s, 'columnDimension': cdim, 'range': s+'!B3'}]
              }
            })
          
- Projection:
    name: v0.all
    newName: pv0
- Projection:
    name: v1.all(i)
    newName: pv1(i)
- Projection:
    name: v4.all(i,j,k,l)
    newName: pv4(i,j,k,l)
- Projection:
    name: v0.lo
    newName: v0_lower
- Projection:
    name: v1.m(i)
    newName: v1_marginal(i)
- Projection:
    name: v4.l(i,j,k,l)
    newName: v4_level(i,j,k,l)
    
- PythonCode:
    code: |
      import os
      symbols = \
      [
        ('pv0', 'v0_{0}.xlsx'),
        ('pv1', 'v1_{0}.xlsx'),
        ('pv4', 'v4_{0}.xlsx'),
        ('v0_lower',    'vs0_{0}.xlsx'),
        ('v1_marginal', 'vs1_{0}.xlsx'),        
        ('v4_level',    'vs4_{0}.xlsx'),
      ]
      
      for s,f in symbols:
        for cdim in range(0, self._cdb.container.data[s].dimension+1):
          if os.path.isfile(f.format(cdim)):
            os.remove(f.format(cdim))
          instructions.append( \
          {
            'ExcelWriter':
            {
              'file': f.format(cdim),
              'symbols': [{'name': s, 'columnDimension': cdim, 'range': s+'!B3'}]
            }
          })
endEmbeddedCode


* ExcelReader
$onEchoV > read.gms
set i, j, k, l, m, n, o, q, r, t;
set s4(n<,o<,q<,r<)
set s1(t<);
scalar p0;
parameter p1(m<);
parameter p4(i<,j<,k<,l<);
$if not set vsK $set vsK "''"
$if not set vsV $set vsV "''"
$onEmbeddedCode Connect:
- ExcelReader:
    file: %sym%_%cdim%.xlsx
    ignoreText: False
    symbols:
      - name: %sym%
        type: %type%
        rowDimension: %rdim%
        columnDimension: %cdim%
        range: %sym%!B3
        valueSubstitutions: { %vsK%: %vsV% }

- GAMSWriter:
    symbols:
      - name: %sym%
$offEmbeddedCode
$offEcho

execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=4 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=3 --rdim=1 --vsK='Y'  gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=2 --rdim=2 --vsK='Y'  gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=1 --rdim=3 --vsK='Y'  gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s4 --type=set --cdim=0 --rdim=4 --vsK=.nan gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s1 --type=set --cdim=1 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=s1 --type=set --cdim=0 --rdim=1 --vsK=.nan gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=4 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=3 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=2 --rdim=2 gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=1 --rdim=3 gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p4 --type=par --cdim=0 --rdim=4 gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p1 --type=par --cdim=1 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p1 --type=par --cdim=0 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read.gms lo=%gams.lo% --sym=p0 --type=par --cdim=0 --rdim=0 gdx=0.gdx > %system.NullFile%";
scalar p0_x; execute_load '0.gdx', p0_x=p0; abort$(abs(p0_x-3.14)>1e-6) 'wrong p0', p0_x;

* ExcelReader to verify that merged cells are written correctly
$onEchoV > read_mc.gms
set i, j, k, l, m, n, o, q, r, t;
set s4(n<,o<,q<,r<)
set s1(t<);
scalar p0;
parameter p1(m<);
parameter p4(i<,j<,k<,l<);
$if not set vsK $set vsK "''"
$if not set vsV $set vsV "''"
$onEmbeddedCode Connect:
- ExcelReader:
    file: %sym%_%cdim%_mc.xlsx
    ignoreText: False
    autoMerge: False
    mergedCells: True
    symbols:
      - name: %sym%
        type: %type%
        rowDimension: %rdim%
        columnDimension: %cdim%
        range: %sym%!B3
        valueSubstitutions: { %vsK%: %vsV% }

- GAMSWriter:
    symbols:
      - name: %sym%
$offEmbeddedCode
$offEcho

execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=4 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=3 --rdim=1 --vsK='Y'  gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=2 --rdim=2 --vsK='Y'  gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=1 --rdim=3 --vsK='Y'  gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s4 --type=set --cdim=0 --rdim=4 --vsK=.nan gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s1 --type=set --cdim=1 --rdim=0 --vsK=.nan gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=s1 --type=set --cdim=0 --rdim=1 --vsK=.nan gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=4 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=3 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=2 --rdim=2 gdx=2.gdx && gdxdiff 0.gdx 2.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=1 --rdim=3 gdx=3.gdx && gdxdiff 0.gdx 3.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p4 --type=par --cdim=0 --rdim=4 gdx=4.gdx && gdxdiff 0.gdx 4.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p1 --type=par --cdim=1 --rdim=0 gdx=0.gdx > %system.NullFile%";
execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p1 --type=par --cdim=0 --rdim=1 gdx=1.gdx && gdxdiff 0.gdx 1.gdx > %system.NullFile%";

execute.checkErrorLevel "gams read_mc.gms lo=%gams.lo% --sym=p0 --type=par --cdim=0 --rdim=0 gdx=0.gdx > %system.NullFile%";
scalar p0_x; execute_load '0.gdx', p0_x=p0; abort$(abs(p0_x-3.14)>1e-6) 'wrong p0', p0_x;

parameter p1_empty(*);

* Test correct (cDim) order of sparse parameter written by ExcelWriter
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p1_empty
      - name: p4
- ExcelWriter:
    file: p4_order.xlsx
    symbols:
      # do not throw an exception if there is no data
      - name: p1_empty
      - name: p4
endEmbeddedCode
embeddedCode Connect:
- ExcelReader:
    file: p4_order.xlsx
    ignoreText: False
    symbols:
      - name: p4
        rowDimension: 3
        columnDimension: 1
        range: p4!A1
- PythonCode:
    code: |
      # we expect the original UEL order: l1, l2, l3, l4
      expected = [['i4', 'j1', 'k3', 'l4', 0.414599358],
                  ['i4', 'j2', 'k4', 'l3', 0.3741985],
                  ['i4', 'j2', 'k2', 'l1', 0.480878863],
                  ['i4', 'j2', 'k2', 'l4', 0.681039283],
                  ['i4', 'j3', 'k3', 'l1', 0.57775716],
                  ['i4', 'j4', 'k4', 'l3', 0.839795967],
                  ['i4', 'j4', 'k1', 'l2', 0.010171892],
                  ['i3', 'j1', 'k4', 'l4', 0.55303282],
                  ['i3', 'j1', 'k2', 'l1', 0.430502537],
                  ['i3', 'j2', 'k3', 'l4', 0.31014418],
                  ['i3', 'j2', 'k2', 'l1', 0.82116568],
                  ['i3', 'j3', 'k3', 'l1', 0.6863915740000001],
                  ['i3', 'j4', 'k4', 'l1', 0.806006615],
                  ['i3', 'j4', 'k4', 'l3', 0.5721642610000001],
                  ['i3', 'j4', 'k4', 'l4', 0.7420390320000001],
                  ['i2', 'j1', 'k2', 'l3', 0.8109937880000001],
                  ['i2', 'j3', 'k4', 'l2', 0.866509716],
                  ['i2', 'j3', 'k4', 'l3', 0.428284381],
                  ['i2', 'j3', 'k1', 'l4', 0.503866822],
                  ['i2', 'j4', 'k2', 'l1', 0.377722234],
                  ['i1', 'j1', 'k4', 'l1', 0.5039530520000001],
                  ['i1', 'j1', 'k4', 'l2', 0.521365665],
                  ['i1', 'j1', 'k4', 'l4', 0.76064865],
                  ['i1', 'j1', 'k2', 'l2', 0.9094419050000001],
                  ['i1', 'j1', 'k2', 'l3', 0.820356749],
                  ['i1', 'j2', 'k3', 'l3', 0.8145443840000001],
                  ['i1', 'j4', 'k2', 'l2', 0.925196663],
                  ['i1', 'j4', 'k2', 'l3', 0.618022658],
                  ['i1', 'j4', 'k2', 'l4', 0.405575083],
                  ['i1', 'j4', 'k1', 'l4', 0.045967406]]
      sym = connect.container.data["p4"]
      sym.records.sort_values(sym.records.columns[:-1].tolist(), inplace=True)
      data = sym.records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data p4")
endEmbeddedCode

* Test correct order of projected parameter written by ExcelWriter with cdim only
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- Projection:
    name: p4(i,j,k,l)
    newName: p4_proj(l,i,k,j)
- ExcelWriter:
    file: p4_order_cdim.xlsx
    columnDimension: 4
    symbols:
      - name: p4_proj
        range: p4!A1
endEmbeddedCode
embeddedCode Connect:
- ExcelReader:
    file: p4_order_cdim.xlsx
    symbols:
      - name: p4
        rowDimension: 0
        columnDimension: 4
        range: p4!A1
- PythonCode:
    code: |
      expected = [
        ['l1', 'i4', 'k3', 'j3', 0.57775716],
        ['l1', 'i4', 'k2', 'j2', 0.480878863],
        ['l1', 'i3', 'k3', 'j3', 0.6863915740000001],
        ['l1', 'i3', 'k2', 'j2', 0.82116568],
        ['l1', 'i3', 'k2', 'j1', 0.430502537],
        ['l1', 'i3', 'k4', 'j4', 0.806006615],
        ['l1', 'i2', 'k2', 'j4', 0.377722234],
        ['l1', 'i1', 'k4', 'j1', 0.5039530520000001],
        ['l2', 'i4', 'k1', 'j4', 0.010171892],
        ['l2', 'i2', 'k4', 'j3', 0.866509716],
        ['l2', 'i1', 'k2', 'j4', 0.925196663],
        ['l2', 'i1', 'k2', 'j1', 0.9094419050000001],
        ['l2', 'i1', 'k4', 'j1', 0.521365665],
        ['l3', 'i4', 'k4', 'j2', 0.3741985],
        ['l3', 'i4', 'k4', 'j4', 0.839795967],
        ['l3', 'i3', 'k4', 'j4', 0.5721642610000001],
        ['l3', 'i2', 'k2', 'j1', 0.8109937880000001],
        ['l3', 'i2', 'k4', 'j3', 0.428284381],
        ['l3', 'i1', 'k3', 'j2', 0.8145443840000001],
        ['l3', 'i1', 'k2', 'j4', 0.618022658],
        ['l3', 'i1', 'k2', 'j1', 0.820356749],
        ['l4', 'i4', 'k3', 'j1', 0.414599358],
        ['l4', 'i4', 'k2', 'j2', 0.681039283],
        ['l4', 'i3', 'k3', 'j2', 0.31014418],
        ['l4', 'i3', 'k4', 'j4', 0.7420390320000001],
        ['l4', 'i3', 'k4', 'j1', 0.55303282],
        ['l4', 'i2', 'k1', 'j3', 0.503866822],
        ['l4', 'i1', 'k2', 'j4', 0.405575083],
        ['l4', 'i1', 'k4', 'j1', 0.76064865],
        ['l4', 'i1', 'k1', 'j4', 0.045967406],
      ]
      sym = connect.container.data["p4"]
      sym.records.sort_values(sym.records.columns[:-1].tolist(), inplace=True)
      data = sym.records.values.tolist()
      if data != expected:
        raise Exception("Unexpected Data p4 (cdim only)")
endEmbeddedCode

* test index parameter
embeddedCode Connect:
- GAMSReader:
    symbols: all
- ExcelWriter:
    file: caxlsrw.xlsx
    index: index!A1
    tableOfContents: True
- ExcelReader:
    file: caxlsrw.xlsx
    symbols:
      - name: p0_1
        range: p0!A1
        rowDimension: 0
        columnDimension: 0
      - name: p1_1
        range: p1!A1
        rowDimension: 0
        columnDimension: 1
      - name: p4_1
        range: p4_1!A1
        rowDimension: 2
        columnDimension: 2
      - name: p4_2
        range: p4_2!B2
        rowDimension: 1
        columnDimension: 3
      - name: p4_3
        mergedCells: True
        range: p4_3!B2
        rowDimension: 3
        columnDimension: 1
      - name: s1_1
        type: set
        ignoreText: False
        valueSubstitutions: {.nan: ""}
        range: s1!A1
        rowDimension: 1
        columnDimension: 0
      - name: s4_1
        type: set
        mergedCells: True
        valueSubstitutions: {"Y": ""}
        range: s4!A1
        rowDimension: 3
        columnDimension: 1
      - name: toc
        type: set
        range: "Table Of Contents!A1"
        rowDimension: 1
        columnDimension: 1
- PythonCode:
    code: |
      p0_expected = connect.container['p0'].records.values.tolist()
      p0_1 = connect.container['p0_1'].records.values.tolist()
      if p0_expected != p0_1:
        raise Exception("Unexpected Data p0_1.")

      p1_expected = connect.container['p1'].records.values.tolist()
      p1_expected = [row[:-1] + [round(row[-1], 16)] for row in p1_expected]
      p1_1 = connect.container['p1_1'].records.values.tolist()
      if p1_expected != p1_1:
        raise Exception("Unexpected Data p1_1.")

      p4_expected = connect.container['p4'].records.values.tolist()
      p4_expected = [row[:-1] + [round(row[-1], 16)] for row in p4_expected]
      p4_1 = connect.container['p4_1'].records.values.tolist()
      p4_1 = [row[:-1] + [round(row[-1], 16)] for row in p4_1]
      if p4_expected != p4_1:
        raise Exception("Unexpected Data p4_1.")

      p4_2 = connect.container['p4_2']
      p4_2.reorderUELs(uels=['l1', 'l2', 'l3', 'l4'], dimensions=3)
      p4_2 = p4_2.records.sort_values(p4_2.records.columns[:-1].tolist()).values.tolist()
      p4_2 = [row[:-1] + [round(row[-1], 16)] for row in p4_2]
      if p4_expected != p4_2:
        raise Exception("Unexpected Data p4_2.")

      p4_3 = connect.container['p4_3'].records.values.tolist()
      p4_3 = [row[:-1] + [round(row[-1], 16)] for row in p4_3]
      if p4_expected != p4_3:
        raise Exception("Unexpected Data p4_3.")

      s1_expected = connect.container['s1'].records.values.tolist()
      s1_1 = connect.container['s1_1'].records.values.tolist()
      if s1_expected != s1_1:
        raise Exception("Unexpected Data s1_1.")

      s4_expected = connect.container['s4'].records.values.tolist()
      s4_1 = connect.container['s4_1']
      s4_1.reorderUELs(uels=['j1', 'j2', 'j3', 'j4'], dimensions=1)
      s4_1.reorderUELs(uels=['k4', 'k3', 'k2', 'k1'], dimensions=2)
      s4_1 = s4_1.records.sort_values(s4_1.records.columns[:-1].tolist()).values.tolist()
      if s4_expected != s4_1:
        raise Exception("Unexpected Data s4_1.")

      toc_expected = [
        ['p0', 'Type', 'Parameter'],
        ['p0', 'Dimension', '0'],
        ['p0', 'Record Count', '1'],
        ['p1', 'Type', 'Parameter'],
        ['p1', 'Dimension', '1'],
        ['p1', 'Record Count', '3'],
        ['p4', 'Type', 'Parameter'],
        ['p4', 'Dimension', '4'],
        ['p4', 'Record Count', '30'],
        ['p4', 'Type', 'Parameter'],
        ['p4', 'Dimension', '4'],
        ['p4', 'Record Count', '30'],
        ['p4', 'Type', 'Parameter'],
        ['p4', 'Dimension', '4'],
        ['p4', 'Record Count', '30'],
        ['s1', 'Type', 'Set'],
        ['s1', 'Dimension', '1'],
        ['s1', 'Record Count', '2'],
        ['s4', 'Type', 'Set'],
        ['s4', 'Dimension', '4'],
        ['s4', 'Record Count', '28']
      ]
      toc = connect.container['toc'].records.values.tolist()
      if toc_expected != toc:
        raise Exception("Unexpected Data toc.")
endEmbeddedCode

* Test case insensitive symbol names and append content to Excel file
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    file: p4_cicap.xlsx
    symbols:
      - name: P4
- ExcelWriter:
    file: p4_cicap.xlsx
    symbols:
      - name: p4
        range: append!A1
endEmbeddedCode

* Make parameter p4 dense for the following tests
p4(i,j,k,l) = uniform(0,1);

* Test range validation 1
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    file: p4_range.xlsx
    symbols:
      - name: p4
        columnDimension: 2
        range: Sheet1!A1:R18
endEmbeddedCode
abort$[execerror <> 0] 'No errors expected';

* Test range validation (failure)
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    file: p4_range.xlsx
    symbols:
      - name: p4
        columnDimension: 2
        range: Sheet1!A1:R17
endEmbeddedCode
abort$[execerror = 0] 'Expected range validation to fail';
execerror = 0;

* Test range validation (failure)
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    file: p4_range.xlsx
    symbols:
      - name: p4
        columnDimension: 2
        range: Sheet1!A1:Q18
endEmbeddedCode
abort$[execerror = 0] 'Expected range validation to fail';
execerror = 0;

* Failing ExcelWriter tests due to Excel row/column limits
* Row Limit with symbols: all
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p5
- ExcelWriter:
    file: p5_excel_row_lim.xlsx
    symbols: all
endEmbeddedCode
abort$[execerror = 0] 'Expected row limit validation to fail';
execerror = 0;

* Column Limit with symbol and range specified
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p6
- ExcelWriter:
    file: p6_excel_col_lim.xlsx
    symbols:
      - name: p6
        range: sheet1!D1
endEmbeddedCode
abort$[execerror = 0] 'Expected column limit validation to fail';
execerror = 0;

* Test invalid option in symbols scope raising an exception
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    file: invalid_option.xlsx
    symbols:
      - name: p4
        invalidOption: invalid
endEmbeddedCode
abort$[execerror = 0] 'Expected invalid option in symbols scope to fail';
execerror = 0;

* Test invalid option in tableOfContents scope raising an exception
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    file: invalid_option.xlsx
    tableOfContents:
      sheetName: "TOC"
      sort: True
      invalidOption: invalid
endEmbeddedCode
abort$[execerror = 0] 'Expected invalid option in tableOfContents scope to fail';
execerror = 0;

* Test range validation for scalar
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p0
- ExcelWriter:
    file: p0_range.xlsx
    symbols:
      - name: p0
        range: Sheet1!A1:A1
endEmbeddedCode

* Test ExcelReader create categorical but never sort and ExcelWriter always sort
EmbeddedCode Connect:
- PythonCode:
    code: |
      import openpyxl
      data = [
          ["i" , "j" , "value", "", "i" , "j" , "k1", "k2"],
          ["i1", "j2", 1      , "", "i1", "j2", ""  , 1   ],
          ["i1", "j1", 2      , "", "i1", "j1", 2   , 3   ],
          ["i2", "j1", 4      , "", "i2", "j1", 6   , ""  ],
          ["i2", "j2", 3      , "", "i2", "j2", 4   , 5   ],
      ]
      wb = openpyxl.Workbook()
      sheet = wb.create_sheet("order")
      for row in data:
          sheet.append(row)
      wb.save("order.xlsx")
- ExcelReader:
    file: order.xlsx
    symbols:
      - name: ij
        range: order!A2:C5
        rowDimension: 2
        columnDimension: 0
      - name: ijk
        range: order!E1
        rowDimension: 2
- PythonCode:
    code: |
        # checks that categoricals are correct
        ij = connect.container.data["ij"]
        ij_data_sorted = ij.records.sort_values(ij.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j2', 1.0],
                    ['i1', 'j1', 2.0],
                    ['i2', 'j2', 3.0],
                    ['i2', 'j1', 4.0]]
        if ij_data_sorted != expected:
            raise Exception("Unexpected Data ij.")

        # reorder UELs to check that ExcelWriter sorts
        ij.reorderUELs(uels=['j1', 'j2'], dimensions=1)

        # checks that categoricals are correct
        ijk = connect.container.data["ijk"]
        ijk_data_sorted = ijk.records.sort_values(ijk.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j2', 'k2', 1.0],
                    ['i1', 'j1', 'k1', 2.0],
                    ['i1', 'j1', 'k2', 3.0],
                    ['i2', 'j2', 'k1', 4.0],
                    ['i2', 'j2', 'k2', 5.0],
                    ['i2', 'j1', 'k1', 6.0]]
        if ijk_data_sorted != expected:
            raise Exception("Unexpected Data ijk.")

        # reorder UELs to check that ExcelWriter sorts
        ijk.reorderUELs(uels=['i2', 'i1'], dimensions=0)
        ijk.reorderUELs(uels=['j1', 'j2'], dimensions=1)
        ijk.reorderUELs(uels=['k2', 'k1'], dimensions=2)
- ExcelWriter:
    file: test_order.xlsx
    symbols:
      - name: ij
        columnDimension: 0
      - name: ijk
- ExcelReader:
    file: test_order.xlsx
    symbols:
      - name: ij_new
        range: ij!A1
        rowDimension: 2
        columnDimension: 0
      - name: ijk_new
        range: ijk!A1
        rowDimension: 2
- PythonCode:
    code: |
        # checks that ExcelWriter sorts
        ij = connect.container.data["ij_new"]
        ij_data_sorted = ij.records.sort_values(ij.records.columns[:-1].tolist()).values.tolist()
        expected = [['i1', 'j1', 2.0],
                    ['i1', 'j2', 1.0],
                    ['i2', 'j1', 4.0],
                    ['i2', 'j2', 3.0]]
        if ij_data_sorted != expected:
            raise Exception("Unexpected Data ij_new.")

        # checks that ExcelWriter sorts
        ijk = connect.container.data["ijk_new"]
        ijk_data_sorted = ijk.records.sort_values(ijk.records.columns[:-1].tolist()).values.tolist()
        expected = [['i2', 'j1', 'k1', 6.0],
                    ['i2', 'j2', 'k2', 5.0],
                    ['i2', 'j2', 'k1', 4.0],
                    ['i1', 'j1', 'k2', 3.0],
                    ['i1', 'j1', 'k1', 2.0],
                    ['i1', 'j2', 'k2', 1.0]]
        if ijk_data_sorted != expected:
            raise Exception("Unexpected Data ijk_new.")
endEmbeddedCode


* Test write multiple symbols into one sheet
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    file: p4_multi.xlsx
    columnDimension: 2
    symbols:
      - name: p4
        range: Sheet1!A1
      - name: p4
        range: Sheet1!A20
- ExcelReader:
    file: p4_multi.xlsx
    rowDimension: 2
    columnDimension: 2
    symbols:
      - name: p4_1
        range: Sheet1!A1
      - name: p4_2
        range: Sheet1!A20
- GDXWriter:
    file: multi1.gdx
    symbols:
      - name: p4
- GDXWriter:
    file: multi2.gdx
    symbols:
      - name: p4_1
        newName: p4
- GDXWriter:
    file: multi3.gdx
    symbols:
      - name: p4_2
        newName: p4
endEmbeddedCode
execute.checkErrorLevel "gdxdiff multi1.gdx multi2.gdx RelEps=0.0000000000000001 > %system.NullFile%";
execute.checkErrorLevel "gdxdiff multi1.gdx multi3.gdx RelEps=0.0000000000000001 > %system.NullFile%";


* Test columnDimension=infer (default)
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
      - name: p1
- ExcelWriter:
    file: p4_cdim_auto.xlsx
    columnDimension: 2
    symbols:
      - name: p4
        columnDimension: infer
- ExcelReader:
    file: p4_cdim_auto.xlsx
    rowDimension: 3
    columnDimension: 1
    symbols:
      - name: p4_new
        range: p4!A1
- ExcelWriter:
    file: p1_cdim_auto.xlsx
    symbols:
      - name: p1
- ExcelReader:
    file: p1_cdim_auto.xlsx
    rowDimension: 0
    columnDimension: 1
    symbols:
      - name: p1_new
        range: p1!A1
- GDXWriter:
    file: p4_cdim_auto_ref.gdx
    symbols:
      - name: p4
- GDXWriter:
    file: p4_cdim_auto.gdx
    symbols:
      - name: p4_new
        newName: p4
- GDXWriter:
    file: p1_cdim_auto_ref.gdx
    symbols:
      - name: p1
- GDXWriter:
    file: p1_cdim_auto.gdx
    symbols:
      - name: p1_new
        newName: p1
endEmbeddedCode
execute.checkErrorLevel "gdxdiff p1_cdim_auto_ref.gdx p1_cdim_auto.gdx RelEps=0.0000000000000001 > %system.NullFile%";
execute.checkErrorLevel "gdxdiff p4_cdim_auto_ref.gdx p4_cdim_auto.gdx RelEps=0.0000000000000001 > %system.NullFile%";


* Test range ending with "!"" being resolved as "!A1"
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- ExcelWriter:
    trace: 4
    file: p4_default_a1.xlsx
    symbols:
      - name: p4
        columnDimension: 2
        range: p4!
- ExcelReader:
    file: p4_default_a1.xlsx
    symbols:
      - name: p4_new
        columnDimension: 2
        rowDimension: 2
        range: p4!
- PythonCode:
    code: |
        data_p4 = connect.container["p4"].records.values.tolist()
        data_p4_new = connect.container["p4_new"].records.values.tolist()
        data_p4 = [row[:-1] + [round(row[-1], 16)] for row in data_p4]
        data_p4_new = [row[:-1] + [round(row[-1], 16)] for row in data_p4_new]
        if data_p4 != data_p4_new:
            raise Exception("Unexpected Data.")
endEmbeddedCode


* Test symbols=all
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: i
      - name: j
      - name: k
      - name: l
      - name: s1
      - name: s4
      - name: p0
      - name: p1
      - name: p4
      - name: p0_x
- ExcelWriter:
    file: write_all.xlsx
    symbols: all
- PythonCode:
    code: |
      import openpyxl
      wb = openpyxl.load_workbook("write_all.xlsx")
      if len(wb.sheetnames) != 10:
        raise Exception("Unexpected number of sheets in workbook.")
      expected_sheets = ["i", "j", "k", "l", "s1", "s4", "p0", "p1", "p4", "p0_x"]
      for sheet in expected_sheets:
        if sheet not in wb.sheetnames:
          raise Exception(f"Expected sheet '{sheet}' to be contained in workbook.")
- ExcelReader:
    file: write_all.xlsx
    symbols:
      - name: p1_new
        range: p1!
        rowDimension: 0
        columnDimension: 1
      - name: p4_new
        range: p4!
        rowDimension: 3
        columnDimension: 1
- PythonCode:
    code: |
      data_p1 = connect.container['p1'].records.values.tolist()
      data_p1 = [row[:-1] + [round(row[-1], 16)] for row in data_p1]
      data_p1_new = connect.container['p1_new'].records.values.tolist()
      data_p1_new = [row[:-1] + [round(row[-1], 16)] for row in data_p1_new]
      if data_p1 != data_p1_new:
        raise Exception("Unexpected Data.")

      data_p4 = connect.container['p4'].records.values.tolist()
      data_p4 = [row[:-1] + [round(row[-1], 16)] for row in data_p4]
      data_p4_new = connect.container['p4_new'].records.values.tolist()
      data_p4_new = [row[:-1] + [round(row[-1], 16)] for row in data_p4_new]
      if data_p4 != data_p4_new:
        raise Exception("Unexpected Data.")
endEmbeddedCode


* test writing of special values as strings (default)
embeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p', ['*'], records=df)
- ExcelWriter:
    file: sv.xlsx
    symbols: all
- ExcelReader:
    file: sv.xlsx
    symbols:
      - name: p_new
        range: p!
        rowDimension: 0
- PythonCode:
    code: |
      import numpy as np
      import gams.transfer as gt
      data_p = connect.container["p"].records.values.tolist()
      data_p_new = connect.container["p_new"].records.values.tolist()
      if not np.array_equal(data_p, data_p_new):
        raise Exception("Unexpected data.")
      if not gt.SpecialValues.isEps(data_p[0][-1]) or not gt.SpecialValues.isEps(data_p_new[0][-1]):
        raise Exception("Unexpected data EPS.")
      if not gt.SpecialValues.isNegInf(data_p[1][-1]) or not gt.SpecialValues.isNegInf(data_p_new[1][-1]):
        raise Exception("Unexpected data -INF.")
      if not gt.SpecialValues.isPosInf(data_p[2][-1]) or not gt.SpecialValues.isPosInf(data_p_new[2][-1]):
        raise Exception("Unexpected data INF.")
      if not gt.SpecialValues.isUndef(data_p[3][-1]) or not gt.SpecialValues.isUndef(data_p_new[3][-1]):
        raise Exception("Unexpected data UNDEF.")
      if not gt.SpecialValues.isNA(data_p[4][-1]) or not gt.SpecialValues.isNA(data_p_new[4][-1]):
        raise Exception("Unexpected data NA.")
      if data_p[5][-1] != 0 or data_p_new[5][-1] != 0:
        raise Exception("Unexpected data 0.")
      if data_p[6][-1] != 1 or data_p_new[6][-1] != 1:
        raise Exception("Unexpected data 1.")
endEmbeddedCode


* test value substitutions for special values
embeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p', ['*'], records=df)
- ExcelWriter:
    file: sv2.xlsx
    symbols: all
    valueSubstitutions: {
      "EPS": 0,
      "-INF": 1,
      "INF": 2,
      "UNDEF": 3,
      "NA": 4
    }
- ExcelReader:
    file: sv2.xlsx
    symbols:
      - name: p_new
        range: p!
        rowDimension: 0
- PythonCode:
    code: |
      expected = [
        ['i1', 0.],
        ['i2', 1.],
        ['i3', 2.],
        ['i4', 3.],
        ['i5', 4.],
        ['i6', 0.],
        ['i7', 1.]
      ]

      data_p_new = connect.container["p_new"].records.values.tolist()
      if expected != data_p_new:
        raise Exception("Unexpected data")
endEmbeddedCode


* test value substitutions for special values not being transitive
embeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', "EPS"],
        ['i2', "-INF"],
        ['i3', "INF"],
        ['i4', "UNDEF"],
        ['i5', "NA"],
        ['i6', 0],
        ['i7', 1]
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p', ['*'], records=df)
- ExcelWriter:
    file: sv2.xlsx
    symbols: all
    valueSubstitutions: {
      "EPS": .nan,  # gets dropped - .nan is gt.SpecialValues.Undef and should not be replaced by the UNDEF:1 value substitution
      "-INF": 0,
      "INF": .nan,  # gets dropped
      "UNDEF": 1,
      "NA": 2
    }
- ExcelReader:
    file: sv2.xlsx
    symbols:
      - name: p_new
        range: p!
        rowDimension: 0
- PythonCode:
    code: |
      expected = [
        ['i2', 0.],
        ['i4', 1.],
        ['i5', 2.],
        ['i6', 0.],
        ['i7', 1.]
      ]
      data_p_new = connect.container["p_new"].records.values.tolist()
      if expected != data_p_new:
        raise Exception("Unexpected data")
endEmbeddedCode


* test ExcelWriter not skipping symbol with empty DataFrame
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
- Filter:
    name: p4
    newName: p4new
    labelFilters:
      - dimension: 1
        keep: ["x"]
- ExcelWriter:
    file: p4_empty.xlsx
    symbols:
      - name: p4new
- ExcelReader:
    file: p4_empty.xlsx
    symbols:
      - name: p4read
        range: p4new!A1
        columnDimension: 1
        rowDimension: 3
- PythonCode:
    code: |
      data = connect.container["p4read"].records.values.tolist()
      if [] != data:
        raise Exception("Unexpected data")
endEmbeddedCode


* test ExcelWriter not skipping symbol with empty DataFrame (2)
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
      - name: p1
- Filter:
    name: p4
    newName: p4new
    labelFilters:
      - dimension: 1
        keep: ["x"]
- ExcelWriter:
    file: empty_sheet.xlsx
    symbols: all
- ExcelReader:
    file: empty_sheet.xlsx
    symbols:
      - name: p4read
        range: p4new!A1
- PythonCode:
    code: |
      data = connect.container["p4read"].records.values.tolist()
      if [] != data:
        raise Exception("Unexpected data")
endEmbeddedCode


* test named ranges being case insensitive
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p1
- ExcelWriter:
    file: caxlsrw.xlsx
    symbols:
      - name: p1
        range: myrange  # MyRange
- ExcelReader:
    file: caxlsrw.xlsx
    symbols:
      - name: p1_read
        columnDimension: 1
        rowDimension: 0
        range: MYRANGE  # MyRange
- PythonCode:
    code: |
      expected = [
        ['i3', 0.456789096],
        ['i2', 0.32300194],
        ['i1', 0.315329019],
      ]
      data_p1_read = connect.container["p1_read"].records.values.tolist()
      if expected != data_p1_read:
        raise Exception("Unexpected data")
endEmbeddedCode


* test ExcelWriter writing toc (default)
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
      - name: p1
      - name: s1
      - name: s4
- ExcelWriter:
    file: toc_default.xlsx
    tableOfContents: True
- ExcelReader:
    file: toc_default.xlsx
    symbols:
      - name: toc
        type: set
        range: Table Of Contents!A1
        rowDimension: 1
        columnDimension: 1
        valueSubstitutions: {.nan: ""}
- PythonCode:
    code: |
      expected = [
        ['s1', 'Type', 'Set'],
        ['s1', 'Dimension', '1'],
        ['s1', 'Record Count', '2'],
        ['s1', 'Explanatory text', ''],
        ['s4', 'Type', 'Set'],
        ['s4', 'Dimension', '4'],
        ['s4', 'Record Count', '28'],
        ['s4', 'Explanatory text', ''],
        ['p1', 'Type', 'Parameter'],
        ['p1', 'Dimension', '1'],
        ['p1', 'Record Count', '3'],
        ['p1', 'Explanatory text', ''],
        ['p4', 'Type', 'Parameter'],
        ['p4', 'Dimension', '4'],
        ['p4', 'Record Count', '256'],
        ['p4', 'Explanatory text', '']
      ]
      if expected != connect.container['toc'].records.values.tolist():
        raise Exception("Unexpected data")
endEmbeddedCode


* test ExcelWriter writing toc (sorting, custom sheet, empty symbols)
option clear=p1;
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p4
      - name: p1
      - name: s1
      - name: s4
- ExcelWriter:
    file: toc_custom.xlsx
    tableOfContents:
      sheetName: "TOC"
      sort: True
- ExcelReader:
    file: toc_custom.xlsx
    symbols:
      - name: toc
        type: set
        range: TOC!A1
        rowDimension: 1
        columnDimension: 1
        valueSubstitutions: {.nan: ""}
- PythonCode:
    code: |
      expected = [
        ['p1', 'Type', 'Parameter'],
        ['p1', 'Dimension', '1'],
        ['p1', 'Record Count', '0'],
        ['p1', 'Explanatory text', ''],
        ['p4', 'Type', 'Parameter'],
        ['p4', 'Dimension', '4'],
        ['p4', 'Record Count', '256'],
        ['p4', 'Explanatory text', ''],
        ['s1', 'Type', 'Set'],
        ['s1', 'Dimension', '1'],
        ['s1', 'Record Count', '2'],
        ['s1', 'Explanatory text', ''],
        ['s4', 'Type', 'Set'],
        ['s4', 'Dimension', '4'],
        ['s4', 'Record Count', '28'],
        ['s4', 'Explanatory text', '']
      ]
      if expected != connect.container['toc'].records.values.tolist():
        raise Exception("Unexpected data")
endEmbeddedCode


* test ExcelWriter value substitutions for parameters
embeddedCode Connect:
- PythonCode:
    code: |
      import pandas as pd
      data = [
        ['i1', 10],
        ['i2', 20],
        ['i3', 30],
        ['i4', 40],
      ]
      df = pd.DataFrame(data)
      connect.container.addParameter('p', ['*'], records=df)
- ExcelWriter:
    file: par_valuesub.xlsx
    symbols: all
    valueSubstitutions: {
      10: 1,
      20: 2,
      30: 3,
      40: "some string",
    }
- ExcelReader:
    file: par_valuesub.xlsx
    valueSubstitutions: { "some string": 4 }
    symbols:
      - name: p_new
        range: p!
        rowDimension: 0
- PythonCode:
    code: |
      expected = [
        ['i1', 1],
        ['i2', 2],
        ['i3', 3],
        ['i4', 4],
      ]
      data_p_new = connect.container["p_new"].records.values.tolist()
      if expected != data_p_new:
        raise Exception("Unexpected data")
endEmbeddedCode


* test ExcelWriter value substitutions for sets
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: s1
- ExcelWriter:
    file: set_valuesub.xlsx
    valueSubstitutions: {"line 3": "new text"}
    symbols:
      - name: s1
- ExcelReader:
    file: set_valuesub.xlsx
    symbols:
      - name: s1_new
        range: s1!
        rowDimension: 0
        type: set
        ignoreText: False
        valueSubstitutions: {.nan: ""}
- PythonCode:
    code: |
      expected = [
        ['i3', 'new text'],
        ['i1', '']
      ]

      data_s1_new = connect.container["s1_new"].records.values.tolist()
      if expected != data_s1_new:
        raise Exception("Unexpected data")
endEmbeddedCode

* test clearSheet - all subsequent EC Connect sections belong together - altering one will affect the others

* prepare data
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p0
      - name: p1
- ExcelWriter:
    file: clear_sheet.xlsx
    symbols:
      - name: p0
        range: p!A1
      - name: p0
        range: p!E4
      - name : p1
        range: p!B2
endEmbeddedCode

* test with full range and clearSheet: range
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p1
- ExcelWriter:
    file: clear_sheet.xlsx
    symbols:
      - name: p1
        range: p!B2:D3
        clearSheet: range
- ExcelReader:
    file: clear_sheet.xlsx
    symbols:
      - name: p0_1
        range: p!A1
        rowDimension: 0
        columnDimension: 0
      - name: p0_2
        range: p!E4
        rowDimension: 0
        columnDimension: 0
      - name: p1_1
        range: p!B2:D3
        rowDimension: 0
        columnDimension: 1
- PythonCode:
    code: |
      expected = [[3.14]]
      data = connect.container['p0_1'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")

      expected = [[3.14]]
      data = connect.container['p0_2'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")

      expected = connect.container['p1'].records.values.tolist()
      expected = [row[:-1] + [round(row[-1], 16)] for row in expected]
      data = connect.container['p1_1'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")
endEmbeddedCode

* test with open range clearSheet: range
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p1
- ExcelWriter:
    file: clear_sheet.xlsx
    symbols:
      - name: p1
        range: p!B2
        clearSheet: range
- ExcelReader:
    file: clear_sheet.xlsx
    symbols:
      - name: p0_1
        range: p!A1
        rowDimension: 0
        columnDimension: 0
      - name: p0_2
        range: p!E4
        rowDimension: 0
        columnDimension: 0
      - name: p1_1
        range: p!B2:D3
        rowDimension: 0
        columnDimension: 1
- PythonCode:
    code: |
      expected = [[3.14]]
      data = connect.container['p0_1'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")

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

      expected = connect.container['p1'].records.values.tolist()
      expected = [row[:-1] + [round(row[-1], 16)] for row in expected]
      data = connect.container['p1_1'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")
endEmbeddedCode

* test scalar with open range clearSheet: range
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p0
- ExcelWriter:
    file: clear_sheet.xlsx
    symbols:
      - name: p0
        range: p!A1
        clearSheet: range
- ExcelReader:
    file: clear_sheet.xlsx
    symbols:
      - name: p1_1
        range: p!A1
        rowDimension: 0
        columnDimension: 0
      - name: empty
        range: p!B2
        rowDimension: 0
        columnDimension: 0
- PythonCode:
    code: |
      expected = [[3.14]]
      data = connect.container['p1_1'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")

      expected = []
      data = connect.container['empty'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")
endEmbeddedCode

* test with open range clearSheet: True
embeddedCode Connect:
- GAMSReader:
    symbols:
      - name: p1
- ExcelWriter:
    file: clear_sheet.xlsx
    symbols:
      - name: p1
        range: p!B2
        clearSheet: True
- ExcelReader:
    file: clear_sheet.xlsx
    symbols:
      - name: empty
        range: p!A1
      - name: p1_1
        range: p!B2
        rowDimension: 0
        columnDimension: 1
- PythonCode:
    code: |
      expected = []
      data = connect.container['empty'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")

      expected = connect.container['p1'].records.values.tolist()
      expected = [row[:-1] + [round(row[-1], 16)] for row in expected]
      data = connect.container['p1_1'].records.values.tolist()
      if expected != data:
        raise Exception("Unexpected data")
endEmbeddedCode