- Excel Desktop Client new !
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 an EASAP's input and output, particular cells, ranges and charts link directly to visible fields in the EASAP GUI and 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.
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.
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.
EASAP→SPREADSHEET LIST→spreadsheet_Mortgage_Calculator→NAMED RANGE LIST will be created at this time or else will be updated if already existing.
The EASAP Builder with EASAP→SPREADSHEET LIST expanded 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
Every new EASAP begins with some existing objects that form a template that is common to every EASAP.
In the Builder this is called the Tree, the highest level branches are always the same, their names are in ALL CAPS.
Let's add some buttons to the USER INTERFACE to help a User
We will implement good GUI design and keep our web interface simple and intuitive for a User.
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 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.
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 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 implemented in an EVENT PROCESSING object.
Child objects of an EVENT will trigger any Child ACTION objects under the associated ACTION GROUP.
When a User clicks on the BUTTON→ button_submit, a BUTTON PRESSED EVENT (Child of event_submitted) is activated.
Following a BUTTON PRESSED EVENT this MENU ACTION submits a run of the EASAP with current inputs and records the timestamped output at:
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.
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 both SUB PANE (M1:V24) and SUB PANE (M24:V46):
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).
Now we 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”)
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.