Excel spreadsheets read and write

Analytica User GuideIntegration with data and applicationsExcel spreadsheets read and write

These functions let you open an Excel spreadsheet to read or write data to or from Analytica and from or to spreadsheet cells and named ranges. You can find them in the Application Integration submenu of the Definitions menu. You need to have Microsoft Excel installed on your computer to use these functions.

SpreadsheetOpen(filename, showDialog)

Opens the indicated Excel workbook file. If the file isn't already open in Excel, it opens the file without Excel's user interface -- i.e. you won't see the spreadsheet in a Window. Set the optional «showDialog» parameter to true to force the display of the file selector dialog, or or false to suppress it. By default, it opens the file dialog only if it can't find «filename» in the current directory. It returns a special «workbook» object which you can use as a parameter to the other Excel functions. For more, see SpreadsheetOpen().

SpreadsheetSave(workbook, filename)

Saves an Excel workbook to a file. The «workbook» parameter must be a workbook object returned from a previous call to SpreadsheetOpen(). The workbook is saved to the same file it was originally loaded from when the «filename» parameter is omitted. This function is used after changes have been made to the workbook using SpreadsheetSetCell() or SpreadsheetSetRange(). For more, see SpreadsheetSave().

SpreadsheetCell(workbook, sheet, column, row, what)

Reads the value of one cell in an Excel worksheet given its coordinates. The function fully array abstracts so that you can use it to read an range of cells by specifying the «column» or «row» parameters as arrays. «Workbook» is the result of a call to SpreadsheetOpen. The «sheet» and «column» parameters may be either the text names or an ordinal number that identifies the sheet in the workbook or column in the sheet. For more, see SpreadsheetCell().

The following demonstrates equivalent methods for obtaining the value from cell C7 of the first worksheet, Sheet1:

SpreadsheetCell(workbook, 'Sheet1', 'C', 7)

SpreadsheetCell(workbook, 1, 3, 7)

The following reads the 2-D array of cells C3: J10 in Sheet1:

Index Year := 2008 .. 2015;

Index Asset := 1 .. 16;

SpreadsheetCell(workbook, 'Sheet1', @Year + 2, @Asset + 3)

You can obtain the contents of a particular cell coordinate from all sheets by specifying «sheet» at '*'. The result is indexed by a local index named .Sheet containing the sheet names. This is particularly useful for obtaining a list of worksheets.

Index Sheet := CopyIndex(SpreadsheetCell(workbook, '*', 1, 1).Sheet)

When the optional «what» parameter is not specified, the value of the cell is retrieved. Use «what» to read other types of cell information: <ocde>'Value', 'Formula', 'RelativeFormula', 'NumberFormat', 'TextColor', 'BackColor', 'FontName', 'FontSize', 'FontStyle', or 'Border[Left|Right|Up|Down][Color|Style|Weight]' (e.g., 'BorderRightColor').

SpreadsheetSetCell(workbook, sheet, col, row, value)

Writes «value» (either a number or text) to the cell identified by «sheet», «col», and «row». Can be used to write multiple values via array abstraction when «col», «row» and «value» are arrays that share one or more indexes. The first parameter, «workbook», must be an object obtained from a previous call to SpreadsheetOpen(). See also SpreadsheetSetCell().

To write the value 3.5 to cell Sheet1!C5:

SpreadsheetSetCell(workbook, 'Sheet1', 'C', 5, 3.5)

SpreadsheetRange(workbook, range, colIndex, rowIndex, howToIndex, sheet, what)

Reads a range of cells from an Excel workbook. The «range» can be the name of a named range in the Excel «workbook», range coordinates such as 'Sheet1!C7' or 'Sheet3!C7:F12', or all populated cells in a sheet as 'Sheet1!'. Alternatively, you can specify a «range» coordinate as 'C7: F12' and specify the sheet either by name or number in the optional «sheet» parameter. If you omit «range» but specify «sheet», then the smallest rectangular range of cells containing all populated cells is returned. When specifying a named range, it is not necessary to specify the worksheet name, but when using range coordinates the sheet must be identified.

Depending on the dimensions of the cells requested, the result may be a scalar (single cell), a column vector, a row vector, or a 2-D array. When the range contains multiple cells, the result is an array. When the optional «colIndex» or «rowIndex» parameters are not provided, local indexes are created for those dimensions when the range for the result contains more than one column or more than one row. If you already have indexes for those dimensions, you should provide them explicitly in the «colIndex» and «rolIndex» parameters.

The optional «howToIndex» parameter provides additional control over how the result is indexed and the range interpreted. It can be any additive combination of the following values:

1 = Force column index when range has only one column.

2 = Force row index when range has only one row.

4 = First row of range contains column labels

8 = First column of range contains row labels

16 = Do not issue error if supplied «colIndex» or «rowIndex» is not the correct length.

Suppose the range of cells, C7: F12 in sheet 'Projection', the third sheet in the Excel workbook, has been labelled in Excel with the name Costs. Then the following are each equivalent and return a 2-D array, indexed by local indexes .Row = [7, 8, 9, 10, 11, 12] and .Column = ['C', 'D', 'E', 'F']:

SpreadsheetRange(workbook, 'Costs')

SpreadsheetRange(workbook, 'Projection!C7: F12')

SpreadsheetRange(workbook, 'C7: F12', sheet: 'Projection')

SpreadsheetRange(workbook, 'C7: F12', sheet: 3)

When the optional «what» parameter is not specified, the value of the cell is retrieved. Use «what» to read other types of cell information: 'Value', 'Formula', 'RelativeFormula', 'Number-Format', 'TextColor', 'BackColor', 'FontName', 'FontSize', 'FontStyle', 'Border[ Left|Right|Up|Down][Color|Style|Weight]' (e.g., 'BorderRightColor'), 'Sheet', 'Address', 'AddressR1C1', or 'RangeName'. For more, see SpreadsheetRange().

SpreadsheetSetRange(workbook, range, value, colIndex, rowIndex, sheet)

Writes «value» (often an array of text or numbers) to a cell range or named range. «Workbook» mustbe an object obtained from a previous call to SpreadsheetOpen(). «Sheet» is the name or number of the worksheet containing the range. «Range may be a coordinate range (e.g., 'Sheet1!C7: F15', or when the optional «sheet» parameter is also specified «range» may be just 'C7: F15') or the name of a named range. «Value» is the value written, and «colIndex» and «rowIndex» are the indexes of «value» (when «value» is an array). See also SpreadsheetSetRange().

SpreadsheetInfo(workbook, item)

Reads a property of a workbook. «Workbook» must be an object obtained from SpreadsheetOpen. «Item» is one of the following

'AcceptLabelsInFormulas': Flag. 1 if cell formulas can use labels (named ranges) in their formulas. 0 if these are not allowed.

'ActiveSheet': Textual name of the active worksheet.

'Author': Author of «workbook».

'CalculationMode': This is either 'Automatic', 'Manual' or 'Semiautomatic',indicating whether cells are automatically recomputed when something changes. 'Semiautomatic' means that cells are calculated automatically except those that are part of a data table.

'CodeName': The textual code name field of the «workbook», if one has been specified.

'Date1904': Flag. 1 if the excel date origin is 1 Jan 1904. 0 if it is 1 Jan 1900.

'Filename': Full path and filename of the «workbook» file.

'Name': The filename part of the workbook.

'Names': A list of all the named ranges in the spreadsheet.

'ReadOnly': Flag. 1 if the workbook is read-only, 0 if it can be changed.

'Saved': Flag. 1 if there are no unsaved changes. 0 if it has been “dirtied”.

'SelectedRange': The range of cells that is currently selected. When you have the spreadsheet open interactively in Excel at the same time your model is using it, this can be used to detect which range of cells you have highlighted, and can be used as a way to make a selection. An example return value is 'Sheet1!B2: C4'.

SelectedRangeR1C1': The range of cells currently selected in R1C1 notation, such as 'Sheet1!R[1]C[1]:R[3]C[2]'.

'Sheets': A list of the worksheet names in the workbook.

'Title':: Textual title (if it has been filled in) for the workbook.

For more, seeSpreadsheetInfo().

SpreadsheetSetInfo(workbook, item, value)

Sets a property of a workbook. «Workbook» must be an object obtained from a previous call to SpreadsheetOpen. «Item» is one of the following

'ActiveSheet': specify the name of a worksheet for the «value» parameter. The worksheet with that name becomes selected.

'Author': sets the author property of the workbook to the indicated textual «value».

'CalculationMode': value should be one of 'Automatic', 'Manual' or 'Semiautomatic'. Sets the calculation mode.

'CodeName': Sets the textual code name property of the workbook.

'Date1904': Value should be 0 or 1. When 1 sets the date origin used by Excel to 1-Jan-1904, the same used by default by Analytica. When 0, uses 1-Jan-1900.

'SelectedRange': Selects an indicated range of cells. Can be used to indicate a range of cells to a user when Excel is open interactively. The value parameter should be something like 'Sheet1!B2: B4'.

'Title': Sets the textual title of the workbook.

For more, see SpreadsheetSetInfo().