| Quick Start Guide | |
|---|---|
| Tutorial Concept |
Learn how to integrate a JavaScript spreadsheet component with Pivot Table functionality in a JavaScript web application using SpreadJS. |
| What You Will Need |
NPM Packages
|
| Controls Referenced |
SpreadJS JavaScript Pivot Table Add-On |
Pivot Tables are undeniably one of the most powerful data analysis tools; they help organize data so users can detect recurring patterns more easily. Pivot Tables help users create customized tables from large data groups. Using pivot functionalities, the user can summarize, sort, reorganize, group, count, total, or average data effortlessly within a table.
SpreadJS, a JavaScript spreadsheet component, has an optional Pivot Tables add-on feature that allows JavaScript developers to create Excel-like Pivot Tables programmatically in their applications or allow end-users to create them easily with a familiar UI.
This blog will go through some of the essential properties and features of the JavaScript spreadsheet Pivot Table API that will help you customize the appearance of your pivot tables and reports derived from these Pivot Tables.
Developers Guide to JavaScript PivotTables
- Create a JavaScript Pivot Table
- Create a Pivot Table Using the Pivot Panel
- Pivot Layout
- Pivot Table Collapsing and Grouping
- Pivot Table Theme
- Pivot Styles
- Pivot Table AutofitColumn
- Pivot Table Slicers
- Pivot ContextMenu
- Pivot Conditional Formatting
- JavaScript Pivot Table Benefits
Ready to Get Started? Download a free trial of SpreadJS today!
Create a JavaScript Pivot Table
Imagine working with a large dataset that contains detailed player performance statistics. In this example, the data source includes FIFA World Cup player records such as position, club, appearances, goals scored, assists provided, and other performance metrics.
The goal is to transform this raw data into a more meaningful summary report. Instead of manually reviewing individual player rows, we can use a Pivot Table to quickly group and aggregate the information in a more insightful format. The following function demonstrates how to programmatically create a Pivot Table and add fields in a JavaScript application using SpreadJS’ PivotTable class API.
function initPivotTable(sheet, tableName) {
sheet.name("Basic PivotTable");
let pivotTableOptions = {
bandRows: true,
bandColumns: true
};
let pivotTable = sheet.pivotTables.add(
"PivotTable",
tableName,
1,
1,
GC.Spread.Pivot.PivotTableLayoutType.tabular,
GC.Spread.Pivot.PivotTableThemes.medium14,
pivotTableOptions
);
pivotTable.suspendLayout();
pivotTable.add(
"Position",
"Position",
GC.Spread.Pivot.PivotTableFieldType.rowField
);
pivotTable.add(
"Club",
"Club",
GC.Spread.Pivot.PivotTableFieldType.rowField
);
pivotTable.add(
"Goals Scored",
"Goals Scored",
GC.Spread.Pivot.PivotTableFieldType.valueField,
GC.Pivot.SubtotalType.sum
);
pivotTable.add(
"Assists Provided",
"Assists Provided",
GC.Spread.Pivot.PivotTableFieldType.valueField,
GC.Pivot.SubtotalType.sum
);
pivotTable.add(
"Appearances",
"Appearances",
GC.Spread.Pivot.PivotTableFieldType.valueField,
GC.Pivot.SubtotalType.sum
);
pivotTable.add(
"Dribbles per 90 Min",
"Avg Dribbles per 90 Min",
GC.Spread.Pivot.PivotTableFieldType.valueField,
GC.Pivot.SubtotalType.avg
);
pivotTable.resumeLayout();
return pivotTable;
}After applying this code, the PivotTable will display in the JavaScript application as a summarized report based on the source data.
Create a Pivot Table Using the Pivot Panel
Another way of creating a Pivot Table is by using the Pivot Panel offered by SpreadJS. Unlike the programmatic approach shown earlier, the Pivot Panel provides an interactive user interface that allows users to dynamically configure the Pivot Table by dragging and dropping fields.
To initialize the Pivot Panel, we first create a container element in the HTML file. In this example, we add a div with the ID panel alongside the spreadsheet container.
<body>
<div class="sample-tutorial">
<div id="ss" class="sample-spreadsheets"></div>
<div class="sample-panel">
<div id="panel"></div>
</div>
</div>
</body>Next, we apply CSS to position the side panel next to the spreadsheet. Developers can customize the styling and appearance of the Pivot Panel as needed.
.sample-tutorial {
display: flex;
width: 100%;
height: 100vh;
}
.sample-spreadsheets {
flex: 1;
height: 100%;
}
.sample-panel {
width: 300px;
background: #fbfbfb;
border-left: 1px solid #ddd;
}
#panel {
width: 100%;
height: 100%;
}Then, use SpreadJS’ PivotPanel constructor to initialize the panel:
const myPivotTable = initPivotTable(pivotSheet, "Table1");
new GC.Spread.Pivot.PivotPanel(
"myPivotPanel",
myPivotTable,
document.getElementById("panel")
);The application will now display the SpreadJS PivotTable with the side panel, allowing end-users to drag and drop needed fields according to the report logic.
Take a look at our Pivot Panel demo to discover more about its features.
Pivot Layout
SpreadJS allows you to choose from three different Pivot Table layouts to provide different visual representations of your data. These include Compact, Outline, and Tabular forms.
By default, the Pivot Table uses the Compact layout. However, you can change the layout either during initialization or dynamically using the layoutType function.
The layoutType method accepts an integer value corresponding to the layout:
- 0 → Compact
- 1 → Outline
- 2 → Tabular
For example, the following code sets the Pivot Table to the Outline layout:
myPivotTable.layoutType(1);Compact Form (0)
The Compact layout displays all row fields in a single column using a hierarchical structure. This makes the Pivot Table more space-efficient and easier to read at a glance. However, because multiple fields are grouped into one column, copying and exporting the data for further analysis can be less convenient.
Outline Form (1)
The Outline layout separates each row field into its own column, making the data structure more explicit and easier to understand. This format is especially useful when you want a clearer view of grouped data or when preparing reports for presentation.
Tabular Form (2)
The Tabular layout displays each field in its own column with repeated labels, similar to a flat table structure. This layout is ideal for exporting data, performing additional analysis, or integrating with other tools, as it closely resembles a standard dataset format.
Pivot Table Collapsing and Grouping
SpreadJS provides built-in support for collapsing Pivot Table fields, allowing users to simplify complex datasets and focus on relevant information.
Collapse Pivot Table Fields
When working with large datasets, Pivot Tables can become difficult to read due to the amount of detailed data displayed. The collapse method allows you to hide specific groups within a field, making the data easier to navigate.
In this example, a Pivot Table is created using FIFA player statistics. Users can collapse all players who have scored 0 goals using a simple checkbox.
pivotTable.collapse("Goals Scored", "0", true);This method accepts three parameters:
- The field name ("Goals Scored")
- The item value to collapse ("0")
- A Boolean value (true to collapse, false to expand)
Interactive Collapse Control
To make the Pivot Table interactive, a checkbox is used to toggle the collapsed state dynamically.
document.getElementById("collapseGoals").addEventListener("change", function (e) {
spread.suspendPaint();
pivotTable.collapse("Goals Scored", "0", e.target.checked);
spread.resumePaint();
});When selected, all players with 0 goals are collapsed, hiding the detailed rows and making the Pivot Table more compact. When unchecked, the rows are expanded again.
Why Use Collapse?
Collapsing Pivot Table fields is useful when:
- You want to hide less relevant data (e.g., players with no goals)
- You need a cleaner, more readable layout
- You are presenting summarized data to users
This feature is especially helpful in dashboards and reporting scenarios where clarity and focus are important.
Summary
The collapse method in SpreadJS provides a simple yet powerful way to control how Pivot Table data is displayed. By combining it with UI controls such as checkboxes, users can create interactive experiences that make large datasets easier to explore and understand.
Pivot Table Theme
The SpreadJS PivotTable provides 85 predefined themes - 29 light, 28 medium, and 28 dark.
You can apply the pivot table theme when adding the pivot table, or you can use the theme function.
let pivotTable = sheet.pivotTables.add("PivotTable", table.name(), 0, 0, GC.Spread.Pivot.PivotTableLayoutType.outline, GC.Spread.Pivot.PivotTableThemes.medium2);
//pivotTable.theme(GC.Spread.Pivot.PivotTableThemes.dark2);
//pivotTable.theme("dark2");Choosing the right theme improves the appearance of your pivot table and offers a more professional presentation of data for the end receivers of your report. See the online JavaScript Pivot Table Theme demo here.
Pivot Styles
You can apply a Pivot Table styles to highlight any specific data. The style method can apply styles to row, column, value, and label fields. For example, the following code snippet applies a style to the value fields.
With this support, JavaScript developers can customize and apply their own rules, styles, and formatting to certain pivot table fields; check out SpreadJS’ online Pivot Style demo for more information.
// PIVOT STYLES
let valuePivotArea = {
dataOnly: true,
references: [
{
fieldName: "Values",
items: ["Count of Players"]
}
]
};
let valueStyle = new GC.Spread.Sheets.Style();
valueStyle.backColor = "#82bc00";
valueStyle.foreColor = "white";
pivotTable.setStyle(valuePivotArea, valueStyle);With this support, JavaScript developers can customize and apply their own rules, styles, and formatting to certain pivot table fields; check out SpreadJS’ online Pivot Style demo for more information.
Pivot Table AutofitColumn
SpreadJS’ pivot table offers an AutofitColumn function that adjusts the pivot table's column width to accommodate the longest value in a field. Overall, it is a helpful function and convenient when working with long, text-based fields.
// Auto fit Pivot Table columns
pivotTable.autoFitColumn();The GIF below demonstrates this function; when the page reloads, the autoFitColumn function is invoked:
Pivot Table Slicers
Pivot Table Slicers are an interactive visual control that allows users to filter and manipulate data within a pivot table. They provide a user-friendly way to narrow down the data displayed in a pivot table by selecting specific criteria, such as dates, categories, or other relevant dimensions. Users can quickly analyze and explore data subsets using slicers without modifying the underlying pivot table structure. SpreadJS supports both the Item Slicer and Timeline Slicer.
| Item Slicer |  |
| Timeline Slicer |  |
Check out SpreadJS’ JavaScript Slicer demo to try the slicers for yourself.
Pivot ContextMenu
SpreadJS offers a Pivot ContextMenu that empowers users to perform various actions on pivot tables effortlessly. This feature allows users to sort data, rearrange field headers, and personalize the control by expanding or collapsing field values. The Pivot ContextMenu provides a user-friendly and compact interface to modify the appearance of pivot table data.
When interacting with different parts of a pivot table and then right-clicking, SpreadJS presents distinct context menu options. Here are some of the JavaScript Pivot Table Context Menus:
Users can also format values with given formats or customized ones by choosing the “Value Field Settings..." option from the data area or the Grand Total area context menu to open the dialog window.
Pivot Conditional Formatting
Conditional Formatting is a powerful tool that allows you to quickly highlight data fields, making it easier to identify outliers or refine your results. In a pivot area, you have the flexibility to apply, retrieve, and remove conditional formatting rules. These rules remain in effect even if you make changes to the PivotTable layout.
You can apply Conditional Formatting to the SpreadJS’s Pivot Table using the addConditionalRule method.
Check out the online demo. This demo applies different color formatting depending on the cell values, with green being the lowest and red the highest.
JavaScript Pivot Table Benefits
Now that you have seen how SpreadJS can be used to add PivotTables to your application, we can go through some of the benefits of doing so:
Interactive Data Analysis
With a few lines of code or a couple of clicks in the Designer, a SpreadJS Pivot Table can reduce hours of work to only a few minutes. Decrease potential errors by manually creating calculated fields, groups, and filters–then let SpreadJS Pivot Tables do the rest. Focus more time on interpreting and analyzing data efficiently rather than manually creating your data breakdowns. You can use the pivot table panel to seamlessly drag-and-drop fields between the different areas and customize how the pivot table displays your data.
Reviewing Product Sales
Reporting sales numbers can be daunting, especially with thousands of records, products, offices, and employees, and that's where SpreadJS Pivot Tables help. A pivot table can summarize a large amount of raw data in more understandable terms. For example, SpreadJS Pivot Tables would allow you to see the sales of a particular salesperson during a specific period of time. Easily report actual sales numbers against a target, analyze employee commission levels, and view reseller transactions.
You can instantly aggregate sales numbers by different fields and filters, giving you a clear and concise view of the data you need. In this case, you can import sales data for multiple stores, filter by promotions, and separate them into different sales channels to get a breakdown of your sales data.
FinTech
Create income statements, balance sheets, and profit and loss statements easily with SpreadJS Pivot Tables. You can also emphasize profit subtotals, including gross and operating subtotals. Create custom groupings that might not be available in your source data to provide the exact data breakdown you need. Include calculated fields to summarize large amounts of data in concise, customizable tables.
Marketing
With SpreadJS Pivot Tables, you can track your company's growth with various KPIs. You can also create a table focusing on multiple marketing strategies and inputs, including paid advertising campaigns, social media engagement, click-through, and conversion rates.
Visualize the analysis of advertising campaigns by geography, date ranges, and channels, and provide a breakdown of the results in an informative pivot table. For example, you could check product categories and see which categories customers purchase the most. Filter those orders by the corresponding promotion to help report on the promotion's effectiveness.
Budgeting
SpreadJS Pivot Tables make creating budgets simple and straightforward. Create detailed pivot tables to compare budgets for the unique aspects of your business, such as tracking actual expenses compared to projected values. Summarize expenditures for different stores and product lines by months, categories, or any other fields with a simple interface–easily dragging and dropping fields to fit your requirements. Analyze and categorize the store spending and compare the year-over-year values.
Ready to Get Started? Download a free trial of SpreadJS today!
We hope you enjoyed learning how to create and customize JavaScript Spreadsheet Pivot Tables. This article only scratches the surface of the full capabilities of SpreadJS, the JavaScript spreadsheet component. Review the documentation to see some of the many available features, and check out our online demos to see the features in action and interact with the sample code.
Tags:
Related Blogs
Try Our JavaScript Spreadsheet Components
SpreadJS Resources
