As more and more companies collect various types of information, the need for data analysis tools, such as spreadsheets, is rapidly increasing. Because of this data volume, it is imperative to ensure data accuracy and consistency to provide consistent and reliable analysis. One way to do so is to create fields in a spreadsheet linking to specific data selections, both large and small, to ensure consistency with spelling, formatting, and definitions.
To help facilitate this concept, SpreadJS supports the use of CellTypes, which creates different types of cells in SpreadJS, including buttons, combo boxes, checkboxes, radio buttons, and much more. Here is a link to our documentation, which provides information on these cell types. This list contains CustomCellType–allowing for the creation of several new cell types to achieve different functionalities within our JavaScript spreadsheet.
In this blog, we discuss using the CellTypes concept to create a custom AutoCompleteCellType utilizing the select2() jquery library to implement an auto-complete drop-down list. As the name suggests, AutoCompleteCellType, when applied to a cell in Edit mode, lets a user begin typing to populate a list of values from the drop-down box. The user can select one of these options to set as the cell value. The options are auto-populated and displayed from the Select2 library, allowing users to connect to a remote database using ajax methods. Let's get started with a simple example to create a cell in a spreadsheet such as this:
In a nutshell:
- Implement the AutoCompleteCellType
- Create the editor element
- Paint the cell on the canvas
- Activate and deactivate the editor
- Get and set the editor value
- Handle reserved keyboard keys
Getting started:
1. Implement the AutoCompleteCellType
// 1. Implement the AutoCompleteCellType custom cell using the Base class
AutoCompleteCellType.prototype = new GC.Spread.Sheets.CellTypes.Base();
2. Create the editor element
First, create a div element and append a select element with set attributes to create the editor element. Then, use the SpreadJS getCellRect method to display the cell's width and height in the new editor accordingly.
Finally, add event listeners from select2, so SpreadJS stops editing the active cell using the endEdit method when a result is selected. Also, when the select2 drop-down is open, it will set the attributes for the select2-dropdown.
// 2. Create the editor
AutoCompleteCellType.prototype.createEditorElement = function (context) {
var editor;
// 2.1) Create a div element to appear as the editor
editor = document.createElement('div')
// 2.2) Set the editors attributes and append a select element
editor.setAttribute("gcUIElement", "gcEditingInput");
editor.appendChild(document.createElement('select'));
editor.children[0].style.width = "100%";
editor.children[0].style.height = "100%";
// 2.3) Get the cells width and height to set the width and height of the editor
var cellRect = context.sheet.getCellRect(context.row, context.col);
editor.style.width = cellRect.width + 'px';
editor.style.height = cellRect.height + 'px';
// 2.4) Whenever a result is selected from the select2 dropdown, end editing on SJS's cell
$(editor).find('select').on('select2:select', function (e) {
context.sheet.endEdit();
});
// 2.5) Whenever the select2 dropdown is opened, set the attributes of the dropdown
$(editor).find('select').on('select2:open', function (e) {
$('.select2-dropdown').attr("gcUIElement", "gcEditingInput");
});
return editor;
}
3. Paint the cell on the canvas
Use the paint method of SpreadJS' Base class to paint a cell on the canvas. For this example, use the id and value of the value and display them based on the selected item from the drop-down.
// 3. Paint a cell on the canvas
AutoCompleteCellType.prototype.paint = function (ctx, value, x, y, w, h, style, options) {
if (value) {
GC.Spread.Sheets.CellTypes.Base.prototype.paint.apply(this, [ctx, value.id + ": " + value.value, x, y, w, h, style, options]);
}
};
4. Activate and deactivate the editor
Next, we show how to activate the editor, using the activateEditor method, to search for and process a searched term's results. The Select2 library supports AJAX methods, which are used to add a remote data source to the select drop-down, based on the search term. To accomplish this, first find the select2 element and add our remote data source by using an ajax call to a specified URL. Then, process the results in a format select2 can understand. For this example, return the item's country name and numericCode.
// 4. Activate and Deactivate the editor
AutoCompleteCellType.prototype.activateEditor = function (eContext, cellStyles, cellRect) {
// 4.1) Find select2 element add a remote data source
$(eContext).find('select').select2({
ajax: {
url: "https://raw.githubusercontent.com/kshkrao3/JsonFileSample/master/select2resp.json",
dataType: 'json',
delay: 250,
// 4.2) return the search term
data: function (params) {
return {
q: params.term // search term
};
},
// 4.3) Process the results into the format expected by Select2
processResults: function (data, params) {
var resData = [];
data.forEach(function (value) {
if (value.name.indexOf(params.term) != -1)
resData.push(value)
})
return {
results: $.map(resData, function (item) {
return {
text: item.name,
id: item.numericCode
}
})
};
},
cache: true
},
minimumInputLength: 1,
});
To deactivate the editor, use the SpreadJS deactiveEditor method and use the select2 libraries destroy method to remove the select2 widget:
AutoCompleteCellType.prototype.deactivateEditor = function (eContext, context) {
// 4.4) Remove the select2 widget
$(eContext).find('select').select2('destroy');
}
5. Get and set the editor value
SpreadJS' getEditorValue method receives the editor value and assigns the id and text as the id and value. Then, set the editor's value using the setEditorValue and append the selected items with the returned id and value.
// 5. Get and set the Editor Value
// 5.1) get the editor value and assign the id and text as the id and value
AutoCompleteCellType.prototype.getEditorValue = function (eContext) {
var data = $(eContext).find('select').select2('data');
if (!data || !data.length) {
return null;
}
return Object.assign({}, {
id: data[0].id,
value: data[0].text
});
}
// 5.2) Set the editor value with an option that uses the returned value and id and appends it to the select element
AutoCompleteCellType.prototype.setEditorValue = function (eContext, val) {
if (!val) {
return;
}
var op = new Option(val.value, val.id, true, true);
$(eContext).find('select').append(op).trigger('change');
}
6. Handle reserved keyboard keys
When a user is typing, and the results are populating in the select drop-down, SpreadJS needs to handle the keyboard arrow keys and escape. To do this, use SpreadJS isReserveKey method. This is used to determine if the cell type handles the keyboard event itself. In this case, use the keycode numbers 40, 38, 13, and 27–representing the down, up, end, and escape keys.
// 6. Handle reserved keyboard keys
AutoCompleteCellType.prototype.isReservedKey = function (e, context) {
if (context.isEditing && (e.keyCode == 40 || e.keyCode == 38 || e.keyCode == 13 || e.keyCode == 27)) {
return true;
}
return GC.Spread.Sheets.CellTypes.Text.prototype.isReservedKey.apply(this, arguments);
}
Outcome:
With this code-logic applied, users can set a cell or range of cells to their cell type to be set as the custom autoCompleteCellType like so:
sheet.getRange(-1, 1, -1, 1).cellType(new AutoCompleteCellType()).width(250);
sheet.getCell(2, 1, GC.Spread.Sheets.SheetArea.viewport).cellType(new AutoCompleteCellType()).value({
id: 123,
value: "Enter Value"
});
When users type in the cells that have the autoCompleteCellType applied to them, the new editor appears, and the drop-down is auto-populated based on what has been entered:
Here is a link to a live sandbox showing these steps in action: https://codesandbox.io/s/spreadjs-autocomplete-celltype-blog-sample-enter-country-g402p?file=/src/index.js
This is just one example SpreadJS' customization options used to create custom cell types. SpreadJS' JavaScript API enables users to add customized behavior to a JavaScript spreadsheet to fit their needs. Developers can even implement different libraries (similarly to how Select2 was implemented here) to achieve different behaviors based on their requirements.
Get started developing with the world's #1 JavaScript spreadsheet, SpreadJS, with a trial today!
Mackenzie Albitz