Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.
Modify Page Field Layout in Pivot Table
Contents
[
Hide
]
This article is a continuation of the Add Page Field in Pivot Table topic. It demonstrates how to control the layout of the page field area — the strip of filter controls at the top of a pivot table — including display order, wrap count, and field reordering.
Introduction
A pivot table in Microsoft Excel exposes a dedicated page field area that sits above the row/column/data body of the table. This area is rendered as a strip of dropdown filter controls (one per page field) and is what end-users click to slice the pivot by criteria such as year or region. Aspose.Cells models this area through the pivotTable.pageFields collection and exposes three properties that control how the strip is visually laid out:
pivotTable.pageFieldOrder(anAspose.Cells.PrintOrderTypevalue) decides whether additional page fields are placed next to the existing ones or below them.pivotTable.pageFieldWrapCountsets how many page fields are placed per row or column before wrapping.pivotTable.pageFields.move(currIndex, destIndex)reorders the page fields without changing the order mode. This article walks through three code examples that demonstrate each of these operations on a shared dataset, so that you can compare the resulting layouts side-by-side.
Source Data
| Fruit | Year | Region | Amount |
|---|---|---|---|
| Apple | 2022 | North | 150 |
| Apple | 2023 | North | 180 |
| Banana | 2022 | South | 120 |
| Banana | 2023 | South | 140 |
| Cherry | 2022 | East | 200 |
| Cherry | 2023 | East | 220 |
| Grape | 2022 | West | 90 |
| Grape | 2023 | West | 110 |
| All eight rows are populated in every code example, in identical order, so the source data never differs between scenarios — only the page-field layout properties do. |
Example 1: Over Then Down
In the first scenario we configure the two page fields (Year, Region) to appear side-by-side in a single row at the top of the pivot table. We assign Fruit to the row axis, place Year first and Region second on the page axis (the order of addFieldToArea calls determines the starting index), add Amount (Sum) as the data field, and then set pivotTable.pageFieldOrder to PrintOrderType.OverThenDown with pivotTable.pageFieldWrapCount = 2. With OverThenDown and a wrap count of 2, the two page fields are laid out horizontally side-by-side in a single row at the top of the pivot table, so the strip occupies one row of width two.
<!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 () => {
const workbook = new Workbook();
const worksheets = workbook.worksheets;
const pivotDataSheet = worksheets.add("PivotData");
const pivotDataCells = pivotDataSheet.cells;
// Headers (row 0)
pivotDataCells.get(0, 0).value = "Fruit";
pivotDataCells.get(0, 1).value = "Year";
pivotDataCells.get(0, 2).value = "Region";
pivotDataCells.get(0, 3).value = "Amount";
// Row 1: Apple, 2022, North, 150
pivotDataCells.get(1, 0).value = "Apple";
pivotDataCells.get(1, 1).value = 2022;
pivotDataCells.get(1, 2).value = "North";
pivotDataCells.get(1, 3).value = 150;
// Row 2: Apple, 2023, North, 180
pivotDataCells.get(2, 0).value = "Apple";
pivotDataCells.get(2, 1).value = 2023;
pivotDataCells.get(2, 2).value = "North";
pivotDataCells.get(2, 3).value = 180;
// Row 3: Banana, 2022, South, 120
pivotDataCells.get(3, 0).value = "Banana";
pivotDataCells.get(3, 1).value = 2022;
pivotDataCells.get(3, 2).value = "South";
pivotDataCells.get(3, 3).value = 120;
// Row 4: Banana, 2023, South, 140
pivotDataCells.get(4, 0).value = "Banana";
pivotDataCells.get(4, 1).value = 2023;
pivotDataCells.get(4, 2).value = "South";
pivotDataCells.get(4, 3).value = 140;
// Row 5: Cherry, 2022, East, 200
pivotDataCells.get(5, 0).value = "Cherry";
pivotDataCells.get(5, 1).value = 2022;
pivotDataCells.get(5, 2).value = "East";
pivotDataCells.get(5, 3).value = 200;
// Row 6: Cherry, 2023, East, 220
pivotDataCells.get(6, 0).value = "Cherry";
pivotDataCells.get(6, 1).value = 2023;
pivotDataCells.get(6, 2).value = "East";
pivotDataCells.get(6, 3).value = 220;
// Row 7: Grape, 2022, West, 90
pivotDataCells.get(7, 0).value = "Grape";
pivotDataCells.get(7, 1).value = 2022;
pivotDataCells.get(7, 2).value = "West";
pivotDataCells.get(7, 3).value = 90;
// Row 8: Grape, 2023, West, 110
pivotDataCells.get(8, 0).value = "Grape";
pivotDataCells.get(8, 1).value = 2023;
pivotDataCells.get(8, 2).value = "West";
pivotDataCells.get(8, 3).value = 110;
// Add PivotTableReport sheet
const pivotTableSheet = worksheets.add("PivotTableReport");
const pivotTables = pivotTableSheet.pivotTables;
// Create pivot table sourced from PivotData!A1:D9 placed at A1 on PivotTableReport
const pivotTable = pivotTables.add("PivotData!A1:D9", 0, 0);
// Add fields
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 0); // Fruit
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Page, 1); // Year
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Page, 2); // Region
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, 3); // Amount
pivotTable.dataFields.get(0).function = AsposeCells.ConsolidationFunction.Sum;
// Configure page field area layout: place page fields across first, wrap after every 2
pivotTable.pageFieldOrder = AsposeCells.PrintOrderType.OverThenDown;
pivotTable.pageFieldWrapCount = 2;
// Refresh and calculate
pivotTable.calculateData();
// Save
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = "pageFieldLayout_overThenDown.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>
Example 2: Down Then Over
In this example we place Fruit on the row axis, Year and Region on the page axis (with Year first), and Amount (Sum) as the data field — exactly as in Example 1. We then set pivotTable.pageFieldOrder to PrintOrderType.DownThenOver and pivotTable.pageFieldWrapCount to 2. With DownThenOver and a wrap count of 2, the two page fields are stacked vertically — Year on top, Region directly below — forming a single column at the top of the pivot table. The strip therefore occupies two rows of width one, in contrast to Example 1.
<!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 () => {
const workbook = new Workbook();
const pivotData = workbook.worksheets.get(0);
pivotData.name = "PivotData";
const pivotReport = workbook.worksheets.add("PivotTableReport");
const headers = ["Fruit", "Year", "Region", "Amount"];
for (let c = 0; c < headers.length; c++) {
pivotData.cells.get(0, c).value = headers[c];
}
const data = [
["Apple", 2022, "North", 150],
["Apple", 2023, "North", 180],
["Banana", 2022, "South", 120],
["Banana", 2023, "South", 140],
["Cherry", 2022, "East", 200],
["Cherry", 2023, "East", 220],
["Grape", 2022, "West", 90],
["Grape", 2023, "West", 110]
];
for (let r = 0; r < data.length; r++) {
for (let c = 0; c < data[r].length; c++) {
pivotData.cells.get(r + 1, c).value = data[r][c];
}
}
const pivotTable = pivotReport.pivotTables.add("=PivotData!A1:D9", 0, 0);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, 0);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Page, 1);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Page, 2);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, 3);
pivotTable.pageFieldOrder = AsposeCells.PrintOrderType.DownThenOver;
pivotTable.pageFieldWrapCount = 2;
pivotTable.calculateData();
const outputData = workbook.save(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 = "pageFieldLayout_downThenOver.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>
Example 3: Move a Page Field
In the third scenario we keep this dataset and field allocation, set a neutral layout (OverThenDown with wrap count 2), and then demonstrate the pivotTable.pageFields.move operation. The move(0, 1) call moves the page field at index 0 (Year) to position 1, and the page field that was at position 1 (Region) shifts to position 0. After this call, Region is the first page field and Year is the second. The wrap and order mode are unchanged, so the strip is still rendered horizontally side-by-side — only the order of the two dropdowns has been swapped.
<!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;
const initialization = 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 () => {
await initialization;
const workbook = new Workbook();
const dataSheet = workbook.worksheets.get(0);
dataSheet.name = "PivotData";
const cells = dataSheet.cells;
cells.get("A1").value = "Fruit";
cells.get("B1").value = "Year";
cells.get("C1").value = "Region";
cells.get("D1").value = "Amount";
cells.get("A2").value = "Apple";
cells.get("B2").value = 2022;
cells.get("C2").value = "North";
cells.get("D2").value = 150;
cells.get("A3").value = "Apple";
cells.get("B3").value = 2023;
cells.get("C3").value = "North";
cells.get("D3").value = 180;
cells.get("A4").value = "Banana";
cells.get("B4").value = 2022;
cells.get("C4").value = "South";
cells.get("D4").value = 120;
cells.get("A5").value = "Banana";
cells.get("B5").value = 2023;
cells.get("C5").value = "South";
cells.get("D5").value = 140;
cells.get("A6").value = "Cherry";
cells.get("B6").value = 2022;
cells.get("C6").value = "East";
cells.get("D6").value = 200;
cells.get("A7").value = "Cherry";
cells.get("B7").value = 2023;
cells.get("C7").value = "East";
cells.get("D7").value = 220;
cells.get("A8").value = "Grape";
cells.get("B8").value = 2022;
cells.get("C8").value = "West";
cells.get("D8").value = 90;
cells.get("A9").value = "Grape";
cells.get("B9").value = 2023;
cells.get("C9").value = "West";
cells.get("D9").value = 110;
const pivotSheet = workbook.worksheets.add("PivotTableReport");
const pivotTable = pivotSheet.pivotTables.add("=PivotData!A1:D9", 2, 0);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Fruit");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Page, "Year");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Page, "Region");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
pivotTable.pageFieldOrder = AsposeCells.PrintOrderType.OverThenDown;
pivotTable.pageFieldWrapCount = 2;
pivotTable.pageFields.move(0, 1);
pivotTable.calculateData();
const outputData = workbook.save(SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = "pageFieldLayout_move.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>
Related Articles
- Add Page Field in Pivot Table — the parent page that introduces how page fields are added to a pivot table.
- Manage Value Fields in Pivot Table — describes how to configure the data (value) area, including the
Sumaggregation used in this article. - Apply Style to Pivot Table — shows how to format the rendered pivot table after the page-field strip has been laid out.