User Tools

Site Tools

Author a simple Excel Client EASAP

Guide Section: Excel Desktop Client
Overview | Get started with EASA Eval | Get started with EDC | Simple EDC Example | EDC reference

An Excel Client interface is launched from within an EASAP.

CASE VIEWER is a GUI object on the Tree with visual and functional similarities to a DATA TABLE but specialized for the search, retrieval and selection of a database 'case' record, and subsequent launch of an Excel Client.

  • CASE VIEWER may have a Child FILTER to narrow a search.
  • An Excel Client instance is opened with data from the cases table.
  • Cells may be modified and new values propagated.
  • After an EASA Ribbon→Save all the named ranges are written back to the cases table.

Below is the detailed architecture that enables an Excel Client EASAP,

Now let's delve into the details….

Author a Simple Excel Client EASAP: Mortgage Calculator

In this example we use the Mortgage Calculator Excel spreadsheet to calculate a loan repayment schedule.

We use the terms 'case' and 'case record' to signify the particular values of all of the Excel named ranges at the beginning or end of some analysis using an Excel spreadsheet.

We do not lose any generality by calling this data a case or a case record.

In this example a case represents the values of the named ranges (inputs and outputs) for a particular mortgage as defined within a mortgage calculator spreadsheet.

The existing spreadsheet contains the default case. We will,

  • Open the spreadsheet in an Excel Client via an EASAP's,
  • Modify the loan amount and repayment period on the spreadsheet.
  • Save the new spreadsheet data as new case in a database; in the Excel Client select,
    • EASA Ribbon→Save

The User may create, view, modify and save a case almost entirely within a familiar Excel spreadsheet interface.

The EASA Ribbon will be installed automatically the first time an Excel Client EASAP is launched.

The completed Mortgage Calculator (Excel Desktop Client) EASAP may be downloaded and imported.

Follow these steps to Author an EASAP that will enable a User to generate database-persistent mortgage loan records from within an Excel Client session.

  • With a web browser download MortgageCalculatorEDC.xls,
  • Navigate to the EASA Server
  • Login as an Author
  • Under EASA→Applications→New
    • Click Create
    • Click the EASAP Builder link
    • Download then double-click the atos.easa file:
      • The New Application Wizard will launch
      • Optionally complete the fields,
        • We have named ours Mortgage Calculator (EDC)
      • Click Next
      • Select Use EASAP Builder
      • Select Finish to manually create an EASAP
        • → The Builder will open

We recommend resizing this browser window to occupy half of the screen and resizing the Builder window to occupy the remaining space to lower the chance of typo or a missed step.

  • The Excel Client does not support the binary .xlsb Excel format.
    • To translate to .xlsm
      • Open an .xlsb file in Excel and save it as .xlsm

In the Builder we upload mort_calc.jpg and MortgageCalculatorEDC.xlsm,

  • Click the ↑Files tab
    • Click Browse and find the file mort_calc_edc.jpg that we downloaded above.
      • Select Upload
    • Do the same Browse then Upload for the spreadsheet MortgageCalculatorEDC.xls
  • Confirm .jpg and .xls spreadsheet files each appear under the ↑Files tab, below
    • author/Easap1/development/MortgageCalculatorEDC.xlsm
    • author/Easap1/development/mort_calc.jpg
  • Click the Tree tab
      • Set→Selection Image: mort_calc_edc.jpg
      • Select Child→Add SPREADSHEET
        • Set,
          • File: MortgageCalculatorEDC.xls
          • Desktop: TRUE
            • → This step activates the Excel Client interface for the EASAP
    • Save the EASAP

The database will create records for the spreadsheet itself and the cases table to store the values of each named range therein.

(Due to some unfortunate internal behavior of Excel, any named range with a prefix prop_ or any combination of this sequence of capital or lowercase letters will cause the complete loss of data integrity within that range. For an Excel Client spreadsheet rename any named range with the prefix prop_ to my_prop_ or property_, etc…)

Additionally, the database inserts metadata fields, below, which along with the spreadsheet named ranges populate a dropdown list for CASE VIEWER's Columns:

  • created datetime
  • created_by varchar(255)
  • last_modified datetime
  • last_modified_by varchar(255)
  • status varchar(255)

To verify that we may now begin to Author an Excel Client EASAP let's configure a CASE VIEWER,

  • Right-click USER INTERFACE→TABBED PANE LIST→tabbed_pane1
    • Select Child→Add CASE VIEWER
      • Click the Columns: field
        • → The Columns: list includes all the named ranges from the spreadsheet
        • Click Cancel as we will configure case_viewer1 in the next part.

We have successfully configured an EASAP to use the Excel Client.

Congratulations, it is time to proceed with Authoring our example.

The next seven sections contain the details an Author needs to write an Excel Client EASAP in the Builder.

1. Search and Select with CASE VIEWER

In this EASAP we give the User the ability to,

  • View existing mortgage loan records inside an EASAP with CASE VIEWER
  • Create a new mortgage loan record in an Excel Client
  • Modify an existing mortgage loan in an Excel Client

Now lets configure a CASE VIEWER,

  • Select tabbed_pane1
    • Set→Tab:Mortgage Calculator
      • Select case_viewer1 from above
        • Set,
          • Columns: loan_amount, term, approx_mo_pay, status, last modified by, last modified
          • Column Names: Loan Amount,Term, Monthly Payment, Status, Last Modified By, Last Modified
          • Formats: $%,.0f/%.0f/$%,.2f/%s/%s/%s
            • ($%,.0f → dollar-sign plus a zero decimal place, floating point number with a regional thousands separator)
            • ($%,.2f → dollar-sign plus a two decimal place, floating point number with a regional thousands separator)
            • (%s → format a string of text)
          • Separator for Formats: /
            • (/ is the separator between the format specifiers in Formats:)
          • Pattern: dd-MMM-yyyy

If we Save and then Test; the following CASE VIEWER will appear in the browser,

2. Create and Modify a Case with DATABASE TO EXCEL DESKTOP

The DATABASE TO EXCEL DESKTOP ACTION follows from an EASAP-originating EVENT, we use a BUTTON.

DATABASE TO EXCEL DESKTOP launches a local Excel Client on a User's desktop via the Action: parameter.

The first BUTTON will set DATABASE TO EXCEL DESKTOP→Action:New
→Open an Excel Client with default fields from the original spreadsheet

The second BUTTON will set DATABASE TO EXCEL DESKTOP→Action:Open
→Open an Excel Client with data from a database case record and selected via a CASE VIEWER

  • Right-click case_viewer1
    • Select Sibling→Add LAYOUT PANEL
      • Rename it layout_panel_buttons
        • Set→Layout: HORIZONTAL
  • Right-click layout_panel_buttons
    • Select Child→Add BUTTON, twice
      • Rename the first one button_new
        • Set→Label: Create New Loan
      • Rename the second one button_open
        • Set→Label: Modify Selected Loan

We now have two buttons that each will need an ACTION in order to,

  • Open an Excel Client with default values from the spreadsheet
  • Open an Excel Client with cell values from an existing database case.

Let's add these ACTION's.

  • Right-click button_new
  • Right-click database_to_excel_desktop1
    • Select Sibling→Add REFRESH OBJECTS
      • Rename it refresh_case_viewer
        • Set→Objects: case_viewer1

The REFRESH OBJECTS will refresh our CASE VIEWER immediately after an Excel Client has launched, we will see that a new record has been INSERTed into the database with default cell values prior to any interaction from within the Excel Client.

Let's create a second ACTION that will open an existing case inside an Excel Client.

  • Right-click button_open
      • Set,
        • Action: Open
        • Case Id: %case_viewer1%
          • (case_viewer1 takes value of a User selected 'case id')

We want our CASE VIEWER in the EASAP GUI to update once the User clicks Save on the EASA Ribbon.

Set a parameter so the EASAP checks the EASA Server every second for updated data and configure the Excel Client to report the EASA Ribbon→Save back to the EASA Server in addition to the database.

  • Set, USER INTERFACE→Results Time: 1
  • Set, USER INTERFACE→TABBED PANE LIST→tabbed_pane1→case_viewer1→Refresh on Save: TRUE

3. Run the Example

  • Save the EASAP
  • Click Test

The EASAP below should appear in a browser,

  • Click Create New Loan

A new case record with default data is created in the cases database table.

The EASAP will prompt the User to save an automatically-generated temporary spreadsheet containing default case data (you may need to disable a popup blocker).

  • Open this Excel spreadsheet.
  • The first time an Excel Desktop Client EASAP is run, the User will be prompted to install the EASA Ribbon
    • Select 'Yes' in the popup window, then click 'Open Spreadsheet' to continue
      • (To later remove the EASA Ribbon from Excel click EASA→Options→Remove)

After the EASA Ribbon is installed and a temporary spreadsheet is opened the interface below will appear.

We call this instance of a database-linked Excel spreadsheet an Excel Client.

  • Change some of the default fields, our example uses:
    • loan_amount→400000
    • term→25
  • Click EASA→Save

Save writes the modified data in Excel named ranges back to a case record in the database.

A message window announces, Your case has been saved successfully → Click OK.

Close the Excel window; if prompted by Excel, choose Don't Save

The Excel Client file (-automaticOpen.xlsm) is no longer a valid Excel Client nor is it a valid Excel spreadsheet.

Every Excel Client session is initiated from the CASE VIEWER and a new temporary spreadsheet is generated, downloaded, opened and eventually 'cleaned' of its formulas, macros and data.

The empty Excel file which remains will, if opened, simply return an Excel error → Error during download

Return to the browser, our new record should be visible, below,

  • Create a few more cases in this way.
  • Now highlight one of them and click Modify Selected Loan button.
  • Change one or more values within an Excel Client
  • Click EASA→Save

4. Complete the Lifecycle of a Case

A case in the cases table is stored with a status field:

  • NEW
  • Creating a new case stores a default case record and metadata with status→NEW
  • Upon EASA→Save within an Excel Client the case data is recorded with status→IN_PROGRESS
  • status→FINISHED may be used to show some later validation step from any database client
  • status→DELETED may be used to remove a case from active use in a non-destructive way

Let's add the ability to delete a case by setting its status→DELETED using SET CASE STATUS.

  • Right-click button_open
    • Select Sibling→Add BUTTON
      • Rename it button_delete
        • Set→Label: Delete Selected Loan
  • Right-click button_delete
    • Select Child→Add SET CASE STATUS
      • For it set,
        • Status: Deleted
          • (this is actually mapped to the string DELETED)
        • Case Id: %case_viewer1%
  • Right-click tabbed_pane1→layout_panel_buttons→button_new→refresh_case_viewer1
    • Select Copy
  • Right-click button_delete→set_case_status1
    • Select Paste (Sibling)
      • (This will update case_viewer1 in the EASAP GUI just as with button_new)

Tip, The Builder supports keyboard shortcuts for selected objects or for highlighted text:

  • CTRL-xCut (object or text selected)
  • CTRL-cCopy (object or text selected)
  • CTRL-vPaste (Child) or Paste text (depends on whether an object is selected or text is highlighted)
  • CTRL-bPaste (Sibling) (has no meaning if text is selected)
  • Two contexts are outlined below,
    1. An object is selected on the Tree
      • Quickly duplicate an object with a new, unique name,
        • CTRL-c
        • CTRL-v
      • Quickly move an object, preserving its name,
        • CTRL-x
        • Then either,
          • Select a Parent then CTRL-v
            • or
          • Select a Sibling then CTRL-b
        • If the Paste (Child or Sibling) is not valid an error message is displayed
    2. Text input rectangle is highlighted in an object's parameter table
      • Quickly Cut, Copy and Paste text to and from different object's parameter fields

Let's add a FILTER to hide from display any record with status→DELETED in case_viewer1

  • Right-click case_viewer1
    • Select Child→Add FILTER
      • Rename it filter_not_deleted
        • Set,
          • Value: DELETED
          • Static Operator: NOT_EQUAL
          • Case Data to Compare: status

Finally we want an exit button so a User may confidently close the EASAP.

  • Right-click button_delete
    • Select Sibling→Add BUTTON
      • Rename it button_exit
        • Set→Label: Exit
  • Right-click button_exit
    • Select Child→Add MENU ACTION
      • Rename it menu_action_exit
        • Select menu_action_exit
          • Set→Action:Exit
            • (Equivalent to a User clicking the grey X in an EASAP)

We want to make some cosmetic enhancements for a better User experience,

  • Let's make our buttons larger by,
    • raising the font size
    • adding a margin around the text
  • Let's add more space or padding between the buttons
  • Right-click USER INTERFACE
        • Select Child→Add USER INTERFACE STYLES
  • Right-click button_style1
    • Select Child→Add TEXT STYLE
      • Set,
        • Font Size:12
        • Weight: bold
  • Right-click TEXT STYLE
    • Select Sibling→Add MARGIN
      • Set,
        • Top: 5px
        • Right: 5px
        • Bottom: 5px
        • Left: 5px
  • Right-click MARGIN
    • Select Sibling→Add PADDING
      • Set,
        • Right: 5px
        • Left: 5px
  • For each of the following buttons:
    • button_new
    • button_open
    • button_delete
    • button_exit
      • Set→ Style: button_style1
  • Save and Test
  • Select one of the mortgage loans
  • Click Delete Selected Loan
  • One second later the case record should disappear from the CASE VIEWER.
    • →Actual destruction of a case record is not supported because of the possibility of a later audit.

We now have a complete Excel Client example.

We have created an EASAP, below, that uses a temporary Excel spreadsheet on the User's desktop as its user interface and stores a modified case record back to a database.

5. Activate a spreadsheet's locked-mode for Excel Client sessions

If MortgageCalculatorEDC.xls were to contain proprietary data in its formulas or VBA we would want to insure the spreadsheet is locked by adding and activating two VBA macros.

For illustration purposes these macros have already been added to MortgageCalculatorEDC.xlsm, we may examine them

  • Start Excel
  • Select the Developer tab
  • Click Visual Basic
  • Expand VBAProject (MortgageCalculatorEDC.xlsm)
  • Enter the workbook password → my_workbook_password
  • Double-click ThisWorkbook to see the two macros. (You may need to select View→Code or <F7>)

Each of these macros is 'active' when the (locked) 'EASA' named range on the (password protected) 'EASA' sheet is non-empty.

Our EASAP needs to set this cell to a string to insure that an Excel Client user will not be able to save the formulas or macros in the spreasheet.

We do this by initializing this named range in DATABASE TO EXCEL DESKTOP to 'locked' (or any non-empty string)

  • Select layout_panel_buttons→button_new→database_to_excel_desktop1
    • Set,
      • Initialize Named Ranges: EASA (MortgageCalculatorEDC.xlsm)
      • Initialization Data: locked
  • Save and Test
  • Click Create New Loan
  • Open the temporary spreadsheet.
  • Select File→Save or click the diskette icon ( )
    • → A message explains that save is disabled because this spreadsheet is locked

Now for MortgageCalculatorEDC.xlsm only EASA→Save is allowed during an Excel Client session.

Conversely, a password-authorized Author may open and modify MortgageCalculatorEDC.xlsm in Excel without any restrictions.

6. FILTER results based on case status and term length

As the cases table grows, we will need more ways to refine and limit the results set shown in the CASE VIEWER.

Let's add two new FILTER's which will take User input from two INPUTBOX's above the CASE VIEWER.

We will adjust the CSS-style attributes of the Label: text on the INPUTBOX's with a new USER INTERFACE STYLE.

    • Select Sibling→Add USER INTERFACE STYLE
      • Rename it label_style1
        • Right-click label_style1
          • Select Child→Add TEXT STYLE
            • Set,
              • Font Size:10
              • Weight: bold
              • Alignment: right
        • Right-click TEXT STYLE
          • Select Sibling→Add PADDING
            • Set,
              • Right: 5px
              • Left: 5px

Now we create a SUB PANE and add two INPUTBOX's.

  • Right-click tabbed_pane1
    • Select Child→Add SUB PANE
      • Rename it sub_pane_filters
        • Set→Layout: HORIZONTAL
  • Right-click sub_pane_filters
    • Select Child→Add INPUTBOX
      • Rename it inputbox_status
        • Set,
          • Label: Status:
          • Label Style: label_style1
  • Right-click inputbox_status
    • Select Child→Add DATA VALIDATION
      • Set,
        • Allow:List
        • Ignore Blank:FALSE
        • Source: ,NEW, IN_PROGRESS, FINISHED
          • (note the initial comma, we allow a blank option which accepts all status possibilities)
  • Right-click inputbox_status
    • Select Copy
      • Right-click inputbox_status
        • Select Paste (Sibling)
          • Rename it inputbox_term
            • Set→Label: Term:
  • Set, Term→DATA VALIDATION→Source: ,15,20,25,30
    • (note the initial comma, we allow a blank option which accepts all term possibilities)

Via a FILTER each INPUTBOX will generate a SQL query that will narrow the results set displayed in our CASE VIEWER.

  • Right-click case_viewer1→filter_not_deleted
    • Select Sibling→Add FILTER
      • Rename it filter_status
        • Set,
          • Value:%inputbox_status%
          • Static Operator: EQUAL
          • Case Data to Compare: status
    • Right-click filter_status
      • Select Copy then Paste (Sibling)
        • Rename it filter_term
          • Set,
            • Value: %inputbox_term%
            • Excel Value To Compare: term

We need our FILTER's to run and our CASE VIEWER's GUI to update when a User makes a change to either INPUTBOX.

An EVENT PROCESSING allows us to trigger a REFRESH OBJECTS ACTION (refresh case_viewer1) from a VALUE CHANGED EVENT (changes in either inputbox_status or inputbox_term).

  • Right-click sub_pane_filters
    • Select Sibling→Add EVENT PROCESSING
      • Rename it event_processing_filters
        • Expand it with the plus box
          • Right-click event1
            • Select Child→Add VALUE CHANGED
              • Rename it value_changed_filters
                • Set→ Objects: inputbox_status, inputbox_term
          • Right-click tabbed_pane1→layout_panel_buttons→database_to_excel_desktop1→refresh_case_viewer
            • Select Copy
          • Right-click action_group1
            • Select Paste (Child)
  • Save and Test the EASAP

We now have two functional filters which respond to User selected values, Status options are shown below.

7. Display the number of cases which meet FILTER criteria

When a User changes one of the INPUTBOX's it would be useful to know how many cases meet the modified criterion. We also should make our CASE VIEWER a larger height to show more cases.

  • Set, case_viewer1→Height: 200
    • (in a real application this number should be slightly less than the vertical display height in pixels)

CASE VIEWER→Count: gives us the size of the results set.

Declare a new SCALAR, total_cases, in Count: then display this number in a LABEL to show the current results set length.

  • Select case_viewer1
    • Set→Count: total_cases
  • Right-click event_processing_filters
    • Select Sibling→Add LABEL
      • Set,
        • Label: Number of Loans: %total_cases%
        • Style: label_style1

Our EASAP appears below:

8. Only enable 'Modify' and 'Delete' buttons when a case is selected

Two of our buttons don't have any significance until a case is selected; we need to disable them until they are valid actions.

CASE VIEWER has no value until a User selects a case and then CASE VIEWER has as SCALAR value corresponding to the case id of the selected case. Below we use an EXPAND to create a SCALAR which will have the value 0 until a case is selected after which it has the value of case id, a positive integer. Then we use Enable If: clauses on each button to activate/deactivate the buttons.

  • Right-click USER INTERFACE→tabbed_pane1
    • Select Child→Add DATA PROCESSING
      • Expand it
        • Right-click SCALAR
          • Select Child→Add EXPAND
            • Rename it Selected_loan
              • Set,
                • Value: %case_viewer1%
                • Value On Error: 0
  • Select USER INTERFACE→tabbed_pane1→layout_panel_buttons→button_open
    • Set→ Enable If: Selected_loan>0
  • Select USER INTERFACE→tabbed_pane1→layout_panel_buttons→button_delete
    • Set→ Enable If: Selected_loan>0

The 'Modify' and 'Delete' buttons are greyed out (below) until a case is selected.

Congratulations, the Excel Desktop Client tutorial is complete.

An Author may now Publish the EASAP to make it available to a User.

Finally, there are a few caveats to using an Excel spreadsheet as an temporary Excel Client interface.

Page Tools