User Tools

Site Tools


Defining Connections To Spreadsheets

This section covers aspects of connecting a spreadsheet to EASA for use in an EASAP.

Uploading an Excel Spreadsheet

The spreadsheet file uploading process is accomplished using either one of the following methods:

  • Selecting the spreadsheet as part of the Excel Range Wizard process.
  • The spreadsheet file is uploaded by the Author using either the Files selection under the Authoring Menu in Author mode or the Files Tab in EASAP Builder.
  • In some cases, it is possible for the spreadsheet file to be uploaded by a user of the EASAP using a FILE CHOICE. However, a “template” version of the spreadsheet file will need to be uploaded by the author in advance.

Using the Spreadsheet List Branch

The process of defining connections to one or more spreadsheets from an EASAP starts with the addition of a SPREADSHEET object under the SPREADSHEET LIST branch. From this point, the addition of objects proceeds in a hierarchical fashion into individual spreadsheets from the specification of the workbook file into the individual worksheets in the file down to the individual cells in the worksheets. The objects available for defining Excel spreadsheet connections in EASAP Builder are as follows:

Object Name Description
SPREADSHEET LISTActs as a Parent to one or more SPREADSHEET's.
SPREADSHEETSpecifies the name of the Excel workbook.
SHEETIdentifies a specific worksheet within the Excel workbook.
RANGE REFERENCEReferences an individual cell within the worksheet.
FORMULA REFERENCEReferences a formula object within the worksheet.
CHART REFERENCEReferences a chart object within the worksheet.
NAMED RANGE LISTA Parent to NAMED RANGE's .
NAMED RANGENAMED RANGEs are created automatically by the Excel Range Wizard Tool.

Range References and Named Ranges

The aim of specifying Excel spreadsheet files within SPREADSHEET's is to ultimately make data connections with individual cells within the spreadsheets. There are two approaches to referencing individual cells within an Excel spreadsheet:

  1. A direct cell reference via a RANGE REFERENCE.
  2. Persistent name referencing via a NAMED RANGE.

NAMED RANGE's in Excel are a feature used to give a persistent name to a given cell. Of the two cell referencing approaches, using NAMED RANGES's is the preferred method. Primarily, the layer of abstraction provided by a persistent naming convention ensures that changes on the source spreadsheet have a minimal effect on the EASAP, thus increasing the reliability and maintainability of the solution. Additionally, the creation of NAMED RANGE objects is performed automatically within EASAP Builder. NAMED RANGES are extracted by EASA from the spreadsheet files defined within a SPREADSHEET and stored within the EASAP. If all your linked cells have names defined in Excel, there is no need to define RANGE REFERENCE's manually. Once the spreadsheet file (.xls, .xlsx, .xlsm) is uploaded, you can go straight to creating user interface objects . Basically, the range reference method should only be used when NAMED RANGES have not been and cannot be defined within an Excel spreadsheet for the desired cell locations. The approach calls for a SHEET to be first created as a Child to the SPREADSHEET and then to create RANGE REFERENCE's below the appropriate SHEET's.


Note: You can mix the Named Range and Range Reference methods if all the cells that need to be referenced are not Named Ranges.


Dependencies

To improve the speed at which data is transferred back and forth between spreadsheets and EASAPs, the concept of determining and then specifying cell dependencies has been introduced into the process of defining connections to spreadsheets. Refreshing all cells can be time consuming for large, complex spreadsheets, so responsiveness of an EASAP can be improved by minimizing the number of cells to be refreshed using the Dependents parameter. To avoid refreshing all cells when a Range Reference value is changed, you must specify the dependent cells in the Dependents parameter. Otherwise, all other referenced cells will have their values updated. Although typing in the list of dependent cells is allowed in the Additional Dependents parameter, the most efficient method is to use the method provided in EASAP Builder for calculating the dependencies automatically. This method will be presented when you save the EASAP.


Note: The process of automatically calculating cell dependencies may take several minutes for large spreadsheets. You can reduce the time it takes by making use of the following SPREADSHEET parameters:

  • Exclude Functions:
  • Simplify Functions:
  • Exclude Sheets:

When the dependency calculation tool is initiated, a form will appear, displaying the status and progress of the calculation.

Forcing Dependency Re-Calculation

In some cases, you may need to force a re-calculation of dependencies. You can clear all current dependency information from the EASAP by using the ‘Clear Dependencies’ button on the Options Tab of the EASAP Builder. After clearing dependencies, you can save the EASAP to initiate a re-calculation.

Connecting To User Spreadsheets

The File Choice parameter of the SPREADSHEET object can be used to connect to a spreadsheet file uploaded by the user in runtime. Example steps to configure this method after the EASAP and a FILE CHOICE object are created are as follows:

  • Upload any Excel file to be used as a template (This can be a blank spreadsheet)
  • Add a Spreadsheet object and set the File parameter to the uploaded Excel file
  • Set the Batch Only parameter of the Spreadsheet object to TRUE
  • Set the File Choice parameter of the Spreadsheet object to the already created File Choice object
  • Add a new Sheet object as child to the Spreadsheet and set the Sheet parameter to the target sheet
  • Create a READ EXCEL ACTION either under an ACTION GROUP bound to an EVENT action under an EVENT PROCESSING object or as a direct child of a BUTTON object.

After the Read Excel Action has been configured as desired, the EASAP is ready to start. After startup, the Excel file to be read must be uploaded first using the File Choice object that is linked to the Spreadsheet object. After the upload completed, the selected file overwrites the template that was already there and the Read Excel Action is ready to be executed.