Excel Side
The designer uses Excel named ranges as connection points between the workbook and SpreadsheetWEB. We’re going to begin by assigning a named range to our input and output fields.
To break it down, our workbook contains 3 types of fields that we’re going to want to use in the web interface,
- Inputs
- Quote!B2 : Description
- Quote!B3 : Brand
- Quote!B4 : Size
- Inputs’ item list for dropdown feature
- Distinct!A2:A48 : DescriptionList
- Distinct!G2:G278 : BrandList
- Distinct!L2:L7 : SizeList
- Outputs
- Quote!B5 : Price
To add named ranges, begin by selecting that particular cell or range. Then go to the FORMULAS tab and press Define Name (Blue below). Alternatively, you can simply enter the name in to the Reference box (Orange below) after making your selection.
Once you define all named ranges, remember to save your file. Our work in Excel is done.
Designer Side
On the designer side, we’re first going to login to need to create a new web application, upload our spreadsheet, build the web interface, and then modify the visual properties of this web application.
Creating a New Web Application
The Excel model with named ranges can be uploaded as is into a server running SpreadsheetWEB. Go to the web address that’s hosting your software and login with your details.
For our public cloud users, this address is https://www1.spreadsheetweb.com/SpreadsheetWEB/ and for trial users https://trial.spreadsheetweb.com/Lite/.
Once logged into the Control Panel, enter your details and proceed to the Applications page.
Press Create a new Application.
Select Designer option and press Next.
Next, click the Excel Spreadsheet box, or drag and drop your Excel file here. Once it’s uploaded, press Next to continue.
Then, configure the application settings. If this is your first time creating a web application, the only two fields that you should change are the Access and Groups. Access type will make this application public or restricted, depending on your selection. A public application can be accessed by anyone who have access to the URL of this web application, whereas a restricted application will ask for user credentials before they can access.
Group determines the access level for this application. Those who are not Admin level users will be restricted to the applications that are assigned to their group. The groups work like a tree hierarchy, a user belonging to a lower level group will not be able to access an application that is assigned to a higher group. Please keep in mind that a web application can be assigned to more than one group.
Creating the Web Interface
Now, we’re going to create the web application using the Designer. Begin by pressing the orange Edit Designer Application button to go to the designer interface.
Then, click either the User Interface or Home buttons to open the Designer’s UI module. Home is name of first page of the application that can be edited anytime from the Pages module. Default name of first page is always Home.
Begin by inserting a Section by dragging and dropping into the page area. You can think of Sections as individual rows in the user interface and it acts as the backbone of the web UI. All UI components must be placed within a section.
Next, we’re going to need to create the inputs for Description, Brand and Size fields. First input will be the Description. To do this, drag and drop a Dropdown input into the Section box.
Click the new Dropdown input to configure it. Clicking the input will open the side bar that contains options for the selected UI object.
We’re going to need to connect the UI with the uploaded Excel file. Begin by pointing the Named Range selection to the corresponding named range field from the spreadsheet. In our example, we’re going to select Description from the dropdown list to connect this UI input.
The next option here, List Named Range, is where we need to select the list named range for this dropdown input. We select DescriptionList for this example.
Name section determines the label of input – the text that will be printed next to this field.
Uncheck Trigger Calculations checkbox if you’d like calculations to be triggered manually by a calculate button (this is only recommended in complicated models). To save this input, scroll down to the end of sidebar and click Submit.
Next, we repeat the same process for the Brand and Size inputs. We end up with a form structure like this,
The last UI element is the Price calculation. The designer allows creating two types of output fields, Content and Output Grid. While Content is suitable for static labels and single cells, Output Grid is designed for range outputs. In this example, we’re going to be using the Content module and place in under the input fields.
Just like we did when configuring the input fields, click the output module to configure it. Press the Edit Content button to open the Content Editor which is essentially a text editor that allows modifying the text, inserting links, and adding media.
Static text, single cell and one-dimensional named ranges can be inserted into a content field by entering the named range inside double curly brackets ( {{ }} ). You will see the available named ranges in a dropdown format as soon as you start typing “{{“.
For example, we enter “Product Price:” for the label, and then add in double curly braces to include the named range “{{“ (it will look like, Product Price: {{). This will populate the dropdown selection.
Once the named range is selected, remember to close brackets with “}}”. You can change the text formatting using the toolbar, or by switching to Code View (to edit the HTML code directly). To open Code View click the </> icon.
To save the Content field, click the Fullscreen icon and then click the Submit button on the sidebar menu.
We are done with the user interface! You can use the Preview mode to confirm the web interface before publishing it. Preview mode can be found under the contextual menu on the top toolbar, or on the bottom of the sidebar when no objects are selected.
Contextual menu access,
Sidebar access,
All calculations and UI elements can be tested in Preview Mode, but Save function will not be available.
Changing the Visual Properties of the Web Application Using CSS
Visual properties of the web application can be further enhanced with themes. Stylesheets offer several CSS templates to choose from. This module also allows applying your own custom CSS based styling scripts. Stylesheets module can be accessed from the dial menu.
To select from a template or add your own stylesheet, click Add Stylesheet.
Preset CSS templates can be selected from Style Template list.
Selecting a style will automatically populate the Stylesheet section. Here, darkly.bootstrap.css was selected.
You can modify the selected template, or simply change it with your own code and then click Update to apply the new style.
If everything looks good, publish the web application by pressing the Publish button found in the Dial Menu.
Once published, the application will be available from the SpreadsheetWEB Control Panel. You can also use the application URL to share it with others.
