Erstellen Sie Pivot-Tabellen und Pivot-Diagramme
Contents
[
Hide
]
Eine Pivot-Tabelle ist eine interaktive Zusammenfassung von Datensätzen. Beispielsweise können Sie Hunderte von Rechnungseinträgen in einer Liste in einem Arbeitsblatt haben. Eine Pivot-Tabelle kann die Rechnungen nach Kunde, Produkt oder Datum summieren. Mit Microsoft Excel ist es möglich, die Informationen in der Pivot-Tabelle schnell neu anzuordnen, indem Sie Schaltflächen an eine neue Position ziehen.
Ein Pivot-Diagramm ist eine interaktive grafische Darstellung der Daten in einer Pivot-Tabelle. Pivot-Diagramme wurden in Excel 2000 eingeführt. Die Verwendung eines Pivot-Diagramms macht es noch einfacher, die Daten zu verstehen, da die Pivot-Tabelle Zwischensummen und Summen automatisch erstellt.
Aspose.Cells unterstütztPivot-Tabellen undPivot-Diagramme.
Hinzufügen von Pivot-Tabellen und Diagrammen
Aspose.Cells stellt einen speziellen Satz von Klassen bereit, die zum Erstellen von Pivot-Tabellen verwendet werden. Diese Klassen werden zum Erstellen und Festlegen von PivotTable-Objekten verwendet, die als grundlegende Bausteine eines PivotTable-Objekts fungieren:
- PivotField, ein Feld in einem Pivot-Tabellenbericht.
- PivotFields, eine Sammlung aller PivotField-Objekte in einer Pivot-Tabelle.
- PivotTable, ein PivotTable-Bericht auf einem Arbeitsblatt.
- PivotTables, eine Auflistung aller PivotTable-Objekte auf dem Arbeitsblatt.
Vorbereitung zur Verwendung von Aspose.Cells
- Laden Sie Aspose.Cells herunter und installieren Sie es:
- Installieren Sie es auf Ihrem Entwicklungscomputer. AllesAspose Komponenten arbeiten, wenn sie installiert sind, im Evaluierungsmodus. Der Bewertungsmodus ist zeitlich unbegrenzt und fügt nur Wasserzeichen in die produzierten Dokumente ein. Um mit der Komponente in vollem Umfang arbeiten zu können, benötigen Sie eine gültige Lizenz.
- Erstellen Sie ein Projekt:
- Starten Sie Visual Studio.Net.
- Erstellen Sie eine neue Konsolenanwendung.
- Referenzen hinzufügen: Fügen Sie Ihrem Projekt einen Verweis auf die Komponente Aspose.Cells hinzu, z. B. …\Programme\Aspose\Aspose.Cells\Bin\Net1.0\Aspose.Cells.dll
Hinzufügen einer Pivot-Tabelle
So erstellen Sie eine Pivot-Tabelle mit Aspose.Cells:
- Fügen Sie mithilfe der PutValue/setValue-Methode eines Cell-Objekts einige Daten zu Arbeitsblattzellen hinzu. Sie verwenden auch eine bereits mit Daten gefüllte Vorlagendatei. Die Daten werden als Datenquelle der Pivot-Tabelle verwendet.
- Fügen Sie dem Arbeitsblatt eine Pivot-Tabelle hinzu, indem Sie die add-Methode der PivotTables-Auflistung aufrufen (eingekapselt im Worksheet-Objekt).
- Greifen Sie auf das neue PivotTable-Objekt aus der PivotTables-Auflistung zu, indem Sie seinen Index übergeben. # Verwenden Sie eines der im PivotTable-Objekt gekapselten PivotTable-Objekte, um die Tabelle zu verwalten.
Codebeispiele sind unten angegeben.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
| // The path to the documents directory. | |
| string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
| // Instantiating an Workbook object | |
| Workbook workbook = new Workbook(); | |
| // Obtaining the reference of the first worksheet | |
| Worksheet sheet = workbook.Worksheets[0]; | |
| // Name the sheet | |
| sheet.Name = "Data"; | |
| Cells cells = sheet.Cells; | |
| // Setting the values to the cells | |
| Cell cell = cells["A1"]; | |
| cell.PutValue("Employee"); | |
| cell = cells["B1"]; | |
| cell.PutValue("Quarter"); | |
| cell = cells["C1"]; | |
| cell.PutValue("Product"); | |
| cell = cells["D1"]; | |
| cell.PutValue("Continent"); | |
| cell = cells["E1"]; | |
| cell.PutValue("Country"); | |
| cell = cells["F1"]; | |
| cell.PutValue("Sale"); | |
| cell = cells["A2"]; | |
| cell.PutValue("David"); | |
| cell = cells["A3"]; | |
| cell.PutValue("David"); | |
| cell = cells["A4"]; | |
| cell.PutValue("David"); | |
| cell = cells["A5"]; | |
| cell.PutValue("David"); | |
| cell = cells["A6"]; | |
| cell.PutValue("James"); | |
| cell = cells["A7"]; | |
| cell.PutValue("James"); | |
| cell = cells["A8"]; | |
| cell.PutValue("James"); | |
| cell = cells["A9"]; | |
| cell.PutValue("James"); | |
| cell = cells["A10"]; | |
| cell.PutValue("James"); | |
| cell = cells["A11"]; | |
| cell.PutValue("Miya"); | |
| cell = cells["A12"]; | |
| cell.PutValue("Miya"); | |
| cell = cells["A13"]; | |
| cell.PutValue("Miya"); | |
| cell = cells["A14"]; | |
| cell.PutValue("Miya"); | |
| cell = cells["A15"]; | |
| cell.PutValue("Miya"); | |
| cell = cells["A16"]; | |
| cell.PutValue("Miya"); | |
| cell = cells["A17"]; | |
| cell.PutValue("Miya"); | |
| cell = cells["A18"]; | |
| cell.PutValue("Elvis"); | |
| cell = cells["A19"]; | |
| cell.PutValue("Elvis"); | |
| cell = cells["A20"]; | |
| cell.PutValue("Elvis"); | |
| cell = cells["A21"]; | |
| cell.PutValue("Elvis"); | |
| cell = cells["A22"]; | |
| cell.PutValue("Elvis"); | |
| cell = cells["A23"]; | |
| cell.PutValue("Elvis"); | |
| cell = cells["A24"]; | |
| cell.PutValue("Elvis"); | |
| cell = cells["A25"]; | |
| cell.PutValue("Jean"); | |
| cell = cells["A26"]; | |
| cell.PutValue("Jean"); | |
| cell = cells["A27"]; | |
| cell.PutValue("Jean"); | |
| cell = cells["A28"]; | |
| cell.PutValue("Ada"); | |
| cell = cells["A29"]; | |
| cell.PutValue("Ada"); | |
| cell = cells["A30"]; | |
| cell.PutValue("Ada"); | |
| cell = cells["B2"]; | |
| cell.PutValue("1"); | |
| cell = cells["B3"]; | |
| cell.PutValue("2"); | |
| cell = cells["B4"]; | |
| cell.PutValue("3"); | |
| cell = cells["B5"]; | |
| cell.PutValue("4"); | |
| cell = cells["B6"]; | |
| cell.PutValue("1"); | |
| cell = cells["B7"]; | |
| cell.PutValue("2"); | |
| cell = cells["B8"]; | |
| cell.PutValue("3"); | |
| cell = cells["B9"]; | |
| cell.PutValue("4"); | |
| cell = cells["B10"]; | |
| cell.PutValue("4"); | |
| cell = cells["B11"]; | |
| cell.PutValue("1"); | |
| cell = cells["B12"]; | |
| cell.PutValue("1"); | |
| cell = cells["B13"]; | |
| cell.PutValue("2"); | |
| cell = cells["B14"]; | |
| cell.PutValue("2"); | |
| cell = cells["B15"]; | |
| cell.PutValue("3"); | |
| cell = cells["B16"]; | |
| cell.PutValue("4"); | |
| cell = cells["B17"]; | |
| cell.PutValue("4"); | |
| cell = cells["B18"]; | |
| cell.PutValue("1"); | |
| cell = cells["B19"]; | |
| cell.PutValue("1"); | |
| cell = cells["B20"]; | |
| cell.PutValue("2"); | |
| cell = cells["B21"]; | |
| cell.PutValue("3"); | |
| cell = cells["B22"]; | |
| cell.PutValue("3"); | |
| cell = cells["B23"]; | |
| cell.PutValue("4"); | |
| cell = cells["B24"]; | |
| cell.PutValue("4"); | |
| cell = cells["B25"]; | |
| cell.PutValue("1"); | |
| cell = cells["B26"]; | |
| cell.PutValue("2"); | |
| cell = cells["B27"]; | |
| cell.PutValue("3"); | |
| cell = cells["B28"]; | |
| cell.PutValue("1"); | |
| cell = cells["B29"]; | |
| cell.PutValue("2"); | |
| cell = cells["B30"]; | |
| cell.PutValue("3"); | |
| cell = cells["C2"]; | |
| cell.PutValue("Maxilaku"); | |
| cell = cells["C3"]; | |
| cell.PutValue("Maxilaku"); | |
| cell = cells["C4"]; | |
| cell.PutValue("Chai"); | |
| cell = cells["C5"]; | |
| cell.PutValue("Maxilaku"); | |
| cell = cells["C6"]; | |
| cell.PutValue("Chang"); | |
| cell = cells["C7"]; | |
| cell.PutValue("Chang"); | |
| cell = cells["C8"]; | |
| cell.PutValue("Chang"); | |
| cell = cells["C9"]; | |
| cell.PutValue("Chang"); | |
| cell = cells["C10"]; | |
| cell.PutValue("Chang"); | |
| cell = cells["C11"]; | |
| cell.PutValue("Geitost"); | |
| cell = cells["C12"]; | |
| cell.PutValue("Chai"); | |
| cell = cells["C13"]; | |
| cell.PutValue("Geitost"); | |
| cell = cells["C14"]; | |
| cell.PutValue("Geitost"); | |
| cell = cells["C15"]; | |
| cell.PutValue("Maxilaku"); | |
| cell = cells["C16"]; | |
| cell.PutValue("Geitost"); | |
| cell = cells["C17"]; | |
| cell.PutValue("Geitost"); | |
| cell = cells["C18"]; | |
| cell.PutValue("Ikuru"); | |
| cell = cells["C19"]; | |
| cell.PutValue("Ikuru"); | |
| cell = cells["C20"]; | |
| cell.PutValue("Ikuru"); | |
| cell = cells["C21"]; | |
| cell.PutValue("Ikuru"); | |
| cell = cells["C22"]; | |
| cell.PutValue("Ipoh Coffee"); | |
| cell = cells["C23"]; | |
| cell.PutValue("Ipoh Coffee"); | |
| cell = cells["C24"]; | |
| cell.PutValue("Ipoh Coffee"); | |
| cell = cells["C25"]; | |
| cell.PutValue("Chocolade"); | |
| cell = cells["C26"]; | |
| cell.PutValue("Chocolade"); | |
| cell = cells["C27"]; | |
| cell.PutValue("Chocolade"); | |
| cell = cells["C28"]; | |
| cell.PutValue("Chocolade"); | |
| cell = cells["C29"]; | |
| cell.PutValue("Chocolade"); | |
| cell = cells["C30"]; | |
| cell.PutValue("Chocolade"); | |
| cell = cells["D2"]; | |
| cell.PutValue("Asia"); | |
| cell = cells["D3"]; | |
| cell.PutValue("Asia"); | |
| cell = cells["D4"]; | |
| cell.PutValue("Asia"); | |
| cell = cells["D5"]; | |
| cell.PutValue("Asia"); | |
| cell = cells["D6"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D7"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D8"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D9"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D10"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D11"]; | |
| cell.PutValue("America"); | |
| cell = cells["D12"]; | |
| cell.PutValue("America"); | |
| cell = cells["D13"]; | |
| cell.PutValue("America"); | |
| cell = cells["D14"]; | |
| cell.PutValue("America"); | |
| cell = cells["D15"]; | |
| cell.PutValue("America"); | |
| cell = cells["D16"]; | |
| cell.PutValue("America"); | |
| cell = cells["D17"]; | |
| cell.PutValue("America"); | |
| cell = cells["D18"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D19"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D20"]; | |
| cell.PutValue("Europe"); | |
| cell = cells["D21"]; | |
| cell.PutValue("Oceania"); | |
| cell = cells["D22"]; | |
| cell.PutValue("Oceania"); | |
| cell = cells["D23"]; | |
| cell.PutValue("Oceania"); | |
| cell = cells["D24"]; | |
| cell.PutValue("Oceania"); | |
| cell = cells["D25"]; | |
| cell.PutValue("Africa"); | |
| cell = cells["D26"]; | |
| cell.PutValue("Africa"); | |
| cell = cells["D27"]; | |
| cell.PutValue("Africa"); | |
| cell = cells["D28"]; | |
| cell.PutValue("Africa"); | |
| cell = cells["D29"]; | |
| cell.PutValue("Africa"); | |
| cell = cells["D30"]; | |
| cell.PutValue("Africa"); | |
| cell = cells["E2"]; | |
| cell.PutValue("China"); | |
| cell = cells["E3"]; | |
| cell.PutValue("India"); | |
| cell = cells["E4"]; | |
| cell.PutValue("Korea"); | |
| cell = cells["E5"]; | |
| cell.PutValue("India"); | |
| cell = cells["E6"]; | |
| cell.PutValue("France"); | |
| cell = cells["E7"]; | |
| cell.PutValue("France"); | |
| cell = cells["E8"]; | |
| cell.PutValue("Germany"); | |
| cell = cells["E9"]; | |
| cell.PutValue("Italy"); | |
| cell = cells["E10"]; | |
| cell.PutValue("France"); | |
| cell = cells["E11"]; | |
| cell.PutValue("U.S."); | |
| cell = cells["E12"]; | |
| cell.PutValue("U.S."); | |
| cell = cells["E13"]; | |
| cell.PutValue("Brazil"); | |
| cell = cells["E14"]; | |
| cell.PutValue("U.S."); | |
| cell = cells["E15"]; | |
| cell.PutValue("U.S."); | |
| cell = cells["E16"]; | |
| cell.PutValue("Canada"); | |
| cell = cells["E17"]; | |
| cell.PutValue("U.S."); | |
| cell = cells["E18"]; | |
| cell.PutValue("Italy"); | |
| cell = cells["E19"]; | |
| cell.PutValue("France"); | |
| cell = cells["E20"]; | |
| cell.PutValue("Italy"); | |
| cell = cells["E21"]; | |
| cell.PutValue("New Zealand"); | |
| cell = cells["E22"]; | |
| cell.PutValue("Australia"); | |
| cell = cells["E23"]; | |
| cell.PutValue("Australia"); | |
| cell = cells["E24"]; | |
| cell.PutValue("New Zealand"); | |
| cell = cells["E25"]; | |
| cell.PutValue("S.Africa"); | |
| cell = cells["E26"]; | |
| cell.PutValue("S.Africa"); | |
| cell = cells["E27"]; | |
| cell.PutValue("S.Africa"); | |
| cell = cells["E28"]; | |
| cell.PutValue("Egypt"); | |
| cell = cells["E29"]; | |
| cell.PutValue("Egypt"); | |
| cell = cells["E30"]; | |
| cell.PutValue("Egypt"); | |
| cell = cells["F2"]; | |
| cell.PutValue(2000); | |
| cell = cells["F3"]; | |
| cell.PutValue(500); | |
| cell = cells["F4"]; | |
| cell.PutValue(1200); | |
| cell = cells["F5"]; | |
| cell.PutValue(1500); | |
| cell = cells["F6"]; | |
| cell.PutValue(500); | |
| cell = cells["F7"]; | |
| cell.PutValue(1500); | |
| cell = cells["F8"]; | |
| cell.PutValue(800); | |
| cell = cells["F9"]; | |
| cell.PutValue(900); | |
| cell = cells["F10"]; | |
| cell.PutValue(500); | |
| cell = cells["F11"]; | |
| cell.PutValue(1600); | |
| cell = cells["F12"]; | |
| cell.PutValue(600); | |
| cell = cells["F13"]; | |
| cell.PutValue(2000); | |
| cell = cells["F14"]; | |
| cell.PutValue(500); | |
| cell = cells["F15"]; | |
| cell.PutValue(900); | |
| cell = cells["F16"]; | |
| cell.PutValue(700); | |
| cell = cells["F17"]; | |
| cell.PutValue(1400); | |
| cell = cells["F18"]; | |
| cell.PutValue(1350); | |
| cell = cells["F19"]; | |
| cell.PutValue(300); | |
| cell = cells["F20"]; | |
| cell.PutValue(500); | |
| cell = cells["F21"]; | |
| cell.PutValue(1000); | |
| cell = cells["F22"]; | |
| cell.PutValue(1500); | |
| cell = cells["F23"]; | |
| cell.PutValue(1500); | |
| cell = cells["F24"]; | |
| cell.PutValue(1600); | |
| cell = cells["F25"]; | |
| cell.PutValue(1000); | |
| cell = cells["F26"]; | |
| cell.PutValue(1200); | |
| cell = cells["F27"]; | |
| cell.PutValue(1300); | |
| cell = cells["F28"]; | |
| cell.PutValue(1500); | |
| cell = cells["F29"]; | |
| cell.PutValue(1400); | |
| cell = cells["F30"]; | |
| cell.PutValue(1000); | |
| // Adding a new sheet | |
| Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()]; | |
| // Naming the sheet | |
| sheet2.Name = "PivotTable"; | |
| // Getting the pivottables collection in the sheet | |
| Aspose.Cells.Pivot.PivotTableCollection pivotTables = sheet2.PivotTables; | |
| // Adding a PivotTable to the worksheet | |
| int index = pivotTables.Add("=Data!A1:F30", "B3", "PivotTable1"); | |
| // Accessing the instance of the newly added PivotTable | |
| Aspose.Cells.Pivot.PivotTable pivotTable = pivotTables[index]; | |
| // Showing the grand totals | |
| pivotTable.RowGrand = true; | |
| pivotTable.ColumnGrand = true; | |
| // Setting the PivotTable report is automatically formatted | |
| pivotTable.IsAutoFormat = true; | |
| // Setting the PivotTable autoformat type. | |
| pivotTable.AutoFormatType = Aspose.Cells.Pivot.PivotTableAutoFormatType.Report6; | |
| // Draging the first field to the row area. | |
| pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 0); | |
| // Draging the third field to the row area. | |
| pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 2); | |
| // Draging the second field to the row area. | |
| pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Row, 1); | |
| // Draging the fourth field to the column area. | |
| pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Column, 3); | |
| // Draging the fifth field to the data area. | |
| pivotTable.AddFieldToArea(Aspose.Cells.Pivot.PivotFieldType.Data, 5); | |
| // Setting the number format of the first data field | |
| pivotTable.DataFields[0].NumberFormat = "$#,##0.00"; | |
| // Saving the Excel file | |
| workbook.Save(dataDir+ "pivotTable_test.out.xlsx"); |
Hinzufügen eines Pivot-Diagramms
So erstellen Sie ein PivotChart mit Aspose.Cells:
- Fügen Sie ein Diagramm hinzu.
- Legen Sie die PivotSource des Diagramms fest, um auf eine vorhandene Pivot-Tabelle in der Tabelle zu verweisen.
- Legen Sie andere Attribute fest.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| // For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET | |
| // The path to the documents directory. | |
| string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); | |
| // Instantiating an Workbook object | |
| // Opening the excel file | |
| Workbook workbook = new Workbook(dataDir+ "pivotTable_test.xlsx"); | |
| // Adding a new sheet | |
| Worksheet sheet3 = workbook.Worksheets[workbook.Worksheets.Add(SheetType.Chart)]; | |
| // Naming the sheet | |
| sheet3.Name = "PivotChart"; | |
| // Adding a column chart | |
| int index = sheet3.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 0, 5, 28, 16); | |
| // Setting the pivot chart data source | |
| sheet3.Charts[index].PivotSource = "PivotTable!PivotTable1"; | |
| sheet3.Charts[index].HidePivotFieldButtons = false; | |
| // Saving the Excel file | |
| workbook.Save(dataDir+ "pivotChart_test_out.xlsx"); |