Description

This model demonstrates the usage of the colMerge parameter when reading empty
cells in the first part and the handling of merged Excel Ranges when reading
with cMerge in the second part.

This model is referenced in "Reading empty Cells with colMerge" resp. "Reading
merged Excel Ranges with cMerge" from the GDXXRW Documentation.

Category : GAMS Data Utilities library

Main file : GDXXRWExample20.gms   includes :  exampleData.xlsx  GDXXRWExample20.gms

$title Reading empty Cells with colMerge and reading merged Excel Ranges with cMerge (GDXXRWExample20,SEQ=122)

$onText
This model demonstrates the usage of the colMerge parameter when reading empty
cells in the first part and the handling of merged Excel Ranges when reading
with cMerge in the second part.

This model is referenced in "Reading empty Cells with colMerge" resp. "Reading
merged Excel Ranges with cMerge" from the GDXXRW Documentation.
$offText

$callTool win32.msappavail Excel
$if errorlevel 1 $abort.noError "No Excel available"


* Reading empty cells with colMerge
* The option colMerge is useful only for the first three columns of the example data
Parameter
   A_d 'results for the default value of colMerge (colMerge=0)'
   A_1 'use the non-empty value of a previous cell as the value for empty cells in the first column'
   A_2 'use the non-empty value of a previous cell as the value for empty cells in the first two columns'
   A_3 'use the non-empty value of a previous cell as the value for empty cells in the first three columns';

$onEcho > howToRead.txt
par=A_d rng=colMerge!B2 rDim=3 cDim=1
par=A_1 rng=colMerge!B2 rDim=3 cDim=1 colMerge=1
par=A_2 rng=colMerge!B2 rDim=3 cDim=1 colMerge=2
par=A_3 rng=colMerge!B2 rDim=3 cDim=1 colMerge=3
$offEcho

$call gdxxrw exampleData.xlsx output=colMerge.gdx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading from spreadsheet using colMerge!

* load the data
$gdxIn colMerge.gdx
$load A_d A_1 A_2 A_3
$gdxIn

* alternatively open colMerge.gdx in the GAMS IDE or in GAMS Studio for a better overview
* as expected, the number of empty labels is decreasing for higher values of colMerge
display A_d, A_1, A_2, A_3;
*-------------------------------------------------------------------------------


* Reading merged Excel ranges with cMerge
* All three values for the option cMerge are demonstrated
Parameter
   B_d 'empty cells of a merged range will remain empty (default, cMerge=0)'
   B_1 'the merged value of a range is used for all empty cells being part of the range (affecting merged ranges in the headers only'
   B_2 'the merged value of a range is used for all empty cells being part of the range';

$onEcho > howToRead.txt
cMerge=0 par=B_d rng=cMerge!A1 rDim=1 cDim=2
cMerge=1 par=B_1 rng=cMerge!A1 rDim=1 cDim=2
cMerge=2 par=B_2 rng=cMerge!A1 rDim=1 cDim=2
$offEcho

$call gdxxrw exampleData.xlsx output=cMerge.gdx @howToRead.txt trace=0
$ifE errorLevel<>0 $abort Error reading from spreadsheet using cMerge!

* load the data
$gdxIn cMerge.gdx
$load B_d B_1 B_2
$gdxIn

* alternatively open cMerge.gdx in the GAMS IDE or in GAMS Studio for a better overview
display B_d, B_1, B_2;