This section covers aspects of connecting a spreadsheet to EASA for use in an EASAP.
The spreadsheet file uploading process is accomplished using either one of the following methods:
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:
|SPREADSHEET LIST||Acts as a Parent to one or more SPREADSHEET's.|
|SPREADSHEET||Specifies the name of the Excel workbook.|
|SHEET||Identifies a specific worksheet within the Excel workbook.|
|RANGE REFERENCE||References an individual cell within the worksheet.|
|FORMULA REFERENCE||References a formula object within the worksheet.|
|CHART REFERENCE||References a chart object within the worksheet.|
|NAMED RANGE LIST||A Parent to NAMED RANGE's .|
|NAMED RANGE||NAMED RANGEs are created automatically by the Excel Range Wizard Tool.|
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:
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.
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:
When the dependency calculation tool is initiated, a form will appear, displaying the status and progress of the 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.
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:
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.