Formatera Pivot tabell
Contents
[
Hide
]
Pivottabellens utseende
Hur man skapar en pivottabell förklarar hur man skapar en enkel pivottabell. Den här artikeln beskriver hur man anpassar en pivottabells utseende genom att ställa in olika egenskaper:
- Pivottabellformatalternativ
- Alternativ för pivottabellfältformat
- Alternativ för datafältformat
Hur man ställer in pivottabellsformatalternativ
Klassen PivotTable kontrollerar den övergripande pivottabellen och kan formateras på olika sätt.
Hur man ställer in automatiskt format typ
Microsoft Excel erbjuder ett antal förinställda rapportformat. Aspose.Cells for JavaScript via C++ stöder dessa formateringsalternativ också. För att komma åt dem:
- Ställ in PivotTable.isAutoFormat(value) till true.
- Tilldela ett formateringsalternativ från uppräkningen PivotTableAutoFormatType.
<!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>
Hur man ställer in formateringsalternativ
Kodprovet nedan visar hur man formaterar pivottabellen för att visa totaler för rader och kolumner, och hur man ställer in rapportens fältsortering. Det visar också hur man ställer in en anpassad sträng för nollvärden.
<!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>
Manuell formatering av utseende
För att manuellt formatera hur pivottabelrapporten ser ut, istället för att använda förinställda rapportformat, använd PivotTable.formatAll(style)- och PivotTable.format(row, column, style)-metoderna. Skapa en stilobjekt för önskad formatering, till exempel:
<!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>
Hur man ställer in pivottabellsfältformatalternativ
Klassen PivotField representerar ett fält i en pivottabell och kan formateras på olika sätt. Kodprovet nedan visar hur man:
- Tillgång till radfält.
- Inställning av delsummer.
- Inställning av autosortering.
- Inställning av autoshow.
Hur man ställer in rad/kolumn/sida fältformat
<!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>
Hur man ställer in datafältformat
Kodprovet nedan visar hur man ställer in visningsformat och nummerformat för datafält.
<!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>
Hur man rensar pivottabellsfält
Klassen PivotFieldCollection har en metod som heter clear() som låter dig rensa pivottabellsfält. Använd den när du vill rensa alla pivottabellfält i områdena, till exempel sida, kolumn, rad eller data. Kodprovet nedan visar hur man rensar alla pivottabellfält i ett dataområde.
<!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>