Guide Section: Excel Desktop Client
Overview | Get started with EDC | Simple EDC Example | EDC template | EDC reference
An Excel Client interface is launched from within an EDC 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.
This schematic details the Excel Desktop Client architecture,
This example provides a concrete introduction to an Excel Desktop Client EASAP.
For an experienced Author we provide streamlined steps, see Build an EDC EASAP from a template
Complete parts A. - I. to build the Mortgage Calculator EDC EASAP - 60min total:
A. Create an EDC EASAP and its database schema - 5min
B. Search and select with CASE VIEWER - 5min
C. Create and modify a case with DATABASE TO EXCEL DESKTOP - 10min
D. Run the example - 5min
E. Complete the lifecycle of a case - 10min
F. Activate a spreadsheet's locked-mode for Excel Client sessions - 5min
G. FILTER results based on case status and term length - 10min
H. Display the number of cases which meet FILTER criteria - 5min
I. Only enable 'modify' and 'delete' buttons when a case is selected - 5min
In this example we use the Mortgage Calculator 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 instance is launched.
The completed Mortgage Calculator (Excel Desktop Client) EASAP may be downloaded and imported
https://help.myeasa.com/resource/easap/6_1/MortgageCalculatorEDC.easap
The steps below generate an EDC EASAP that will enable a User to generate database-persistent mortgage loan records from within an Excel Client session.
1. With a browser download the Mortgage Calculator spreadsheet and an image file:
2. Create a new EASAP.
We recommend resizing this browser window to occupy half of the screen and resizing the EASAP 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
3. Upload a spreadsheet file to the EASAP via the Builder.
4. Set and activate the spreadsheet to use, then 'Save' the EASAP to generate the database schema.
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.
At first 'Save' the spreadsheet's named ranges alongside the metadata fields below, populate a dropdown list for CASE VIEWER > Columns:
5. Use a CASE VIEWER to verify the schema has been created.
We have successfully configured an EASAP to utilize the database-backed Excel Client interface.
In this EASAP we give the User the ability to:
1. Configure a CASE VIEWER.
Below are column formatting details for our choices for Formats:
2. 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 an Action:
1.Add a LAYOUT PANEL and two BUTTONs.
Each button will be linked to an ACTION in order to open an Excel Client with:
2. Add a DATABASE TO EXCEL DESKTOP ACTION to open a new case.
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.
3. Add a DATABASE TO EXCEL DESKTOP to open an Excel Client with an existing case.
4. We want our CASE VIEWER in the EASAP GUI to update once the User clicks: EASA Ribbon > Save
1. Save the EASAP.
2. Click Test, the EASAP below should appear in a browser:
3. 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).
4. Download and launch the temporary spreadsheet:
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.
5. Interact with the spreadsheet as a User might.
6. Click 'EASA > Save' to write 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
7. Close the Excel window; if prompted by Excel, choose 'Don't Save'.
Now the Excel Client file, edc.xlsm, is no longer a valid Excel Client nor is it a valid Excel spreadsheet.
The life cycle for each Excel Client session is below:
Any subsequent attempt to open the empty Excel file simply returns an Excel error; 'Error during download'
8. Return to the browser, our new record should be visible:
9. Explore the rest of the functionality:
A case in the cases table is stored with a 'status' field:
1. Add the ability to delete a case by setting status to DELETED using SET CASE STATUS
2. Refresh the CASE VIEWER after a Delete.
The Builder supports keyboard shortcuts for selected objects or for highlighted text:
CTRL-x to Cut - object or text selected CTRL-c to Copy - object or text selected CTRL-v to Paste (Child) or Paste text - depends on whether an object is selected or text is highlighted CTRL-b to Paste (Sibling) - has no meaning if text is selectedCopy-paste an object on the Tree to make a copy with a new unique name:
Select an object on the Tree then type: CTRL-c Then do either of:
Select a new parent then type: CTRL-v
Select a new sibling then type: CTRL-b If 'Paste (Child or Sibling)' is not valid an error message is displayedCut-paste an object on the Tree, preserving its name:
Select an object on the Tree then type: CTRL-x Then do either of:
Select a new parent then type: CTRL-v
Select a new sibling then type: CTRL-b If 'Paste (Child or Sibling)' is not valid an error message is displayedText cut-copy-paste in the parameter table:
CTRL-x (cut), CTRL-C (copy) and CTRL-V (paste) text between different object's parameter fields.
3. Add a FILTER to hide from display any record with status of DELETED in case_viewer1
4. Add an exit button so a User may confidently close the EASAP.
5. We want to make some cosmetic enhancements for a better User experience.
6. Save and test.
We now have a complete Excel Desktop 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.xlsm 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, detailed here: Implement locked-mode for an Excel Client spreadsheet
1. For illustration purposes these macros have already been added to MortgageCalculatorEDC.xlsm and 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.
2. Our EASAP needs to set this cell to insure that an Excel Client user will not be able to save the formulas or macros in the spreadsheet.
We do this by initializing this named range for each DATABASE TO EXCEL DESKTOP to 'locked' (or any non-empty string)
Now for MortgageCalculatorEDC.xlsm, the only way to extract data is via 'EASA > Save' during an Excel Client session.
Conversely, a password-authorized Author may open and modify MortgageCalculatorEDC.xlsm in Excel without any restrictions.
3. Protect the spreadsheet intellectual property.
In general a customer may wish to protect the spreadsheet content by either:
As the cases table grows, we will need more ways to refine and limit the results set shown in the CASE VIEWER.
We will add two new FILTERs which will take User input from two INPUTBOXs above the CASE VIEWER.
1. First define CSS-style attributes for Label: text on the INPUTBOXs with a new USER INTERFACE STYLE.
2. Create a SUB PANE and add two INPUTBOXs.
3. Add a FILTER for each INPUTBOX to narrow the results set displayed in our CASE VIEWER.
We need our FILTERs to run, and, our CASE VIEWER GUI to update when a User makes a change to either INPUTBOX.
4. Add an EVENT PROCESSING to trigger a REFRESH OBJECTS ACTION for case_viewer1 to follow a VALUE CHANGED EVENT from either inputbox_status or inputbox_term.
5. Save and test the EASAP.
We now have two functional filters which respond to User selected values, 'Status' options are shown below.
When a User changes one of the INPUTBOXs it would be useful to know how many cases meet the modified criterion.
1. Make our CASE VIEWER a larger height to show more cases.
2. CASE VIEWER gives us the size of the results set, if we declare a new SCALAR in Count:
3. Display the size of the current results set in a LABEL.
4. Confirm the EASAP appears as 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.
The SCALAR case_viewer1 has no value until a User selects a case and then case_viewer1 becomes equal 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 User selects a case when it is set to the case ID.
Then we use Enable If: clauses on each button to activate/deactivate the buttons.
1. Create an EXPAND which is '0' until it until it contains a case ID.
2. Enable 'Open Selected Loan' and 'Delete Selected Loan' once a case is selected.
3. Confirm the 'Modify…' and 'Delete…' buttons are greyed out (below) until a case is selected.
Congratulations, our EDC EASAP/Excel 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 interface, see Excel Desktop Client Reference
Overview | Get started with EDC | Simple EDC Example | EDC template | EDC reference