Functions to Write Data to Excel Worksheets
These functions write directly to an Excel spreadsheet. They complement the SpreadsheetCell and SpreadsheetRange functions for reading data from Excel worksheets. They are a simpler alternative to OLE Linking or ODBC, two other methods for sending data to spreadsheets. They are available only from the Enterprise edition or higher, including ADE. For the functions that read frpm Excel, see Functions To Read Excel Worksheets.
These functions write the data to a spreadsheet when the function is evaluated. If upstream Analytica variables change, they won't automatically update the data in Excel until the variable (or button) that calls them is re-evaluated. In this way, they are different from outgoing OLE links which do update automatically if Auto recompute outgoing OLE links is set and the spreadsheet is currently loaded in Excel. These updates become permantent only when SpreadsheetSave is called.
SpreadsheetSave(workbook, filename)
Saves any changes to the «workbook» to the indicated filename, or to the original file if «filename» is omitted. The «filename» is interpreted relative to the CurrentDataFolder. The «workbook» must be an object obtained from SpreadsheetOpen().
SpreadsheetSetCell(workbook, sheet, col, row, value)
Writes «value» (either a number or text) to the cell identified by «sheet», «col» and «row». You can write multiple values via array abstraction when «col», «row» and «value» share one or more indexes. The «workbook» must be an object obtained from SpreadsheetOpen().
For writing large arrays, SpreadsheetSetRange runs faster.
It writes «value» into the formula of each cell. Thus, you can set an actual cell formula such as "=Sum(D4:D24)" by writing a
text values starting with =.
Examples
For these examples we assume that, Wb is a variable defined as:
Variable Wb := SpreadsheetOpen("MyWorkbook.xls").
Either of the following two calls write the value 3.5 to cell Sheet1!C5. Note that «col» can use either the character label or the numeric column position:
SpreadsheetSetCell(Wb , "Sheet1", "C", 5, 3.5)SpreadsheetSetCell(Wb , "Sheet1", 3, 5, 3.5)
Here A is a 1-D array indexed by I. Writes the array as a column-vector starting at cell "D5":
SpreadsheetSetCell(Wb , "Sheet1", 4, 4+@I, A)
Writes the same 1-D array as a row-vector starting at cell "E1":
SpreadsheetSetCell(Wb , "Sheet1", "E", @I, A)
Writes a 2-D array B, indexed by I and J, to the sheet with the upper-left corner at cell B7, with the I dimension on the horizontal, the J on the vertical:
SpreadsheetSetCell(Wb , "Sheet1", 1+@I, 6+@J, B)
SpreadsheetSetRange(workbook, range, value, colIndex, rowIndex, sheet)
Writes «value» to «range» in spreadsheet «workbook». «range» may be a named range, or a cell coordinate range, such as 'C6:F9' or single cell 'C9'. If it's a coordinate range, you must specify the «sheet», either within the coordinate range, such as 'Sheet1!C6: F9' or using the optional «sheet» parameter , with sheet name as text or sheet number as a number from 1 to n.
«value» may be atomic -- i.e., a single number or text value -- or an array with 1 or 2 dimensions. If 1-D, you should specify either , «colIndex» or «rowIndex» as an index parameter. If2-D, you must specify both «colIndex» and «rowIndex».
Ideally, the target range matches the data with the same number of rows and columns. If the number of columns in «colIndex» exceeds the number of columns in «range» or «rowIndex» exceeds the number of rows, it will not write the extra columns or rows. If «value» has only one column, it repeats the same data for all rows in «range». If «value» has more than 1 column but less than the number of columns in «range»(or more than 1 row, but fewer than the number of rows in «range»), it writes #N/A into the extra cells in «range».
If value is text and begins with the = character, the spreadsheet will treat it as a formula, just as if you had typed the formula directly into the spreadsheet.
Examples
Writes the value 6.0 to the cell Sheet1!B5:
SpreadsheetSetRange(Wb , "Sheet1!B5", 6.0)
Writes the 1-D array Cash_flow as a column-vector to a named range, already labelled as "Cash_flow":
SpreadsheetSetRange(Wb , "Cash_flow", Cash_flow, Time)
Writes a 1-D array B, indexed by I, as a row-vector, here in the third worksheet:
SpreadsheetSetRange(Wb , "D5:D15", B, , I, sheet: 3)
Writes a 2-D array ShippingCosts, indexed by Destination and Origin, to the range labelled "Shipping_costs":
SpreadsheetSetRange(Wb , "Shipping_costs", ShippingCosts, Destination, Origin)
The next expression includes the row and column header labels with the data written by concatenating them to the data before the data is written to the range. The original array is in Variable A indexed by indexes Row and Col. Temporary index R and C prepend one item for the header row and column.
Index R:=Concat("",Row);Index C:= Concat("",Col);var temp := Concat([Row],A,,Col,C);var data := Concat([C],d1,,Row,R);SpreadsheetSetRange(Wb ,range,data,C,R)
SpreadsheetSetInfo(workbook, item, value)
SpreadsheetSetInfo lets you change properties of the workbook itself, as opposed to SpreadsheetSetCell and SpreadsheetSetRange which change properties or contents of cells. The «item» parameter must be one of these:
"ActiveSheet"{ Changes the selected sheet to the be one with the name «value» }"Author""CalculationMode"{ and «value» must be "Automatic", "Manual" or "Semiautomatic" }"Date1904""SelectedRange"{ makes the workbook and sheet visible if they aren't already, and selects the indicated cell range }"Title"
Examples
SpreadsheetSetInfo(wb, "ActiveSheet", "Sheet3")SpreadsheetSetInfo(wb, "CalculationMode", "Manual")SpreadsheetSetInfo(wb, "SelectedRange", "Sheet2!B3:E9")
History
In early 4.2 beta builds, before 4.2.0.21, these functions were present as SaveExcelWorkbook, WriteWorksheetCell and WriteWorksheetRange. Those names have now been deprecated. They will still work for a while, but may be removed in a future Analytica build. Also, the parameters of SpreadsheetSetRange differ slightly from WriteWorksheetRange -- the «sheet» parameter has been made optional and moved from being the 2nd parameter to being the last parameter. It is now not necessary for named ranges.
Enable comment auto-refresher