Download SIMPROCESS - computer structures and systems laboratory

Interfacing With A Spreadsheet
WriteToSpreadsheet(Model.ObjectAttribute, "Worksheet", 27, 1,
"=SUM(R[-26]C:R[-2]C)");
The R[-26]C portion refers to a cell that is 26 rows above the one in which the formula appears. That
is, its location is relative to that cell (at row 27, in the above example) and its row number can be
discovered by adding the bracketed value [-26]to that row number, thus resulting in a row number
of 1. The C portion refers to a column and is also an offset computed against the column number of
the cell into which the formula is being written. An offset value of zero is usually omitted, but the column
offset in this formula could also be written as C[0]. It is therefore possible to refer to any row and
column by using appropriate offset values. And since this form is relative, the addition of rows and
columns to the worksheet would cause the result of the formula’s calculation to change.
As with the earlier Workbook formula example, the colon character indicates a range. The cell at the
opposite end of the range is identified in the same manner as the first. In this example, the ending cell’s
row is the formula’s row (27) added to the offset value (-2), or 25. The column number, just as with
the previous reference, is the same as that of the formula (since an offset of zero is omitted).
If the resulting XML spreadsheet containing this formula were then opened in Excel, the formula bar
would show the same thing for cell A27 as in the Workbook file: =SUM(A1:A25). And the cell would
display the same numeric result as the Workbook (assuming that both had the same values written into
the cells being summed).
Absolute cell references are much easier to determine than the relative form when writing to XML
spreadsheet files. A formula such as =SUM(R1C1:R25C1)uses absolute row and column number
references, so that it too represents a formula that sums rows 1 through 25 of column 1. The key
difference in this format is the lack of bracketed numbers, so that the values are absolute row or column
number references rather than offset values based on the formula’s cell location. Since absolute row
and column numbers must be used here, each must have a value of one or greater.
As a reminder, when using ReadFromSpreadsheet to read a cell containing a formula from an
XML spreadsheet, its value will be returned if present. However, when a formula is written as described
above using WriteToSpreadsheet, the cell’s new value will not be calculated until Excel or a
compatible spreadsheet program opens the file, recalculates the formula result, and then saves the file
to XML format with this new value in place. Any attempt to reopen the XML spreadsheet file for input
and read from the cell before that has occurred would result in a run-time error.
Additional Spreadsheet XML Information
Much more information on the format of XML spreadsheets, formally known as SpreadsheetML
(Spreadsheet Markup Language), is available from Microsoft. Among the complex topics discussed
in that documentation are matters such as XML namespace usage and the additional capabilities of
this file format. While it is possible to construct and manipulate these files using any tool that is fully
SIMPROCESS User’s Manual
306