要创建一个组合框单元格，请按照以下示例操作：
您可以使用 editorValueType 方法来获取和设置写入底层数据模型的值。编辑器值类型是一个 EditorValueType 枚举。
text: 将所选项目的文本值写入模型。
index: 将所选项目的索引写入模型。
value: 将所选项目的对应数据值写入模型。
不同的 editorValueType 设置会创建不同类型的编辑器值。组合框的值取决于组合框中下拉列表的项目。您可以使用 items 方法来获取和设置项目。例如：
您还可以使用 dataBinding 方法将组合框绑定到数据源。数据源将在运行时替换组合框中的项目。例如：
使用 editable 方法设置用户是否可以在组合框编辑器中输入。默认值为 false；仅允许选择。例如：
您可以使用 itemHeight 方法设置下拉列表中每个项目的高度。例如：
使用 allowFloat 方法设置是否允许下拉列表浮动在 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$/zh/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 name="spreadjs culture" content="zh-cn" />
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<link rel="stylesheet" type="text/css"
href="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets/dist/gc.spread.sheets.all.min.js"
type="text/javascript"></script>
<script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script>
<script
src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-resources-zh/dist/gc.spread.sheets.resources.zh.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>Items Text:</label>
<input id="txtComboCellItemsText" type="text" />
</div>
<div class="option-row">
<label>Items Value:</label>
<input id="txtComboCellItemsValue" type="text" />
</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;
}
