With SpreadJS v13, we have added new features to help execute complicated forms. The main feature is cell drop-downs, which are built-in menus that can be implemented as styles within cells in the sheet. We have also added cell buttons and states, which will allow you to control how the user interacts with your form.
Here, we'll show you how to use all of these features (in addition to cell padding and labels) to create a submission form for product issues.
Creating a Template for the Javascript Spreadsheet
I created a template for the Spread instance: one sheet to list the issues that the user has submitted, and one that houses the form that the user will fill out when creating or editing an issue. For the "Issues" sheet, I left it blank as we will add a data-bound table with code. For the "Issue Details" sheet, I merged a few cells and set up background colors and borders, in addition to removing gridlines and headers. Saving this SSJSON and then exporting to a JavaScript file, we can now reference this file in our application.
Creating the Application
In this demo, our application is just going to be a simple HTML page with references to JavaScript files. We can go ahead and create the HTML page and reference the SpreadJS files, as well as our newly-created template:
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta charset="utf-8" />
<title>Spread JS Cell Drop Downs</title>
<link href="./gc.spread.sheets.excel2013white.13.0.0.css" rel="stylesheet" type="text/css">
<script type="text/javascript" src=".gc.spread.sheets.all.13.0.0.min.js"></script>
<script type='text/javascript" src=./CellDropDownsTemplate.js"></script>
</head>
<body>
<div id="spreadSheet" style="width: 1450px; height: 200 px; border: 1px solid gray"></div>
</body>
</html>
We can initialize the Spread instance and set some preliminary options, as well as bind the Spread instance to the template:
window.onload = function () {
var spread = new GC.Spread.Sheets.Workbook(document.getElementById("spreadSheet"), { sheetCount: 1 });
spread.fromJSON(CellDropDownsTemplate); spread.options.newTabVisible = false;
var activeSheet = spread.getSheet(1);
var source = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
spread.suspendPaint();
// Set these options to lock the sheet for certain cells var options = {
allowSelectLockedCells: false, allowSelectUnlockedCells: true,
allowResizeRows: false,
allowResizeColumns: false,
allowEditObjects: true
};
activeSheet.options.protectionOptions = options; activeSheet.options.isProtected = true; spread.getSheet(1).visible(false);
spread.resumePaint();
}
Initialize the List of Issues
The next step is to create a table using an array of JavaScript objects as the data source. This array will be kept up to date: a user can add issues to it or edit issues already in the list. To set the table up, simply set the data source and bind the columns:
// Set up the intial data binding and table in the Spread instance for the list of issues
function initializeIssueList(data, source, spread) { var sheet = spread.getActiveSheet();
var tableColumns = [],
names = ['uid', 'status', 'title', 'product', 'assignedTo', 'priority', 'submissionDate', 'estimatedHours', 'tags', 'description'],
labels = ['ID', 'Status', 'Title', 'Product', 'Assigned To', 'Priority', 'Sub. Date', 'Est. Hours', 'Tags', 'Description'];
var table = sheet.tables.add('tableIssues', 0, 1, 1, 10);
table.autoGenerateColumns(false);
names.forEach(function (name, index) {
var tableColumn = new GC.Spread.Sheets.Tables.TableColumn(); tableColumn.name(labels[index]); tableColumn.dataField(name);
tableColumns.push(tableColumn);
});
table.bindColumns(tableColumns);
table.bindingPath('issueList');
sheet.setDataSource(source);
sheet.setColumnWidth(0, 58);
sheet.setColumnWidth(1, 40);
sheet.setColumnWidth(2, 100);
sheet.setColumnWidth(3, 200);
sheet.setColumnWidth(4, 100);
sheet.setColumnWidth(5, 100);
sheet.setColumnWidth(6, 70);
sheet.setColumnWidth(7, 100);
sheet.setColumnWidth(8, 100);
sheet.setColumnWidth(9, 100);
sheet.setColumnWidth(10, 400);
}
Adding Cell Buttons
This sample has 4 different kinds of buttons: an Add button for submitting new issues from the issue list, an Edit button to change an issue already in the list, a Submit button for submitting a new issue or changes to an existing one, and finally a "Cancel" button for canceling the creation of a new issue or any changes to existing issues. With the new Cell button implementation, these buttons are created as styles and then set on cells in the sheet. Since these 4 buttons do different things and may look different from one another, we will need to create a new style for each one.
// Set the style for the cell buttons
function setButtonStyles(data, source, spread) {
var issuesSheet = spread.getSheet(0);
var newIssueButtonStyle = new GC.Spread.Sheets.Style();
newIssueButtonStyle.cellButtons = [
{
caption: "Add",
width: 60,
position: GC.Spread.Sheets.ButtonPosition.center,
// Execute this command when the Add button is clicked
// Adds a new issue
command: (sheet, row, col, option) => { clearAddIssue(spread); newIssueSheet.setValue(0, 4, issueUID); newIssueSheet.setText(0, 6, "New"); switchHideSheet(spread);
}
}
];
newIssueButtonStyle.locked = false; issuesSheet.setStyle(0, 0, newIssueButtonStyle);
var editButtonStyle = new GC.Spread.Sheets.Style();
editButtonStyle.cellButtons = [
{
caption: "Edit",
width: 60,
position: GC.Spread.Sheets.ButtonPosition.center, // Execute this command when the Edit button is clicked
// Edit an existing issue
command: (sheet, row, col, option) => { newIssueSheet.setValue(0, 4, issuesSheet.getValue(row, 1)); newIssueSheet.setText(0, 6, issuesSheet.getText(row, 2)); newIssueSheet.setText(1, 0, issuesSheet.getText(row, 3)); newIssueSheet.setText(2, 0, issuesSheet.getText(row, 4)); newIssueSheet.setText(2, 4, issuesSheet.getText(row, 5)); newIssueSheet.setValue(3, 0, issuesSheet.getValue(row, 6)); newIssueSheet.setText(3, 1, issuesSheet.getText(row, 7)); newIssueSheet.setValue(3, 3, issuesSheet.getValue(row, 8)); newIssueSheet.setText(3, 6, issuesSheet.getText(row, 9)); newIssueSheet.setText(4, 0, issuesSheet.getText(row, 10));
switchHideSheet(spread);
}
}
];
editButtonStyle.locked = false;
// Add the Edit button to the right of all existing issues in the table
for (var i = 0;i < data.issueList.length; i++){
issuesSheet.setStyle(i+1, 0, editButtonStyle);
}
var newIssueSheet = spread.getSheet(1);
var submitButtonStyle = new GC.Spread.Sheets.Style(); submitButtonStyle.cellButtons = [
{
caption: "Submit Issue",
width: 125,
position: GC.Spread.Sheets.ButtonPosition.left,
// Execute this command when the Submit button is clicked
// Submit the new issue or changes to an existing issue
command: (sheet, row, col, option) => { var issueData = {
uid: sheet.getValue(0, 4), status: sheet.getText(0, 6), title: sheet.getText(1, 0), product: sheet.getText(2, 0), assignedTo: sheet.getText(2, 4),priority: sheet.getValue(3, 0), submissionDate: sheet.getText(3,1),
estimatedHours: sheet.getValue(3, 3),
tags: sheet.getText(3, 6), description: sheet.getText(4,
}
// Test if the issue already exists
if (data.issueList.some(issue => issue.uid === issueData.uid)) {
var issueIndex = data.issueList.findIndex(issue => issue.uid === issueData.uid); data.issueList[issueIndex] = issueData;
} // Add the new issue if it doesn't exist
else {
data.issueList.push(issueData); source = new GC.Spread.Sheets.Bindings.CellBindingSource(data); issuesSheet.setDataSource(source); issuesSheet.setStyle(data.issueList.length, 0, editButtonStyle);
issueUID++;
}
source = new GC.Spread.Sheets.Bindings.CellBindingSource(data);
issuesSheet.setDataSource(source);
switchHideSheet(spread);
}
}
];
submitButtonStyle.locked = false; newIssueSheet.setStyle(6, 2, submitButtonStyle);
var cancelButtonStyle = new GC.Spread.Sheets.Style();
cancelButtonStyle.cellButtons = [
{
caption: "Cancel",
width: 125,
position: GC.Spread.Sheets.ButtonPosition.left,
// Execute this command when the Cancel button is clicked command: (sheet, row, col, option) => {
switchHideSheet(spread);
}
}
]; cancelButtonStyle.locked = false; newIssueSheet.setStyle(6, 4, cancelButtonStyle);
}
For the "Add" button, we will want to implement something to clear the sheet's values so that we can enter new ones in the form:
// Prep the new issue sheet by clearing the data function clearAddIssue(spread) {
spread.getSheet(1).clear(1, 0, 4, 9, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
}
For all of the buttons, we will want to switch to the other sheet and hide the current one, that way the user only sees one sheet at a time. We can simply use the visible property, and, in this case, change the size of the Spread instance as well:
// Switch sheets and hide the inactive sheet
function switchHideSheet(spread) {
var originalSheet = spread.getActiveSheet();
if (originalSheet.name() == "Issues") { spread.setActiveSheet(1);
spread.getSheet(1).visible(true); document.getElementById("spreadSheet").style.width = "620px";
document.getElementById("spreadSheet").style.height = "500px";
} else {
spread.setActiveSheet(0);
spread.getSheet(0).visible(true); document.getElementById("spreadSheet").style.width = "1450px";
document.getElementById("spreadSheet").style.height = "200px";
}
originalSheet.visible(false);
}
Implementing Drop-Downs
Similar to buttons, drop-downs are implemented as styles, and specific types can be set to specify which drop-down to use. We can start by implementing a workflow list for the status of an issue, which has different transitions that the drop-down can switch between:
// Set the style for the "Status" drop-down
function setWorkflowDropDown(sheet) {
var workflowStyle = new GC.Spread.Sheets.Style(); workflowStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown, command: "openWorkflowList", useButtonStyle: true
}
];
workflowStyle.dropDowns = [
{
type: GC.Spread.Sheets.DropDownType.workflowList, option: {
items: [
{ value: "New", transitions: [1] }, { value: "Open",transitions: [0, 2, 3, 5] },
{ value: "In Progress", transitions: [1, 3, 5] },
{ value: "Resolved", transitions: [5, 4] },
{ value: "Reopened",transitions: [5, 3, 2] },
{ value: "Closed", transitions: [4] }
]
}
}
];
workflowStyle.backColor = 'lightGray'; workflowStyle.locked = false;
sheet.setStyle(0, 6, workflowStyle); sheet.getRange(0, 6, 1, 3).borderBottom(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thick));
}
The drop-down for "Product" is a cascade list, which essentially means you can have secondary options for the list items that you specify. In this case, it is useful to choose the different sub-products of the main product lines:
// Set the style for the "Product" drop-down
function setVerticalCascadeGroupList(sheet) {
var cascadeListData= {
items: [
{
text: 'Spread',
value: 'Spread',
layout: {displayAs: GC.Spread.Sheets.LayoutDisplayAs.popup}, items: [
{
text: 'SpreadJS',
value: 'SpreadJS'
},
{
text: 'Spread .NET',
value: 'Spread .NET'
}
]
},
{
text: 'ActiveReports',
value: 'ActiveReports',
layout: {displayAs: GC.Spread.Sheets.LayoutDisplayAs.popup},items: [
{
text: 'ActiveReportsJS',
value: 'ActiveReportsJS'
},
{
text: 'ActiveReports Professional',
value: 'ActiveReports Professional'
}
]
},
{
text: 'DataViewsJS',
value: 'DataViewsJS',
},
{
text: 'Wijmo',
value: 'Wijmo',
}
]
};
var verticalCascadeListStyle = new GC.Spread.Sheets.Style(); verticalCascadeListStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown,
command: "openList", useButtonStyle: true,
}
];
verticalCascadeListStyle.dropDowns =[
{
type: GC.Spread.Sheets.DropDownType.list,
option: cascadeListData
}
];
verticalCascadeListStyle.backColor = 'lightGray'; verticalCascadeListStyle.locked = false;
sheet.setStyle(2, 0, verticalCascadeListStyle); sheet.getRange(2, 0, 1, 4).borderBottom(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin));
}
With the "Priority" drop-down, the value will only be between 1 and 6 so we can create a slider:
// Set the style for the "Priority" drop-down
function setSliderDropDown(sheet) {
var sliderStyle = new GC.Spread.Sheets.Style();
sliderStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown, command: "openSlider",
useButtonStyle: true
}
];
sliderStyle.dropDowns = [
{
type: GC.Spread.Sheets.DropDownType.slider, option: {
max: 6,
min: 1,
marks: [1,2,3,4,5,6],
step: 1,
direction: GC.Spread.Sheets.LayoutDirection.horizontal
}
}
];
sliderStyle.backColor = 'lightGray'; sliderStyle.locked = false;
sheet.setStyle(3, 0, sliderStyle);
sheet.getCell(3, 0).borderBottom(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin));
sheet.getCell(3, 0).borderRight(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin));
}
To set the "Submission Date", we can use the DateTimePicker drop-down:
// Set the style for the "Submission Date" drop-down function setDateTimeDropDown(sheet) {
var dateTimeStyle = new GC.Spread.Sheets.Style(); dateTimeStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown,command: "openDateTimePicker", useButtonStyle: true
}
];
dateTimeStyle.dropDowns=[
{
type: GC.Spread.Sheets.DropDownType.dateTimePicker,
option: {
showTime:false
}
}
];
dateTimeStyle.backColor = 'lightGray'; dateTimeStyle.locked = false;
sheet.setStyle(3, 1, dateTimeStyle);
sheet.getRange(3, 1, 1, 2).borderBottom(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin));
}
The final type of drop-down we can use is a calculator for the "Estimated Hours" field, which drops down a calculator whose result is placed in the cell:
// Set the style for the "Estimated Hours" drop-down function setCalculatorDropDown(sheet) {
var calculatorStyle = new GC.Spread.Sheets.Style(); calculatorStyle.cellButtons = [
{
imageType: GC.Spread.Sheets.ButtonImageType.dropdown,command: "openCalculator", useButtonStyle: true
}
];
calculatorStyle.backColor = 'lightGray'; calculatorStyle.locked = false;
sheet.setStyle(3, 3, calculatorStyle); sheet.getRange(3, 3, 1, 3).borderBottom(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin));
}
Adding Cell States to the JavaScript Spreadsheet
For the form sheet, we want the users to know which cells they can edit. We can show this via the "hover" cell state, which can change the style of a cell when the mouse cursor is hovering over them. We will need to specify the style that the cell should switch to when hovering and add that state to specific ranges
// Set the style when cells are hovered over with the cursor
function setHoverStyles(sheet) {
var hoverStyle = new GC.Spread.Sheets.Style(); hoverStyle.backColor = 'white';
var range = sheet.getRange(0, 6, 1, 3); sheet.cellStates.add(range, GC.Spread.Sheets.CellStatesType.hover, hoverStyle);
range = sheet.getRange(1, 0, 4, 9); sheet.cellStates.add(range, GC.Spread.Sheets.CellStatesType.hover, hoverStyle);
}
Adding Labels
Instead of adding separate cells for labels to let the user know which field they are editing, we can use cell padding and cell labels within the same cell:
// Set the labels for all of the data entry fields function setLabels(sheet) {
var labelOptions = {
visibility: 0,
font: '11px Arial',
margin: '0 0 0 0'
}
sheet
.getCell(0, 6)
.watermark('STATUS')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(1, 0)
.watermark('ISSUE TITLE')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(2, 0)
.watermark('PRODUCT')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(2, 4)
.watermark('ASSIGNED TO')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(3, 0)
.watermark('PRIORITY')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(3, 1)
.watermark('SUBMISSION DATE')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(3, 3)
.watermark('ESTIMATED HOURS')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(3, 6)
.watermark('TAGS')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
sheet
.getCell(4, 0)
.watermark('ISSUE DESCRIPTION')
.cellPadding('25 0 0 0')
.labelOptions(labelOptions);
}
Setting Form Cells
As for the other cells in the form that aren't drop-downs or buttons, we want to set the styles and borders of those cells:
function newFormStyle(){
var formStyle = new GC.Spread.Sheets.Style(); formStyle.backColor = 'lightGray';
formStyle.locked = false;
return formStyle;
}
// Set the style for the cells for simple data entry function setFormCells(sheet) {
sheet.setStyle(1, 0, newFormStyle());
sheet.setStyle(2, 4, newFormStyle());
sheet.setStyle(3, 6, newFormStyle());
sheet.setStyle(4, 0, newFormStyle());
var bottomBorder = new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin) sheet.getRange(1, 0, 1, 9).borderBottom(bottomBorder);
sheet.getRange(2, 4, 1, 5).borderBottom(bottomBorder);
sheet.getRange(3, 6, 1, 3).borderBottom(bottomBorder);
sheet.getRange(4, 0, 1, 9).borderBottom(bottomBorder);
}
That's all that's needed to ceate a custom form using the new drop-down and button functionality in SpreadJS v13! To see these features and more in action, download a trial of SpreadJS.
Check out our demos | Download the sample
Kevin Ashley