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

SpreadJS v13 Cell Drop Downs

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); 
} 

SpreadJS v13 Cell Drop Downs

Adding Cell Buttons

This sample has four 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)); 
} 

SpreadJS v13 Cell Drop Downs

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); 
} 

SpreadJS v13 Cell Drop Downs

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

Try SpreadJS free for 30 days

Download SpreadJS

Download Now!