User Tools

Site Tools


SPREADSHEET

Guide Section: Interacting with an Excel spreadsheet | EASAP Tree: SPREADSHEET LIST branch


SPREADSHEET specifies the name of the Excel workbook (*.xls, *.xlsx, *.xlsm) file.

Avoid the following characters in an Excel filename.

\

/

*

[

]

:

?

-

.

. or <PERIOD> is allowed in the normal position between the filename prefix and the extension suffix (myfile.xls, myfile.xlsm, etc….)

'Two consecutive spaces' or a prohibited <PERIOD> character in an Excel spreadsheet name removes the ability to re-open an EASAP run from the Results folder, so should be avoided.

$ or <DOLLAR SIGN> should not be used in the name of an Excel sheet, Excel dependency calculations will fail.

A sheet name in an Excel spreadsheet must not end with a space.

Before an EASAP can be connected to a spreadsheet file, the file must first be uploaded into the EASAP. The file can then be selected using the File: parameter within the SPREADSHEET. In the EASAP Builder, a Save will generate a SHEET object for each sheet (not for EDC or Batch Only: TRUE).

The File: parameter must be unique across multiple SPREADSHEET's.

No named range in an Excel Client (or 'EDC') spreadsheet may have the prefix 'prop_' nor any combination of this sequence using captial letters, please rename any of these named ranges.

For an EDC spreadsheet a subset of all the spreadsheet's named ranges may be specified in Named Ranges List: or Named Ranges Prefixes: (but not both) to limit the number of values stored in a 'case record' (for an example see Use a subset of named ranges for a case record)

SPREADSHEET
Essential Parameters:
File:select spreadsheet *.xls file.
Optional Parameters:
Global Dependencies: Enable a global dependency tree, setting this to TRUE will significantly lengthen save times for an EASAP in return for shorter run times (default: FALSE, TRUE)
If set to TRUE with a 'global' CELL REFRESH (ie. Objects: left blank), performance will be slower than either FALSE here or where specific CELL REFRESH > Objects: are specified.
Desktop:Enable an Excel Client interface to initialize, modify and store named ranges to a database (default: FALSE, TRUE)
Password:
Password to open password-protected spreadsheet files
Excel Server Type:
Restrict to an Administrator-defined EXCEL SERVER TYPE, if unset any EXCEL SERVER may be used.
For preloading create a uniquely named EXCEL SERVER TYPE and enter it in LOCAL EXCEL SERVER > Excel Server Types:
Exclude Formulas:Cells containing formulas will be ignored in dependency calculations.
Simplify Formulas:Select types of Excel formulas to ignore to simplify dependency calculations, use when no referenced cells are found or referenced within the look up data.
We recommend to simplify OFFSET, VLOOKUP, HLOOKUP
Exclude Sheets:Select SHEET's to exclude from dependency calculations, only select worksheets that do not contain cells referenced in any Range Reference: or Named Range: parameters
On Parse Error:When an Excel reference cannot be parsed set it will have ALL_CELL dependencies, in some cases an Author may wish to set NO_CELL here and enter dependent cells manually.
Batch Only:Set the spreadsheet to be opened only on submission (default: FALSE, TRUE)
Disable Named Ranges:Disable caching of named ranges from this spreadsheet when the EASAP is saved (default: FALSE, TRUE)
Calculate Dependencies:Specify when to calculate dependencies (default: Never, If Unset, Force)
File Choice:Select a FILE CHOICE to specify a spreadsheet to upload and run after the EASAP has already been started
available when Batch Only: TRUE
when Desktop: TRUE
Excluded Sheets:Exclude sheets based on one or more prefix strings listed here (ie. create a 'blacklist')
Included Sheets:Include sheets based on one or more prefix strings listed here (ie. create a 'whitelist')
Named Ranges Prefixes:Named ranges that match these prefixes will be stored and retrieved via an Excel Client
alternately explicitly specify named ranges in Named Ranges List: below
Named Ranges List: A LIST (instead of prefixes, above) of named ranges to be stored and retrieved via an Excel Client
Save Type: Allow the workbook be saved as well as named range data, (default: Data Only, Data and Workbook)

Calculate Dependencies:

Dependencies between object in an EASAP may be calculated in advance to optimize EASAP interactions, choose from:

  • Never - by default dependencies are not calculated, all objects are updated upon any change in the GUI
  • If Unset - dependencies are calculated once at first save, useful when an Author saves an EASAP frequently
  • Force - dependencies are calculated each time an EASAP is saved, should be done prior to publishing, once Authoring is complete

Desktop: and Batch Only:

There are four ways to utilize an Excel spreadsheet from an EASAP.

For a SPREADSHEET:

  1. keep the default Batch Only: FALSE
    • Excel-linked 'implicit' objects update in real time
    • Excel-linked 'explicit' objects update via EVENT' s and ACTION's

  2. set Batch Only: TRUE
  3. set File Choice: to a FILE CHOICE (with Batch Only: TRUE)
    • allow the User to specify a spreadsheet (or version) prior to a PROCESSES branch run

  4. set Desktop: TRUE (keeping the default of Batch Only: FALSE)
    • allow an Excel Client instance to open with this spreadsheet