编辑数据透视表

SpreadJS PivotTables支持在数据区编辑值。覆盖的值可以更新小计的计算。

数据透视表支持在数据区编辑数值。 数据透视表节点信息 要编辑透视表的值,第一步是要描述单元格。 我们定义了一个名为PivotNodeInfo的接口,可以描述透视表节点的字段和值信息。 覆盖值 覆盖透视表中的数值将导致这些数值在计算中被更新。 这只支持[ sum, count, countNums, max, min ] 类型的小计包含在计算中。其他类型的小计将只改变该小计值 如果被覆盖的值是一个小计,它的子节点的值不会改变,它的父节点将直接使用它被覆盖的值来计算,而不是计算它的子节点的结果。 更新源将删除将被设置的值 API 列表
window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), {sheetCount: 2}); let detailsSpread = new GC.Spread.Sheets.Workbook(document.getElementById("pivot-details"), {sheetCount: 1}); initSpread(spread, detailsSpread); }; function initSpread(spread, detailsSpread) { spread.suspendPaint(); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let table = getDataSource(sheet1, pivotSales); let pivotTable = initPivotTable(sheet2, table.name()); spread.setActiveSheet("PivotTable"); let detailsSheet = detailsSpread.getSheet(0); detailsSheet.name("PivotTable Details"); addEvent(sheet2, detailsSheet, pivotTable, table); spread.resumePaint(); window.sheet = sheet1; window.table = table; window.pt = pivotTable; } function addEvent(sheet2, detailsSheet, pivotTable, table) { detailsSheet.setRowCount(700); detailsSheet.setColumnCount(10); detailsSheet.addSpan(0, 0, 2, 5); let style = new GC.Spread.Sheets.Style(); style.wordWrap = true; detailsSheet.setStyle(0, 0, style); detailsSheet.setValue(0, 0, "In PivotTable of SpreadJS, pivot table show detail will find all merged data in data source"); sheet2.addSpan(0, 0, 2, 5); sheet2.setStyle(0, 0, style); sheet2.setValue(0, 0, "You Can Edit Cell Values in Data Area.\nuncheck the 'Enable Edit Value in Data Area' to close edit ablilty"); sheet2.bind(GC.Spread.Sheets.Events.CellClick, (sender, args) => { _getElementById("nodeInfo").innerHTML = ""; updateDetailSheet(detailsSheet, args.row, args.col, pivotTable); }); _getElementById("setNodeValue").addEventListener("click", () => { let value = parseInt(_getElementById("nodeValue").value, 10); let nodeInfo = _getElementById("nodeInfo").innerHTML; if (value !== undefined && value !== null && !isNaN(value) && nodeInfo) { nodeInfo = JSON.parse(nodeInfo); if (nodeInfo) { pivotTable.setNodeValue(nodeInfo, value); } } }); _getElementById("enableDataValueEditing").addEventListener("change", (e) => { pivotTable.options.enableDataValueEditing = !!e.target.checked; }); _getElementById("syncValue").addEventListener("click", (e) => { syncValue(pivotTable, table, sheet2.parent.getSheet(0)) _getElementById("nodeValue").value = ""; updateDetailSheet(detailsSheet, sheet2.getActiveRowIndex(), sheet2.getActiveColumnIndex(), pivotTable); }); } function updateDetailSheet (detailsSheet, row, col, pivotTable) { detailsSheet.suspendPaint(); let dataSource = getPivotDetails(row, col, pivotTable); detailsSheet.tables.remove("details"); if (dataSource) { var table = detailsSheet.tables.add('details', 2, 0, dataSource.length, dataSource[0].length); table.style(GC.Spread.Sheets.Tables.TableThemes["none"]); } detailsSheet.getCell(-1, 0).formatter("YYYY-mm-DD"); detailsSheet.getRange(-1, 4, 0, 2).formatter("$ #,##0"); detailsSheet.setColumnWidth(0, 120); detailsSheet.setArray(2, 0, dataSource); detailsSheet.resumePaint(); } function syncValue (pivotTable, table, sheet) { let list = pivotTable.getOverwriteList(); let tableRange = table.dataRange(); sheet.clearFormula(tableRange.row, 5, tableRange.rowCount, 1, function () {return true}); if (list && list.length > 0) { let columnNames = []; for (let i = tableRange.col; i < tableRange.col + tableRange.colCount; i ++) { columnNames[i] = table.getColumnName(i); } let filter = table.rowFilter(); let compareType = GC.Spread.Sheets.ConditionalFormatting.TextCompareType.equalsTo; list.forEach((info) => { let fieldInfos = info.fieldInfos; let details = pivotTable.getPivotDetails(fieldInfos); fieldInfos.forEach((fieldInfo) => { let expected = fieldInfo.fieldItem; let ptField = pivotTable.getField(fieldInfo.fieldName); let fieldType = ptField.dataType; let sourceName = ptField.sourceName; let columnIndex = columnNames.indexOf(sourceName); if (fieldType === GC.Pivot.PivotDataType.date) { for (let i = 1; i < details.length; i ++) { let dateValue = GC.Spread.Sheets.CellTypes.Base.prototype.format(new Date(details[i][columnIndex]), "YYYY-mm-DD"); let condition = new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {compareType: compareType, expected: dateValue}); filter.addFilterItem(columnIndex, condition); } } else { filter.addFilterItem(columnIndex, new GC.Spread.Sheets.ConditionalFormatting.Condition(GC.Spread.Sheets.ConditionalFormatting.ConditionType.textCondition, {compareType: compareType, expected: expected})); } }); filter.filter(); let valueColIndex = columnNames.indexOf((info.valueInfo.sourceName)); let leftRowIndexes = []; for (let i = tableRange.row; i < tableRange.row + tableRange.rowCount; i ++) { if (!filter.isRowFilteredOut(i)) { leftRowIndexes.push(i); } } let avgValue = info.value / leftRowIndexes.length; for (let i = 0; i < leftRowIndexes.length; i ++) { sheet.setValue(leftRowIndexes[i], valueColIndex, avgValue); } filter.reset(); }) } pivotTable.updateSource(); } function getPivotDetails(row, col, pivotTable) { let pivotInfo = pivotTable.getPivotInfo(row, col), detailsObj = []; if (! pivotInfo || pivotInfo.area !== 4) { return void 0; } let colInfo = pivotInfo.colInfos; let rowInfo = pivotInfo.rowInfos; setDetails(colInfo, detailsObj); setDetails(rowInfo, detailsObj); let dataSource = pivotTable.getPivotDetails(detailsObj); updateNodeInfo(pivotTable, row, col); return dataSource; } function updateNodeInfo (pivotTable, row, col) { let nodeInfo = pivotTable.getNodeInfo(row, col); if (!nodeInfo) { return; } _getElementById("nodeInfo").innerHTML = JSON.stringify(nodeInfo, null, 4); _getElementById("nodeValue").value = pivotTable.getNodeValue(nodeInfo) || ""; _getElementById("enableDataValueEditing").checked = pivotTable.options.enableDataValueEditing; } function setDetails(rowOrColInfo, detailsObj) { if (rowOrColInfo && rowOrColInfo.length > 0) { for (let item of rowOrColInfo) { if (item.isGrandTotal) { break; } detailsObj.push({fieldName: item.fieldName, fieldItem: item.itemName}); } } } function getDataSource(sheet, tableSource) { sheet.name("DataSource"); sheet.setRowCount(117); sheet.setColumnWidth(0, 120); sheet.getCell(-1, 0).formatter("YYYY-mm-DD"); sheet.getRange(-1, 4, 0, 2).formatter("$ #,##0"); let table = sheet.tables.add('table', 0, 0, 117, 6); for (let i = 2; i <= 117; i++) { sheet.setFormula(i - 1, 5, '=D' + i + '*E' + i) } table.style(GC.Spread.Sheets.Tables.TableThemes["none"]); sheet.setArray(0, 0, tableSource); return table; } function initPivotTable(sheet, tableName) { sheet.name("PivotTable"); sheet.setRowCount(1000); let option = { showRowHeader: true, showColumnHeader: true, bandRows: true, bandColumns: true, enableDataValueEditing: true }; let pivotTable = sheet.pivotTables.add("pivotTable", tableName, 2, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium8, option); pivotTable.suspendLayout(); pivotTable.add("salesperson", "Salesperson", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); let groupInfo = { originFieldName: "date", dateGroups: [ { by: GC.Pivot.DateGroupType.quarters } ] }; pivotTable.group(groupInfo); pivotTable.add("季度 (date)", "Date", GC.Spread.Pivot.PivotTableFieldType.columnField); pivotTable.add("total", "Totals", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); let style = new GC.Spread.Sheets.Style(); style.formatter = "$ #,##0"; pivotTable.setStyle({ dataOnly: true }, style); pivotTable.resumeLayout(); pivotTable.autoFitColumn(); return pivotTable; } function _getElementById (id) { return document.getElementById(id); }
<!doctype html> <html style="height:100%;font-size:14px;"> <head> <meta charset="utf-8" /> <meta name="spreadjs culture" content="zh-cn" /> <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-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/zh/purejs/node_modules/@grapecity-software/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.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="$DEMOROOT$/spread/source/data/pivot-data.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 class="sjss"> <div id="ss" class="sample-spreadsheets"></div> <div id="pivot-details" class="sample-spreadsheets-details"></div> </div> <div class="sample-panel"> <div id="config"> <h2>Overwrite Panel</h2> <div class="option node"> <label class="control-label"> <input id="enableDataValueEditing" type="checkbox" style="display: none;" checked> <div class="check"></div> <div class="circle"></div> </label> <label for="enableDataValueEditing" class="label-text">Enable Edit Value in Data Area</label> </div> <div class="node"> <div class="textarea-block"> <span>Active Cell Node Info:</span> <textarea name="nodeInfo" id="nodeInfo" cols="20" rows="10"></textarea> </div> <div class="label-block"> <span>Node Value:</span> <input type="number" name="nodeValue" id="nodeValue" placeholder="value"> </div> <button id="setNodeValue">SET</button> </div> <div class="sync-value"> <h3>Notice:</h3> <div> "Sync Value" will sync changing data back to the orginal data table by average. </div> <div> The PivotTable will update source and the overwrite info will be cleared. </div> <button id="syncValue">Sync Value</button> </div> </div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; } .sjss { width: 70%; height: 100%; display: inline-block; } .sample-spreadsheets { width: 100%; height: 58%; overflow: hidden; } .sample-spreadsheets-details{ width: 100%; height: 40%; overflow: hidden; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; overflow: hidden; } #config { width: 75%; margin-left: 10%; } .sample-panel { position: relative; float: right; width: 30%; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow-y: auto; overflow-x: hidden; } #app { height: 100%; } label{ position: relative; } .check{ width: 2rem; height: 1rem; border-radius: 100rem; border: 1px solid #a19b9b; transition: .3s; } .circle{ width: 1rem; height: 1rem; border-radius: 50%; background: #a19b9b; position: absolute; left: 1px; top: 1px; transform: translateX(0rem); transition: .3s; } input:checked ~.check{ background: #0099CC; transition: .3s; border-color: #0099CC; } input:checked ~ .circle{ transform: translateX(1rem); transition: .3s; background: #EEEEEE; } .control-label { display: inline-block; top: 3px; } #config h2 { width: 100%; margin-top: 0; text-align: center; } .label-text { user-select: none; line-height: 20px; white-space: nowrap; } .option { font-size: 15px; vertical-align: middle; white-space: nowrap; } .node { width: 100%; position: relative; } .textarea-block textarea { outline: none; resize: none; border: none; width: 100%; border-top: 1px solid #0099CC; font-size: 12px; font-family: 'Calibri', 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif; background-color: transparent; } #config button { line-height: 18px; padding: 8px 4px; font-size: 13px; border: 1px solid #0099CC; cursor: pointer; background: transparent; color: #0099CC; text-align: center; -webkit-transition-duration: 0.3s; /* Safari */ transition-duration: 0.3s; text-decoration: none; text-transform: uppercase; } #config button:hover { background-color: #008CBA; color: white; } #setNodeValue { position: absolute; width: 30%; right: 0; } .label-block { position: absolute; left: 0; border: 1px solid #0099CC; padding: 8px 4px; color: #0099CC; width: 50%; white-space:nowrap; font-size: 13px; overflow: hidden; line-height: 18px; } .label-block input { display: inline-block; border: none; outline: none; width: 48%; } .label-block span { display: inline-block; width: 48%; } .textarea-block { display: block; margin-bottom: 10px; margin-top: 10px; padding: 8px 8px; color: #0e84ab; border: 1px solid #0099CC; } input[type=number]::-webkit-inner-spin-button, input[type=number]::-webkit-outer-spin-button { -webkit-appearance: none; margin: 0; } input[type=number] { -moz-appearance:textfield; } #syncValue { display: inline-block; margin-left: 25%; margin-top: 10px; width: 50%; } .sync-value { margin-top: 60px; }