User Tools

Site Tools

Exchange data with a spreadsheet

  • Once an Excel spreadsheet has been uploaded and a SPREADSHEET created, there are two methods for exchanging data between the EASAP and these spreadsheets,
    1. Use a Range Reference: parameter within a data entry object to synchronize the value of the object with the value in the referenced cell or named range.
    2. Use an action under EVENT PROCESSING to read and write data in Excel spreadsheets.

Referencing Ranges in Data Entry Objects

The following Child objects to a TABBED PANE contain a Range Reference: parameter.

The Range Reference: parameter allows you to select from a list of available NAMED RANGE and RANGE REFERENCE objects. Once Range Reference: is specified, the data entry object will be automatically linked to the referenced cell in the Excel spreadsheet.

  • Range Reference: allows only a NAMED RANGE that links to a single cell within an Excel spreadsheet
  • Use an ACTION to link to a NAMED RANGE which represents more than one cell

Input / Output Cell Types

The behavior of the linkage between the cell and the data entry object depends on the cell is a Input or Output type.

  • When the referenced cell is an Input, changes to the value of the data entry object automatically trigger the EASAP to change the cell value in the spreadsheet and also to read all the specified dependent cell values.
  • When the referenced cell is an Output, the data entry object is automatically shown in a disabled state so that it behaves as a display object for the referenced cell value. The value displayed in the data entry object will be updated anytime the referenced cell is changed due to a dependency or when a CELL REFRESH ACTION is triggered.
  • When the referenced cell has a Cell Type: of Both, the user will be able to edit the value, but if the referenced cell contains a formula, then the value will update automatically. However, as soon as the user enters a value manually the formula will be overwritten.
    Either way, using the Range Reference parameters in data entry objects results in data refreshing automatically to and from the underlying spreadsheets. Thus, the data appears “live” to the user with a response time similar to using the spreadsheet itself.

Using EVENT PROCESSING to Interact with Excel

Sometimes it will not make sense for you to directly reference cells in data entry objects; for instance, when a CHOICE LIST is used to retrieve input from a user, but the actual cell values come from a MAP derived from the user’s selection in the choice list. In these cases, special event and action objects designed to interact with Excel spreadsheets can be used to perform a variety of tasks:

  • Read object values from spreadsheet cells
  • Write object values into spreadsheet cells
  • Extract images of sheets or charts from spreadsheet and display in an IMAGE in a DIAGRAM.
  • Run macros in a spreadsheet

A summary of the available Excel EVENT's and ACTION's is given in the table below. These EVENT's and ACTION's will be found within either EVENT PROCESSING objects in the USER INTERFACE branch or the PROCESSES branch.

Object NameDescription
READ EXCEL ACTIONReads values from specified cell ranges in Excel spreadsheets into specified list objects
WRITE EXCEL ACTIONWrites values from specified list objects into specified cell ranges in Excel spreadsheets
IMAGE EXCEL ACTIONExtracts a screen capture of cell range or chart in an Excel spreadsheet for display by an IMAGE in a DIAGRAM
GET EXCEL FILE ACTIONGets the current copy of the Excel spreadsheet from the Excel Server. Allows an author to download it from the EASAP
RUN MACRO ACTIONExecutes a macro in an Excel spreadsheet
CELL REFRESHRefreshes data associated with RANGE REFERENCE objects either to or from an Excel spreadsheet
CLEAR EXCEL RANGEClears values from cells within specified ranges