A pivot table is a powerful tool that allows you to quickly extract and analyze large amounts of data–making seemingly endless data more manageable to analyze. Excel pivot tables allow users to summarize their data tables in a more meaningful way by allowing them to “pivot” (or rotate) data in tables to, for example, compare totals between different products, sort, summarize, group, and much more.
With the v14.1.0 release of SpreadJS, our JavaScript Excel Spreadsheet, we added support for pivot tables as an additional add-on feature. Users who purchase the pivot table add-on license can leverage the pivot table feature for deep customization and quick data analysis.
In this blog, we go over how to use SpreadJS’s Pivot Table add-on feature, its application, setting utilization, and how to use the different layout options to view and analyze data seamlessly.
How to Use SpreadJS Pivot Tables
- Add Data
- Add a Pivot Table
- Add a Pivot Table Side Panel
- Set Pivot Table Sections
- Pivot Table Filters and Sorting
- Pivot Table Collapse and Grouping
Add Data
Before diving into the pivot table's features, we will create a data source for our pivot table to use. You can create a pivot table by providing table data or by using existing table data. For this example, we will use an existing table and its data to create the new pivot table.
First, we will create a simple function that will set data from an array to the SpreadJS instances using the setArray method and then add a table to the set data range. Then we will enact this function when the sheet is initialized. Here is the array of sample data we will use in these steps:
// Table Data
let pivotSales = [
["OrderDate", "Region","Rep","Item","Units","UnitCost", "Total"],
["1/6/2019", "East", "Jones", "Pencil", 95, 1.99, 189.05],
["1/6/2019", "Central", "Kivell", "Desk", 2, 125.00, 250.00],
["1/6/2019", "West", "Sorvino", "Desk", 4 ,255.00, 1020.00],
["1/6/2019", "East", "Jardine", "Desk", 2 ,100.00, 200.00],
["1/23/2019","Central","Kivell","Binder",50,19.99, 999.50],
["2/9/2019","Central","Jardine","Pencil", 36,4.99,179.64],
["2/26/2019","Central","Gill","Pen", 27,19.99,539.73],
["3/15/2019","West","Sorvino","Pencil",56,2.99,167.44],
["4/1/2019","East","Jones","Binder",60,4.99,299.40],
];
Next, create a simple function, getDataSource, that sets the array to the sheet and adds a table from the range of data (1a). Enact the function, set the data, and add a table to be set to the second sheet in the workbook (1b).
function initSpread(spread) {
spread.suspendPaint();
let sheet = spread.getActiveSheet();
let sheet2 = spread.getSheet(1);
// 1b: Get the data sources table name
let tableName = getDataSource(sheet2, pivotSales);
spread.resumePaint();
}
// 1a: Set data array to sheet and add table and return the table name
function getDataSource(sheet, tableSource){
sheet.name("DataSource");
sheet.setRowCount(700);
sheet.setColumnWidth(5, 150);
let table = sheet.tables.add('table', 0, 0, 11, 6);
sheet.setArray(0, 0, tableSource);
return table.name();
}
The data is now displayed in a table on the second worksheet in the workbook titled DataSource:
Add a Pivot Table
Now that we have our data in a table within the SpreadJS instance, we are going to add a pivot table using the data table's name as the pivot table constructor’s owner worksheet (2a) and apply the pivot table options, theme, and layout to style the appearance of the table (2b).
*Note: To use the Pivot Table feature, users must include the PivotTable script file: gc.spread.pivot.pivottables.x.x.x.min.
This is located in the SpreadJS release files following this path: \SpreadJS\scripts\plugins\gc.spread.pivot.pivottables.x.x.x.min.
Options: SpreadJS Pivot Table style options include setting banded rows or columns and enabling row or column header visibility.
Theme: Apply a variety of Pivot Table themes, based on your preference, to change the style and theme of the table.
Layout: There are three PivotTableLayoutType’s that can be applied to a pivot table. In this example, we are using the outline layout.
function initPivotTable(sheet, tableName){
sheet.name("Basic PivotTable");
sheet.setRowCount(100);
sheet.setColumnWidth(0, 20);
// 2b:Set the pivot tables options, theme and layout
let options = { bandRows:true, bandColumns:true };
let theme = GC.Spread.Pivot.PivotTableThemes.medium9;
let layout = GC.Spread.Pivot.PivotTableLayoutType.outline;
// 2a: Add a pivot table
let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, layout, theme, options);
}
There is now a blank pivot table in our SpreadJS instance because we have not made any of the data available to the sections yet.
There are two ways to make data available to the pivot table–using the Pivot Table side panel to select and drag data to specified sections or using our JavaScript spreadsheet API to set the Pivot Table sections in your code.
Add a Pivot Table Side Panel
SpreadJS supports a Pivot Table Fields side panel, allowing users to drag-and-drop and apply filtering to either a set or blank Pivot Table. To apply the side panel to our current sample, we need to add a DIV element to hold the panel in our HTML file like so:
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="sample-panel">
<div id="panel"></div>
</div>
</div>
In our initPivotTable, apply a PivotPanel to the DIV with id panel:
// 3: PivotTable side panel
var panel = new GC.Spread.Pivot.PivotPanel("PivotTable", pivotTable, document.getElementById("panel"));
The SpreadJS instance now shows a PivotPanel to manipulate the data view. This is one way to set the data view within the SpreadJS instance, but you also can apply this via code by setting the Pivot Table sections.
Setting Pivot Table Sections:
Using our blank pivot table, we will make our data available by setting the pivot table sections using SpreadJS’ API: Row area, Column area, Value area, Filter area.
The pivot table will display depending on how you set these sections. Note that when you set a row or column field, it will become the row or column header of the table. For this example, we create a function called _initPivotTable _to set the SpreadJS Pivot Table sections, making the selected data now available in a pivot table (4).
function initPivotTable(sheet, tableName){
sheet.name("Basic PivotTable");
sheet.setRowCount(100);
sheet.setColumnWidth(0, 20);
// 2b:Set the pivot tables options, theme and layout
let options = { bandRows:true, bandColumns:true };
let theme = GC.Spread.Pivot.PivotTableThemes.medium9;
let layout = GC.Spread.Pivot.PivotTableLayoutType.outline;
// 2a: Add a pivot table
let pivotTable = sheet.pivotTables.add("PivotTable", tableName, 1, 1, layout, theme, options);
// 4: Set the PivotTable Sections:
// rowField
pivotTable.add("Rep", "Rep", GC.Spread.Pivot.PivotTableFieldType.rowField);
pivotTable.add("OrderDate", "Order Date", GC.Spread.Pivot.PivotTableFieldType.rowField);
// columnField
pivotTable.add("Item", "Item", GC.Spread.Pivot.PivotTableFieldType.columnField);
// valueField
pivotTable.add("Total", "Sum of Sales", GC.Spread.Pivot.PivotTableFieldType.valueField, GC.Pivot.SubtotalType.sum);
// filterArea
pivotTable.add("Region", "Region", GC.Spread.Pivot.PivotTableFieldType.filterField);
}
With these sections set, the table will now be displayed in your browser and can be interacted with to PivotTable to drill down into the data:
Apply Data Filters and Sorting
In this section, we set the Filter area for the entire pivot table, but there are even more ways you can drill-down and apply filtering. SpreadJS allows users to apply and perform multiple filtering operations using Label Filters, Value Filters, and Manual filters. This section will show how to add a PivotTable Fields panel to interact with the data and also demonstrate how to use these different filters.
Please note in order to if use multiple filters when using SpreadJS’ Pivot Tables, you must select “Allow multiple filters” in the Designer:
You can also use the code settings below:
pivottable.options.allowMultipleFiltersPerField = true;
Manual Filters:
Manual filters allow users to filter through the different row or column fields. With SpreadJS, you might want to hide one or more items in a Row field or Column field. To do so, simply click the drop-down arrow for the Row or Column Labels, then remove the checkmark by the items you want to remove.
Label Filters:
Label filters are another way that users can specify the data they want to view. To hide even more items in the Row/Column fields, apply a Label Filter. Using the "less than" Label Filter operator, the example shows a user who is filtering the month column to only show the months from January to June:
Value Filters:
Value filters allow you to apply filtering to the values in the row fields to drill-down specific data. The example below shows how to apply a value filter to show the top 3 items by cost:
Sorting:
The SpreadJS Pivot Table supports sorting by labels and values, even when a filter is already applied to a field. When sorting by labels, users can use SpreadJS' JavaScript API to specify sorting or use the filter sort dialog boxes. Using SpreadJS' API, you can set the SortType to be asc, desc, or custom. Users can also apply sorting to a field in the row area by selecting the sort/filter dialog and then choosing either ascending or descending order using the A-Z or Z-A options, similar to Excel.
When sorting values, SpreadJS gives you the options to sort from smallest to largest or largest to smallest:
Pivot Table Collapsing and Grouping
SpreadJS supports pivot table grouping and collapsing to view data more easily. Users can set the collapse info for a specified field of the pivot table by the field name using the collapse method.
Users can also group dates, numbers, and text fields. See below to see how to collapse a field and break down the different grouping and ungrouping techniques supported with SpreadJS by using the group and ungroup method.
Collapse:
To collapse a field, use SpreadJS' collapse method and indicate the target field's name and the collapsed item's name. The example shows a variety of listening regions collapsed.
spread.suspendPaint();
let isCollapse = false;
if(e.target.checked){
isCollapse = true;
}
pivotTable.collapse("region","Africa",isCollapse);
pivotTable.collapse("region","Asia",isCollapse);
pivotTable.collapse("region","Australia",isCollapse);
pivotTable.collapse("region","Europe",isCollapse);
pivotTable.collapse("region","North America",isCollapse);
pivotTable.collapse("region","South America",isCollapse);
spread.resumePaint();
});
Notice that when selected, all of the specified fields are collapsed–making the data easier to view:
Grouping Dates:
When grouping dates with SpreadJS, users can indicate the DateGroupType by hours, days, quarter, years, and more. For example, if a user wants to group their dates by quarters and years, they could use dateGroups and initialize the new groupInfo like so:
let groupInfo = { originFieldName: "date", dateGroups: [{ by: GC.Pivot.DateGroupType.quarters }, { by: GC.Pivot.DateGroupType.years }] };
pivotTable.group(groupInfo);
pivotTable.add("Years", "Years", GC.Spread.Pivot.PivotTableFieldType.columnField);
When ungrouping date groups, all of the dates with the same origin field will be removed, and the origin field revert to the normal date. Grouping Text:
A user may want to group specific text items to differentiate among the rest of the data. To do this, the user can create a group of text values. When adding a text group, a new field will be created. The field's name is the fieldName given in groupInfo.textGroup. Use the new field as a group field. This example shows a new group being added as a column field:
let groupInfo = { originFieldName: "country",textGroup: {
fieldName:"country1",
groupItems:{
group1:["Spain","China","Australia","Egypt"],
group2:["Germany","Republic of Korea","South Africa"]
}
} };
pivotTable.group(groupInfo);
pivotTable.add("country1", "country1", GC.Spread.Pivot.PivotTableFieldType.columnField);
When ungrouping text groups, the field will be removed when ungrouped.
Grouping Numbers:
Grouping numbers with SpreadJS will add a number group. The origin field specified in the groupInfo will be changed to the group field to use as such. Grouping numbers appears as follows:
let groupInfo = { originFieldName: "id",numberGroup: { by: 100 } };
pivotTable.group(groupInfo);
When ungrouping number groups, the field will revert to the normal number field.
Pivot Tables are a handy and interactive add-on feature of SpreadJS that is included with our v14.1.0 release. Pivot Tables can enhance your end-user's ability to calculate, summarize, and visualize their raw data to focus on comparisons, patterns, and trends.
The Pivot Table add-on component can be used locally and will display a small watermark. Please note that, to deploy an app using PivotTables feature, you will need a SpreadJS distribution key that is PivotTable enabled and set in the applications.
Additional information about the pivot table licensing can be found on the Pricing page or by contacting sales via email at us.sales@grapecity.com.
If you have any questions, feel free to reach out to our team by submitting a case under the My Support section.
Mackenzie Albitz