Functions To Read Excel Worksheets

What's new in Analytica 4.2? >

What's new in Analytica 4.4? >

These functions let you to easily read and write cells and ranges from and to an Excel spreadsheet. They are generally simpler to use than OLE linking or ODBC, two alternative methods for integrating Analytica with spreadsheets. They may be also used from ADE.

These functions read from or write to the spreadsheet when evaluated, but do not maintain dependencies. So, if the spreadsheet data changes, it will not invalidate or cause recomputation of the Analytica results, as would a change in an Analytica variable, or a change in the spreadsheet using OLE linking. But, when you restart your model (or otherwise cause the functions to be re-evaluated), it will read the most recent data from your spreadsheet.

See also Functions to Write Data to Excel Worksheets.

SpreadsheetOpen( filename, showDialog )

Opens the Excel file at the indicated filename and returns a Workbook object for use by the other spreadsheet read and write functions. The result displays in a result table as <<ExcelWorkbook>>.

The filename parameter is relative to the CurrentDataDirectory.

If you set showDialog to true, it displays a file browser to let you specify the file interactively, even if the file filename exists. If showDialog is false (the default), it displays the dialog only if it can't find the file (similar to ReadTextFile).

The filename can also accept a workbook name that is currently open in Excel, even if it is not yet saved.

Example

SpreadsheetOpen( "C:\MyModels\Sales Numbers.xls" ) → «ExcelWorkbook»

Use with Office 2010

Use of Excel by an application such as Analytica may not be possible if you have installed the "Click-to-Run" version of Office 2010. This is a new "feature" of the 2010 version of Office that apparently disables lots of previously common operations. If you installed over the web using the click-to-run option, you may need to re-install the MSI-based version of office. Instructions for changing the Click-to-run edition to the MSI-based edition are found at:

http://office.microsoft.com/en-gb/excel-help/click-to-run-switch-to-using-a-full-office-edition-HA101850538.aspx

Remembering selected filename

When the file dialog appears for the user, and the user selects the file, your definition is not altered, so that the next time the user loads the model, he'll see the dailog and have to select the file again. You may want your model to remember which file the user selected, so the next time it will just load it without asking, or if you use showDialog:1 it will ask but will provide the previously selected file as the default. To do this, use the SpreadsheetOpenEx function in the Spreadsheet Helper Library.

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

Returns the value (or other information) of a cell of a worksheet given its coordinates. The function fully array abstracts, so you can get a range of cells by specifying the column and/or row as an array.

Parameters:

• «workbook»: A workbook object returned by SpreadsheetOpen()
• «sheet»: The name of a worksheet inside the workbook, or its number, where 1 is the first worksheet, etc.

If you specify sheet: "*", it returns the cell contents at column, row from all sheets in the workbook. The result is indexed by .Sheet, a local index containing the names of the worksheets. This is a way to get a list of all the worksheets in the workbook. If you specify column and/or rows as arrays, you can also use this to get a 3D array for a range over all worksheets.

• «column»: The column label, e.g., "A", "B", or "AB", or the column number as an integer.
• «row»: The row number as an integer
• «what»: optional. Let's you get the formula or format information from the cell. See below under SpreadsheetRange for details.

If the worksheet cell is empty, it returns Null. It flags an error if «workbook» is not a valid workbook, if it does not contain «sheet», or if the coordinates are invalid.

Note: Microsoft Excel does not automically propagate updates to Analytica the way Analytica variables do (and Excel cells do). So, if you make a change to the spreadsheet -- e.g. by resetting a value in a cell that affects the cell whose value you read using SpreadsheetCell() -- the value of the Analytica variable using SpreadsheetCell() will not update, unless you cause it to be recalculated explicitly.

Examples

These expressions are different ways to get the same result, the value from cell C7 in the first sheet, "Sheet1" of workbook:

SpreadsheetCell( workbook, "Sheet1", "C", 7 )
SpreadsheetCell( workbook, "Sheet1", 3, 7 )
SpreadsheetCell( workbook, 1, 3, 7 )

Suppose the spreadsheet contains a 2-D table in the region C4:J19. The columns of this table correspond to the years 2008..2015. The rows correspond to different assets. It is easier to refer to the columns by number, so that the columns "C" thru "J" are columns 3 thru 10. To hold this 2-D table, we need two indexes in Analytica, Time and Asset.

 Index Time := 2008..2015
 Index Asset := 1..16
 Variable Workbook := SpreadsheetOpen( "C:\Asset Data.xls" )
 Variable Data := SpreadsheetCell( workbook, "Sheet1", @Time+2, @Asset+3 )

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

Returns the values (or other information) for a range of cells from an Excel worksheet. The «range» can be can be a cell range such as "C7" or "C7:F12", or the name of a range defined in the spreadsheet. If you want to read or write several cells or ranges in a spreadsheet, it is often convenient to use Excel's name mechanism and refer to them by name in Analytica.

Parameters:

• «workbook»: A workbook object returned by SpreadsheetOpen() may be a named range label in the spreadsheet, or it
• «range»: A cell range. This may be of the form "Sheet1!A1:BC99", "A1:BC99", or the name defined in the spreadsheet for a cell. If you don't mention the sheet name, you must specify «sheet» as a parameter.

If you specify the range as "Sheet1!", with nothing following the "!", or omit «range» and specify only «sheet», it returns the smallest rectangular range that includes all used cells within the sheet.

• «colIndex»: (optional) A pre-existing index for indexing the column dimension of the result.
• «rowIndex»: (optional) A pre-existing index for indexing the row dimension of the result.
• «howToIndex»: (optional) A set of flags controlling how the result is indexed when colIndex or rowIndex is not specified. Any of the following values can be added together for this parameter:
  • 1 = Force column index. Forces a column index to be included even if the range spans only a single column. Has no effect if colIndex is specified.
  • 2 = Force row index. Forces a row index to be included even if the range spans only a single row. Has no effect if rowIndex is specified.
  • 4 = Use first row for column labels: The first row in the range is not included in the result returned, but the values in the first row are used as the column labels for the local index .Column.
  • 8 = Use first column for row labels: The first column in the range is not included in the result returned, but the values from the first column are used as row labels for the local index .Row.
  • 16 = Do not issue an error if the supplied colIndex or rowIndex are not the correct length.
• «sheet»: The name of a worksheet inside the workbook, or a number.
• «what»: optional. (new to Analytica 4.3) Indicates whether you want to retrieve the value or the formula. Possible values:
  • "Value": The computed value. In Analytica 4.3 and later, dates are read as date-time numbers, and thus display in Analytica as dates.
  • "NumericValue": The computed value, but the numeric value of dates is read, so they display as numbers.
  • "Formula": The textual cell formula in the format you normally see in the Excel interface, e.g., "=Sum(D4:D10)"
  • "RelativeFormula": The cell formula in relative offset format, e.g., "=Sum(RC[-9]:R[+6]C[-9])"

  These require Analytica 4.4:

  • "NumberFormat": Textual description of the cell number format
  • "BackColor": Cell background color, integer value: red*65536 + green*256 + blue
  • "Text Color": Font color, integer value: red*65536 + green*256 + blue
  • "FontName": Name of the font used to display the cell
  • "FontSize": Point size of font used to display the cell
  • "FontStyle": Special font styles for cell separated by spaces, may include "bold italic underline strikethrough subscript superscript outline shadow"
  • "WrapText": 0 or 1 indicating whether text should be word wrapped in the cell.
  • "Border[Left|Right|Up|Down]Color": Color of the indicated side of the border. E.g., "BorderLeftColor" returns an integer equal to red*65535+green*256+blue
  • "Border[Left|Right|Up|Down]Style": Style of indicated border, or Null if not set. May be "Solid", "Dash", "DashDot", "DashDotDot", "Dot", "Double", or "SlantDashDot".
  • "Border[Left|Right|Up|Down]Weight": Thickness of indicated border, usually between 1 and 4
  • "DirectPrecedents": Description of cells that are referenced directly by the cell formula in the cell. This includes only precedents in the same worksheet. It unfortunately does not include references to cells appearing on other sheets. Each cell or cell range is separated by a comma.
  • "DirectPrecedentsRelative": Same as "DirectPrecedents", but cells are identified by their offset relative to the current cell, e.g., R[-3]C[6].
  • "DirectDependents": Description of cells that reference the given cell in their formula. This includes only dependents in the worksheet. It unfortunately does not include references to dependents appearing in other sheets. Each cell or cell range description is separated by a comma.
  • "DirectDependentsRelative": Same as "DirectDependents", but cells are identified by their offset relative to the current cell, e.g., R[-3]C[6].
  • "Precedents": Description of all cells in the current worksheet that the given cell formula depends on directly or indirectly. It does not include cells reached by paths passing through other sheets.
  • "PrecedentsRelative": Same as "Precedents", but cells are identified by their offset relative to the current cell, e.g., R[-3]C[6].
  • "Descendants": Description of all cells in the current worksheet that depend directly or indirectly on the given cell. It does not include cells reached by paths passing through other sheets.
  • "DirectDescendantsRelative": Same as "DirectDescendants", but cells are identified by their offset relative to the current cell, e.g., R[-3]C[6].

The «what» parameter defaults to "Value" when not specified.

Note: Options 4, 8 and 16 require release 4.1.1 or later.

Depending on the dimensions of the cell range, the result may be a scalar (single cell), a column vector, a row vector, or a 2-D array.

If the range spans more than one column in the spreadsheet, the result must be indexed by a column index. If you already have this index, you can specify it in the optional colIndex parameter; otherwise, it creates a local index named .Column as a dimension of the result. By default, the elements of the column index are set to the sheet's column labels, e.g., "A","B",...."AA","AB",..]. If desired, you can also use the first row of values as elements for the .Column index (this row is then not included in the actual array retrieved -- thus should only be used when the range spans at least two rows). To use the first row for the elements of the local column index, specify howToIndex:4 and leave the «colIndex» parameter unspecified.

If your result falls entirely within a single column, but you wish to force a local column index to be included, so that the result is a 2-dimensional array of size 1xR, rather than a 1-D vector of length R, then specify howToIndex:1. If you are using a named range and don't know how many columns there actually are, and it is possible there is only one, then you would want to force a column index so that an error doesn't occur if you use result.Column in an expression.

If the range spans more than one row in the spreadsheet, then a row index must dimension the result. If you already have this index, specify it in the rowIndex parameter; otherwise, if the range spans more than one row, a local index named .Row is created by the function and used to dimension the result. The local .Row index that is created contains the row numbers from the spreadsheet as its elements, so for example, if the range is "C7:F12", the .Row index would contain the elements [7,8,9,10,11,12]. To force the creation of a local .Row index when the result spans only a single row, specify howToIndex:2. To use the values from the first column as the elements of the local .Row index, specify howToIndex:8.

If you specify «colIndex» or «rowIndex», and the length of the indexes do not match the length of the specified range, the range is truncated if the index is too short, or the result filled out with Null values if the index is too long. If the flag howToIndex:16 is specified, it issues an error if the index length does not match.

Empty cells in the range are Null in the result.

No dependency is maintained between the original data in the spreadsheet and the result. The data retrieved are the value that were there at the time the function is evaluated. If the data in the spreadsheet changes after the function has been evaluated, the results are not invalidated and continue to retain the original values.

(Requires Analytica 4.4 or later) You can obtain the entire used range of a worksheet named "Sheet1" by specifying the «range» as "Sheet1!" or by omitting the «range» parameter and specifying just the «sheet» parameter.

Examples

These example use the following spreadsheet:

In the above spreadsheet, several cell ranges are labelled as named ranges, as follows:

Label	Range
Rate	B1
Year	B3:F3
Cash_flow	B4:F4
Divisions	A7:A9
Employee_count	B7:F9

SpreadsheetRange( wb, "Rate" ) → 0.08

SpreadsheetRange( wb, "Sheet1!B1") → 0.08

SpreadsheetRange( wb, "Sheet1!B3:F3" ) →

.Column →	'B'	'C'	'D'	'E'	'F'
	2008	2009	2010	2011	2012

Index Year := CopyIndex( SpreadsheetRange( wb, "Year", howToIndex:1 ) );
SpreadsheetRange( wb, "Cash_flow", colIndex:Year ) 
→

.Year →	2008	2009	2010	2011	2012
	-100	10	30	50	60

Note: howToIndex:1 was specified for Year here so that we would have a 1-D array even if only one year were present in the spreadsheet.

SpreadsheetRange( wb, "Employee_count" ) →

	.Column →	'B'	'C'	'D'	'E'	'F'
.Row ↓	7	24	27	28	32	35
	8	13	13	13	13	13
	9	25	22	21	19	16

Index Time := [2008,2009,2010,2011,2012];
SpreadsheetRange( wb, "A7:F9", colIndex:Time, howToIndex:8, sheet:1)
→

	Time →	2008	2009	2010	2011	2012
.Row ↓	"Div A"	24	27	28	32	35
	"Div B"	13	13	13	13	13
	"Div C"	25	22	21	19	16

The examples below require Analytica 4.4 or later:

To obtain the list of worksheet names:

SpreadsheetCell(wb, "*", 1, 1).Sheet

To obtain all used cells in sheet named "Sheet2":

SpreadsheetRange(wb,sheet:"Sheet2")

To obtain the number format of all cells in "Sheet2":

SpreadsheetRange(wb,sheet:"Sheet2",what:"NumberFormat")

Legacy Notes

Functions for reading cells from Excel were first present in Analytica 4.1 with functions named OpenExcelFile, WorksheetCell and WorksheetRange, although these were labelled as experimental, and the present functions were not officially available until 4.2.0. The old names are now deprecated, replaced with SpreadsheetOpen, SpreadsheetCell and SpreadsheetRange. The old functions still work at present, but may be removed in future Analytica builds. The parameters have changed slightly from WorksheetRange to SpreadsheetRange, with the sheet parameter moved from being the second to being the last parameter and now optional -- no longer required for named ranges or ranges of the form "Sheet1!A1:Z99".

See Also

• Functions to Write Data to Excel Worksheets -- SpreadsheetSetCell, SpreadsheetSetRange and SpreadsheetSave
• DbQuery -- ODBC can also be used to read from Excel worksheets
• ReadTextFile
• ReadCsvFile
• Excel Functions from ADE