An organization may use a spreadsheet for various essential operations:
Several users may need secure access to an up-to-date 'master' version of a spreadsheet. Users may be in different locations; they may use different devices to enter and receive data; they may need to save the results in different formats and save them to different repositories.
Distributing the raw spreadsheet is not an ideal solution in these cases for several reasons.
The following tutorial demonstrates a turnkey EASA solution to the problems above.
We will create a basic Mortgage Calculator EASAP that derives its user interface and functionality from an existing Microsoft® Excel spreadsheet.
The underlying spreadsheet define's our EASAP's input and output, particular cells and charts link directly to fields in the EASAP GUI. These values and graphs will change in real time when a User modifies an input.
As we build this application, think about your organization's key spreadsheets and the potential value created if they could be easily transformed into standalone web applications. (They can be!)
Below is our complete EASAP user interface. The second image is an example of the report output.
Please ensure a compatible version of Excel is installed on the server.
The following tutorial involves many straightforward steps. Below is a broad summary of this process:
An Author uses the EASAP Builder to create an EASA application without writing any code. The Builder interface consists of a Tree of EASAP objects on the left and their parameters on the right. In this tutorial we will build a fully functioning application by applying iterations of EASA's simple design process:
There will be no coding involved.
Obtain from the EASA Administrator a user ID with Author privileges.
domain-name is the computer name or domain name of the EASA Server (often its localhost).
On the EASA Server web page:
We have created a new EASAP.
The New Application Wizard will appear, below.
The New Application Wizard opens any time we create a new EASAP. Here we set some parameters in the PROPERTIES object.
We now have some options on how we wish to define the user interface of our new EASAP. The first option is to open the EASAP Builder and begin to add objects to our EASAP manually. However, in our case we want the EASAP to quickly be able to use the functionality we already have implemented in our Excel spreadsheet. We will use the Excel Range Wizard.
The Excel Range Wizard effortlessly reproduces the look and function of an Excel spreadsheet within an EASAP.
Later we add additional interface objects to the EASAP.
Our EASAP will not only mimic the look of our spreadsheet, it directly links to the relevant cells in the secured master version of the spreadsheet on the server.
The EASAP User will update input cells, the Excel spreadsheet will do calculations based on the updated values, and the EASAP will return and display updated values to the User in real time.
Let's begin using the Excel Range Wizard:
On the Select Excel Ranges screen, we can select the part or parts of our original spreadsheet which we want to expose to Users in the EASAP. For now, we will add just one range of cells to the EASAP.
The Mortgage Calculator.xls file will open in Excel;
The EASAP Builder window will appear. The EASAP Tree will be populated with new objects created by the wizard.
At this time, the NAMED RANGE LIST will be created or updated if already existing, and then a dependency calculation will be initiated.
The EASAP Builder is shown below:
We now have a functional EASAP. By default, the Excel Range Wizard replicates the formatting of the Excel spreadsheet. Let's check it.
Once the test EASAP has opened, change some inputs, and then exit the test window.
Every new EASAP begins with some existing objects that form a template that is common to all EASAPs. In the Builder this is called the Tree, the highest level branches are always the same, their names are in ALL CAPS. In this section, we will add some custom buttons to the USER INTERFACE object to help our users save their work and to create reports that can be viewed by colleagues. We will implement good GUI design keeping our web interface simple and intuitive for the user.
This object and its Child objects were automatically created when we ran the Excel Range Wizard and created the form for the basic input and output.
Now add a SPACER to create some space between the existing form and the new buttons.
Add a second SUB PANE for the buttons:
Let’s add two BUTTON's to this SUB PANE
The Label: text will be displayed inside the rectangular button.
To be clear this new BUTTON is named, button_submit which we, as Authors, will use elsewhere within the Builder. button_submit has a Label: which is text a User will see enclosed within the button's outline in the EASAP GUI.
Press <Enter> after typing a parameter value to ensure it updates. For the moment button_submit will not do anything.
We can also add a Tooltip: such as ‘Submit with these inputs and create a report’ that will be visible when the User places the mouse pointer over the button.
ACTION's may be added as Child objects under a BUTTON, each will be executed sequentially when a BUTTON is pressed.
We now introduce our first ACTION called MENU ACTION which initiates activities common to many EASAP's. Here we create a way for the User to exit the EASAP gracefully.
We have now defined what happens when a User presses the exit BUTTON. In a moment we will define another ACTION and associate it with button_submit.
Let's check our work.
In this section, we will introduce EVENT's and associated ACTION's. In this case, the EVENT is BUTTON PRESSED which will be activated when button_submit is pressed. This alternative way to configure a BUTTON to trigger an ACTION demonstrates a more general EVENT/ACTION model via the object, EVENT PROCESSING.
Add an EVENT PROCESSING to the Tree.
An EVENT PROCESSING has two Child objects:
When a User clicks on the BUTTON→ button_submit, a BUTTON PRESSED EVENT (Child of event_submitted) is activated. Now we define an ACTION that will be triggered as a result.
Following a BUTTON PRESSED EVENT this MENU ACTION submits a run of the EASAP with current inputs and records the timestamped output at:
If there is an error, there will be an error message to identify which object to fix.
The EASAP is now perfectly usable, but let’s suppose that, after publishing it for testing by Users, they provide some feedback. In this case, they ask to include two charts from the spreadsheet and display each of them on a new tabbed pane in the EASAP. These charts will enable a User to see live results graphically even before the EASAP is submitted.
On the Select Excel Ranges screen,
Because these selections were made from the Excel tab ‘Basic Information’, two new SUB PANES under tab_Basic_Information now contain the 'Amortization' and 'Rate History' charts. We can move these objects wherever we want them in the user interface by changing their location in the Tree.
The Tree should look like the panel to the right:
For SUB PANE (M1:V24) and SUB PANE (M24:V46):
Let’s change the default size in pixels of our application to allow for the larger graphical content. Otherwise, we will have unwanted scroll-bars.
When a user submits an EASAP, a report is created. We define the content and presentation of this output with the REPORT object.
In the Tree:
We are going to create two images from the spreadsheet and write them to the report. We could have simply defined the actions for button_submit by adding Child objects in much the same way as we did for button_exit. However, we wanted to demonstrate the BUTTON PRESSED EVENT within the more general EVENT PROCESSING object which can use various EVENT's (eg. VALUE CHANGED, RESULTS COMPLETED, APPLICATION OPENED).
To pull an image from the spreadsheet and put it in a report we use an IMAGE EXCEL ACTION in the PROCESSES branch of the Tree. PROCESSES contains a sequence of tasks that begin when the User 'submits' the EASAP.
On the Tree:
These IMAGE EXCEL ACTION's will create images of the summary region and output data table.
The Builder will highlight the text between the '%' delimiters in blue and remove the '%' signs, this indicates the Builder recognized the strings as pre-existing scalar variables (eg. “Report for loan amount $cell_Basic_Information_loan_amount_1 for cell_Basic_Information_term_1 years”).
The Builder allows variables (green for lists or blue for scalars) to be entered as parameters for many objects giving the Author the power and flexibility of conventional computer programming as explained in more advanced examples (see Batch Tutorial).
This will insert summary notes in the Results page so that a User later browsing Results may distinguish one submission from another.
Again, the SCALAR's should be recognized and be highlighted in blue without '%' symbols: (eg. “Report for loan amount $cell_Basic_Information_loan_amount_1 for cell_Basic_Information_term_1 years”)
From the EASA web pages, go to:
Test EASAP simulates what a User will see.
Our mortgage calculator stores the results of each EASAP run as an HTML page at:
A User may vary inputs and generate different runs and view the results in the browser. Once an EASAP run meets a User's needs it may be exported to a PDF. Now we will add EXPORT TO PDF functionality to our EASAP.
This will be run after the user clicks “submit” and before the report is generated by the MENU ACTION that follows.
For the EXPORT TO PDF
|Parameter Name||Parameter Value|
|Output File Name:||Report.pdf|
|Open in Browser:||FALSE|
The above Ranges: correspond to the summary and the calculated data output tables.
Now we put a link to the output PDF file in the HTML report. In the Tree,
We have completed the Excel Tutorial.