Functions To Read Excel Worksheets

(Redirected from SpreadsheetRange)



These functions let you open an Excel spreadsheet file, and read cells and ranges from it. For writing to a spreadsheet, see Functions to Write Data to Excel Worksheets.

SpreadsheetOpen(filename, showDialog, title)

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

Unless you include a complete file path in «filename», it looks for it in the CurrentDataFolder.

If you omit optional parameter «showDialog», it opens the file browser dialog to let you find a spreadsheet only if it can't find the file (as does ReadTextFile). If you set «showDialog» to true (1), it opens the file browser even if it finds the file «filename». If «showDialog» is false (0), it never prompts you even if it can't find the file. In each case, it flags an error if it doesn't end up opening a file.

(new to Analytica 5.0) You can customize the caption to the open file dialog by passing the caption text to the optional «title» parameter.

«filename» can also be the name of a workbook that is currently open in Excel, even if it has not yet been saved.

Example

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

Use with Office 2010

If you have installed the "Click-to-Run" version of Office 2010 from a web download, these spreadsheet functions may not work, due to a "feature" introduced in Office 2010 that apparently disables several common operations. In this case, you may need to re-install Office using the MSI-based edition. See how to do this at:

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

Excel 64-bit requires Analytica 64-bit

Analytica 32-bit cannot launch Excel 64-bit. (The other way around works). Thus, if you have installed Excel 64-bit (which we recommend), make sure you have installed Analytica 64-bit. If you are a Free Edition user, you probably have 32-bit installed, but you can install Analytica 64-bit from the Analytica Downloads page.

Remembering the selected filename

When SpreadsheetOpen() shows the file dialog and you select a file, it does not save the file name. So, the next time you load the model, you'll have to select the file again. If you want the model to remember the selected file, so it will just load it without asking, prompt using that file name as the default, you can use the SpreadsheetOpenEx function in the Spreadsheet Helper Library.

Having same spreadsheet open in Excel at the same time

It is often useful to have the spreadsheet you are working with open in Excel at the same time your model is working with it. When you want to do this, is it best to open it Excel first, before evaluating SpreadsheetOpen, in which case SpreadsheetOpen connects to the existing Excel process and to the currently open spreadsheet. If you change cells in Excel, then evaluate a spreadsheet read functions, you'll read the new values, and if your model writes to the spreadsheet, you'll see those values reflected immediately in the Excel interface.

When you call SpreadsheetOpen before opening the model in Excel, the situation is more complex. To understand what happens and how to view the same model in the Excel UI at the same time, see Simultaneously opening a spreadsheet in Excel and Analytica.

SpreadsheetOpenFlags

A registry setting named SpreadsheetOpenFlags can be set to alter how SpreadsheetOpen connects to Excel and the initial settings in Excel. There is usually no reason to fiddle with these flags unless you encounter a specific problem. It has been more common to set these flags in server-based applications using ADE than from desktop Analytica.

You'll need to modify the sitting from RegEdit. You can set it in either

HKEY_LOCAL_MACHINE/Software/Lumina Decision Systems/Analytica/6.1

or

HKEY_CURRENT_USER/Software/Lumina Decision Systems/Analytica/6.1

For ADE, set it in one of these hives:

HKEY_LOCAL_MACHINE/Software/Lumina Decision Systems/Analytica/6.1
HKEY_CURRENT_USER/Software/Lumina Decision Systems/Analytica/6.1

Setting it in HKLM causes it to apply from any account on your computer, while setting it from HKCU causes it to apply only to your own account. A setting in HKCU takes precedence over the same setting in HKLM.

Initially the value SpreadsheetOpenFlags will not be present. Create a new 32-bit DWORD with this name. The set the numeric value to an addition of any of these flags that you want:

  • 1 = Launch using a COMCreateObject mechanism. (unset)=Launch using a BindToObject method.
    A BindToObject method (the default for Desktop Analytica) makes it possible to connect to a Workbook running in an active Excel UI. A COMCreateObject mechanism launches a separate instance of Excel every time.
  • 2 = Turn off Excel's Interactive flag.
  • 4 = Turn off Excel's "Ask to update OLE links" flag.
  • 8 = Turn off Excel's "Display Alerts"
  • 16 = Disable Excel macros (for security)
  • 32 = Close when visible.
    Normally, if the workbook is currently visible in an Excel UI, Analytica simply disconnects from it, but doesn't force the workbook to close. The Excel UI is then responsible for eventually closing it. This overrides this and forces the workbook to close when the model releases it, even if it is visible.


From ADE

When using from ADE on a Web Server, we strongly advise against using Excel 2016 on the server. Excel 2010 works fairly well, but Excel 2016 is extremely unstable and has a tendency to fail unpredictably and lock up all other Excel instances. Microsoft responds by saying that Excel 2016 is not supported nor licensed for use on a web server.

When SpreadsheetOpen is evaluated in the Analytica Decision Engine (ADE) and a dialog needs to be shown to the end-user, it calls IAdeUICallbacks::GetFilename(...). From within that callback, the parent application can interact with the end-user to resolve the file path, and a web applications can instruct the end-user to upload a file. Once complete, the callback returns the full path to the file which is then read. To receive this callback, the parent application must have previously registered the callback with ADE using CAEngine::SetCallbackObject( ). If it has not registered a callback and the file doesn't exist, returns an empty text.

Once the open completes, it calls IAdeUICallbacks::FileOpenCompleted().

Debugging Errors

This section documents failures when SpreadsheetOpen has been unable to open Excel, and solutions.

  • Library not registered:
    If this error occurs when SpreadsheetOpen is evaluated...
    • The article Library not registered error explains how to solve this problem when it is caused by an Excel plug-in. It may be caused by a bad Excel add-in library. You should also run excel.exe /regserver.
    • In one case, an Analytica user concluded that an older version of Excel was interfering with his newer 32-bit version of Excel. He uninstalled both and re-installed Excel 64-bit and the problem corrected itself. But, for a different user with this problem, these steps did not correct the problem.
    • A common cause of this problem is when stray registry settings from Excel versions that had been installed and uninstalled interfere with your current version of Excel. This is most common after you roll back to an earlier release after uninstalling a later release. To test for this cause, start Power Shell and run:
      get-childitem -Path "HKLM:\Software\Classes\TypeLib\{00020813-0000-0000-C000-000000000046}"
If you see more than one version listed, with the most recent version number missing its mapping to Excel, then this is probably the cause. To fix, use RegEdit to delete the hive for the later version number.

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 or number of a worksheet from the workbook. Number 1 is the first worksheet, etc.
If you specify sheet: "*", it returns the cell value from column, row for all sheets in the workbook, 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.

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 address such as "C7" or cell range "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.

If the range has multiple columns, the result has local index .Column unless you specify «colindex» as a parameter. Similarly, if the range has multiple rows, the result has local index .Row unless you specify «rowindex» as a parameter. Flags in «howToIndex» let you control whether the first row (column) should be used as labels for local index .Row or .Column.

If you specify a sheet name with no cells, e.g. "Inputs!", it returns a table that includes all cells from that sheet that contain anything.

By default, it returns the number or text values from the range (or NULL if the cell is empty). You can use the «what» parameter to obtain the cell formula, address, format, styles, precedent, and dependent cells for each cell.

SpreadsheetRange Parameters

SpreadsheetRange has two required parameters:

«workbook»
A workbook object returned by SpreadsheetOpen()
«range»
A cell range. It may be a single cell address, e.g. "B10", a range, e.g. "A1:BC99", optionally with sheet name, e.g. "Sheet1!A1:BC99", or a named range, e.g. "Discount_rate" defined in the spreadsheet. If the «range» doesn't mention the sheet name, you must specify «sheet» as a separate parameter.
If you specify the range as "Sheet1!", with nothing after the "!", or omit «range» and specify only «sheet», it returns the smallest rectangular range that includes all used cells within the sheet.

SpreadsheetRange has four optional parameters relating to the indexes for a range with multiple columns or rows, or over multiple sheets:

«colIndex»
(optional) An index to use for the column dimension of the result.
«rowIndex»
(optional) An index to use for the row dimension of the result.
«howToIndex»
(optional) Flags controlling how to index the result when «colIndex» or «rowIndex» are not specified. You can add any of these values to combine their effects:
1: Force a column index even if the range spans only a single column. Has no effect if you specify «colIndex».
2: Force a row index even if the range spans only a single row. Has no effect if you specify «rowIndex».
4: Use the first row of «range» as column labels in the local index .Column. Exclude this first row in the result returned.
8: Use the first column of «range» as labels in the local index .Row. Exclude this first column in the result returned..
16: Suppress the error message that is otherwise given if the sizes of «colIndex» or «rowIndex» do not match the size of the range.
«sheet»
(optional) The name or number of a worksheet inside the workbook. It can be a list of sheets, in which case, the function will return a 3D table, indexed by this list as the third dimension.
«what»
(optional) See below for details on this parameter.

Indexes of a cell range

The result may be a scalar (single cell), a column vector, a row vector, or a 2-D array, depending on the dimensions of the cell range. If the range has more than one row (or column), it will use a local index .Row (.Column) by default. By default, the elements of the .Row index contain the range's row numbers and elements of the column index contain its column labels. For example, if the range is "C7:E12", .Row would contain the elements [7, 8, 9, 10, 11, 12] and .Column would contain ['C', 'D', 'E'].

Or, you can use the first column (row) of the range as the values for the local index .Row (.Column), by specifying howToIndex: 8 (howToIndex: 4, or howToIndex: 12 for both .Row and .Column.) If you use, the first row (column) of the range as values of the local indexe(es), they will not be included in the value of the array returned. So, in that case, the range must have at least two rows (columns).

Alternatively, if you already have index(es), you can supply them to the «rowIndex» («colIndex») parameters. If you specify a «rowIndex» or «colIndex», that is shorter than the number of rows (columns) in the range, it truncates the result. If an index is too long, it pads the result with Null. In these cases, it gives a warning message unless you set flag howToIndex: 16.

If the range has just one column, the result normally will not have a local .Column index. But, you can force it to use a .Column with one element by setting howToIndex: 1. If you are using a named range and don't know how many columns it has, you might use this option to prevent an error occurring if you use result.Column in an expression. Similarly, you can force it to use local .Row index even when the result has only a single row by specifying howToIndex: 2.

You can obtain the entire range of a worksheet with all cells that contain anything named "Sheet1" by specifying the «range» as "Sheet1!" or by omitting the «range» parameter and specifying just the «sheet» parameter.

Examples

The following examples use this spreadsheet:

WorksheetRange ExcelShot.jpg

This spreadsheet contains these named ranges:

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

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")

SpreadsheetRange «what» parameter

By default, SpreadsheetRange() returns the value of the cell(s) in the range, but you can use the «what» parameter to obtain the formula, cell style and formats, cell address, predecessor or dependent cells of each cell:

«what»
(optional). By default, SpreadsheetRange returns the value of the range, but you can use this parameter to obtain its formula, or cell style parameters. Possible values:
"Value": (Default) The computed value. Excel dates become Analytica date-time numbers, which display as dates.
"NumericValue": The computed value, but dates are returned as numbers.
"Formula": The cell formula as a text value in the normal Excel format starting with "=", e.g., "=Sum(D4:D10)"
"RelativeFormula": The cell formula using relative offset format, e.g., "=Sum(RC[-9]:R[+6]C[-9])"

«what» parameter cell formats

"NumberFormat": The cell number format as text.
"BackColor": Cell background color as integer: red*65536 + green*256 + blue
"Text Color": Font color as an integer: red*65536 + green*256 + blue
"FontName": Name of the font used to display the cell
"FontSize": Point size of the font displayed in the cell
"FontStyle": Special font styles for cell separated by spaces, may include "bold italic underline strikethrough subscript superscript outline shadow"
"HorizontalAlignment": Text justification, one of: 'Left', 'Center', 'Right', 'Justify', 'Distributed', 'Fill'. (new to Analytica 5.0)
"VerticalAlignment": Text vertical justification, one of: 'Top', 'Middle', 'Bottom', 'Justify', 'Distributed'. (new to Analytica 5.0)
"WrapText": 0 or 1 controls whether text is word wrapped to fit in the cell.
"Border(Left|Right|Up|Down)" show a border to left, right, above, or below the cell.
"Border(Left|Right|Up|Down)Color": Return the color of the specified side of the border as an RGB number -- 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

«what» parameter cell addresses

"Address": The address of the cell range, e.g., "B12:C13"
"AddressR1C1": The address of the cell range in R1C1 format, e.g., "R12C2:R13C3"
"Sheet": The sheet name where the cell range exists.
"RangeName": The name of the range, if it is a named range.

«what» parameter cell precedents and dependents

"DirectPrecedents": Addresses of all cells mentioned in the cell formula, separated by commas. Unfortunately, Excel lists only cells in the same sheet, but not precedents in other sheets.
"DirectPrecedentsRelative": Same as "DirectPrecedents", but cells are given by their offset relative to the current cell, e.g., R[-3]C[6].
"DirectDependents": Addresses of all cells whose formula mentions this cell, separated by commas. Unfortunately, Excel lists only cells in the same sheet, but not dependents in other sheets.
"DirectDependentsRelative": Same as "DirectDependents", but cells are identified by their offset relative to the current cell, e.g., R[-3]C[6].
"Precedents": Addresses of all cells in the current worksheet mentioned in the formula of this cell and the formulas of its direct precedents. 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]</code..
"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].

Errors in SpreadsheetRange parameters

In a call to SpreadsheetRange(wb, range):

  • If range refers to a sheet, e.g. "sheet!x" that does not exist in workbook wb, it gives an error message saying "the worksheet 'sheet' was not found."
  • If range refers to a named range, e.g. "sheet!x" that does not exist in workbook wb, it gives an error message saying "the indicated named cell range, 'x', was not found."
  • If range refers to a cell address with bad syntax, e.g. "ted!A1:R3C6", it gives an error message saying "the range named A1:R3C6 was not found in Excel worksheet 'ted'."

SpreadsheetInfo(workbook, item)

SpreadsheetInfo gets various kinds of information about the spreadsheet («workbook») specified by parameter «item»:

item Description
"AcceptLabelsInFormulas" True when you can use labels in worksheet formulas. This is usually false.
"ActiveSheet" The number of the active (displayed) worksheet.
"Author" The name of the author, usually the name of the person who created the spreadsheet as recorded by Windows OS.
"CalculationMode" The calculation mode set for the workbook, which may be "Automatic", "Manual" or "Semiautomatic", meaning automatic except for data tables.
"CalculationState" (5.0) The current state of Excel's calculation engine, either "Calculating", "Pending" or "Done".
"CalculationVersion" (5.0) Number equal to 10000 * major + minor, encoding the version of the Excel calculation engine that the current workbook was last calculated in. If it was saved in an earlier version of Excel and hasn't yet been fully calculated, the value is 0. You can compare this to the "Excel.CalculationVersion" to determine whether it was last re-calculated using the same calculation engine as your current installed Excel.
"CodeName"
"Date1904" The base for dates used in the workbook.
"DecimalSeparator" (5.0) The character used to separate a whole number from its fractional part. In English-speaking countries this is '.' (a dot).
"Excel.CalculationVersion" (5.0) Number equal to 10000 * major + minor, encoding the version of calculation engine for your installed version of Excel.
"Filename" The name of the file, including the full file path.
"Name" The name of the file, without the file path.
"Names" A list of all the named ranges.
"OperatingSystem" (5.0) The name of the operating system that your Excel instance is running on, as reported by Excel.
"ReadOnly" True (1) if the file is saved as Readonly.
"Saved" False (0) if it has unsaved changes.
"SelectedRange" The currently selected Range.
"SelectedRangeR1C1" The currently selected Range specified by row and column number.
"Sheets" A list of the names of all the worksheets
"ThousandsSeparator" (5.0) The character Excel uses to group thousands when displaying a large number. In English-speaking countries this is ',' (a comma). For example, in the number 1,234,456.78, groups of thousands are separated by commas.
"Title" The title of the spreadsheet
"UseSystemSeparators" (5.0) True when Excel uses "DecimalSeparator" and "ThousandsSeparator" for displaying numbers.
"Version" (5.0) The version number (text) for the installed release of Excel. Excel 2010 is "14.0", Excel 2013 is "15.0" and Excel 2016 is "16.0".
"Visible" True when the Excel UI is visible.

The items above marked with (5.0) require Analytica 5.0 or better.

History

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, but may be removed in future Analytica releases. 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".

SpreadsheetInfo was introduced in Analytica 4.5. These options to SpreadsheetInfo were added in Analytica 5.0: "Version", "CalculationVersion", "Excel.CalculationVersion", "UseSystemSeparators", "DecimalSeparator", "ThousandsSeparator", and "CalculationState".

The color options for «what» incorrectly returned numbers in 0x00bbggrr order, instead of 0x00rrggbb order prior to Analytica 5.0. (This was a bug -- the documentation stated it should be 0x00rrggbb). Various options to the «what» parameter of SpreadsheetCell and SpreadsheetRange have appeared at different releases. The options 'HorizontalAlignment' and 'VerticalAlignment' appeared in Analytica 5.0. Options 'Address', 'AddressR1C1', 'Sheet' and 'RangeName' appeared in Analytica 4.6. The remaining options appeared in Analytica 4.4, except for 'Value', 'NumericValue', 'Formula'' and 'RelativeFormula', which appeared when the «what» parameter was introduced in Analytica 4.3.

See Also
Comments


You are not allowed to post comments.