When writing to a spreadsheet one can also use the Clear option to control data handling and set matching. When 'Clear' is active the only data that will be written to the spreadsheet are those data for which the set element names match row and column labels that are in the spreadsheet already but all data and formulas in the target range will be removed. Element ordering is explicitly specified overriding the default that would occur as controlled by the GAMS element ordering rules and the unique element list as discussed in the Rules for Item Capitalization and Ordering chapter.
Example:
Suppose I have a range in a spreadsheet just like that in the spreadsheet above for the merge example gdxxrwss.xls in the range i1:n4.
and use the commands (gdxxrwskipempty.gms)
$call"Gdxxrw GDXse.gdx o=gdxxrwss.xls par=moded4 Rng=sheet2!i1 rdim=2 cdim=1 clear"
then the result is
which shows results similar to those under merge but the old data in the column labeled horse has been removed.
Notes:
| • | Using the clear option will force the data to be presented in the order in which the row and column labels are entered. |
| • | GDX file contents that do not have matching row/column pair of named elements in the spreadsheet will be overlooked. |
| • | A write under a clear option addressing a blank area of a spreadsheet will always be blank as there will not be matching set elements. |
| • | The matching of labels is not case sensitive. |
| • | Warning: The Clear option will clear all Excel formulas and values in the rectangle used, even if the cells do not have matching row / column headings in the GDX file. |