Table of Contents
The Designer is a new component of the SpreadsheetWeb platform that allows users to design and develop responsive web applications while utilizing a spreadsheet model as a back-end calculation engine.
Comparing the Approach
The standard approach for generating a SpreadsheetWeb application is known as the 'converted' approach, wherein the user generates the user interface using the Excel add-in (known as the SpreadsheetWeb Wizard).
The display formatting and a whole variety of settings are captured from the spreadsheet model during this conversion process and the finalized application mirrors the aesthetics of the spreadsheet's user interface.
Some of the benefits of this approach are as follows:
- Quick and easy generation of a user interface and web application based on the spreadsheet model.
- Mirrors the appearance of the underlying spreadsheet model if your users have become accustomed to it.
- Minimal configuration necessary for implementation and definition of data structure and input-to-workbook mapping.
However, the ease of conversion comes at the cost of design flexibility and customization, since the user interface directly mirrors the appearance of the spreadsheet model. This can prove detrimental in an increasingly mobile-oriented landscape, where end users prefer to access their web applications from non-desktop devices with limited screen real estate.
Likewise, making modifications to the user interface requires back-tracking to the underlying spreadsheet model and conversion wizard, meaning reliance on a client-side and a server-side component when making changes to the appearance of the web application.
The purpose of the Designer was to create a new means of generating user interfaces for spreadsheet-based calculation models that could be responsive and decoupled from the underlying calculation model.
This approach alleviates the requirement for returning to the SpreadsheetWeb Wizard add-in for conversion and user interface modifications - the spreadsheet model is solely used for calculations while the entire user interface can be designed and created from the web browser.
To facilitate the creation of these responsive applications, we endeavored to develop the most extensible and accessible design platform for our end users, tailoring the experience around familiar concepts to spreadsheet-oriented business users, such as the extensive use of named ranges to create linkages throughout the system.
We apportioned the application development process into several unique components that can be used in conjunction to develop your ideal application:
- The User Interface Designer, which is a friendly, web-based, drag-and-drop UI designer for customizing and building out the pages of your application.
- The Database Designer, which is used to generate database tables for storage and retention of the data that you seek to capture within your application.
- The Page Designer, which is used to create multiple screens and segment your application's workflow and end-user experience fluidly.
- The Stylesheet Designer, which allows you full customization control over the appearance of your application through standard Cascading Style Sheets (CSS) support.
- The built-in Help Module, which provides contextual hints throughout the system to aid you in generating the perfect user interface.
On the Home Screen of the Designer, you will notice these corresponding modules, as well as a breakdown of their usage throughout your application. These deep links can aid you in navigation throughout the system.
One of the most critical differences between the Designer approach and the standard approach to generating SpreadsheetWeb applications is the idea of a Transaction.
In standard, converted applications, your development environment is Excel. You build your user interface to your satisfaction by selecting from ranges in the workbook that are then serialized and ultimately converted into your user interface at runtime. Once you are done, you simply upload the application to the web server and it replaces your existing application.
Since the Designer approach is purely web-based, you no longer have a separate Excel-based user interface designer component. As a result, the designing process is actively occurring on the web server as you make changes to the various components of your application.
In turn, there must be a delineation between an application that is in design-mode versus an application that is in production-mode, since you may be making changes to your application and its user interface that you are not ready to deploy for your end users.
This is the concept of a Transaction. Each transaction constitutes a version of your application. When you first create a Designer application, the system creates your first work-in-progress transaction. Insofar as you are capable of editing the application's user interface, you are acting upon a work-in-progress version of your application.
When you are modifying your application as part of your first transaction, there is no production version of your application. Your end users are not capable of accessing your application, entering or saving any data, or performing any calculations. You are free to make all of your modifications without fear that your changes will have an impact on your end users.
The process of turning your application over to your end users (i.e. finalizing and committing your transaction) is called Publishing. Publishing a transaction locks the transaction down and commits your changes to the production/published version of your application.
Until you publish your transaction, you will be able to freely leave the system and enter the design-mode of the application again. All changes that you make will be saved with your current work-in-progress transaction until that transaction is published.
Publishing a transaction is a permanent action - you will not be able to edit that transaction again. Instead, when you next want to make changes to your spreadsheet model or to your web interface, you will be prompted with a message indicating that there is no current work-in-progress transaction and that you can create a new transaction.
Upon starting a new transaction, all of your application's data, changes, controls, and interface settings will be copied over to your new transaction, where you can freely make any and all additional changes. Changes made to your new transaction will not affect the production/published version of your application until you publish again.
This means that while you are in design-mode on the new work-in-progress transaction, you can make any changes without concern for how it will affect the current users of your application.
In this way, the system catalogues all of the changes that are made to your application over time and allows you to safely make modifications to your user interface without impacting your existing users.
If you have a work-in-progress transaction that you would like to remove, either for the purpose of starting again from the previous transaction as a baseline or because it was created unintentionally, any work-in-progress transactions (after the first publish) can always be deleted from the Transaction History screen in the Edit Application page of the SpreadsheetWeb Control Panel.
In order to preview an application while it is in design-mode, you can use the Preview button, which is always accessible from the dial in the Designer header, or from the properties menu in the User Interface Designer module.
Previewing an application will allow you to see exactly what that application will look like once it has been published. Preview actions also include calculations, so your model will be fully operational and testable. It also includes validation logic, navigation, and custom stylesheet support, in order to precisely mirror the finalized version of your application. The only feature that is disabled during Preview actions is save actions.
Before a preview occurs, a subset of the application validation rules will be run against your model to indicate whether there are any issues with the generated design that would prevent the user interface from operating appropriately. Critical errors will prevent you from previewing your application until you have resolved them based on the details provided on the validation screen.
Performance during preview actions will be degraded slightly from the final published version of the application. Preview actions utilize dedicated sessions, meaning that your calculations occur on node that is dedicated to your user session. This means that previewing the application includes the overhead of loading a clean version of your calculation model onto the engine from scratch each time that the preview occurs.
Once published, your application will be pre-loaded onto nodes and concurrency distribution and load balancing will be the same as with standard SpreadsheetWeb applications.
In order to publish a work-in-progress transaction from design-mode, you can use the Publish button, which is always accessible from the dial in the Designer header.
Before a publish occurs, a full set of application validation rules will be run against your model to indicate whether there are any issues with the generated design that would prevent the user interface from operating appropriately. Critical errors will prevent you from publishing your application until you have resolved them based on the details provided on the validation screen.
For more details regarding publishing and transactions, see the Transactions section above.
In order to prevent common mistakes throughout the system, a series of system-wide validation rules have been implemented to ensure the integrity of your web application before and after critical application life-cycle actions are taken:
- Uploading a New Workbook
- Previewing an Application
- Publishing a Transaction
When errors of critical severity are detected, you will be prevented from performing the target action and redirected to the Validations page, where a list of these messages and detailed information on how to resolve them will be provided.
If any ambiguous or unclear messages are shown, feel free to reach out to the SpreadsheetWeb Support Team. The finalized validation messaging will be released with the full release of the Designer.
Throughout the system, the Designer will make reference to Named Ranges. This is the primary construct by which the designer interacts with the spreadsheet model.
To utilize a cell or series of cells in the Designer, you must first define that range as a Named Range in Excel.
As a result, it is prudent to go through your workbook and identify inputs and outputs before beginning the development process. Of course, if you need to add additional ranges in the future, you can simply modify your workbook and then re-upload the new version from the Edit Application page of the Designer.
In many portions of the system, we identify named ranges by their respective sizes:
- Single Named Ranges (SNR) are ranges that refer to a single cell. These ranges are 1 x 1 (Width x Height) and have clearly identifiable types. For example, you may see reference to Boolean Single Named Ranges, which are SNRs that evaluate into TRUE or FALSE. These are the baseline for binding any single columns on a database table or single inputs, such as Textboxes or Calendars.
- List Named Ranges (LNR) are ranges that refer to a one-dimensional set of cells. These ranges are N x 1 or 1 x N (Width x Height) and are most commonly utilized in defining lists of various types throughout the system, such as those associated with Radio Button Lists or Dropdown selection values.
- Table Named Ranges (TNR) are ranges that refer to a two-dimensional set of cells. These ranges N x M (Width x Height) and are most commonly utilized when binding grid-type inputs, such as Output and Input Grids, or defining ranges for One-to-Many Secondary Tables.
Layout of Designer
The Designer layout is broken up into multiple panels and sections.
The top menu is known as the Header. The Header is available from everywhere in the system and includes a series of useful buttons for quick navigation and help.
The button to toggle the Context Menu is located at the left side of the Header (three horizontal lines, highlighted above). As noted by its name, the Context Menu differs in purpose depending on which module you are in.
For example, the context menu in the database designer module will allow you to quickly navigation between database tables or create new ones. In the user interface designer, the context menu is a toolbox with all of the available controls that you can use to build your user interface.
The Search Bar can be used to search the help module pages for any relevant information regarding your search criteria. It is always available from the central area of the Header.
Some suggested pages will be visible below the search bar as you type. If you would like the full search results, simply hit Enter in the search bar to be taken to a list of all results.
The Navigation Dial facilitates quick navigation between modules in the Designer. It is always available from the right-hand side of the Search Bar.
The Validation Notifications will indicate if there are any warning or critical error messages from the most recent validation process run against your application. Clicking the icon will show the first several validation messages. Clicking on Show All will bring you to the full validation message list.
The Properties Menu is only accessible from the User Interface Designer module. It houses the Quick Navigation links, as well as the properties of a control when one is selected.
Application Removal from Control Panel
When you remove a designer application, any data that has been stored in association with the application will be deleted permanently and cannot be recovered.