Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.
.xls files) and modern named or custom pivot table styles (intended for .xlsx, .xlsm, and .xlsb files). The API you should call depends on the file format the workbook is saved to, not the format it was loaded from.
Aspose.Cells exposes two parallel style APIs for pivot tables. The decision between them is driven by the file format you save the workbook to, not by the format you read it from. A workbook loaded from an .xls file can be re-saved as an .xlsx, and in that case the modern style API applies rather than the legacy one.
pivotTable.pivotTableStyleType selects one of the built-in named styles (light and dark themes, including the styles added in Excel 2017). These presets are read-only.pivotTable.pivotTableStyleName selects a custom style you define yourself through workbook.worksheets.tableStyles.addPivotTableStyle(...). Custom styles are required whenever you want to modify colors, borders, or fonts beyond what the presets offer.
In addition, pivotTable.formatAll(style) is a shortcut that applies a single style object to every cell of the pivot, overriding whatever is set through either of the style-name APIs above. This is useful when a uniform appearance is required regardless of the underlying theme.pivotTable.autoFormatType accepts a value from the Aspose.Cells.Pivot.PivotTableAutoFormatType enumeration. The available values are Report1 through Report10, Classic, and Table1 through Table10.
The following example loads a fresh workbook, populates the Fruit/Year/Amount sample data, adds a pivot table, applies PivotTableAutoFormatType.Report5, and saves the result as .xls.
Report1 through Report10, Table1 through Table10) were designed in classic Excel for single-dimension pivot tables with row fields and values only — they have no built-in styling for column-field headers. If your pivot needs column fields, use the modern PivotTableStyleType presets from Scenario 2 instead, which are designed for the two-dimensional layout modern Excel uses.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example</h1>
<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 () => {
// Scenario 1: Apply a legacy XLS preset autoformat
// API in use: PivotTable.AutoFormatType
// Target file format: .xls (legacy)
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// Create a new workbook
const workbook = new Workbook();
// Get the first worksheet
const sheet = workbook.worksheets.get(0);
const cells = sheet.cells;
// Populate the source data with header row (Fruit, Year, Amount)
// and 9 data rows covering grape, blueberry, kiwi, cherry across 2020 and 2021
cells.get(0, 0).value = "Fruit";
cells.get(0, 1).value = "Year";
cells.get(0, 2).value = "Amount";
cells.get(1, 0).value = "grape";
cells.get(1, 1).value = 2020;
cells.get(1, 2).value = 50;
cells.get(2, 0).value = "blueberry";
cells.get(2, 1).value = 2020;
cells.get(2, 2).value = 30;
cells.get(3, 0).value = "kiwi";
cells.get(3, 1).value = 2020;
cells.get(3, 2).value = 25;
cells.get(4, 0).value = "cherry";
cells.get(4, 1).value = 2020;
cells.get(4, 2).value = 40;
cells.get(5, 0).value = "grape";
cells.get(5, 1).value = 2021;
cells.get(5, 2).value = 60;
cells.get(6, 0).value = "blueberry";
cells.get(6, 1).value = 2021;
cells.get(6, 2).value = 35;
cells.get(7, 0).value = "kiwi";
cells.get(7, 1).value = 2021;
cells.get(7, 2).value = 28;
cells.get(8, 0).value = "cherry";
cells.get(8, 1).value = 2021;
cells.get(8, 2).value = 45;
cells.get(9, 0).value = "grape";
cells.get(9, 1).value = 2020;
cells.get(9, 2).value = 45;
// Add a pivot table at destination cell E3, using source range A1:C10
const pivotTable = sheet.pivotTables.add("=A1:C10", 2, 4);
// Assign fields: Fruit -> Rows, Amount -> Data
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Fruit");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
// Apply the legacy XLS preset autoformat "Report5"
// Note: This property is only meaningful when saving as .xls.
// When saved as .xlsx/.xlsm/.xlsb, Excel ignores AutoFormatType
// and uses whatever PivotTableStyleType / PivotTableStyleName specifies.
pivotTable.autoFormatType = AsposeCells.PivotTableAutoFormatType.Report5;
// Save the workbook in legacy .xls format
const outputData = workbook.save(SaveFormat.Excel97To2003);
const blob = new Blob([outputData], { type: "application/vnd.ms-excel" });
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = "output.xls";
downloadLink.style.display = "block";
document.getElementById('result').innerHTML = '<p style="color: green;">Done! Click the download link to get the result file.</p>';
});
</script>
</html>
pivotTable.pivotTableStyleType selects one of the built-in named styles that Excel 2007 introduced for pivot tables. The PivotTableStyleType enumeration exposes three theme families: the light family from PivotTableStyleLight1 through PivotTableStyleLight28, the medium family from PivotTableStyleMedium1 through PivotTableStyleMedium28, and the dark family from PivotTableStyleDark1 through PivotTableStyleDark28. It also defines None and Custom. The higher-numbered members of each family are the styles added in Excel 2017.
These presets are read-only. As soon as you need to change colors, borders, or fonts beyond what a preset provides, you have to define a custom pivot style instead, as shown in Scenario 3.
Report1 through Report10 and Table1 through Table10 values of PivotTableAutoFormatType predate pivot column fields, so they carry no styling for column-field headers and Excel renders those headers unstyled. The PivotTableStyleType presets were designed for the two-dimensional layout modern Excel uses and style row-field and column-field headers alike.
This scenario reuses the Fruit/Year/Amount dataset of Scenario 1, adds Year to the column area alongside the Fruit row field via PivotFieldType.Column, applies the dark-theme preset PivotTableStyleDark1 with pivotTable.pivotTableStyleType, and saves the result as .xlsx — the format for which PivotTableStyleType is meaningful.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example</h1>
<button id="runExample">Run Example</button>
<a id="downloadLink" style="display: none;">Download Result</a>
<div id="result"></div>
<script src="aspose.cells.js.min.js"></script>
<script type="text/javascript">
const { Workbook, SaveFormat } = AsposeCells;
const readyPromise = 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 () => {
// Scenario 2: Apply a modern Excel 2007+ named preset style using PivotTableStyleType.
// Target file format: .xlsx. The PivotTableStyleType enum lives in the Aspose.Cells namespace
// (not in Aspose.Cells.Pivot) -- that is why we do not need any extra using for it.
// GitHub reference: https://github.com/aspose-cells/Aspose.Cells-for-.NET/blob/master/Examples/CSharp/PivotTables/ApplyStyleToPivotTable2.cs
const workbook = new Workbook();
const worksheet = workbook.worksheets.get(0);
const cells = worksheet.cells;
// Header row: Fruit / Year / Amount
cells.get("A1").value = "Fruit";
cells.get("B1").value = "Year";
cells.get("C1").value = "Amount";
// 9 data rows of Fruit / Year / Amount
cells.get("A2").value = "Grape";
cells.get("B2").value = 2020;
cells.get("C2").value = 100;
cells.get("A3").value = "Blueberry";
cells.get("B3").value = 2020;
cells.get("C3").value = 150;
cells.get("A4").value = "Kiwi";
cells.get("B4").value = 2020;
cells.get("C4").value = 200;
cells.get("A5").value = "Cherry";
cells.get("B5").value = 2020;
cells.get("C5").value = 180;
cells.get("A6").value = "Grape";
cells.get("B6").value = 2021;
cells.get("C6").value = 120;
cells.get("A7").value = "Blueberry";
cells.get("B7").value = 2021;
cells.get("C7").value = 170;
cells.get("A8").value = "Kiwi";
cells.get("B8").value = 2021;
cells.get("C8").value = 210;
cells.get("A9").value = "Cherry";
cells.get("B9").value = 2021;
cells.get("C9").value = 190;
cells.get("A10").value = "Grape";
cells.get("B10").value = 2021;
cells.get("C10").value = 130;
// Add a pivot table at E3, sourced from A1:C10
const pivotTable = worksheet.pivotTables.add("=A1:C10", 2, 4);
// Assign pivot fields: Fruit -> Row area, Year -> Column area, Amount -> Data area
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Fruit");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "Year");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
// Apply a modern Excel 2007+ named preset pivot style.
// PivotTableStyleType is the correct API for .xlsx / .xlsm / .xlsb files; AutoFormatType
// is ignored by Excel for those formats. PivotTableStyleDark1 belongs to the dark-theme
// family (PivotTableStyleDark1..PivotTableStyleDark28), and the same enum also exposes the
// newer Excel 2017 light/dark themes (PivotTableStyleLight1..Light28 / Dark1..Dark28).
pivotTable.pivotTableStyleType = AsposeCells.PivotTableStyleType.PivotTableStyleDark1;
// Save as modern .xlsx -- this is the format for which PivotTableStyleType is meaningful.
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = "output.xlsx";
downloadLink.style.display = "block";
document.getElementById('result').innerHTML = '<p style="color: green;">Done! Click the download link to get the result file.</p>';
});
</script>
</body>
</html>
The built-in presets cannot be modified. Whenever you need to override colors, borders, or fonts, you must define a custom pivot style. The workflow has three steps:
tableStyles collection via workbook.worksheets.tableStyles.addPivotTableStyle(name). This returns the index of the newly created style.WholeTable or GrandTotalRow) through tableStyle.tableStyleElements.add(tableStyleElementType), then assign a style to each element via tableStyleElement.setElementStyle(style).pivotTable.pivotTableStyleName to the style’s name. Do not use pivotTable.pivotTableStyleType here, since that property selects built-in presets.pivotTable.pivotTableStyleName and pivotTable.pivotTableStyleType are not interchangeable. Use pivotTable.pivotTableStyleType for built-in presets, and pivotTable.pivotTableStyleName for custom styles you have defined through addPivotTableStyle. Setting both is harmless, but only the one matching the intended source is rendered.
The available TableStyleElementType values include WholeTable, FirstRow, LastRow, FirstColumn, LastColumn, GrandTotalRow, GrandTotalColumn, PageFieldLabels, and PageFieldValues.
The following example defines a custom pivot style with a thin black border on WholeTable and a bold red font on GrandTotalRow, then applies it via pivotTable.pivotTableStyleName and saves as .xlsx.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example</h1>
<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 } = 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 workbook = new Workbook();
const worksheet = workbook.worksheets.get(0);
const cells = worksheet.cells;
// Populate source data: header row + 9 data rows (A1:C10)
cells.get("A1").value = "Fruit";
cells.get("B1").value = "Year";
cells.get("C1").value = "Amount";
cells.get("A2").value = "Grape";
cells.get("B2").value = 2020;
cells.get("C2").value = 100;
cells.get("A3").value = "Blueberry";
cells.get("B3").value = 2020;
cells.get("C3").value = 200;
cells.get("A4").value = "Kiwi";
cells.get("B4").value = 2020;
cells.get("C4").value = 300;
cells.get("A5").value = "Cherry";
cells.get("B5").value = 2020;
cells.get("C5").value = 400;
cells.get("A6").value = "Grape";
cells.get("B6").value = 2021;
cells.get("C6").value = 500;
cells.get("A7").value = "Blueberry";
cells.get("B7").value = 2021;
cells.get("C7").value = 600;
cells.get("A8").value = "Kiwi";
cells.get("B8").value = 2021;
cells.get("C8").value = 700;
cells.get("A9").value = "Cherry";
cells.get("B9").value = 2021;
cells.get("C9").value = 800;
cells.get("A10").value = "Grape";
cells.get("B10").value = 2021;
cells.get("C10").value = 900;
// Add pivot table sourced from A1:C10, anchored at E3
const pivotTable = worksheet.pivotTables.add("=A1:C10", 2, 4);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Fruit");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "Year");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
// Step 1: register a new custom pivot table style and capture its index
const styleIndex = workbook.worksheets.tableStyles.addPivotTableStyle("CustomPivotStyle");
const tableStyle = workbook.worksheets.tableStyles.get(styleIndex);
// Step 2: add a WholeTable element and apply thin black borders on all four sides
const wholeTableElementIndex = tableStyle.tableStyleElements.add(AsposeCells.TableStyleElementType.WholeTable);
const wholeTableElement = tableStyle.tableStyleElements.get(wholeTableElementIndex);
const wholeTableStyle = workbook.createStyle();
const topBorder = wholeTableStyle.borders.get(AsposeCells.BorderType.TopBorder);
topBorder.lineStyle = AsposeCells.CellBorderType.Thin;
topBorder.color = "black";
const bottomBorder = wholeTableStyle.borders.get(AsposeCells.BorderType.BottomBorder);
bottomBorder.lineStyle = AsposeCells.CellBorderType.Thin;
bottomBorder.color = "black";
const leftBorder = wholeTableStyle.borders.get(AsposeCells.BorderType.LeftBorder);
leftBorder.lineStyle = AsposeCells.CellBorderType.Thin;
leftBorder.color = "black";
const rightBorder = wholeTableStyle.borders.get(AsposeCells.BorderType.RightBorder);
rightBorder.lineStyle = AsposeCells.CellBorderType.Thin;
rightBorder.color = "black";
wholeTableElement.setElementStyle(wholeTableStyle);
// Step 3: add a GrandTotalRow element and apply bold red font
const grandTotalElementIndex = tableStyle.tableStyleElements.add(AsposeCells.TableStyleElementType.GrandTotalRow);
const grandTotalElement = tableStyle.tableStyleElements.get(grandTotalElementIndex);
const grandTotalStyle = workbook.createStyle();
grandTotalStyle.font.isBold = true;
grandTotalStyle.font.color = "red";
grandTotalElement.setElementStyle(grandTotalStyle);
// Step 4: apply the custom style by name (NOT by PivotTableStyleType, which is for built-in presets)
pivotTable.pivotTableStyleName = "CustomPivotStyle";
const outputData = workbook.save(AsposeCells.SaveFormat.Xlsx);
const blob = new Blob([outputData], {
type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
});
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = "output.xlsx";
downloadLink.style.display = "block";
document.getElementById('result').innerHTML = '<p style="color: green;">Done! Click the download link to get the result file.</p>';
});
</script>
</html>
pivotTable.formatAll(style) is a shortcut that applies a single style object to every cell of the pivot table, including the data area, row and column headers, and totals. Whatever was previously set through pivotTable.pivotTableStyleType or pivotTable.pivotTableStyleName is overridden.
formatAll overrides both pivotTable.pivotTableStyleType and pivotTable.pivotTableStyleName. Use it only when a uniform, theme-independent appearance is required across the entire pivot.
The following example creates a style with a yellow solid fill, a bold dark-blue font, and thin black borders on all sides, then applies it with formatAll and saves as .xlsx.
<!DOCTYPE html>
<html>
<head>
<title>Aspose.Cells Example</title>
</head>
<body>
<h1>Aspose.Cells Example</h1>
<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 () => {
// Scenario 4: Apply a single Style to every pivot table cell using FormatAll
// API in use: PivotTable.FormatAll(Style)
// Target format: .xlsx
// GitHub reference: see Aspose.Cells-for-.NET repository — pivot table styling examples
const workbook = new Workbook();
const worksheet = workbook.worksheets.get(0);
// Populate source data: header row (row 1) + 9 data rows (rows 2-10)
const cells = worksheet.cells;
cells.get("A1").value = "Fruit";
cells.get("B1").value = "Year";
cells.get("C1").value = "Amount";
cells.get("A2").value = "Grape";
cells.get("B2").value = 2020;
cells.get("C2").value = 5000;
cells.get("A3").value = "Blueberry";
cells.get("B3").value = 2020;
cells.get("C3").value = 3000;
cells.get("A4").value = "Kiwi";
cells.get("B4").value = 2020;
cells.get("C4").value = 4000;
cells.get("A5").value = "Cherry";
cells.get("B5").value = 2020;
cells.get("C5").value = 2000;
cells.get("A6").value = "Grape";
cells.get("B6").value = 2021;
cells.get("C6").value = 6000;
cells.get("A7").value = "Blueberry";
cells.get("B7").value = 2021;
cells.get("C7").value = 3500;
cells.get("A8").value = "Kiwi";
cells.get("B8").value = 2021;
cells.get("C8").value = 4500;
cells.get("A9").value = "Cherry";
cells.get("B9").value = 2021;
cells.get("C9").value = 2500;
cells.get("A10").value = "Grape";
cells.get("B10").value = 2021;
cells.get("C10").value = 5500;
// Add pivot table: source range A1:C10, destination cell E3
const pivotTable = worksheet.pivotTables.add("=A1:C10", 2, 4);
// Assign pivot fields: Fruit -> Row area, Year -> Column area, Amount -> Data area
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Fruit");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "Year");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
// Build a Style that will be forced onto every cell of the pivot table
const style = workbook.createStyle();
style.foregroundColor = AsposeCells.Color.Yellow;
style.pattern = AsposeCells.BackgroundType.Solid;
style.font.isBold = true;
style.font.color = AsposeCells.Color.DarkBlue;
style.borders.get(AsposeCells.BorderType.TopBorder).lineStyle = AsposeCells.CellBorderType.Thin;
style.borders.get(AsposeCells.BorderType.TopBorder).color = AsposeCells.Color.Black;
style.borders.get(AsposeCells.BorderType.BottomBorder).lineStyle = AsposeCells.CellBorderType.Thin;
style.borders.get(AsposeCells.BorderType.BottomBorder).color = AsposeCells.Color.Black;
style.borders.get(AsposeCells.BorderType.LeftBorder).lineStyle = AsposeCells.CellBorderType.Thin;
style.borders.get(AsposeCells.BorderType.LeftBorder).color = AsposeCells.Color.Black;
style.borders.get(AsposeCells.BorderType.RightBorder).lineStyle = AsposeCells.CellBorderType.Thin;
style.borders.get(AsposeCells.BorderType.RightBorder).color = AsposeCells.Color.Black;
// Apply FormatAll: forces this single style onto every cell of the pivot table,
// overriding any PivotTableStyleType / PivotTableStyleName previously set
pivotTable.formatAll(style);
// Save the workbook in the modern .xlsx format
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = "output.xlsx";
downloadLink.style.display = "block";
document.getElementById('result').innerHTML = '<p style="color: green;">Done! Click the download link to get the result file.</p>';
});
</script>
</html>
The choice of style API depends on the file format you are saving to. Use the table below as a quick reference.
| Target file format | API to use | Notes |
|---|---|---|
.xls (legacy) |
pivotTable.autoFormatType |
Values from Aspose.Cells.Pivot.PivotTableAutoFormatType (e.g. Report1–Report10, Classic, Table1–Table10). Ignored when saving as modern formats. |
.xlsx / .xlsm / .xlsb (modern, built-in style) |
pivotTable.pivotTableStyleType |
Values from Aspose.Cells.PivotTableStyleType (light/dark themes, including Excel 2017 additions). |
.xlsx / .xlsm / .xlsb (modern, custom style) |
pivotTable.pivotTableStyleName + workbook.worksheets.tableStyles.addPivotTableStyle(...) |
Use when the built-in presets are not enough. Configure via tableStyleElement.setElementStyle(...). |
| Any format (uniform override) | pivotTable.formatAll(style) |
Shortcut that overrides every other style setting across the entire pivot. |
When in doubt, save as .xlsx and use pivotTable.pivotTableStyleType for built-in themes, or pivotTable.pivotTableStyleName for custom themes. |
Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.