An organization may require a spreadsheet to:
Multiple Users may:
Distributing the raw spreadsheet is not an ideal solution in these cases as:
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 Excel spreadsheet.
The underlying spreadsheet defines an EASAP's input and output. Certain cells, ranges and charts link directly to browser-visible fields in the EASAP GUI. These Excel objects generate media that may be included in HTML or PDF reports.
On the EASAP's GUI Excel-linked values and graphs will change in realtime 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.
This tutorial involves a number of straightforward steps:
An Author uses the EASAP Builder to create an EASA application without writing any code.
The EASAP Builder interface consists of a Tree of EASAP objects on the left and their parameters on the right.
In this tutorial we build a fully-functioning application via iterations of EASA's design process:
There will be no coding involved.
This tutorial contains seven parts A - G:
1. Access tutorial files and login to EASA:
2. Login to the EASA Server web page.
3. Create a new EASAP.
Under EASA > Applications :
4. Launch the EASAP Builder:
5. The New Application Wizard will appear.
The New Application Wizard opens any time we create a new EASAP.
Here we set some parameters which will be stored and easily changed in PROPERTIES :
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.
6. 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.
1. Let's begin using the Excel Range Wizard:
2. Expose parts of the original spreadsheet to Users.
For now, we will add just one range of cells to the EASAP:
3. The Tree will be populated with new objects created by the wizard.
The EASAP Builder with EASAP > SPREADSHEET LIST expanded is shown below,
We now have a functional EASAP.
4. The Excel Range Wizard replicates the formatting of the Excel spreadsheet.
Let's test it:
Every new EASAP begins with some existing objects that form a template that is common to every EASAP.
In the EASAP Builder this is called the Tree, the highest level branches are always the same, their names are in ALL CAPS.
1. Let's add 'Save' and 'Create report' buttons to the USER INTERFACE
We will implement good GUI design and keep our web interface simple and intuitive for a User:
layout_panel and its child objects were automatically created when we ran the Excel Range Wizard to create the form for basic input and output.
Add a SPACER to create some space between the existing form and the new buttons:
Add a second LAYOUT PANEL for the buttons:
2. Let’s add two BUTTONs to this LAYOUT PANEL:
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 EASAP Builder.
button_submit has a Label: which is the text that a User will see enclosed within the button's outline in the GUI.
Press <Enter> after typing a parameter value to ensure it updates.
For the moment button_submit will not do anything.
ACTION's may be added as child objects under a BUTTON, each will be executed sequentially when a BUTTON is pressed.
3. Let's create a way for the User to exit the EASAP gracefully.
We now introduce our first ACTION called MENU ACTION which initiates activities common to many EASAP's:
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.
4. Since LAYOUT PANEL doesn't add an space around its child objects, we add space between each button.
5. Let's check our work:
In this section, we will introduce an EVENT and its associated ACTION.
In this case, the BUTTON PRESSED EVENT will be activated and an ACTION triggered when button_submit is pressed.
A BUTTON PRESSED EVENT is an alternative way that a BUTTON can trigger an ACTION.
Here we use BUTTON PRESSED to introduce a general EVENT/ACTION model.
1. Let's add an EVENT PROCESSING to the Tree:
An EVENT PROCESSING has two child objects:
Child objects of an EVENT will trigger any child ACTION objects under the associated ACTION GROUP.
When a User clicks on button_submit a BUTTON PRESSED EVENT (child of event_submitted) is activated.
2. Let's define an ACTION to trigger a submit for the EASAP.
After a BUTTON PRESSED EVENT this MENU ACTION submits a run of the EASAP with current inputs.
The timestamped output is recorded at: EASA > Authoring > Testing
Click Save ( ), if there were an error, an error message would identify which object to fix.
The EASAP is now perfectly usable, but let’s suppose that, after its published, a few Users provide feedback. In this case, they ask to include two charts from the spreadsheet and display each of them on new TABBED PANEs in the EASAP.
1. Let's add these charts to enable a User to see live results graphically even before the EASAP is submitted.
These selections were made from the 'Basic Information' Excel tab so there are two new LAYOUT PANELs under tab_Basic_Information which contain the Amortization and Rate History charts.
2. Let's make sure we've captured the Excel-derived objects we wanted.
3. We may change the position of these objects in the user interface by changing their location on the Tree.
4. The Tree should look like the following panel.
Save the EASAP: click the Save button or type CTRL-s
5. Test the EASAP, click 'Web Browser Test'.
When the application opens, select each new tab, the Amortization tab should look something like the following figure:
When a user submits an EASAP, a report is created. We define the content and presentation of this output in a REPORT.
1. We are going to create two images from the spreadsheet and write them to the report.
In the Tree, select the PROCESSES branch.
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).
2. To pull an image from the spreadsheet we use an IMAGE EXCEL ACTION in the PROCESSES branch.
PROCESSES contains a sequence of tasks that begin when the User 'submits' the EASAP.
These IMAGE EXCEL ACTIONs will create images of the summary region and output data table, respectively.
Later we will add the two images 'summary.png' and 'table.png' as PLOT objects in our report.
First let's insert summary notes in the Results page so that a User later browsing Results may distinguish one submission from another.
3. The first step is to format the loan amount (displayed as dollars) and the term length (as an integer number of years).
4. We want to store a few bits of data about the particular submission as NOTES in a text file.
The EASAP Builder will highlight the text between the percent (' % ') delimiters in blue and remove the percent (' % ') signs, this indicates that it recognized the strings as references to pre-existing SCALAR's.
The value of the Value: parameter should show as format_term year loan for format_loan_amount
The EASAP Builder allows object references (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 Intro Batch Tutorial ).
5. Let's assemble the rest of the report.
6. Let's test the EASAP to generate the output that a User will see:
Our mortgage calculator stores the results of each EASAP run as an HTML page at: EASA > Authoring > Testing
A User may vary inputs for a number of EASAP runs and and view the results in the browser.
Once an EASAP run meets the User's requirements we want the output to be exported to a PDF.
1. Let's add an EXPORT TO PDF to our EASAP:
2. For the EXPORT TO PDF set:
3. We put a link to the output PDF file in the HTML report.
We have completed the Excel Tutorial.