- Excel Desktop Client new !
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.
Below is the detailed architecture that enables an Excel Client EASAP,
Now let's delve into the details….
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,
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.
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.
In the Builder we upload mort_calc.jpg and MortgageCalculatorEDC.xlsm,
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:
To verify that we may now begin to Author an Excel Client EASAP let's configure a CASE VIEWER,
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.
In this EASAP we give the User the ability to,
Now lets configure a CASE VIEWER,
If we Save and then Test; the following CASE VIEWER will appear in the browser,
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
We now have two buttons that each will need an ACTION in order to,
Let's add these ACTION's.
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.
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.
The EASAP below should appear in a browser,
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).
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.
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,
A case in the cases table is stored with a status field:
Let's add the ability to delete a case by setting its status→DELETED using SET CASE STATUS.
Tip, The Builder supports keyboard shortcuts for selected objects or for highlighted text:
Let's add a FILTER to hide from display any record with status→DELETED in case_viewer1
Finally we want an exit button so a User may confidently close the EASAP.
We want to make some cosmetic enhancements for a better User experience,
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.
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
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)
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.
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.
Now we create a SUB PANE and add two INPUTBOX's.
Via a FILTER each INPUTBOX will generate a SQL query that will narrow the results set displayed in our CASE VIEWER.
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).
We now have two functional filters which respond to User selected values, Status options are shown below.
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.
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.
Our EASAP appears below:
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.
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.