Pivot Field

The placement and order of a PivotTable's fields in each area of the PivotPanel affects the appearence of the PivotTable. Users can drag the field values up or down within the areas (like the Rows area) to adjust which data appears first in the PivotTable.

In the PivotTable field, there are 4 kinds of fields: Pivot Filter Fields By default, the Report Filters are shown in a single vertical list at the top of the PivotTable. PivotTable supports the options of displayFieldsInPageFilterArea and reportFilterFieldsPerColumn to define the filter layout: reportFilterFieldsPerColumn: The number of report filter fields per column. displayFieldsInPageFilterArea: Whether the field displays in page area shows first over then down or first down then over. Its value is an enumeration of GC.Spread.Pivot.DisplayFields. Pivot Row Fields The row fields define the data distribution in the PivotTable, it can be a tree like when there are multiple row fields. When more than one field is placed in the row area, they will be expanded and displayed nested, and the field ranked first is expanded first. For the different layout types, the row field appearence will show the different tree appearances. Pivot Column Fields Column fields are similar to the row fields. It can be a tree when there are multiple column fields. When more than one field is placed in the column area, they will be expanded and dispalyed nested, and the field ranked first is expanded first. Pivot Value Fields The value range is the statistical data area. Through the value area, the user can choose the statistics data and summary types, like the PivotTable subtotal type. PivotTable fields support the below APIs. 1.User can get or set sorting for a field in the PivotTable. 2.User can group or ungroup the items of the field. 3.User can get or set collapse info for a field of PivotTable by field name. 4.User can get or set label filter / value filter info of pivotTable by field name. 5.User can get or set SubTotalType of the PivotTable by field.
window.onload = function () { var spread = new GC.Spread.Sheets.Workbook(_getElementById('ss'), { sheetCount: 2 }); initSpread(spread); var pivotLayoutSheet = spread.getSheet(0); var pt = initPivotTable(pivotLayoutSheet); doAutoFitColumn(pivotLayoutSheet); }; function initSpread(spread) { spread.suspendPaint(); var sheet1 = spread.getSheet(1); sheet1.name("DataSource"); sheet1.setRowCount(700); sheet1.setColumnWidth(5, 150); sheet1.setArray(0, 0, pivotSales); sheet1.tables.add('tableSales', 0, 0, 642, 6); var sheet0 = spread.getSheet(0); sheet0.name("PivotLayout"); spread.resumePaint(); } function initPivotTable(sheet) { var myPivotTable = sheet.pivotTables.add("myPivotTable", "tableSales", 1, 1, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.dark2); myPivotTable.suspendLayout(); myPivotTable.options.showRowHeader = true; myPivotTable.options.showColumnHeader = true; myPivotTable.add("region", "Region", GC.Spread.Pivot.PivotTableFieldType.rowField); myPivotTable.add("country", "Country", GC.Spread.Pivot.PivotTableFieldType.rowField); var groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, { by: GC.Pivot.DateGroupType.years }] }; myPivotTable.group(groupInfo); myPivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.columnField); myPivotTable.add("date", "Qt", GC.Spread.Pivot.PivotTableFieldType.columnField); myPivotTable.add("amount", "Sum of amount", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum); var panel = new GC.Spread.Pivot.PivotPanel("myPivotPanel", myPivotTable, document.getElementById("panel")); myPivotTable.resumeLayout(); return myPivotTable; } function doAutoFitColumn (sheet) { sheet.suspendPaint(); var colCount = sheet.getColumnCount(); for (var i = 0; i < colCount; i++) { sheet.autoFitColumn(i); } sheet.setColumnWidth(0, 20); sheet.setRowHeight(0, 20); sheet.resumePaint(); } function _getElementById(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-pivot-addon/dist/gc.spread.pivot.pivottables.min.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/data/pivotSales.js" type="text/javascript"></script> <script src="$DEMOROOT$/spread/source/js/license.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="sample-panel"> <div id="panel"></div> </div> </div> </body> </html>
.sample-tutorial { position: relative; height: 100%; } .sample-spreadsheets { width: calc(100% - 300px); height: 100%; overflow: hidden; float: left; } body { position: absolute; top: 0; bottom: 0; left: 0; right: 0; overflow: hidden; } .sample-panel { float: right; width: 300px; padding: 12px; height: 100%; box-sizing: border-box; background: #fbfbfb; overflow: auto; } .gc-panel { padding: 10px; background-color: rgb(230, 230, 230); } #panel { position: absolute; right: 0; width: 300px; height: 100%; top: 0; } #app { height: 100%; }