Analyzing your prompt, please hold on...
An error occurred while retrieving the results. Please refresh the page and try again.
Manage Pivot Table Value Fields in Aspose.Cells for JavaScript via C++
Contents
[
Hide
]
Adding a Field to the Data Region
Adding a base field to the data (value) region is the first step in shaping how a pivot table aggregates the source data. Aspose.Cells for JavaScript via C++ exposes pivotTable.addFieldToArea(PivotFieldType.Data, "ColumnName"), an overload that accepts the constant PivotFieldType.Data and the source-column name. Once a field is added to the data region, the API exposes it through the pivotTable.getDataFields() collection, in the order in which the fields were added. By default, a numeric source column is summarised with ConsolidationFunction.Sum, while a non-numeric column defaults to Count.
<!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 worksheet = workbook.worksheets.get(0);
worksheet.name = "Data";
const headers = ["Category", "Item", "Year", "Amount"];
for (let j = 0; j < headers.length; j++) {
worksheet.cells.get(0, j).value = headers[j];
}
const data = [
["Fruit", "Apple", 2020, 100],
["Fruit", "Apple", 2021, 150],
["Fruit", "Banana", 2020, 80],
["Fruit", "Banana", 2021, 90],
["Vegetable", "Carrot", 2020, 50],
["Vegetable", "Carrot", 2021, 60],
["Vegetable", "Daikon", 2020, 40],
["Vegetable", "Daikon", 2021, 45]
];
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j++) {
worksheet.cells.get(i + 1, j).value = data[i][j];
}
}
const pivotTable = worksheet.pivotTables.add("=A1:D9", 2, 5);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Category");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Item");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "Year");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
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 = "output_drag.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>
Changing the Summary Function
Once a field lives in the data region, Aspose.Cells exposes it through the PivotField object returned by pivotTable.getDataFields(). Each PivotField has a writable function property of type ConsolidationFunction, which controls the aggregate applied to that field’s underlying values. ConsolidationFunction is an enum with members Sum, Count, Average, Max, Min, Product, StdDev, StdDevp, Var, and Varp — the first six cover the vast majority of real-world use cases, while the last four are statistical aggregates useful for variance analysis.
Changing
function only affects the aggregate; the source column and the pivot’s row/column structure are not modified. To switch the aggregate for an existing data field, call pivotTable.getDataFields()[i].setFunction(ConsolidationFunction.X) and then call pivotTable.calculateData() to re-render the pivot.
<!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;
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 worksheet = workbook.worksheets.get(0);
worksheet.name = "Data";
const headers = ["Category", "Item", "Year", "Amount"];
for (let j = 0; j < headers.length; j++) {
worksheet.cells.get(0, j).value = headers[j];
}
const data = [
["Fruit", "Apple", 2020, 100],
["Fruit", "Apple", 2021, 150],
["Fruit", "Banana", 2020, 80],
["Fruit", "Banana", 2021, 90],
["Vegetable", "Carrot", 2020, 50],
["Vegetable", "Carrot", 2021, 60],
["Vegetable", "Daikon", 2020, 40],
["Vegetable", "Daikon", 2021, 45]
];
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j++) {
worksheet.cells.get(i + 1, j).value = data[i][j];
}
}
const pivotTable = worksheet.pivotTables.add("=A1:D9", 2, 5);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Category");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Item");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "Year");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
const countField = pivotTable.dataFields.get(1);
countField.function = AsposeCells.ConsolidationFunction.Count;
pivotTable.calculateData();
const outputData = workbook.save(AsposeCells.SaveFormat.Xlsx);
const blob = new Blob([outputData]);
const downloadLink = document.getElementById('downloadLink');
downloadLink.href = URL.createObjectURL(blob);
downloadLink.download = "output_function.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>
Plotting Value Fields to Row or Column Axis
When a pivot table contains two or more data fields, Aspose.Cells exposes an additional virtual field called pivotTable.getValuesField(). This virtual field represents the aggregate of every data field that lives in the data region. You can drag it into the Row or Column region as a base pivot field, which is useful for laying out multiple measures side by side.
The virtual values field returned by
pivotTable.getValuesField() does not work if there is no or only one value field.
<!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 worksheet = workbook.worksheets.get(0);
worksheet.name = "Data";
const cells = worksheet.cells;
const headers = ["Category", "Item", "Year", "Amount"];
for (let j = 0; j < headers.length; j++) {
cells.get(0, j).value = headers[j];
}
const data = [
["Fruit", "Apple", 2020, 100],
["Fruit", "Apple", 2021, 150],
["Fruit", "Banana", 2020, 80],
["Fruit", "Banana", 2021, 90],
["Vegetable", "Carrot", 2020, 50],
["Vegetable", "Carrot", 2021, 60],
["Vegetable", "Daikon", 2020, 40],
["Vegetable", "Daikon", 2021, 45]
];
for (let i = 0; i < data.length; i++) {
for (let j = 0; j < data[i].length; j++) {
cells.get(i + 1, j).value = data[i][j];
}
}
const pivotTable = worksheet.pivotTables.add("=A1:D9", 2, 5);
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Category");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Row, "Item");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, "Year");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Data, "Amount");
pivotTable.dataFields.get(1).function = AsposeCells.ConsolidationFunction.Count;
pivotTable.addFieldToArea(AsposeCells.PivotFieldType.Column, pivotTable.valuesField.name);
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 = "output_plot.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>