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.
import * as React from 'react';
import * as ReactDOM from 'react-dom';
import './styles.css';
import { AppFunc } from './app-func';
// import { App } from './app-class';
// 1. Functional Component sample
ReactDOM.render(<AppFunc />, document.getElementById('app'));
// 2. Class Component sample
// ReactDOM.render(<App />, document.getElementById('app'));
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
const spreadNS = GC.Spread.Sheets;
class Country {
constructor(shortName, fullName) {
this.value = this.shortName = shortName;
this.text = this.fullName = fullName;
}
}
let spread = null;
export function AppFunc() {
const [comBoxCellTypeOption, setComBoxCellTypeOption] = React.useState({
editorValueType: 0,
itemsText: '',
itemsValue: '',
itemHeight: 0,
editable: false,
disabled: false,
editorValueTypeList: Object.keys(GC.Spread.Sheets.CellTypes.EditorValueType).filter(key => isNaN(Number(key))),
allowFloat: true,
bindingType: 0,
dataSourceType: 0,
dataSource: '',
text: '',
value: '',
tableName: '',
textColumn: '',
valueColumn: ''
});
const initSpread = async (currSpread) => {
spread = currSpread;
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());
spread.setSheetCount(2);
var sheet1 = spread.getSheet(0);
initStaticItemsSheet(sheet1);
var sheet2 = spread.getSheet(1);
initDataBindingItemsSheet(sheet2);
fetchDataSource(spread);
spread.resumePaint();
loadingTip.remove();
}
const 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);
}
const propertyChange = (e, settings) => {
const sheet = spread.getActiveSheet();
const sels = sheet.getSelections();
if (sels && sels.length > 0) {
const sel = getActualRange(sels[0], sheet.getRowCount(), sheet.getColumnCount());
const comboBoxCellType = sheet.getCellType(sel.row, sel.col);
if (!(comboBoxCellType instanceof spreadNS.CellTypes.ComboBox)) {
setComBoxCellTypeOption({ ...comBoxCellTypeOption, disabled: true });
return;
}
if (!settings) {
const items = comboBoxCellType.items();
const { texts, values } = getTextAndValueStringArray(items);
const dataBinding = comboBoxCellType.dataBinding();
const dataBindingEditorValue = dataBindingToDataBindingEditorValue(dataBinding, spread);
setComBoxCellTypeOption({
...comBoxCellTypeOption,
disabled: false,
editorValueType: comboBoxCellType.editorValueType(),
itemsText: texts,
itemsValue: values,
editable: comboBoxCellType.editable(),
itemHeight: comboBoxCellType.itemHeight(),
allowFloat: comboBoxCellType.allowFloat(),
bindingType: dataBinding ? 1 : 0,
...dataBindingEditorValue
});
} else {
comboBoxCellType.editorValueType(Number(settings.editorValueType));
const itemsText = settings.itemsText.split(",");
const itemsValue = settings.itemsValue.split(",");
const itemsLength = itemsText.length > itemsValue.length ? itemsText.length : itemsValue.length;
const items = getTextAndValueArray(itemsText, itemsValue, itemsLength);
comboBoxCellType.items(items);
comboBoxCellType.editable(settings.editable);
comboBoxCellType.allowFloat(settings.allowFloat);
const itemHeight = Number(settings.itemHeight);
if (!isNaN(itemHeight) && itemHeight > 0) {
comboBoxCellType.itemHeight(itemHeight);
}
if (+settings.bindingType === 1) {
if (settings.dataSourceType === 1) {
//settings.dataSource = this.formulaBox.text();
}
const dataBinding = dataBindingEditorValueToDataBinding(settings);
comboBoxCellType.dataBinding(dataBinding);
} else {
comboBoxCellType.dataBinding(null);
}
}
}
sheet.repaint();
}
const getActualRange = (range, maxRowCount, maxColCount) => {
const row = range.row < 0 ? 0 : range.row;
const col = range.col < 0 ? 0 : range.col;
const rowCount = range.rowCount < 0 ? maxRowCount : range.rowCount;
const colCount = range.colCount < 0 ? maxColCount : range.colCount;
return new spreadNS.Range(row, col, rowCount, colCount);
}
const getTextAndValueStringArray = (items) => {
let texts = '', values = '';
for (let i = 0, len = items.length; i < len; i++) {
const item = items[i];
if (!item) {
continue;
}
if (item.text) {
texts += item.text + ',';
}
if (item.value) {
values += item.value + ',';
}
}
texts = texts.slice(0, texts.length - 1);
values = values.slice(0, values.length - 1);
return { texts, values };
}
const getTextAndValueArray = (itemsText, itemsValue, itemsLength) => {
const items = [];
for (let count = 0; count < itemsLength; count++) {
const t = itemsText.length > count && itemsText[0] != "" ? itemsText[count] : undefined;
const 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 };
}
}
return items;
}
const initStaticItemsSheet = (sheet) => {
sheet.name("Static-Items");
sheet.bind(spreadNS.Events.SelectionChanged, (e) => propertyChange(e));
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(null);
sheet.resumePaint();
}
const initDataBindingItemsSheet = (sheet) => {
sheet.name("Binding-Items");
sheet.bind(spreadNS.Events.SelectionChanged, (e) => propertyChange(e));
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(null);
sheet.resumePaint();
}
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={initSpread}>
<Worksheet />
</SpreadSheets>
</div>
<Panel propertyChange={propertyChange} setting={comBoxCellTypeOption} spread={spread} />
</div>);
}
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 dataBindingToDataBindingEditorValue(dataBinding, workBook) {
let dataBindingEditorValue;
const defDataSource = getDefaultDataSource(workBook);
if (!dataBinding) {
dataBindingEditorValue = defDataSource;
} else {
const dataSourceType = isDataTable(dataBinding.dataSource, workBook) ? 0 : 1;
if (dataSourceType === 0) {
dataBindingEditorValue = {
dataSourceType: dataSourceType,
tableName: dataBinding.dataSource,
textColumn: dataBinding.text,
valueColumn: dataBinding.value,
};
} else { // custom
dataBindingEditorValue = {
...defDataSource,
dataSourceType: dataSourceType,
dataSource: dataBinding.dataSource,
text: dataBinding.text,
value: dataBinding.value,
};
}
}
return dataBindingEditorValue;
}
function getDefaultDataSource(workBook) {
const tables = getDataTables(workBook);
if (tables.length === 0) {
return { dataSourceType: 0 };
}
const column = getColumns(tables[0], workBook)[0];
return { dataSourceType: 0, tableName: tables[0], textColumn: column, valueColumn: column };
}
function dataBindingEditorValueToDataBinding(uiData) {
if (+uiData.dataSourceType === 0) {
return { dataSource: uiData.tableName, text: uiData.textColumn, value: uiData.valueColumn };
} else {
return { dataSource: uiData.dataSource, text: uiData.text, value: uiData.value };
}
}
function isDataTable(table, workBook) {
const lowerTableName = table.toLowerCase();
return getDataTables(workBook).some((t) => t.toLowerCase() === lowerTableName);
}
function getDataTables(workBook) {
if (!workBook) {
return [];
}
const dataManager = workBook.dataManager();
if (!dataManager) {
return [];
}
const tables = workBook.dataManager().tables;
if (!tables) {
return [];
}
return Object.keys(tables);
}
function getColumns(tableName, workBook) {
if (!workBook) {
return [];
}
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 Panel(props) {
const [setting, setSetting] = React.useState(props.setting);
React.useEffect(() => {
setSetting(props.setting);
}, [props.setting]);
const optionsList = () => {
return setting.editorValueTypeList.map((item, index) => {
return <option value={index}>{item.charAt(0).toUpperCase() + item.slice(1)}</option>;
});
};
const bindingTypeList = () => {
return ["Static Items", "Data Binding"].map((item, index) => {
return <option value={index}>{item}</option>;
});
}
const tablesList = () => {
const tables = getDataTables(props.spread);
return tables.map((item) => {
return <option value={item}>{item}</option>;
});
}
const columnsList = () => {
return getColumns(setting.tableName, props.spread).map((item) => {
return <option value={item}>{item}</option>;
});
}
const changeTableName = (tableName) => {
const columns = getColumns(tableName, props.spread);
const defaultColumn = columns[0];
setSetting({ ...setting, tableName, textColumn: defaultColumn, valueColumn: defaultColumn });
}
return (
<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>Editor Value Type:</label>
<select id="textAlign" value={setting.editorValueType} onChange={(e) => { setSetting({ ...setting, editorValueType: e.target.value }); }}>
{optionsList()}
</select>
</div>
<div class="option-row">
<label>Binding Type:</label>
<select id="binding-type" value={setting.bindingType} onChange={(e) => { setSetting({ ...setting, bindingType: e.target.value }); }}>
{bindingTypeList()}
</select>
</div>
{
+setting.bindingType === 0 &&
<div>
<div class="option-row">
<label for="itemsText">Items Text:</label>
<input id="itemsText" type="text" value={setting.itemsText} onChange={(e) => { setSetting({ ...setting, itemsText: e.target.value }); }} />
</div>
<div class="option-row">
<label for="itemsValue">Items Value:</label>
<input id="itemsValue" type="text" value={setting.itemsValue} onChange={(e) => { setSetting({ ...setting, itemsValue: e.target.value }); }} />
</div>
</div>
}
{
+setting.bindingType === 1 &&
<div class="option-row">
<label >Data Source Type:</label>
<select id="selComboDataSourceType" value={setting.dataSourceType} onChange={e => setSetting({ ...setting, dataSourceType: e.target.value })}>
<option value="0">Table</option>
<option value="1">Custom</option>
</select>
</div>
}
{(+setting.bindingType === 1) && (+setting.dataSourceType === 0) &&
<div>
<div class="option-row">
<label >Data Source:</label>
<select id="selComboDataSource" value={setting.tableName} onChange={e => changeTableName(e.target.value)}>
{tablesList()}
</select>
</div>
<div class="option-row">
<label>Binding Text:</label>
<select id="selComboText" value={setting.textColumn} onChange={e => setSetting({ ...setting, textColumn: e.target.value })}>
{columnsList()}
</select>
</div>
<div class="option-row">
<label>Binding Value:</label>
<select id="selComboValue" value={setting.valueColumn} onChange={e => setSetting({ ...setting, valueColumn: e.target.value })}>
{columnsList()}
</select>
</div>
</div>
}
{(+setting.bindingType === 1) && (+setting.dataSourceType === 1) &&
<div>
<div class="option-row">
<label >Data Source:</label>
{/* <div id="formulaBar" spellcheck="false" style={{border: "1px solid #808080",width:"100%"}}>
</div> */}
<input id="txtFormula" type="text" value={setting.dataSource} onChange={e => setSetting({ ...setting, dataSource: e.target.value })} />
</div>
<div class="option-row">
<label>Binding Text:</label>
<input id="txtText" type="text" value={setting.text} onChange={e => setSetting({ ...setting, text: e.target.value })} />
</div>
<div class="option-row">
<label>Binding Value:</label>
<input id="txtValue" type="text" value={setting.value} onChange={e => setSetting({ ...setting, value: e.target.value })} />
</div>
</div>
}
<div class="option-row">
<label for="itemHeight">Item Height:</label>
<input id="itemHeight" type="text" value={setting.itemHeight} onChange={(e) => { setSetting({ ...setting, itemHeight: e.target.value }); }} />
</div>
<div class="option-row">
<input id="editable" type="checkbox" value={setting.editable} onChange={(e) => { setSetting({ ...setting, editable: e.target.value }); }} />
<label for="editable">Editable:</label>
</div>
<div className="option-row">
<input id="allowFloat" type="checkbox" checked={setting.allowFloat} onChange={(e) => {
setSetting({ ...setting, allowFloat: e.target.checked });
}} />
<label htmlFor="allowFloat">Allow Float:</label>
</div>
<div class="option-row">
<input type="button" id="setProperty" value="Update" disabled={setting.disabled} onClick={(e) => { props.propertyChange(e, setting) }} />
</div>
</div>
);
}
import * as React from 'react';
import GC from '@mescius/spread-sheets';
import "@mescius/spread-sheets-tablesheet";
import { SpreadSheets, Worksheet } from '@mescius/spread-sheets-react';
const spreadNS = GC.Spread.Sheets;
class Country {
constructor(shortName, fullName) {
this.value = this.shortName = shortName;
this.text = this.fullName = fullName;
}
}
const Component = React.Component;
export class App extends Component {
constructor(props) {
super(props);
this.spread = null;
this.formulaBox = null;
this.state = {
editorValueType: 0,
itemsText: '',
itemsValue: '',
itemHeight: 0,
editable: false,
disabled: false,
editorValueTypeList: Object.keys(GC.Spread.Sheets.CellTypes.EditorValueType).filter(key => isNaN(Number(key))),
allowFloat: true,
bindingType: 0,
dataSourceType: 0,
dataSource: '',
text: '',
value: '',
tableName: '',
textColumn: '',
valueColumn: ''
}
}
render() {
return (<div class="sample-tutorial">
<div class="sample-spreadsheets">
<SpreadSheets workbookInitialized={spread => this.initSpread(spread)}>
<Worksheet />
</SpreadSheets>
</div>
<Panel propertyChange={(e, settings) => { this.propertyChange(e, settings) }} setting={this.state} spread={this.spread} />
</div>);
}
async initSpread(spread) {
this.spread = spread;
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());
spread.setSheetCount(2);
var sheet1 = spread.getSheet(0);
this.initStaticItemsSheet(sheet1);
var sheet2 = spread.getSheet(1);
this.initDataBindingItemsSheet(sheet2);
this.fetchDataSource(spread);
spread.resumePaint();
// this.formulaBox = new GC.Spread.Sheets.FormulaTextBox.FormulaTextBox(document.getElementById('formulaBar'), { rangeSelectMode: true });
// this.formulaBox.workbook(spread);
loadingTip.remove();
}
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);
}
propertyChange(e, settings) {
const sheet = this.spread.getActiveSheet();
const sels = sheet.getSelections();
if (sels && sels.length > 0) {
const sel = this.getActualRange(sels[0], sheet.getRowCount(), sheet.getColumnCount());
const comboBoxCellType = sheet.getCellType(sel.row, sel.col);
if (!(comboBoxCellType instanceof spreadNS.CellTypes.ComboBox)) {
this.setState({ disabled: true });
return;
}
if (!settings) {
const items = comboBoxCellType.items();
const { texts, values } = this.getTextAndValueStringArray(items);
const dataBinding = comboBoxCellType.dataBinding();
const dataBindingEditorValue = dataBindingToDataBindingEditorValue(dataBinding, this.spread);
if (+dataBindingEditorValue.bindingType === 1 && dataBindingEditorValue.dataSourceType === 1) {
//this.formulaBox.text(dataBindingEditorValue.dataSource);
}
this.setState({
disabled: false,
editorValueType: comboBoxCellType.editorValueType(),
itemsText: texts,
itemsValue: values,
editable: comboBoxCellType.editable(),
itemHeight: comboBoxCellType.itemHeight(),
allowFloat: comboBoxCellType.allowFloat(),
bindingType: dataBinding ? 1 : 0,
...dataBindingEditorValue
});
} else {
comboBoxCellType.editorValueType(Number(settings.editorValueType));
const itemsText = settings.itemsText.split(",");
const itemsValue = settings.itemsValue.split(",");
const itemsLength = itemsText.length > itemsValue.length ? itemsText.length : itemsValue.length;
const items = this.getTextAndValueArray(itemsText, itemsValue, itemsLength);
comboBoxCellType.items(items);
comboBoxCellType.editable(settings.editable);
comboBoxCellType.allowFloat(settings.allowFloat);
const itemHeight = Number(settings.itemHeight);
if (!isNaN(itemHeight) && itemHeight > 0) {
comboBoxCellType.itemHeight(itemHeight);
}
if (+settings.bindingType === 1) {
if (settings.dataSourceType === 1) {
//settings.dataSource = this.formulaBox.text();
}
const dataBinding = dataBindingEditorValueToDataBinding(settings);
comboBoxCellType.dataBinding(dataBinding);
}else{
comboBoxCellType.dataBinding(null);
}
}
}
sheet.repaint();
}
getActualRange(range, maxRowCount, maxColCount) {
const row = range.row < 0 ? 0 : range.row;
const col = range.col < 0 ? 0 : range.col;
const rowCount = range.rowCount < 0 ? maxRowCount : range.rowCount;
const colCount = range.colCount < 0 ? maxColCount : range.colCount;
return new spreadNS.Range(row, col, rowCount, colCount);
}
getTextAndValueStringArray(items) {
let texts = '', values = '';
for (let i = 0, len = items.length; i < len; i++) {
const item = items[i];
if (!item) {
continue;
}
if (item.text) {
texts += item.text + ',';
}
if (item.value) {
values += item.value + ',';
}
}
texts = texts.slice(0, texts.length - 1);
values = values.slice(0, values.length - 1);
return { texts, values };
}
getTextAndValueArray(itemsText, itemsValue, itemsLength) {
const items = [];
for (let count = 0; count < itemsLength; count++) {
const t = itemsText.length > count && itemsText[0] != "" ? itemsText[count] : undefined;
const 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 };
}
}
return items;
}
initStaticItemsSheet(sheet) {
sheet.name("Static-Items");
sheet.bind(spreadNS.Events.SelectionChanged, (e) => this.propertyChange(e));
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);
this.propertyChange(null);
sheet.resumePaint();
}
initDataBindingItemsSheet(sheet) {
sheet.name("Binding-Items");
sheet.bind(spreadNS.Events.SelectionChanged, (e) => this.propertyChange(e));
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);
this.propertyChange(null);
sheet.resumePaint();
}
}
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 dataBindingToDataBindingEditorValue(dataBinding, workBook) {
let dataBindingEditorValue;
const defDataSource = getDefaultDataSource(workBook);
if (!dataBinding) {
dataBindingEditorValue = defDataSource;
} else {
const dataSourceType = isDataTable(dataBinding.dataSource, workBook) ? 0 : 1;
if (dataSourceType === 0) {
dataBindingEditorValue = {
dataSourceType: dataSourceType,
tableName: dataBinding.dataSource,
textColumn: dataBinding.text,
valueColumn: dataBinding.value,
};
} else { // custom
dataBindingEditorValue = {
...defDataSource,
dataSourceType: dataSourceType,
dataSource: dataBinding.dataSource,
text: dataBinding.text,
value: dataBinding.value,
};
}
}
return dataBindingEditorValue;
}
function getDefaultDataSource(workBook) {
const tables = getDataTables(workBook);
if (tables.length === 0) {
return { dataSourceType: 0 };
}
const column = getColumns(tables[0], workBook)[0];
return { dataSourceType: 0, tableName: tables[0], textColumn: column, valueColumn: column };
}
function dataBindingEditorValueToDataBinding(uiData) {
if (+uiData.dataSourceType === 0) {
return { dataSource: uiData.tableName, text: uiData.textColumn, value: uiData.valueColumn };
} else {
return { dataSource: uiData.dataSource, text: uiData.text, value: uiData.value };
}
}
function isDataTable(table, workBook) {
const lowerTableName = table.toLowerCase();
return getDataTables(workBook).some((t) => t.toLowerCase() === lowerTableName);
}
function getDataTables(workBook) {
if (!workBook) {
return [];
}
const dataManager = workBook.dataManager();
if (!dataManager) {
return [];
}
const tables = workBook.dataManager().tables;
if (!tables) {
return [];
}
return Object.keys(tables);
}
function getColumns(tableName, workBook) {
if (!workBook) {
return [];
}
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;
}
class Panel extends Component {
constructor(props) {
super(props);
this.state = this.props.setting;
}
componentWillReceiveProps(nextProps) {
this.setState(nextProps.setting);
}
render() {
const optionsList = () => {
return this.state.editorValueTypeList.map((item, index) => {
return <option value={index}>{item.charAt(0).toUpperCase() + item.slice(1)}</option>;
});
};
const bindingTypeList = () => {
return ["Static Items", "Data Binding"].map((item, index) => {
return <option value={index}>{item}</option>;
});
}
const tablesList = () => {
const tables = getDataTables(this.props.spread);
return tables.map((item) => {
return <option value={item}>{item}</option>;
});
}
const columnsList = () => {
return getColumns(this.state.tableName, this.props.spread).map((item) => {
return <option value={item}>{item}</option>;
});
}
const changeTableName = (tableName) => {
const columns = getColumns(tableName, this.props.spread);
const defaultColumn = columns[0];
this.setState({ tableName, textColumn: defaultColumn, valueColumn: defaultColumn });
}
return (
<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>Editor Value Type:</label>
<select id="textAlign" value={this.state.editorValueType} onChange={(e) => { this.setState({ editorValueType: e.target.value }); }}>
{optionsList()}
</select>
</div>
<div class="option-row">
<label>Binding Type:</label>
<select id="binding-type" value={this.state.bindingType} onChange={(e) => { this.setState({ bindingType: e.target.value }); }}>
{bindingTypeList()}
</select>
</div>
{
+this.state.bindingType === 0 &&
<div>
<div class="option-row">
<label for="itemsText">Items Text:</label>
<input id="itemsText" type="text" value={this.state.itemsText} onChange={(e) => { this.setState({ itemsText: e.target.value }); }} />
</div>
<div class="option-row">
<label for="itemsValue">Items Value:</label>
<input id="itemsValue" type="text" value={this.state.itemsValue} onChange={(e) => { this.setState({ itemsValue: e.target.value }); }} />
</div>
</div>
}
{
+this.state.bindingType === 1 &&
<div class="option-row">
<label >Data Source Type:</label>
<select id="selComboDataSourceType" value={this.state.dataSourceType} onChange={e => this.setState({ dataSourceType: e.target.value })}>
<option value="0">Table</option>
<option value="1">Custom</option>
</select>
</div>
}
{(+this.state.bindingType === 1) && (+this.state.dataSourceType === 0) &&
<div>
<div class="option-row">
<label >Data Source:</label>
<select id="selComboDataSource" value={this.state.tableName} onChange={e => changeTableName(e.target.value)}>
{tablesList()}
</select>
</div>
<div class="option-row">
<label>Binding Text:</label>
<select id="selComboText" value={this.state.textColumn} onChange={e => this.setState({ textColumn: e.target.value })}>
{columnsList()}
</select>
</div>
<div class="option-row">
<label>Binding Value:</label>
<select id="selComboValue" value={this.state.valueColumn} onChange={e => this.setState({ valueColumn: e.target.value })}>
{columnsList()}
</select>
</div>
</div>
}
{(+this.state.bindingType === 1) && (+this.state.dataSourceType === 1) &&
<div>
<div class="option-row">
<label >Data Source:</label>
{/* <div id="formulaBar" spellcheck="false" style={{border: "1px solid #808080",width:"100%"}}>
</div> */}
<input id="txtFormula" type="text" value={this.state.dataSource} onChange={e => this.setState({ dataSource: e.target.value })} />
</div>
<div class="option-row">
<label>Binding Text:</label>
<input id="txtText" type="text" value={this.state.text} onChange={e => this.setState({ text: e.target.value })} />
</div>
<div class="option-row">
<label>Binding Value:</label>
<input id="txtValue" type="text" value={this.state.value} onChange={e => this.setState({ value: e.target.value })} />
</div>
</div>
}
<div class="option-row">
<label for="itemHeight">Item Height:</label>
<input id="itemHeight" type="text" value={this.state.itemHeight} onChange={(e) => { this.setState({ itemHeight: e.target.value }); }} />
</div>
<div class="option-row">
<input id="editable" type="checkbox" value={this.state.editable} onChange={(e) => { this.setState({ editable: e.target.value }); }} />
<label for="editable">Editable:</label>
</div>
<div className="option-row">
<input id="allowFloat" type="checkbox" checked={this.state.allowFloat} onChange={(e) => {
this.setState({ allowFloat: e.target.checked });
}} />
<label htmlFor="allowFloat">Allow Float:</label>
</div>
<div class="option-row">
<input type="button" id="setProperty" value="Update" disabled={this.state.disabled} onClick={(e) => { this.props.propertyChange(e, this.state) }} />
</div>
</div>
)
}
}
<!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/react/node_modules/@mescius/spread-sheets/styles/gc.spread.sheets.excel2013white.css">
<!-- SystemJS -->
<script src="$DEMOROOT$/en/react/node_modules/systemjs/dist/system.src.js"></script>
<script src="systemjs.config.js"></script>
<script>
System.import('$DEMOROOT$/en/lib/react/license.js').then(function () {
System.import('./src/app');
});
</script>
</head>
<body>
<div id="app"></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;
overflow: auto;
padding: 12px;
height: 100%;
box-sizing: border-box;
background: #fbfbfb;
}
.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;
}
body {
position: absolute;
top: 0;
bottom: 0;
left: 0;
right: 0;
}
#app {
height: 100%;
}
export function getData() {
return [
{ name: "Stoves S0", line: "Washers", color: "Green", discontinued: true, rating: "Average" },
{ name: "Computers C1", line: "Washers", color: "Green", discontinued: true, rating: "Average" },
{ name: "Washers W3", line: "Washers", color: "Green", discontinued: true, rating: "Average" }
]
}
(function (global) {
System.config({
transpiler: 'plugin-babel',
babelOptions: {
es2015: true,
react: true
},
meta: {
'*.css': { loader: 'css' }
},
paths: {
// paths serve as alias
'npm:': 'node_modules/'
},
// map tells the System loader where to look for things
map: {
'@mescius/spread-sheets': 'npm:@mescius/spread-sheets/index.js',
'@mescius/spread-sheets-tablesheet': 'npm:@mescius/spread-sheets-tablesheet/index.js',
'@mescius/spread-sheets-react': 'npm:@mescius/spread-sheets-react/index.js',
'@grapecity/jsob-test-dependency-package/react-components': 'npm:@grapecity/jsob-test-dependency-package/react-components/index.js',
'react': 'npm:react/umd/react.production.min.js',
'react-dom': 'npm:react-dom/umd/react-dom.production.min.js',
'css': 'npm:systemjs-plugin-css/css.js',
'plugin-babel': 'npm:systemjs-plugin-babel/plugin-babel.js',
'systemjs-babel-build':'npm:systemjs-plugin-babel/systemjs-babel-browser.js'
},
// packages tells the System loader how to load when no filename and/or no extension
packages: {
src: {
defaultExtension: 'jsx'
},
"node_modules": {
defaultExtension: 'js'
},
}
});
})(this);
