数据透视表字段标签格式

SpreadJS 数据透视表支持透视字段标签的自定义格式。

你可以使用数据透视表字段标签的样式弹出框来格式化字段,它可以帮助你在没有API的情况下设置透视表样式。 例如,在PivotTable.setStyle()中,你应该首先构建你想设置的字段的透视区域。 并在字段设置对话框中使用格式对话框,你只需要设置一个像 "0.00 "这样的格式字符串就可以达到上述目的。
window.onload = function () { let spread = new GC.Spread.Sheets.Workbook(document.getElementById("ss"), { sheetCount: 2 }); initSpread(spread); }; function initSpread(spread) { spread.suspendPaint(); let sheet1 = spread.getSheet(0); let sheet2 = spread.getSheet(1); let tableName = getSource(sheet2, pivotSales); let pivotTable = addPivotTable(sheet1, tableName); bindEvent(pivotTable,spread); spread.resumePaint(); } function getSource(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.name(); } function addPivotTable(sheet, source) { sheet.suspendPaint(); sheet.name("PivotTable"); sheet.setRowCount(10000); let pivotTable = sheet.pivotTables.add("PivotTable", source, 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.light8); 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("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); let carsStyle = new GC.Spread.Sheets.Style(); carsStyle.formatter = '[red]@'; let valueStyle = new GC.Spread.Sheets.Style(); valueStyle.formatter = '#0.00'; pivotTable.setStyle(initArea('Cars', pivotTable), carsStyle); pivotTable.setStyle(initArea('Quantity', pivotTable), valueStyle); pivotTable.resumeLayout(); sheet.resumePaint(); pivotTable.autoFitColumn(); return pivotTable; } function _isNullOrUndefined (obj) { return obj === null || obj === undefined; } function bindEvent(pivotTable,spread) { _get('fieldName').addEventListener('change', (e) => { let fieldName = e.target.value; let area = initArea(fieldName, pivotTable); let style = pivotTable.getStyle(area); _get('formatter').value = (style && style.formatter) ? style.formatter : ""; }) _get('set').addEventListener('click', (e) => { let fieldName = _get('fieldName').value; let formatter = _get('formatter').value; let area = initArea(fieldName, pivotTable); let style = pivotTable.getStyle(area) || new GC.Spread.Sheets.Style(); style.formatter = formatter; pivotTable.setStyle(area, style); }) _get('reset').addEventListener('click', (e) => { let fieldName = _get('fieldName').value; let area = initArea(fieldName, pivotTable); let formatter = pivotTable.getStyle(area)?.formatter || ""; _get('formatter').value = formatter; }) } function initArea (fieldName, pt) { let fieldArea = pt.getField(fieldName).pivotArea; if (fieldArea === 1 || fieldArea === 2) { return { labelOnly: true, references: [ { fieldName } ] } } else if (fieldArea === 3) { return { dataOnly: true, references: [ { fieldName: "值", items: [fieldName] } ] } } } function _get (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-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 id="ss" class="sample-spreadsheets"></div> <div class="options-container"> <div class="whole-field">The following operations all work on <b>Salesperson</b> Field:</div> <div>Field Name:</div> <select name="fieldName" id="fieldName" class="field-name"> <option value="Salesperson" selected="">Salesperson</option> <option value="Cars">Cars</option> <option value="季度 (date)">Date</option> <option value="Quantity">Quantity</option> </select> <div>Formatter:</div> <input type="text" class="label-filter-input filter-input" id="formatter" /> <input type="button" class="format-button" value="Set" id="set"> <input type="button" class="format-button" value="Reset" id="reset"> </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; } .filter-input { width: 200px; height: 20px; display: block; /* margin-left: 15px; */ margin-top: 10px; } .format-button { width: 45%; margin-top: 20px; /* float: right; */ display: inline-block; } .field-name { width: 200px; height: 25px; display: block; margin-bottom: 10px; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .whole-field { margin-bottom: 10px; }