To create a combo box cell, follow this example:
You can use the editorValueType method to get and set the value that is written to the underlying data model. The editor value type is an EditorValueType enumeration.
text: Writes to the model the text value of the selected item.
index: Writes to the model the index of the selected item.
value: Writes to the model the corresponding data value of the selected item.
The different editorValueType settings create different types of editor values. The combo box's value depends on items for the drop-down list in the combo box. You can use the items method to get and set the items. For example:
You can also use the dataBinding method to bind the combo box to a data source. The data source will replace the items in the combo box in runtime. For example:
Use the editable method to set whether the user can type in the combo box editor. The default value is false; only selection is allowed. For example:
You can use the itemHeight method to set the height of each item in the drop-down list. For example:
Use the allowFloat method to set whether to allow the drop-down list to float outside the Spread.
var spreadNS = GC.Spread.Sheets;
var spread, formulaBox;
window.onload = async function () {
spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"));
spread.suspendPaint();
const loadingTip = addLoadingTip();
const res = await fetch('$DEMOROOT$/en/sample/features/cells/cell-types/combobox/spread.json');
await spread.fromJSON(await res.json());
initSpread(spread);
spread.resumePaint();
// formulaBox = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById('formulaBar'), {rangeSelectMode: true});
// formulaBox.workbook(spread);
loadingTip.remove();
};
function addLoadingTip() {
const div = document.createElement('div');
div.style.position = 'absolute';
div.style.inset = '0';
div.style.display = 'flex';
div.style.alignItems = 'center';
div.style.justifyContent = 'center';
div.style.background = 'white';
div.style.zIndex = '100';
div.textContent = 'Loading data from server ...';
document.body.appendChild(div);
return div;
}
function Country(shortName, fullName) {
this.value = this.shortName = shortName;
this.text = this.fullName = fullName;
}
let UI = {
get selEditorVauleType() { return _getElementById("selComboCellEditorValueType"); },
get btnUpdate() { return _getElementById("changeProperty"); },
get selBindingType() { return _getElementById('selComboBindingType'); },
get selDataSourceType() { return _getElementById('selComboDataSourceType'); },
get selDataSource() { return _getElementById('selComboDataSource'); },
get panelStaticItems() { return _getElementById("panel-static-items"); },
get txtItemTexts() { return _getElementById("txtComboCellItemsText"); },
get txtItemValues() { return _getElementById("txtComboCellItemsValue"); },
get panelBindingItems() { return _getElementById("panel-data-binding-items"); },
get panelBindingTable() { return _getElementById("panel-data-binding-table"); },
get selTableName() { return _getElementById('selComboDataSource'); },
get selTextColumn() { return _getElementById('selComboText'); },
get selValueColumn() { return _getElementById('selComboValue'); },
get panelBindingCustom() { return _getElementById("panel-data-binding-custom"); },
get txtFormula() { return _getElementById("txtFormula"); },
get txtText() { return _getElementById("txtText"); },
get txtValue() { return _getElementById("txtValue"); },
get chkEditable() { return _getElementById("chkEditable"); },
get chkAllowFloat() { return _getElementById("chkAllowFloat"); },
get txtItemHeight() { return _getElementById("txtItemHeight") }
}
function fetchDataSource(spread) {
const productsSheets = spread.addSheetTab(0, 'Products', GC.Spread.Sheets.SheetType.tableSheet);
productsSheets.options.allowAddNew = false;
const productsTable = spread.dataManager().tables.Products;
productsTable.fetch().then(() => {
var view = productsTable.addView("myView", Object.keys(productsTable.columns).map(c => ({ value: c, width: 150 })));
productsSheets.setDataView(view);
});
const customerSheets = spread.addSheetTab(1, 'Customers', GC.Spread.Sheets.SheetType.tableSheet);
customerSheets.options.allowAddNew = false;
const customersTable = spread.dataManager().tables.Customers;
customersTable.fetch().then(() => {
var view = customersTable.addView("myView", Object.keys(customersTable.columns).map(c => ({ value: c, width: 150 })));
customerSheets.setDataView(view);
});
const employeesSheets = spread.addSheetTab(2, 'Employees', GC.Spread.Sheets.SheetType.tableSheet);
employeesSheets.options.allowAddNew = false;
const employeesTable = spread.dataManager().tables.Employees;
employeesTable.fetch().then(() => {
var view = employeesTable.addView("myView", Object.keys(employeesTable.columns).map(c => ({ value: c, width: 150 })));
employeesSheets.setDataView(view);
});
spread.setActiveSheetIndex(0);
}
function initSpread(spread) {
spread.setSheetCount(2);
var sheet1 = spread.getSheet(0);
initStaticItemsSheet(sheet1);
var sheet2 = spread.getSheet(1);
initDataBindingItemsSheet(sheet2);
fetchDataSource(spread);
UI.btnUpdate.addEventListener('click', function () {
propertyChange(true);
});
UI.selBindingType.addEventListener('change', function (e) {
changeBindingType(+e.target.value === 1);
});
UI.selDataSourceType.addEventListener('change', function (e) {
changeDataSourceType(+e.target.value === 0);
});
var selDataSource = UI.selDataSource;
var tables = getDataTables(spread);
tables.forEach(function (table) {
var option = document.createElement('option');
option.value = table;
option.text = table;
selDataSource.appendChild(option);
});
selDataSource.addEventListener('change', function (e) {
var tableName = e.target.value;
initTableColumns(tableName);
});
//selDataSource.dispatchEvent(new Event('change'));
}
function initTableColumns(tableName) {
var selTextColumn = UI.selTextColumn;
var selValueColumn = UI.selValueColumn;
selTextColumn.innerHTML = '';
selValueColumn.innerHTML = '';
var columns = getColumns(tableName, spread);
columns.forEach(function (column) {
var option = document.createElement('option');
option.value = column;
option.text = column;
selTextColumn.appendChild(option);
option = document.createElement('option');
option.value = column;
option.text = column;
selValueColumn.appendChild(option);
});
}
function changeBindingType(isDataBinding) {
var staticItems = UI.panelStaticItems;
var dataBindingItems = UI.panelBindingItems;
staticItems.classList.toggle("hidden-element", isDataBinding);
dataBindingItems.classList.toggle("hidden-element", !isDataBinding);
if (isDataBinding) {
changeDataSourceType(true);
}
}
function changeDataSourceType(isTable) {
UI.selDataSourceType.value = isTable ? 0 : 1;
var table = UI.panelBindingTable;
var custom = UI.panelBindingCustom;
table.classList.toggle("hidden-element", !isTable);
custom.classList.toggle("hidden-element", isTable);
if (isTable && UI.selTextColumn.children.length < 1) {
initTableColumns(UI.selTableName.value);
}
}
function propertyChange(isSet) {
var sheet = spread.getActiveSheet();
var sels = sheet.getSelections();
if (sels && sels.length > 0) {
var sel = getActualRange(sels[0], sheet.getRowCount(), sheet.getColumnCount());
var comboBoxCellType = sheet.getCellType(sel.row, sel.col);
if (!(comboBoxCellType instanceof spreadNS.CellTypes.ComboBox)) {
UI.btnUpdate.setAttribute("disabled", 'disabled');
return;
}
var dataBinding = comboBoxCellType.dataBinding();
var isDataBinding = (!!dataBinding) && (!!dataBinding.dataSource);
if (!isSet) {
UI.btnUpdate.removeAttribute("disabled");
UI.selEditorVauleType.value = comboBoxCellType.editorValueType();
UI.selBindingType.value = isDataBinding ? 1 : 0;
changeBindingType(isDataBinding);
if (isDataBinding) {
var isTable = isDataTable(dataBinding.dataSource, spread);
changeDataSourceType(isTable);
if (isTable) {
initTableColumns(dataBinding.dataSource);
UI.selTableName.value = dataBinding.dataSource;
if (dataBinding.text) {
UI.selTextColumn.value = dataBinding.text;
}
if (dataBinding.value) {
UI.selValueColumn.value = dataBinding.value;
}
} else {
//formulaBox.text(dataBinding.dataSource);
UI.txtFormula.value = dataBinding.dataSource;
UI.txtText.value = dataBinding.text;
UI.txtValue.value = dataBinding.value;
}
}
else {
var items = comboBoxCellType.items(), text = '', value = '';
for (var i = 0, len = items.length; i < len; i++) {
var item = items[i];
if (!item) {
continue;
}
if (item.text) {
text += item.text + ',';
}
if (item.value) {
value += item.value + ',';
}
}
UI.txtItemTexts.value = text.slice(0, text.length - 1);
UI.txtItemValues.value = value.slice(0, value.length - 1);
}
UI.chkEditable.checked = comboBoxCellType.editable();
UI.chkAllowFloat.checked = comboBoxCellType.allowFloat();
UI.txtItemHeight.value = "" + comboBoxCellType.itemHeight();
} else {
comboBoxCellType.editorValueType(parseInt(UI.selEditorVauleType.value));
const bindTypeValue = UI.selBindingType.value;
const isDataBinding = +bindTypeValue === 1;
if (!isDataBinding) {
comboBoxCellType.dataBinding(null);
} else {
const sourceTypeValue = UI.selDataSourceType.value;
const isTable = +sourceTypeValue === 0;
const dataBinding = {};
if (isTable) {
dataBinding.dataSource = UI.selTableName.value;
dataBinding.text = UI.selTextColumn.value;
dataBinding.value = UI.selValueColumn.value;
} else {
//dataBinding.dataSource = formulaBox.text();
dataBinding.dataSource = UI.txtFormula.value;
dataBinding.text = UI.txtText.value;
dataBinding.value = UI.txtValue.value;
}
comboBoxCellType.dataBinding(dataBinding);
}
const itemsText = UI.txtItemTexts.value.split(",");
var itemsValue = UI.txtItemValues.value.split(",");
var itemsLength = itemsText.length > itemsValue.length ? itemsText.length : itemsValue.length;
var items = [];
for (var count = 0; count < itemsLength; count++) {
var t = itemsText.length > count && itemsText[0] != "" ? itemsText[count] : undefined;
var v = itemsValue.length > count && itemsValue[0] != "" ? itemsValue[count] : undefined;
if (t != undefined && v != undefined) {
items[count] = { text: t, value: v };
}
else if (t != undefined) {
items[count] = { text: t };
} else if (v != undefined) {
items[count] = { value: v };
}
}
comboBoxCellType.items(items);
comboBoxCellType.editable(UI.chkEditable.checked);
comboBoxCellType.allowFloat(UI.chkAllowFloat.checked);
var itemHeight = parseInt(UI.txtItemHeight.value, 10);
if (!isNaN(itemHeight) && itemHeight > 0) {
comboBoxCellType.itemHeight(itemHeight);
}
}
}
sheet.repaint();
}
function getActualRange(range, maxRowCount, maxColCount) {
var row = range.row < 0 ? 0 : range.row;
var col = range.col < 0 ? 0 : range.col;
var rowCount = range.rowCount < 0 ? maxRowCount : range.rowCount;
var colCount = range.colCount < 0 ? maxColCount : range.colCount;
return new spreadNS.Range(row, col, rowCount, colCount);
}
function initStaticItemsSheet(sheet) {
sheet.name("Static-Items");
sheet.bind(spreadNS.Events.SelectionChanged, function () {
propertyChange(false);
});
sheet.suspendPaint();
sheet.setColumnWidth(2, 120);
sheet.setColumnWidth(1, 200);
var combo = new spreadNS.CellTypes.ComboBox();
combo.items([{ text: "Oranges", value: "11k" }, { text: "Apples", value: "15k" }, { text: "Grape", value: "100k" }])
.editorValueType(spreadNS.CellTypes.EditorValueType.text);
sheet.setValue(0, 3, "Result:");
sheet.getCell(1, 2, spreadNS.SheetArea.viewport).cellType(combo).value("Apples");
sheet.setValue(1, 1, "ComboBoxCellType");
sheet.setFormula(1, 3, "=C2");
var editableCombo = new spreadNS.CellTypes.ComboBox(),
data = [new Country("CN", "China"), new Country("JP", "Japan"), new Country("US", "United States")];
editableCombo.editable(true)
.items(data)
.itemHeight(24)
.editorValueType(spreadNS.CellTypes.EditorValueType.value);
sheet.getCell(3, 2, spreadNS.SheetArea.viewport).cellType(editableCombo).value("US");
sheet.setValue(3, 1, "Editable ComboBoxCellType");
sheet.setFormula(3, 3, "=C4");
var allowFloatCombo = new spreadNS.CellTypes.ComboBox();
allowFloatCombo.items(Array.from({ length: 100 }, (_, index) => {
return { text: index + 1, value: index + 1 }
}));
sheet.getCell(22, 2).cellType(allowFloatCombo);
sheet.setValue(22, 1, "Try Allow Float ComBoxCellType");
sheet.setActiveCell(1, 2);
propertyChange(false);
sheet.resumePaint();
}
function initDataBindingItemsSheet(sheet) {
sheet.name("Binding-Items");
sheet.bind(spreadNS.Events.SelectionChanged, function () {
propertyChange(false);
});
sheet.suspendPaint();
sheet.setColumnWidth(1, 200);
sheet.setColumnWidth(2, 200);
sheet.setColumnWidth(3, 200);
//--------------------Binding to Table--------------------
var combo = new spreadNS.CellTypes.ComboBox();
combo.dataBinding({ dataSource: "Products", text: "productName", value: "productId" });
combo.editorValueType(spreadNS.CellTypes.EditorValueType.text);
sheet.setValue(0, 3, "Result:");
sheet.getCell(1, 2, spreadNS.SheetArea.viewport).cellType(combo).value("Chang");
sheet.setValue(1, 1, "Binding to Table");
sheet.setFormula(1, 3, "=C2");
//--------------------Binding to a formula--------------------
var editableCombo = new spreadNS.CellTypes.ComboBox();
editableCombo.editable(true)
.dataBinding({ dataSource: '=SORT(UNIQUE(QUERY("Products", {"productName","productId"})))', text: 0, value: 1 })
.itemHeight(24)
.editorValueType(spreadNS.CellTypes.EditorValueType.value);
sheet.getCell(3, 2, spreadNS.SheetArea.viewport).cellType(editableCombo).value(1);
sheet.setValue(3, 1, "Binding to a formula");
sheet.setFormula(3, 3, "=C4");
//--------------------Binding to a range--------------------
sheet.setArray(6, 6, [["Oranges", "11k"], ["Apples", "15k"], ["Grape", "100k"]])
combo = new spreadNS.CellTypes.ComboBox();
combo.editorValueType(spreadNS.CellTypes.EditorValueType.value);
combo.dataBinding({ dataSource: "'Binding-Items'!G7:H9", text: 0, value: 1 });
sheet.getCell(5, 2, spreadNS.SheetArea.viewport).cellType(combo).value('15k');
sheet.setValue(5, 1, "Binding to range");
sheet.setFormula(5, 3, "=C6");
sheet.setActiveCell(1, 2);
propertyChange(false);
sheet.resumePaint();
}
function isDataTable(table, workBook) {
const lowerTableName = table.toLowerCase();
return getDataTables(workBook).some((t) => t.toLowerCase() === lowerTableName);
}
function getDataTables(workBook) {
const tables = workBook?.dataManager()?.tables;
if (!tables) {
return [];
}
return Object.keys(workBook.dataManager().tables);
}
function getColumns(tableName, workBook) {
const tables = workBook?.dataManager()?.tables;
if (!tables) {
return [];
}
const table = getTableIgnoreCase(tables, tableName);
if (!table) {
return [];
}
return Object.keys(table.columns);
}
function getTableIgnoreCase(tables, tableName) {
const lowerTableName = tableName.toLowerCase();
for (const key in tables) {
if (tables.hasOwnProperty(key) && key.toLowerCase() === lowerTableName) {
return tables[key];
}
}
return null;
}
function _getElementById(id) {
return document.getElementById(id);
}
<!doctype html>
<html style="height:100%;font-size:14px;">
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/en/purejs/node_modules/@mescius/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script src="$DEMOROOT$/spread/source/js/license.js" type="text/javascript"></script>
<script src="app.js" type="text/javascript"></script>
<link rel="stylesheet" type="text/css" href="styles.css">
</head>
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="options-container">
<label>Select one of the combo box cells in Spread and edit its options with these text boxes.</label>
<div class="option-row">
<label>EditorValueType: </label>
<select id="selComboCellEditorValueType">
<option value="0" selected="selected">Text</option>
<option value="1">Index</option>
<option value="2">Value</option>
</select>
</div>
<div class="option-row">
<label>Binding Type: </label>
<select id="selComboBindingType">
<option value="0">Static Items</option>
<option value="1">Data Binding</option>
</select>
</div>
<div id="panel-static-items">
<div class="option-row">
<label >Items Text:</label>
<input id="txtComboCellItemsText" type="text" />
</div>
<div class="option-row">
<label>Items Value:</label>
<input id="txtComboCellItemsValue" type="text" />
</div>
</div>
<div id="panel-data-binding-items">
<div class="option-row">
<label >Data Source Type:</label>
<select id="selComboDataSourceType">
<option value="0">Table</option>
<option value="1">Custom</option>
</select>
</div>
<div id="panel-data-binding-table">
<div class="option-row">
<label >Data Source:</label>
<select id="selComboDataSource">
</select>
</div>
<div class="option-row">
<label>Binding Text:</label>
<select id="selComboText">
</select>
</div>
<div class="option-row">
<label>Binding Value:</label>
<select id="selComboValue">
</select>
</div>
</div>
<div id="panel-data-binding-custom">
<div class="option-row">
<label >Data Source:</label>
<input id="txtFormula" type="text"/>
</div>
<div class="option-row">
<label>Binding Text:</label>
<input id="txtText" type="text" />
</div>
<div class="option-row">
<label>Binding Value:</label>
<input id="txtValue" type="text" />
</div>
</div>
</div>
<div class="option-row">
<label>Item Height:</label>
<input id="txtItemHeight" type="text" />
</div>
<div class="option-row">
<label></label>
<input type="checkbox" id="chkEditable" />
<label for="chkEditable">Editable</label>
</div>
<div class="option-row">
<label></label>
<input type="checkbox" id="chkAllowFloat" />
<label for="chkAllowFloat">Allow Float</label>
</div>
<div class="option-row">
<label></label>
<input type="button" id="changeProperty" value="Update"/>
</div>
</div>
</div>
</body>
</html>
.sample-tutorial {
position: relative;
height: 100%;
overflow: hidden;
}
.sample-spreadsheets {
width: calc(100% - 280px);
height: 100%;
overflow: hidden;
float: left;
}
.options-container {
float: right;
width: 280px;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
overflow: auto;
}
.option-row{
padding-bottom: 12px;
}
label {
padding-bottom: 4px;
display: block;
}
input,
select {
width: 100%;
padding: 4px 8px;
box-sizing: border-box;
}
input[type=checkbox] {
width: auto;
}
input[type=checkbox] + label {
display: inline-block;
width: auto;
user-select: none;
}
.hidden-element {
display: none;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
