Formatting Pivot Table
Contents
[
Hide
]
Pivot Table Appearance
How to Create a Pivot Table explains how to create a simple pivot table. This article describes how to customize a pivot table’s appearance by setting various properties:
- Pivot table format options
- Pivot fields format options
- Data field format options
How to Set Pivot Table Format Options
The PivotTable class controls the overall pivot table and can be formatted in a number of ways.
How to Set the AutoFormat Type
Microsoft Excel offers a number of different pre-set report formats. Aspose.Cells for JavaScript via C++ support these formatting options too. To access them:
- Set PivotTable.isAutoFormat(value) to true.
- Assign a formatting option from the PivotTableAutoFormatType enumeration.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>PivotTable AutoFormat Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object by opening the uploaded Excel file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
const pivotindex = 0;
// Accessing the first worksheet
const worksheet = workbook.worksheets.get(0);
// Accessing the PivotTable
const pivotTable = worksheet.pivotTables.get(pivotindex);
// Setting the PivotTable report is automatically formatted
pivotTable.isAutoFormat = true;
// Setting the PivotTable autoformat type.
pivotTable.autoFormatType = AsposeCells.PivotTableAutoFormatType.Report5;
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
resultDiv.innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to Set Format Options
The code sample below shows how to format the pivot table to show grand totals for rows and columns, and how to set the report’s field order. It also shows how to set a customer string for null values.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells PivotTable Update Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object by opening the Excel file through the file stream
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Accessing the first worksheet in the Excel file
const worksheet = workbook.worksheets.get(0);
const pivotindex = 0;
// Accessing the PivotTable
const pivotTable = worksheet.pivotTables.get(pivotindex);
// Setting the PivotTable report shows grand totals for rows.
pivotTable.rowGrand = true;
// Setting the PivotTable report shows grand totals for columns.
pivotTable.columnGrand = true;
// Setting the PivotTable report displays a custom string in cells that contain null values.
pivotTable.displayNullString = true;
pivotTable.nullString = "null";
// Setting the PivotTable report's layout
pivotTable.pageFieldOrder = AsposeCells.PrintOrderType.DownThenOver;
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Pivot table updated successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
Formatting Look and Feel Manually
To formatting how the pivot table report looks manually, instead of using pre-set report formats, use the PivotTable.formatAll(style) and PivotTable.format(row, column, style) methods. Create a style object for your desired formatting, for example:
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Pivot Table Style Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate workbook from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Access the first worksheet
const worksheet = workbook.worksheets.get(0);
// Access the first pivot table
const pivot = worksheet.pivotTables.get(0);
// Set pivot table style (converted setter -> property)
pivot.pivotTableStyleType = AsposeCells.PivotTableStyleType.PivotTableStyleDark1;
// Create and configure a style
const style = workbook.createStyle();
style.font.name = "Arial Black";
style.pattern = AsposeCells.BackgroundType.Solid;
style.foregroundColor = AsposeCells.Color.Yellow;
// Apply style to pivot table
pivot.formatAll(style);
// Save the modified workbook (Excel97-2003 format for .xls)
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Pivot table styled successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to Set Pivot Field Format Options
The PivotField class represents a field in a pivot table and can be formatted in a number of ways. The code sample below shows how to:
- Access row fields.
- Setting subtotals.
- Setting autosort.
- Setting autoshow.
How to Set Row/Column/Page Fields Format
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells PivotTable Example</title>
</head>
<body>
<h1>PivotTable Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object using the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get the first worksheet
const worksheet = workbook.worksheets.get(0);
const pivotindex = 0;
// Accessing the PivotTable
const pivotTable = worksheet.pivotTables.get(pivotindex);
// Setting the PivotTable report shows grand totals for rows.
pivotTable.rowGrand = true;
// Accessing the row fields.
const pivotFields = pivotTable.rowFields;
// Accessing the first row field in the row fields.
const pivotField = pivotFields.get(0);
// Setting Subtotals.
pivotField.subtotals = AsposeCells.PivotFieldSubtotalType.Sum, true;
pivotField.subtotals = AsposeCells.PivotFieldSubtotalType.Count, true;
// Setting autosort options.
// Setting the field auto sort.
pivotField.isAutoSort = true;
// Setting the field auto sort ascend.
pivotField.isAscendSort = true;
// Setting the field auto sort using the field itself.
pivotField.autoSortField = -5;
// Setting autoShow options.
// Setting the field auto show.
pivotField.isAutoShow = true;
// Setting the field auto show ascend.
pivotField.isAscendShow = false;
// Setting the auto show using field(data field).
pivotField.autoShowField = 0;
// Saving the Excel file (Excel97-2003 format for .xls)
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">PivotTable modified successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to Set Data fields format
The code sample below shows how to set display formats and number format for data fields.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Pivot Field Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, PivotFieldDataDisplayFormat, PivotItemPositionType, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
if (!fileInput.files.length) {
document.getElementById('result').innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiating a Workbook object from the uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get the first worksheet
const worksheet = workbook.worksheets.get(0);
const pivotindex = 0;
// Accessing the PivotTable
const pivotTable = worksheet.pivotTables.get(pivotindex);
// Accessing the data fields.
const pivotFields = pivotTable.dataFields;
// Accessing the first data field in the data fields.
const pivotField = pivotFields.get(0);
// Setting data display format
pivotField.showValuesAs(PivotFieldDataDisplayFormat.PercentageOf, 1, PivotItemPositionType.Next, 0);
// Setting number format (converted from setNumber to property assignment)
pivotField.number = 10;
// Saving the modified Excel file
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
document.getElementById('result').innerHTML = '<p style="color: green;">Operation completed successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>
How to Clear Pivot Fields
The PivotFieldCollection has a method named clear() that allows you to clear pivot fields. Use it when you want to clear all the pivot fields in the areas, for example, page, column, row or data. The code sample below shows how to clear all the pivot fields in a data area.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Pivot Table Example</title>
</head>
<body>
<h1>Aspose.Cells Pivot Table Example</h1>
<input type="file" id="fileInput" accept=".xls,.xlsx,.csv" />
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
</body>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat, Worksheet, Cell, Utils } = AsposeCells;
AsposeCells.onReady({
license: "/lic/aspose.cells.enc",
fontPath: "/fonts/",
fontList: [
"arial.ttf",
"NotoSansSC-Regular.ttf"
]
}).then(() => {
console.log("Aspose.Cells initialized");
});
document.getElementById('runExample').addEventListener('click', async () => {
const fileInput = document.getElementById('fileInput');
const resultDiv = document.getElementById('result');
if (!fileInput.files.length) {
resultDiv.innerHTML = '<p style="color: red;">Please select an Excel file.</p>';
return;
}
const file = fileInput.files[0];
const arrayBuffer = await file.arrayBuffer();
// Instantiate workbook from uploaded file
const workbook = new Workbook(new Uint8Array(arrayBuffer));
// Get the first worksheet
const sheet = workbook.worksheets.get(0);
// Get the pivot tables in the sheet
const pivotTables = sheet.pivotTables;
// Get the first PivotTable
const pivotTable = pivotTables.get(0);
// Clear all the data fields
pivotTable.dataFields.clear();
// Add new data field
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Betrag Netto FW");
// Set the refresh data flag on
pivotTable.refreshDataFlag = true;
// Refresh and calculate the pivot table data
pivotTable.refreshData();
pivotTable.calculateData();
// Turn off refresh flag
pivotTable.refreshDataFlag = false;
// Save the modified Excel file (Excel 97-2003 .xls)
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = 'output.xls';
downloadLink.style.display = 'block';
downloadLink.textContent = 'Download Modified Excel File';
resultDiv.innerHTML = '<p style="color: green;">Pivot table updated successfully! Click the download link to get the modified file.</p>';
});
</script>
</html>