Worksheet Data Types

The GC.Data.DataManager supports custom cell data types, which can be projected data-bound cells or formula references.

Similar to indexing a database, creating indexes in the table will enhance the search performance. Create indexes in table After creating a table using the GC.Data.DataManager, you can create indexes by using table.createIndexes(fields). The indexes of specific fields will update automatically when inserting/deleting/updating records in a table. Search records in specific field You could use table.search(value, field) to search records in a specific field. Regardless if the target field you search has indexes, you will always have records returned if the search is successful, but indexing will speed up this process significantly. Indexes operations If you want to know which field has been indexed already, you can use the table.getIndexes() method. Redundant indexes could bring extra performance overhead, so you can also drop specific indexes by using the table.dropIndexes(fields) method. Alternatively, you can use the table.clearIndexes() method to clear all existing indexes in a table.
window.onload = function() { var globalDataSource = {}; var showDetailDialog = false; var spread = new GC.Spread.Sheets.Workbook(document.getElementById('ss')); spread.options.newTabVisible = false; spread.setSheetCount(2); var sheet1 = spread.sheets[0]; var sheet2 = spread.sheets[1]; sheet1.name("Cell Data Types"); sheet1.setColumnWidth(1, 120); sheet2.name("Source Data"); var highlightStyle = new GC.Spread.Sheets.Style(); highlightStyle.backColor = '#b3e5fc'; var dataManager = spread.dataManager(); var supplierTable = dataManager.addTable("supplierTable", { remote: { read: { url: 'https://northwind.vercel.app/api/suppliers' } } }); supplierTable.fetch().then(function(res) { var data = res.data; data.pop(); // remove the unusual item traverseDataSource(data); spread.suspendPaint(); spread.options.autoFitType = GC.Spread.Sheets.AutoFitType.cellWithHeader; sheet2.autoGenerateColumns = true; sheet2.setDataSource(data); for (var i = 0; i < sheet2.getColumnCount(); i++) { sheet2.autoFitColumn(i); } spread.resumePaint(); initKeyColumnField(); sheet1.setValue(1, 1, "Peter Wilson"); sheet1.setValue(2, 1, "Charlotte Cooper"); sheet1.setValue(3, 1, "Guylène Nodier"); sheet1.setSelection(1, 1, 3, 1); convertButton.click(); }); var convertButton = document.getElementById("convert"); convertButton.addEventListener('click', function() { if (spread.getActiveSheet() !== sheet1) { alert('Only support to convert cells in "Cell Data Types" sheet.'); return; } var selections = sheet1.getSelections(); var fields = supplierTable.getIndexes(); for (var i = 0; i < selections.length; i++) { var selection = selections[i]; for (var r = selection.row; r < selection.row + selection.rowCount; r++) { for (var c = selection.col; c < selection.col + selection.colCount; c++) { var value = sheet1.getText(r, c); if (value) { var field = fields[0]; var records = supplierTable.search(value, field); if (records) { var rowData = records[0]; if (rowData) { var cellPosition = getCellPosition(r, c); globalDataSource[cellPosition] = rowData; sheet1.getCell(r, c) .value(rowData) .formatter('=PROPERTY(@,"' + field + '")') .font("bold 14.6667px Calibri"); } } } } } } var source = new GC.Spread.Sheets.Bindings.CellBindingSource(globalDataSource); sheet1.setDataSource(source); if (selections.length === 1) { onSelectionChanged(selections); } }); var subItemList = document.getElementById('subItemList'); var richBlock = document.getElementById('rich'); sheet1.bind(GC.Spread.Sheets.Events.SelectionChanged, function(e, args) { var selections = args.newSelections; if (selections.length === 1) { onSelectionChanged(selections); } else { richBlock.classList.remove('show'); } }); sheet1.bind(GC.Spread.Sheets.Events.CellChanged, function(e, args) { var propertyName = args.propertyName; var newValue = args.newValue; var oldValue = args.oldValue; if (propertyName !== "[styleinfo]") { var row = args.row; var col = args.col; var formatter = sheet1.getFormatter(row, col); if (newValue === null || typeof newValue !== 'object') { sheet1.getCell(row, col).font("14.6667px Calibri").formatter(""); } if (typeof oldValue === 'object' && formatter) { var cellPosition = getCellPosition(row, col); if (globalDataSource[cellPosition]) { delete globalDataSource[cellPosition]; } } } }); sheet2.bind(GC.Spread.Sheets.Events.CellChanged, function(e, args) { var propertyName = args.propertyName; var row = args.row; var col = args.col; var newValue = args.newValue; var oldValue = args.oldValue; if (typeof oldValue === 'object' && oldValue !== null && newValue !== oldValue && propertyName !== "[styleinfo]") { sheet2.setValue(row, col, oldValue); } }); sheet1.bind(GC.Spread.Sheets.Events.ClipboardPasted, function(e, args) { var cellRange = args.cellRange; var row = cellRange.row; var col = cellRange.col; sheet1.getCell(row, col).font("14.6667px Calibri").formatter(""); var cellPosition = getCellPosition(row, col); if (globalDataSource[cellPosition]) { delete globalDataSource[cellPosition]; } }); spread.bind(GC.Spread.Sheets.Events.SheetChanged, function(e, args) { var sheet = spread.getSheet(args.sheetIndex); var selections = sheet.getSelections(); if (sheet === sheet1) { onSelectionChanged(selections); subItemList.classList.remove('show'); } else if (sheet === sheet2) { richBlock.classList.remove('show'); } }); function onSelectionChanged(selections) { var sel = selections[0]; var row = sel.row; var col = sel.col; var dataSource = sheet1.getDataSource(); if (dataSource) { var cellPosition = getCellPosition(row, col); var data = dataSource.getSource()[cellPosition]; if (data) { richBlock.classList.add('show'); var cellRect = sheet1.getCellRect(row, col); richBlock.style.left = cellRect.x + cellRect.width + 4 + "px"; richBlock.style.top = cellRect.y - 20 + "px"; updatePropertyList(data); } else { richBlock.classList.remove('show'); } } } var richButton = document.getElementById('rich-button'); var propertyList = document.getElementById('property-list'); richButton.addEventListener('mousedown', function() { richButton.classList.add('active'); }); richButton.addEventListener('mouseup', function() { richButton.classList.remove('active'); }); richButton.addEventListener('click', function() { if (propertyList.classList.contains('show')) { propertyList.classList.remove('show'); } else { propertyList.classList.add('show'); } }); propertyList.addEventListener('click', function(e) { var target = e.target; if (target.classList.contains('list-item')) { var selections = sheet1.getSelections(); var columnCount = sheet1.getColumnCount(); for (var i = 0; i < selections.length; i++) { var sel = selections[i]; for (var r = sel.row; r < sel.rowCount + sel.row; r++) { for (var c = sel.col; c < sel.colCount + sel.col; c++) { for (var j = c + 1; j < columnCount; j++) { if (isNullOrUndefined(sheet1.getValue(r, j))) { var property = target.innerHTML; var cellPosition = getCellPosition(r, c); sheet1.setBindingPath(r, j, cellPosition + "." + property); propertyList.classList.remove('show'); break; } } } } } } }); function updatePropertyList(data) { var container = document.getElementById("property-list").getElementsByClassName("content")[0]; container.innerHTML = ''; propertyList.classList.remove('show'); var field = supplierTable.getIndexes()[0]; var item; for (var key in data) { if (key === field) { continue; } if (typeof data[key] === 'object') { for (var subKey in data[key]) { item = document.createElement("div"); item.classList.add('list-item'); item.innerHTML = key + '.' + subKey; container.appendChild(item); } } else { item = document.createElement("div"); item.classList.add('list-item'); item.innerHTML = key; container.appendChild(item); } } } var fieldsRecommend = []; function traverseDataSource(data) { data.forEach(function(item, row) { var newItem = {}; var col = 0; for (var key in item) { if (typeof item[key] !== 'object' || item[key] === null) { newItem[key] = item[key]; if (fieldsRecommend.indexOf(key) === -1) { fieldsRecommend.push(key); } } else { setListPicker(item[key], row, col); for (var _key in item[key]) { var actualKey = key + '.' + _key; newItem[actualKey] = item[key][_key]; if (fieldsRecommend.indexOf(actualKey) === -1) { fieldsRecommend.push(actualKey); } } } col++; } }); } function setListPicker(dataSource, row, col) { let style = new GC.Spread.Sheets.Style(); style.cellButtons = [{ useButtonStyle: true, imageType: GC.Spread.Sheets.ButtonImageType.search, command: function (sheet, row, col, option) { if (!showDetailDialog) { var data = sheet.getValue(row, col); var subItem = ''; var prop = sheet.getValue(0, col, 1); for (var key in data) { subItem += '<div class="subItem">' + ' <label>' + key + '</label>' + ' <input type="text" data-index="'+ row + '" data-prop="'+ prop + '" data-key="'+ key + '" value="'+ data[key] + '">' + '</div>'; } subItemList.innerHTML = subItem; subItemList.classList.add('show'); var cellRect = sheet2.getCellRect(row, col); subItemList.style.left = cellRect.x + cellRect.width + 10 + "px"; subItemList.style.top = cellRect.y + "px"; } else { subItemList.classList.remove('show'); } showDetailDialog = !showDetailDialog; }, }]; sheet2.setStyle(row, col, style); } subItemList.addEventListener('input', function(e) { var target = e.target; var index = target.dataset.index; var prop = target.dataset.prop; var key = target.dataset.key; var dataSource = sheet2.getDataSource(); dataSource[index][prop][key] = target.value; }); function initKeyColumnField() { if (fieldsRecommend.length > 0) { var columnIndex = 2; supplierTable.createIndexes([fieldsRecommend[columnIndex]]); sheet2.setStyle(-1, columnIndex, highlightStyle); } } function isNullOrUndefined(value) { return value === null || value === undefined; } function getCellPosition(row, col) { return GC.Spread.Sheets.CalcEngine.rangeToFormula( new GC.Spread.Sheets.Range(row, col, 1, 1), 0, 0, GC.Spread.Sheets.CalcEngine.RangeReferenceRelative.allRelative ); } document.body.addEventListener('keyup', function(e) { if (e.keyCode === 27 && showDetailDialog) { subItemList.classList.remove('show'); showDetailDialog = false; } }); };
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <title>Sample of Search</title> <link rel="stylesheet" type="text/css" href="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <!-- Promise Polyfill for IE, https://www.npmjs.com/package/promise-polyfill --> <script src="https://cdn.jsdelivr.net/npm/promise-polyfill@8/dist/polyfill.min.js"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/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> <link rel="stylesheet" type="text/css" href="styles.css"> <script src="app.js" type="text/javascript"></script> </head> <body> <div class="container"> <div id="ss"></div> <div id="panel"> <div class="panel-item"> <div class="description"> <p> In this demo, we have indexed the <span class="important">contactName</span> column in the <span class="important">Source Data</span> sheet. </p> <p> Copy some data from the indexed column and paste it into the <span class="important">Cell Data Types</span> sheet as a search item. </p> <p> Then click the button below to search the record. You will see all of the fields of a record in a popup list if the search is successful. </p> </div> <button id="convert">Search and Convert</button> </div> </div> <div id="rich"> <div id="rich-button"></div> <div id="property-list"> <div class="content"></div> </div> </div> <div id="subItemList"></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 { font-size: 14px; padding: 5px; margin-top: 10px; } label { display: block; margin-bottom: 6px; } input { padding: 4px 6px; } input[type=button] { margin-top: 6px; display: block; } body * { box-sizing: border-box; } body, html { width: 100%; height: 100%; position: relative; overflow: hidden; margin: 0; padding: 0; } .container{ width: 100%; height: 100%; } .container:after { display: block; content: ""; clear: both; } #ss { width: calc(100% - 300px); height: 100%; float: left; } #panel { width: 300px; height: 100%; padding: 4px; float: left; } #keyColumnSelect { width: 100%; height: 20px; } .panel-item { margin-bottom: 20px; } #convert { width: 100%; height: 20px; } #rich { width: 0; height: 0; position: absolute; left: 0; top: 0; display: none; } #rich.show { display: block; } #rich-button { width: 24px; height: 24px; background: url("$DEMOROOT$/spread/source/images/search_button.png") no-repeat center center; background-size: 100% 100%; box-shadow: 0px 2px 4px #aaa; margin-bottom: 4px; } #rich-button:hover { background: url("$DEMOROOT$/spread/source/images/search_button_hover.png") no-repeat center center; } #rich-button.active { background: url("$DEMOROOT$/spread/source/images/search_button_active.png") no-repeat center center; } #property-list { width: 192px; border: 1px solid rgb(100, 100, 100); max-height: 480px; /* 15 items height */ overflow-y: scroll; background: #fff; display: none; } #property-list.show { display: block; } #property-list > .content { background: #fff; } #property-list > .content > .list-item { line-height: 32px; text-indent: 14px; background: rgb(255, 255, 255); transition: 0.2s; cursor: pointer; font-family: Arial, Helvetica, sans-serif; } #property-list > .content > .list-item:hover { background: rgb(197, 197, 197); } #dataSource-board, #cell-board { width: 100%; height: 114px; } #formatter-board, #binding-board { width: 100%; height: 26px; line-height: 26px; } #subItemList { width: 300px; position: absolute; left: 100px; top: 100px; background: white; padding: 6px 10px; box-shadow: 0px 2px 4px #aaa; display: none; } #subItemList.show { display: block; } #subItemList > .subItem { width: 100%; line-height: 26px; border-bottom: 1px solid #333; } #subItemList > .subItem > label { display: inline-block; width: 100px; text-indent: 10px; font-weight: bold; } #subItemList > .subItem > input { width: calc(100% - 110px); } .important { font-weight: bold; }