PivotTable Show Items With No Data

SpreadJS PivotTables support setting the 'show items with no data' property for fields.

You can set the 'show items with no data' property through the field setting dialog or API. This property will show items in the PivotTable even if those item do not have data in them. By default, these items are omitted. For example, you can set 'show items with no data' in the following way: You can obtain 'show items with no data' property as shown below:
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("date", "Date", GC.Spread.Pivot.PivotTableFieldType.rowField); pivotTable.add("car", "Cars", GC.Spread.Pivot.PivotTableFieldType.rowField); let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.months}] }; pivotTable.group(groupInfo); pivotTable.add("quantity", "Quantity", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); pivotTable.resumeLayout(); sheet.resumePaint(); pivotTable.autoFitColumn(); return pivotTable; } function _isNullOrUndefined (obj) { return obj === null || obj === undefined; } function bindEvent(pivotTable) { _get('date').addEventListener('change', (e) => { pivotTable.showNoData("date", e.target.checked); }) _get('car').addEventListener('change', (e) => { pivotTable.showNoData("car", e.target.checked); }) } function _get (id) { return document.getElementById(id); }
<!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/purejs/node_modules/@grapecity/spread-sheets/styles/gc.spread.sheets.excel2013white.css"> <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-shapes/dist/gc.spread.sheets.shapes.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/en/purejs/node_modules/@grapecity/spread-sheets-pivot-addon/dist/gc.spread.pivot.pivottables.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">Set the <b>show items with no data</b> information for the following fields:</div> <div class="show-items-with-no-data"> <div class="show-items-with-no-data-field"> <input type="checkbox" value="field1" id="date"/> <label for="salesperson">Date</label> </div> <div class="show-items-with-no-data-field"> <input type="checkbox" value="field2" id="car"/> <label for="car">Cars</label> </div> </div> </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; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; } .whole-field { margin-bottom: 10px; } .show-items-with-no-data { width: 200px; height: 60px; margin-top: 10px; } .show-items-with-no-data-field{ width: 100%; height: 20px; margin-bottom: 10px; }