Functions to Write Data to Excel Worksheets
The functions let you to 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 only available from the Enterprise edition or higher, including ADE.
When you use these functions to write data to a spreadsheet, the values are sent when the function is evaluated. If upstream Analytica variables change, the result is not automatically transferred to Excel unless the variable (or button) that calls these functions is re-evaluated. This aspect differentiates it somewhat from outgoing OLE links when the Auto recompute outgoing OLE links preference is on. If the spreadsheet is currently loaded in Excel when these are evaluated, the running copy of the spreadsheet is altered. The changes only persist when SpreadsheetSave is called.
Only the functions relevant to writing data are described on this page. For the functions that read data, see Functions To Read Excel Worksheets.
SpreadsheetSave(workbook, filename)
Saves «workbook», which was originally obtained via SpreadsheetOpen, but which may have been modified since it was first opened, to the indicated filename, or to the original file if «filename» is omitted. The «filename» is interpreted relative to the CurrentDataFolder.
SpreadsheetSetCell(workbook, sheet, col, row, value)
Writes «value» (either a number or text) to the cell identified by «sheet», «col» and «row». Can also be used to write multiple values via array abstraction when «col», «row» and «value» share one or more indexes. The first parameter, «workbook» must be an object obtained from the SpreadsheetOpen() function.
For writing very large arrays, SpreadsheetSetRange is faster.
The «value» is written to the formulas of each cell. Using textual values beginning with a = character, you can thus set actual cell formula such as "=Sum(D4:D24)".
Examples
For these, «wb» is a variable defined as a call to SpreadsheetOpen, such as 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)
New in Analytica 4.5
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 the following:
"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