表单数据类型

使用GC.Data.DataManager来帮助自定义单元格数据类型。数据类型可以在数据绑定的单元格或公式引用中体现出来。

正如数据库中的索引,在表格中建立索引也会改善查找的性能。 在表格中建立索引 通过GC.Data.DataManager创建了一个表格之后,您可以使用table.createIndexes(fields)来创建索引。 当在表格中插入,删除或者更新记录时,特定字段的索引会自动更新。 在特定字段中搜索记录 您可以在特定字段中使用table.search(value, field)来搜索记录。 无论您搜索的目标字段是否具有索引,如果搜索成功,您都会得到记录。但是索引将大大加快此过程。 索引操作 如果您想要知道哪个字段已经创建过索引,您可以使用table.getIndexes()。 毫无疑问,冗余的索引将会带来额外的性能开销。因此您可以使用table.dropIndexes(fields)来删除指定的索引。 或者使用table.clearIndexes()清除表格中所有存在的索引。
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 item; for (var key in data) { 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="zh"> <head> <meta name="spreadjs culture" content="zh-cn" /> <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$/zh/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$/zh/purejs/node_modules/@grapecity/spread-sheets/dist/gc.spread.sheets.all.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/spread-sheets-tablesheet/dist/gc.spread.sheets.tablesheet.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity/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> <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 previously indexed a certain column <span class="important">contactName</span> in <span class="important">Source Data</span> sheet. </p> <p> So you can copy a certain data of the index column, paste it into <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 the all fields of record in a popup list if search successfully. </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; }