Chelsea DevereauxMany business applications need to interact with various data sources and present them in creative and intuitive ways, such as dashboards for quick data analysis and help in making better business decisions. Most recently, in the v16 release SpreadJS, our JavaScript spreadsheet provides the new applicable Excel-Like forms to make adding advanced data entry and selections to your worksheets easier.
The form controls can be used to easily reference and interact with cell data, such as adding controls to chart sheets. Excel provides several controls for dialog sheets that are useful for selecting items from a list.
In this blog, we create a fully functional Loan Calculator using the SpreadJS Form Controls to add several different types of form controls within the JavaScript Spreadsheet.
Getting Started
The form controls of SpreadJS can be used to add advanced data entry and selections to your worksheets, making it easier to reference and interact with cell data. With the v16 release of SpreadJS, some exciting Excel-Like form controls are waiting for you to try.
In this sample, we will guide how to create a functional loan calculator using these new imperative features. You will see how user-friendly form controls can be organized by styling and positioning them on different areas of the worksheet.
So let's get started!
Download the example file here and follow along.
Loan Amount
The Spin Button control can be great for specifying the loan amount. It allows you to increase or decrease a value easily, and by a predetermined amount.
In SpreadJS, you can add the spinButton form control to the worksheet by passing the spinButton of the FormControlType enumeration as a parameter in the addFormControl method, as shown below:
//add spinButton
let spinButton = sheet.shapes.addFormControl("spin button", GC.Spread.Sheets.Shapes.
FormControlType.spinButton 50, 50, 160, 100);
You can also set spinButton options using the options method and assign value.
//set spinButton options
var options = spinButton.options();
options.minValue = 100000;
options.maxValue = 3000000;
options.step = 100;
options.cellLink = "'Loan Calculations'!C4";
spinButton.options(options);
Another way to add this control to your worksheet is by using the Designer component that SpreadJS offers.
In the Insert tab, click on the Controls panel and choose the ‘Spin Button’ control type. You can define spinButton options by using the Format Shape dialog box.
Control options give you the capability to define the spin button options and assign values as below:
Loan Term
To help you determine the loan term, a possible option can be using the Combo Box control. Combo Boxes are drop-down elements that allow the selection of any value from the values listed in the drop-down list.
To make our calculator suitable for both short-term and long-term loans, we have listed 1 to 25-year periods for you to specify your loan time frame.
You can add in your worksheet the comboBox option of the FormControlType enumeration as a parameter in the addFormControl method.
//add comboBox
var comboBox = sheet.shapes.addFormControl("comboBox", GC.Spread.Sheets.Shapes.
FormControlType.comboBox, 100, 50, 200, 30);
You can also set the comboBox options using the options method, assign input range, set dropdown length, and link a cell.
//set comboBox options
var options = comboBox.options();
options.inputRange = "'Loan Term'!A2:A26";
options.cellLink = "'Loan Calculations'!C7";
options.dropDownLines = 4;
comboBox.options(options);
comboBox.value(14);
Interest Rate
To define the Interest Rate, a good idea is to use the Group Box control since it logically groups the controls in the form, improving its readability. We have presented Interest Rates as Option Buttons that only allow exclusive selections inside the groupBox.
To add a groupBox form control to the JavaScript worksheet, you need to pass the groupBox option of the FormControlType enumeration as a parameter in the addFormControl method.
//add groupBox
var groupBox = sheet.shapes.addFormControl("groupBox", GC.Spread.Sheets.Shapes.
FormControlType.groupBox, 40, 130, 240, 80);
If you use the Designer component, you can insert the groupBox by clicking on the Insert tab and choosing the groupBox control on the Form Control panel.
Similarly, you can add an optionButton in a Javascript spreadsheet, bypassing the optionButton control of the FormControlType enumeration as a parameter in the addFormControl method.
//add optionButton
var optionButton = sheet.shapes.addFormControl("optionButton", GC.Spread.Sheets.Shapes.
FormControlType.optionButton, 50, 50, 100, 30);
In addition, set the control's options using the options method and linking a cell.
//set optionButton options
var options = optionButton.options();
options.cellLink = "'Loan Calculations'!C8";
optionButton.options(options);
optionButton.value(true);
Another way to apply to add an optionButton to the worksheet is by clicking on the Control panel under the Insert tab.
You can specify the control options below by clicking on the Format Shape dialog box.
We have linked the interest rate optionButton with the C8 cell from the ‘Loan Calculations’ sheet, where we have calculated the Interest Rate.
Down Payment
A Down Payment is a sum of money a buyer pays in the early stages of purchasing an expensive good or service. As mentioned, form controls can be incorporated into your dashboards, making interacting with charts and formulas easy. One of these form controls is the List Box control that we have used to define our Down Payment.
You can add a listBox form control to the worksheet. This can be done by passing the listBox option of the FormControlType enumeration as a parameter in the addFormControl method.
//add listBox
var listBox = sheet.shapes.addFormControl("listBox", GC.Spread.Sheets.Shapes.FormControlType
.listBox, 100, 50, 200, 150);
You can also set the listbox options using the options method, assign input range, set selection type and link a cell. So, when the user changes the value in the listbox control, the value in the cell also changes.
//set listBox options
var options = listBox.options();
options.inputRange = "'Down Payment (%)'!A2:A11;
options.cellLink = "'Loan Calculations'!C5";
options.selectionType = GC.Spread.Sheets.Shapes.ListBoxSelectionType.single;
listBox.options(options);
Loan Calculations
You will find below the formula references that we have used to calculate the financed amount, monthly repayment, total interest, and total repayment of the loan amount defined earlier.
- Amount Financed: Loan Amount - (Loan Amount * Down Payments) - Arrangement Fee.
- Monthly Repayment: PMT(Interest/12, Term*12, Amount Financed)
- Total Repayment: Monthly Repayment * Term
- Total Interest: Total Repaid - Amount Financed
Finally, you have created a Loan Calculator using the SpreadJS form controls feature.
