Formatieren der Pivot Tabelle
Contents
[
Hide
]
Aussehen der Pivot-Tabelle
Wie man eine Pivot-Tabelle erstellt, erklärt, wie man eine einfache Pivot-Tabelle erstellt. Dieser Artikel beschreibt, wie man das Aussehen einer Pivot-Tabelle anpasst, indem man verschiedene Eigenschaften einstellt:
- Optionen zum Formatieren von Pivot-Tabellen
- Optionen zum Formatieren von Pivot-Feldern
- Optionen zum Formatieren von Datenfeldern
Wie man die Formatierungsoptionen für Pivot-Tabellen festlegt
Die Klasse PivotTable steuert die gesamte Pivot-Tabelle und kann auf verschiedene Arten formatiert werden.
Wie man den AutoFormat-Typ festlegt
Microsoft Excel bietet eine Reihe vordefinierter Berichtformate. Aspose.Cells for JavaScript via C++ unterstützt auch diese Formatierungsoptionen. Um darauf zuzugreifen:
- Setzen Sie PivotTable.isAutoFormat(value) auf true.
- Weisen Sie eine Formatierungsoption aus der PivotTableAutoFormatType-Aufzählung zu.
<!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>
Wie man Formatierungsoptionen festlegt
Das unten stehende Codebeispiel zeigt, wie die Pivot-Tabelle formatiert wird, um Gesamtsummen für Zeilen und Spalten anzuzeigen, und wie die Feldreihenfolge des Berichts festgelegt wird. Es zeigt auch, wie eine benutzerdefinierte Zeichenfolge für Nullwerte festgelegt wird.
<!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>
Manuelles Anpassen von Aussehen und Anmutung
Um das Erscheinungsbild des Pivot-Tabellenberichts manuell anzupassen, anstatt voreingestellte Berichtsformate zu verwenden, verwenden Sie die Methoden PivotTable.formatAll(style) und PivotTable.format(row, column, style). Erstellen Sie ein Style-Objekt für Ihre gewünschte Formatierung, zum Beispiel:
<!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>
So setzen Sie Pivot-Feldformatoptionen
Die Klasse PivotField stellt ein Feld in einer Pivot-Tabelle dar und kann auf verschiedene Arten formatiert werden. Das unten stehende Codebeispiel zeigt, wie:
- Auf Zeilenfelder zugreifen.
- Untergesamtsummen einstellen.
- Autosortierung einstellen.
- Autoshow einstellen.
So setzen Sie Zeilen-/Spalten-/Seitenfeldformatoptionen
<!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>
So setzen Sie Datenfeldformatoptionen
Das unten stehende Codebeispiel zeigt, wie man Anzeigeformate und Zahlenformat für Datenfelder einstellt.
<!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>
So löschen Sie Pivot-Felder
Die Methode clear() der PivotFieldCollection-Klasse ermöglicht es Ihnen, Pivot-Felder zu löschen. Verwenden Sie sie, wenn Sie alle Pivot-Felder in den Bereichen wie Seite, Spalte, Zeile oder Daten löschen möchten. Das unten stehende Codebeispiel zeigt, wie man alle Pivot-Felder in einem Datenbereich löscht.
<!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>