An organization may use a spreadsheet for various essential operations:

• To run a product pricing model
• To estimate a future budget, revenue, and costs
• To analyze insurance risk and calculate a premium

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.

• New spreadsheet versions may be confused with older ones.
• Proprietary information may be embedded in the spreadsheet.
• An end user might inadvertently modify the spreadsheet and introduce error.
• A user might need to share a final report with many colleagues or store it in a central archive.

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:

1. Launch the New Application Wizard to set basic properties
2. Use the Excel Range Wizard to create a default working application from a spreadsheet
3. Modify the new EASAP in the Builder
   • Add buttons to the GUI that activate events and trigger actions
   • Import live Excel charts to the EASAP GUI
   • Create finished HTML and PDF output with formatted User results

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:

1. Create an EASA object with a right-mouse-click
2. Populate that object's parameters in one of two ways:
   • Type in literal text
   • Select a value from a drop-down list

There will be no coding involved.

Obtain from the EASA Administrator a user ID with Author privileges.

• Open a browser window
• Navigate to the EASA Server URL sent by the Administrator
• The URL has the following structure:
  • http://domain-name/easa/

domain-name is the computer name or domain name of the EASA Server (often its localhost).

On the EASA Server web page:

• Select EASA→Set Mode→Author (enter an Author's login credentials)
• Under EASA→Help→Tutorials
  • Find the heading 'Excel Application Tutorial.'
    • Click the Tutorial Files (Windows zip format) link
    • Download the .zip file
    • Unzip the .zip file
    • The two files we will need in the next step are:
      • app_image.jpg to identify our new EASAP
      • Mortgage Calculator.xls with tables, formulas and charts

Under EASA→Applications,

• Select New
• Click on the Create button

We have created a new EASAP.

• Click on the EASAP Builder link
• Download the file atos.easa
• Open the file

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.

• Set the Title: to 'Mortgage Calculator' (without quotes)
• Set an icon as the Image:
  • Click the folder icon
  • Browse to app_image.jpg
• Set the Category: to 'Training’
• Click Next

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.

• Select 'Use Excel Range Wizard (2.0)'
• Click Finish and the screen below should open

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:

• Click on the Choose File button
• Navigate to and select the tutorial Excel file, Mortgage Calculator.xls from above.
• Click Next.

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.

• Click on the Add a Range from Excel button ( ).

The Mortgage Calculator.xls file will open in Excel;

• Select cells A1 through G23 → click and drag to highlight these cells.
• Click on Finish button to process these ranges and exit the Excel Range Wizard.

The EASAP Builder window will appear. The EASAP Tree will be populated with new objects created by the wizard.

• Click Save to save our work ( ).

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.

• Click the Test Web Browser button as shown on the next page:

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.

• Expand the USER INTERFACE object by clicking on the “plus box” () icon.

• Under USER INTERFACE→TABBED PANE LIST→tab_Basic_Information, find one SUB PANE simply called sub_pane.

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.

• Select sub_pane and minimize it by clicking on the “minus box” () icon.

Now add a SPACER to create some space between the existing form and the new buttons.

• Right-click sub_pane
  • Select Sibling→Add SPACER.

Add a second SUB PANE for the buttons:

• Right-click the above SPACER
  • Select Sibling→Add SUB PANE (The default name is fine.)
    • Set:
      • Layout: HORIZONTAL
      • Show Border: FALSE

Let’s add two BUTTON's to this SUB PANE

• Right-click the SUB PANE
  • Select Child→Add BUTTON, twice.

• Rename the first BUTTON→ button_submit
• (ie. modify the rectangular text field to the right of BUTTON: above the parameter table)
  • Set (without quotes):
    • Label: 'Save to Results & Create a Report'

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.

• Rename the second BUTTON→ button_exit
  • Set (without quotes):
    • Label: 'Exit without saving'

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.

• Right-click button_exit
  • Select Child→Add MENU ACTION
    • Rename it→ menu_action_exit
    • Set→ Action: Exit

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.

• Save
• Click on the Test Web Browser () button. Once the test EASAP has opened,
• Test the new 'Exit button' → button_exit.

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.

• Right-click tab_Basic_Information
  • Select Sibling→Add EVENT PROCESSING

An EVENT PROCESSING has two Child objects:

• EVENT
• ACTION GROUP

• Click the 'plus box' () to expand the EVENT PROCESSING to see each Child.
• Rename the EVENT→ event_submitted

• Right-click event_submitted
  • Select Child→Add BUTTON PRESSED.
    • Set (choose from drop-down list):
      • Buttons: button_submit

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.

Under event_submitted,

• Select the ACTION GROUP
• Rename it action_submitted
  • Click 'Yes' to rename the 'other usage' of this ACTION
  • The Actions: parameter of event_submitted will update to the new name
• Right-click action_submitted
  • Select Child→Add MENU ACTION
    • Set→ Action: Submit

Following a BUTTON PRESSED EVENT this MENU ACTION submits a run of the EASAP with current inputs and records the timestamped output at:

• EASA→Author→Authoring→Testing

• Save ( ) our work.

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.

• Re-run the Excel Range Wizard from within EASAP Builder (Tools→Excel Range Wizard).

• Click on the Choose File button
• Select the Excel file from the tutorial .zip archive→ Mortgage Calculator.xls
• Click Next.

On the Select Excel Ranges screen,

• Click on the Add a Range from Excel button ( ).
• Add a range for the 'amortization chart':
  • Select cells M1 through V24
• Add a range for the 'rate history chart'
  • Select cells M24 through V46
• Click Finish.

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.

• Right-click tab_Basic_Information
  • Select Sibling→Add TABBED PANE, twice.
    • Rename each of them, respectively:
      • tabbed_pane_Amortization
      • tabbed_pane_Rate_History
      • Set (for each of them, respectively):
        • Tab: Amortization
        • Tab: Rate History

• Drag and drop:
  • SUB PANE (M1:V24) into tabbed_pane_Amortization
  • SUB PANE (M24:V46) into tabbed_pane_Rate_History

The Tree should look like the panel to the right:

For SUB PANE (M1:V24) and SUB PANE (M24:V46):

• Set→ Show Border: FALSE
• Set→ Visual Compatibility: <blank>

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.

• Select PROPERTIES→APPLICATION near the top of the Tree
  • Set→ Width: 650
  • Set→ Height: 530

• Save the EASAP → click the Save button or type Ctrl-S.

• Test the EASAP.
• When the application opens, select the new tabs. The window 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 with the REPORT object.

In the Tree:

• Select the PROCESSES branch.

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:

• Right-click EASAP→PROCESSES
  • Select Child→Add IMAGE EXCEL ACTION
    • Name it image_excel_action_summary
      • Set:
        • Sheet: sheet_Basic_Information (from parameter dropdown list)
        • Range: A1:L25
        • File Name: summary.png (type inthis new filename)
          • (then click on a different field→ ENTER will erase new filename)
        • Queue: TRUE (avoids running out of free Excel processes)

• Right-click image_excel_action_summary
  • Copy and then right-click Paste (Sibling)
    • Rename it image_excel_action_table
      • Set→ File Name: table.png (type in this new filename)
        • (then click on a different field→ ENTER will erase new filename)
      • Set→ Range: A26:L387

These IMAGE EXCEL ACTION's will create images of the summary region and output data table.

• Select and expand the OUTPUT branch.

• Right-click OUTPUT
  • Select Child→Add NOTES
    • Set→ Value: %cell_Basic_Information_term_1% year loan for $%cell_Basic_Information_loan_amount_1%

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).

• Right-click NOTES
  • Select Sibling→Add REPORT.

This will insert summary notes in the Results page so that a User later browsing Results may distinguish one submission from another.

• Right-click the REPORT
  • Select Child→Add TEXT OUTPUT
    • Set→ Style: Heading 2
    • Set→ Text: Report for loan amount $%cell_Basic_Information_loan_amount_1% for %cell_Basic_Information_term_1% years

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”)

• Right-click the TEXT OUTPUT
  • Select Sibling→Add PLOT
    • Set→ Image: summary.png

• Right-click this PLOT, select Copy and then right-click Paste (Sibling)
  • Set→ Image: table.png

• Save the EASAP→ click the Save button () or type Ctrl-S.
• Minimize or close the EASAP.

From the EASA web pages, go to:

• EASA→Author→Authoring→Testing→Test EASAP

Test EASAP simulates what a User will see.

• Change some values, and click on the ‘Save to Results & Create a Report’ button.
• Refresh the EASA→Author→Authoring→Testing page the test should appear.
• Check the Notes reflect the values just entered.
• Check the report by clicking on the icon under Output.

Our mortgage calculator stores the results of each EASAP run as an HTML page at:

• EASA→Author→Authoring→Testing

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.

• Close the window with the EASAP that we just tested
• Return to the Tree in the Builder

• Expand the EVENT PROCESSING to show event_submitted and action_submitted
• Right-click action_submitted
  • Select Child→Add EXPORT TO PDF.

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

• Set:

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,

• Right-click OUTPUT→report1
  • Select Child→Add HTML OUTPUT
    • Set:
      • Text: <h2>This report in PDF form: <a href=Report.pdf>Report.pdf</a></h2>

• Save. Test. Now go to EASA→Author→Authoring→Testing. Confirm there is a link to the PDF below in the HTML report under Output.

We have completed the Excel Tutorial.