View source for Functions To Read Excel Worksheets

[[Category:Excel to Analytica mappings]]
[[Category:Doc Status C]] <!-- For Lumina use, do not change -->

__TOC__


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]].

<div id="SpreadsheetOpen" >
== SpreadsheetOpen(filename'', showDialog, title'') ==
</div>

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 (<code>1</code>), it opens the file browser even if it finds the file «filename».  If «showDialog» is false (<code>0</code>), 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 ===

:<code>SpreadsheetOpen("C:\MyModels\Sales Numbers.xls") &rarr; ''«ExcelWorkbook»''</code>

<div id="SpreadsheetCell">

=== 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 http://office.microsoft.com/en-gb/excel-help/click-to-run-switch-to-using-a-full-office-edition-HA101850538.aspx]

=== 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 [[Analytica_Libraries_and_Templates#Spreadsheet_Helper_Library|Spreadsheet Helper Library]].

== SpreadsheetCell(workbook, sheet, column, row'', what'') ==
</div>

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 <code>sheet: "*"</code>, it returns the cell value from ''column, row'' for all sheets in the workbook, indexed by <code>.Sheet</code>, 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., <code>"A", "B"</code>, or <code>"AB"</code>, 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, <code>"Sheet1"</code> of workbook:
:<code>SpreadsheetCell(workbook, "Sheet1", "C", 7)</code>
:<code>SpreadsheetCell(workbook, "Sheet1", 3, 7)</code>
:<code>SpreadsheetCell(workbook, 1, 3, 7)</code>

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, <code>Time</code> and <code>Asset</code>.  

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

<div id="SpreadsheetRange">

== SpreadsheetRange(workbook, range'', colIndex, rowIndex, howToIndex, sheet, what'') ==
</div>

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 <code>"C7"</code> or cell range <code>"C7:F12"</code>, 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 <code>.Column</code> unless you specify «colindex» as a parameter. Similarly, if the range has multiple rows, the result has local index <code>.Row</code> 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  <code>.Row</code> or <code>.Column</code>.  

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

Normally, it returns the number or text values from the range (or <code>NULL</code> if the cell is empty). You can use  «what» to request the formula or cell format information for each cell.

Parameters:
;«workbook»: A workbook object returned by [[SpreadsheetOpen]]()
; «range»: A cell range.  It may be a single cell address, e.g. <code>"B10"</code>, a range, e.g. <code>"A1:BC99"</code>, optionally with sheet name, e.g.  <code>"Sheet1!A1:BC99"</code>, or a named range, e.g. <code>"Discount_rate"</code> 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 <code>"Sheet1!"</code>, 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. 
;«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:
::<code>1</code>: Force a column index even if the range spans only a single column. Has no effect if you specify «colIndex».
::<code> 2</code>: Force a row index even if the range spans only a single row.  Has no effect if you specify «rowIndex».
::<code>4</code>: Use the first row of «range» as column labels in the local index <code>.Column</code>. Exclude this first row in the result returned.
::<code>8</code>: Use the first column of «range» as labels in the local index <code>.Row</code>. Exclude this first column in the result returned..
::<code>16</code>: 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). 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: 
::<code>"Value"</code>: (Default) The computed value.  It reads Dates as date-time numbers, which display as dates  in Analytica.
::<code>"NumericValue"</code>: The computed value, but dates are returned as numbers.
::<code>"Formula"</code>: The cell formula as text in the normal Excel format starting with "=", e.g., <code>"=Sum(D4:D10)"</code>
::<code>"RelativeFormula"</code>: The cell formula in relative offset format, e.g., <code>"=Sum(RC[-9]:R[+6]C[-9])"</code>
::<code>"NumberFormat"</code>: The cell number format as text.
::<code>"BackColor"</code>: Cell background color, integer value: ''red*65536 + green*256 + blue''
::<code>"Text Color"</code>: Font color, integer value: ''red*65536 + green*256 + blue''
::<code>"FontName"</code>: Name of the font used to display the cell
::<code>"FontSize"</code>: Point size of font used to display the cell
::<code>"FontStyle"</code>: Special font styles for cell separated by spaces, may include "bold italic underline strikethrough subscript superscript outline shadow"
::<code>"WrapText"</code>: <code>0</code> or <code>1</code> controls whether text is word wrapped to fit in the cell.
::<code>"Border (Left|Right|Up|Down) Color": Color of the indicated side of the border.  E.g., <code>"BorderLeftColor"</code> returns an integer equal to ''red*65535+green*256+blue''
::<code>"Border[Left|Right|Up|Down]Style"</code>: Style of indicated border, or [[Null]] if not set. May be <code>"Solid", "Dash", "DashDot", "DashDotDot", "Dot", "Double"</code>, or <code>"SlantDashDot"</code>.
::<code>"Border[Left|Right|Up|Down]Weight"</code>: Thickness of indicated border, usually between 1 and 4
::<code>"DirectPrecedents"</code>: 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.  
::<code>"DirectPrecedentsRelative"</code>: Same as "DirectPrecedents", but cells are given by their offset relative to the current cell, e.g., <code>R[-3]C[6]</code>.
::<code>"DirectDependents"</code>: 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. 
::<code>"DirectDependentsRelative"</code>: Same as <code>"DirectDependents"</code>, but cells are identified by their offset relative to the current cell, e.g., <code>R[-3]C[6]</code>.
::<code>"Precedents"</code>:  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.
::<code>"PrecedentsRelative"</code>: Same as <code>"Precedents"</code>, but cells are identified by their offset relative to the current cell, e.g., <code>R[-3]C[6]</code..
::<code>"Descendants"</code>: 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.
::<code>"DirectDescendantsRelative"</code>: Same as <code>"DirectDescendants"</code>, but cells are identified by their offset relative to the current cell, e.g., <code>R[-3]C[6]</code>.
::<code>"Address"</code>: The address of the cell range, e.g., <code>"B12:C13"</code>
::<code>"AddressR1C1"</code>: The address of the cell range in R1C1 format, e.g., <code>"R12C2:R13C3"</code>
::<code>"Sheet"</code>: The sheet name where the cell range exists.
::<code>"RangeName"</code>: The name of the range, if it is a named range. 
::<code>"HorizontalAlignment"</code>: Text justification, one of: <code>'Left', 'Center', 'Right', 'Justify', 'Distributed', 'Fill'</code>.  (''new to [[Analytica 5.0]]'')
::<code>"VerticalAlignment"</code>: Text vertical justification, one of: <code>'Top', 'Middle', 'Bottom', 'Justify', 'Distributed'</code>.  (''new to [[Analytica 5.0]]'')

=== Indexes of a cell range ===

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 <code>.Column</code> as a dimension of the result.  By default, the elements of the column index are set to the sheet's column labels, e.g., <code>["A","B",...."AA","AB",..]</code>.  If desired, you can also use the first row of values as elements for the <code>.Column</code> 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 <code>howToIndex: 4</code> 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 <code>howToIndex: 1</code>.  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 [[Dot_operator::A.I|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 <code>.Row</code> is created by the function and used to dimension the result.  The local <code>.Row</code> index that is created contains the row numbers from the spreadsheet as its elements, so for example, if the range is <code>"C7:F12"</code>, the <code>.Row</code> index would contain the elements <code>[7, 8, 9, 10, 11, 12]</code>. To force the creation of a local <code>.Row</code> index when the result spans only a single row, specify <code>howToIndex: 2</code>.  To use the values from the first column as the elements of the local <code>.Row</code> index, specify <code>howToIndex: 8</code>.

If you specify «colIndex» or «rowIndex», and the size of the indexes don't match the length of the range, it  truncates the result if the index is too short, or pads it out with [[Null]] values if the index is too long.  It gives an error message unless the flag <code>''howToIndex: 16''</code>.

You can obtain the entire used range of a worksheet named <code>"Sheet1"</code> by specifying the «range» as <code>"Sheet1!"</code> or by omitting the «range» parameter and specifying just the «sheet» parameter.

=== Examples ===

The following examples use this spreadsheet:

:[[Image:WorksheetRange ExcelShot.jpg]]

This spreadsheet contains these named ranges:
:{| class="wikitable"
! Label !! Range 
|-
| Rate || B1
|-
| Year || B3:F3
|-
| Cash_flow || B4:F4
|-
| Divisions || A7:A9
|-
| Employee_count || B7:F9
|}

:<code>SpreadsheetRange(wb, "Rate") &rarr; 0.08</code>
:<code>SpreadsheetRange(wb, "Sheet1!B1") &rarr; 0.08</code>
:<code>SpreadsheetRange(wb, "Sheet1!B3:F3") &rarr;</code>
:{| class="wikitable"
 ! .Column &rarr; !! 'B' !! 'C' !! 'D' !! 'E' !! 'F'
 |-
 | || 2008 || 2009 || 2010 || 2011 || 2012
 |}

:<code>Index Year := CopyIndex( SpreadsheetRange(wb, "Year", howToIndex: 1));</code>
:<code>SpreadsheetRange(wb, "Cash_flow", colIndex: Year) &rarr;</code>
:{| class="wikitable"
 ! .Year &rarr; !! 2008 !! 2009 !! 2010 !! 2011 !! 2012
 |-
 | || -100 || 10 || 30 || 50 || 60
 |}

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

:<code>SpreadsheetRange(wb, "Employee_count") &rarr;</code>
:{| class="wikitable"
 ! !! .Column &rarr; !! 'B' !! 'C' !! 'D' !! 'E' !! 'F'
 |-
 ! rowspan="3" | .Row<br>&darr; !! 7 
 | 24 || 27 || 28 || 32 || 35
 |-
 ! 8 
 | 13 || 13 || 13 || 13 || 13
 |-
 ! 9 
 | 25 || 22 || 21 || 19 || 16
 |}

:<code>Index Time := [2008, 2009, 2010, 2011, 2012];</code>
:<code>SpreadsheetRange(wb, "A7:F9", colIndex: Time, howToIndex: 8, sheet: 1)  &rarr;</code>
:{| class="wikitable"
 ! !! Time &rarr; !! 2008 !! 2009 !! 2010 !! 2011 !! 2012
 |-
 ! rowspan="3" | .Row<br>&darr; !! "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:
:<code>SpreadsheetCell(wb, "*", 1, 1).Sheet</code>

To obtain all used cells in sheet named "Sheet2":
:<code>SpreadsheetRange(wb, sheet:"Sheet2")</code>

To obtain the number format of all cells in "Sheet2":
:<code>SpreadsheetRange(wb, sheet:"Sheet2", what:"NumberFormat")</code>


=== Errors in 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'."

<div id="SpreadsheetInfo">

== SpreadsheetInfo(workbook, item) == 
</div>  

SpreadsheetInfo gets various kinds of information about the spreadsheet («workbook») specified by parameter «item»:
*:{| class="wikitable"
! item !! Description
|-
| <code>"AcceptLabelsInFormulas"</code> || True when you can use labels in worksheet formulas. This is usually false.
|-
| <code>"ActiveSheet"</code> || The number of the active (displayed) worksheet.
|-
| <code>"Author"</code> || The name of the author, usually the name of the person who created the spreadsheet as recorded by Windows OS.
|-
| <code>"CalculationMode"</code>   || The calculation mode set for the workbook, which may be "Automatic", "Manual" or "Semiautomatic", meaning automatic except for data tables.
|-
| <code>"CalculationState"</code> || ''([[Analytica 5.0|5.0]])'' The current state of Excel's calculation engine, either <code>"Calculating"</code>, <code>"Pending"</code> or <code>"Done"</code>.
|-
| <code>"CalculationVersion"</code> || ''([[Analytica 5.0|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.
|-
| <code>"CodeName"</code> ||
|-
| <code>"Date1904"</code> || The base for dates used in the workbook.
|-
| <code>"DecimalSeparator"</code> || ''([[Analytica 5.0|5.0]])'' The character used to separate a whole number from its fractional part. In English-speaking countries this is '.' (a dot).
|-
| <code>"Excel.CalculationVersion"</code> || ''([[Analytica 5.0|5.0]])'' Number equal to 10000 * major + minor, encoding the version of calculation engine for your installed version of Excel. 
|-
| <code>"Filename"</code>   || The name of the file, including the full file path.
|-
| <code>"Name"</code>         || The name of the file, without the file path.
|-
| <code>"Names"</code>          || A list of all the named ranges.
|-
| <code>"OperatingSystem"</code> || ''([[Analytica 5.0|5.0]])'' The name of the operating system that your Excel instance is running on, as reported by Excel. 
|-
| <code>"ReadOnly"</code> || True (1) if the file is saved as Readonly.
|-
| <code>"Saved"</code>      || False (0) if it has unsaved changes.
|-
| <code>"SelectedRange"</code> || The currently selected Range.
|-
| <code>"SelectedRangeR1C1"</code> || The currently selected Range specified by row and column number.
|-
| <code>"Sheets"</code>     || A list of the names of all the worksheets
|-
| <code>"ThousandsSeparator"</code> || ''([[Analytica 5.0|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 <code>1,234,456.78</code>, groups of thousands are separated by commas.
|-
| <code>"Title"</code> || The title of the spreadsheet
|-
| <code>"UseSystemSeparators"</code> || ''([[Analytica 5.0|5.0]])'' True when Excel uses <code>"DecimalSeparator"</code> and <code>"ThousandsSeparator"</code> for displaying numbers.
|-
| <code>"Version"</code> || ''([[Analytica 5.0|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".
|-
| <code>"Visible"</code> || True when the Excel UI is visible.
|}  The items above marked with ''([[Analytica 5.0|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 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 <code>"Sheet1!A1:Z99"</code>.  [[SpreadsheetInfo]] was introduced in [[Analytica 4.5]]. The following additional 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 <code>'HorizontalAlignment'</code> and <code>'VerticalAlignment'</code> appeared in [[Analytica 5.0]]. Options <code>'Address'</code>, <code>'AddressR1C1'</code>, <code>'Sheet'</code> and <code>'RangeName'</code> appeared in [[Analytica 4.6]]. The remaining options appeared in [[Analytica 4.4]], except for <code>'Value'</code>, <code>'NumericValue'</code>, <code>'Formula'</code>' and <code>'RelativeFormula'</code> which appeared when the «what» parameter was first introduced in [[Analytica 4.3]].  == See Also == <div style="column-count:2;-moz-column-count:2;-webkit-column-count:2">
*  [[media:Spreadsheet Helper lib.ana|Spreadsheet Helper lib.ana]]
* [[Analytica_Libraries_and_Templates#Spreadsheet_Helper_Library|Spreadsheet Helper Library]]
* [[Media:Functions for Reading Excel Worksheets.ana|Reading Excel Worksheets.ana]]
* [[Read and Write Spreadsheets]]
* [[Excel spreadsheets read and write]]
* [[Functions to Write Data to Excel Worksheets]] -- [[SpreadsheetSetCell]], [[SpreadsheetSetRange]] and [[SpreadsheetSave]]
* You can also use [[DbQuery| ODBC]] -- a standard database access method to read from Excel spreadsheets.
* [[SuppressExcelAlerts]]
* [[Excel to Analytica Translation]]
* [[Excel to Analytica Mappings]]
* [[ReadTextFile]]
* [[ReadCsvFile]]
* You can use these functions from [[Spreadsheets in ACP| ACP]] and [[Excel Functions from ADE| ADE]].
* These spreadsheet functions above are more flexible than [[OLE linking]] which is also available.
* [[OLE linking]] </div>
<blockquote>In a call to SpreadsheetRange(wb, range):</blockquote>
* 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'."