Skapa pivottabeller och pivotdiagram
En pivottabell är en interaktiv sammanfattning av poster. Till exempel kan du ha hundratals faktura-poster i en lista i ett kalkylblad. En pivottabell kan summera fakturorna efter kund, produkt eller datum. Med Microsoft Excel är det möjligt att snabbt omorganisera informationen i pivottabellen genom att dra knappar till en ny position.
Ett pivotdiagram är en interaktiv grafisk representation av data i en pivottabell. Pivotdiagram introducerades i Excel 2000. Att använda ett pivotdiagram gör det ännu enklare att förstå data eftersom pivottabellen skapar delsummer och totaler automatiskt.
Aspose.Cells för Python via .NET stöder pivottabeller och pivottabell-diagram.
Lägg till pivottabeller och diagram med Aspose.Cells för Python Excel-bibliotek
Aspose.Cells för Python via .NET tillhandahåller en speciell uppsättning klasser som används för att skapa pivottabeller. Dessa klasser används för att skapa och ställa in PivotTable-objekt, vilket fungerar som grundläggande byggstenar för ett PivotTable-objekt:
- PivotField, ett fält i en pivottabellrapport.
- PivotFields, en samling av alla PivotField-objekt i en pivottabell.
- PivotTable, en pivottabellrapport på ett kalkylblad.
- PivotTables, en samling av alla PivotTable-objekt på kalkylbladet.
Förbered att använda Aspose.Cells för Python via .NET
- Installera Aspose.Cells for Python via .NET från pypi, använd kommandot: $ pip install aspose-cells-python.
- Du kan också följa steg-för-steg-instruktionerna om hur du installerar “Aspose.Cells för Python via .NET” till din utvecklingsmiljö.
Hur du lägger till en pivottabell med hjälp av Aspose.Cells för Python Excel-bibliotek
För att skapa en pivottabell med Aspose.Cells för Python via .NET:
- Lägg till lite data i ett kalkylbladsceller med hjälp av en Cell-objekts put_value-metod. Du använder också en mallfil som redan är ifylld med data. Datan kommer att användas som pivottabellens datakälla.
- Lägg till en pivot tabell till arbetsbladet genom att anropa PivotTables -samlingens add-metod (inkapslad i Worksheet-objektet).
- Hämta det nya PivotTable-objektet från PivotTables-samlingen genom att ange dess index. # Använd något av de pivottabellobjekt som är inkapslade i PivotTable-objektet för att hantera tabellen.
Kodexempel ges nedan.
from aspose.cells import Workbook | |
from aspose.cells.pivot import PivotFieldType, PivotTableAutoFormatType | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Instantiating an Workbook object | |
workbook = Workbook() | |
# Obtaining the reference of the first worksheet | |
sheet = workbook.worksheets[0] | |
# Name the sheet | |
sheet.name = "Data" | |
cells = sheet.cells | |
# Setting the values to the cells | |
cell = cells.get("A1") | |
cell.put_value("Employee") | |
cell = cells.get("B1") | |
cell.put_value("Quarter") | |
cell = cells.get("C1") | |
cell.put_value("Product") | |
cell = cells.get("D1") | |
cell.put_value("Continent") | |
cell = cells.get("E1") | |
cell.put_value("Country") | |
cell = cells.get("F1") | |
cell.put_value("Sale") | |
cell = cells.get("A2") | |
cell.put_value("David") | |
cell = cells.get("A3") | |
cell.put_value("David") | |
cell = cells.get("A4") | |
cell.put_value("David") | |
cell = cells.get("A5") | |
cell.put_value("David") | |
cell = cells.get("A6") | |
cell.put_value("James") | |
cell = cells.get("A7") | |
cell.put_value("James") | |
cell = cells.get("A8") | |
cell.put_value("James") | |
cell = cells.get("A9") | |
cell.put_value("James") | |
cell = cells.get("A10") | |
cell.put_value("James") | |
cell = cells.get("A11") | |
cell.put_value("Miya") | |
cell = cells.get("A12") | |
cell.put_value("Miya") | |
cell = cells.get("A13") | |
cell.put_value("Miya") | |
cell = cells.get("A14") | |
cell.put_value("Miya") | |
cell = cells.get("A15") | |
cell.put_value("Miya") | |
cell = cells.get("A16") | |
cell.put_value("Miya") | |
cell = cells.get("A17") | |
cell.put_value("Miya") | |
cell = cells.get("A18") | |
cell.put_value("Elvis") | |
cell = cells.get("A19") | |
cell.put_value("Elvis") | |
cell = cells.get("A20") | |
cell.put_value("Elvis") | |
cell = cells.get("A21") | |
cell.put_value("Elvis") | |
cell = cells.get("A22") | |
cell.put_value("Elvis") | |
cell = cells.get("A23") | |
cell.put_value("Elvis") | |
cell = cells.get("A24") | |
cell.put_value("Elvis") | |
cell = cells.get("A25") | |
cell.put_value("Jean") | |
cell = cells.get("A26") | |
cell.put_value("Jean") | |
cell = cells.get("A27") | |
cell.put_value("Jean") | |
cell = cells.get("A28") | |
cell.put_value("Ada") | |
cell = cells.get("A29") | |
cell.put_value("Ada") | |
cell = cells.get("A30") | |
cell.put_value("Ada") | |
cell = cells.get("B2") | |
cell.put_value("1") | |
cell = cells.get("B3") | |
cell.put_value("2") | |
cell = cells.get("B4") | |
cell.put_value("3") | |
cell = cells.get("B5") | |
cell.put_value("4") | |
cell = cells.get("B6") | |
cell.put_value("1") | |
cell = cells.get("B7") | |
cell.put_value("2") | |
cell = cells.get("B8") | |
cell.put_value("3") | |
cell = cells.get("B9") | |
cell.put_value("4") | |
cell = cells.get("B10") | |
cell.put_value("4") | |
cell = cells.get("B11") | |
cell.put_value("1") | |
cell = cells.get("B12") | |
cell.put_value("1") | |
cell = cells.get("B13") | |
cell.put_value("2") | |
cell = cells.get("B14") | |
cell.put_value("2") | |
cell = cells.get("B15") | |
cell.put_value("3") | |
cell = cells.get("B16") | |
cell.put_value("4") | |
cell = cells.get("B17") | |
cell.put_value("4") | |
cell = cells.get("B18") | |
cell.put_value("1") | |
cell = cells.get("B19") | |
cell.put_value("1") | |
cell = cells.get("B20") | |
cell.put_value("2") | |
cell = cells.get("B21") | |
cell.put_value("3") | |
cell = cells.get("B22") | |
cell.put_value("3") | |
cell = cells.get("B23") | |
cell.put_value("4") | |
cell = cells.get("B24") | |
cell.put_value("4") | |
cell = cells.get("B25") | |
cell.put_value("1") | |
cell = cells.get("B26") | |
cell.put_value("2") | |
cell = cells.get("B27") | |
cell.put_value("3") | |
cell = cells.get("B28") | |
cell.put_value("1") | |
cell = cells.get("B29") | |
cell.put_value("2") | |
cell = cells.get("B30") | |
cell.put_value("3") | |
cell = cells.get("C2") | |
cell.put_value("Maxilaku") | |
cell = cells.get("C3") | |
cell.put_value("Maxilaku") | |
cell = cells.get("C4") | |
cell.put_value("Chai") | |
cell = cells.get("C5") | |
cell.put_value("Maxilaku") | |
cell = cells.get("C6") | |
cell.put_value("Chang") | |
cell = cells.get("C7") | |
cell.put_value("Chang") | |
cell = cells.get("C8") | |
cell.put_value("Chang") | |
cell = cells.get("C9") | |
cell.put_value("Chang") | |
cell = cells.get("C10") | |
cell.put_value("Chang") | |
cell = cells.get("C11") | |
cell.put_value("Geitost") | |
cell = cells.get("C12") | |
cell.put_value("Chai") | |
cell = cells.get("C13") | |
cell.put_value("Geitost") | |
cell = cells.get("C14") | |
cell.put_value("Geitost") | |
cell = cells.get("C15") | |
cell.put_value("Maxilaku") | |
cell = cells.get("C16") | |
cell.put_value("Geitost") | |
cell = cells.get("C17") | |
cell.put_value("Geitost") | |
cell = cells.get("C18") | |
cell.put_value("Ikuru") | |
cell = cells.get("C19") | |
cell.put_value("Ikuru") | |
cell = cells.get("C20") | |
cell.put_value("Ikuru") | |
cell = cells.get("C21") | |
cell.put_value("Ikuru") | |
cell = cells.get("C22") | |
cell.put_value("Ipoh Coffee") | |
cell = cells.get("C23") | |
cell.put_value("Ipoh Coffee") | |
cell = cells.get("C24") | |
cell.put_value("Ipoh Coffee") | |
cell = cells.get("C25") | |
cell.put_value("Chocolade") | |
cell = cells.get("C26") | |
cell.put_value("Chocolade") | |
cell = cells.get("C27") | |
cell.put_value("Chocolade") | |
cell = cells.get("C28") | |
cell.put_value("Chocolade") | |
cell = cells.get("C29") | |
cell.put_value("Chocolade") | |
cell = cells.get("C30") | |
cell.put_value("Chocolade") | |
cell = cells.get("D2") | |
cell.put_value("Asia") | |
cell = cells.get("D3") | |
cell.put_value("Asia") | |
cell = cells.get("D4") | |
cell.put_value("Asia") | |
cell = cells.get("D5") | |
cell.put_value("Asia") | |
cell = cells.get("D6") | |
cell.put_value("Europe") | |
cell = cells.get("D7") | |
cell.put_value("Europe") | |
cell = cells.get("D8") | |
cell.put_value("Europe") | |
cell = cells.get("D9") | |
cell.put_value("Europe") | |
cell = cells.get("D10") | |
cell.put_value("Europe") | |
cell = cells.get("D11") | |
cell.put_value("America") | |
cell = cells.get("D12") | |
cell.put_value("America") | |
cell = cells.get("D13") | |
cell.put_value("America") | |
cell = cells.get("D14") | |
cell.put_value("America") | |
cell = cells.get("D15") | |
cell.put_value("America") | |
cell = cells.get("D16") | |
cell.put_value("America") | |
cell = cells.get("D17") | |
cell.put_value("America") | |
cell = cells.get("D18") | |
cell.put_value("Europe") | |
cell = cells.get("D19") | |
cell.put_value("Europe") | |
cell = cells.get("D20") | |
cell.put_value("Europe") | |
cell = cells.get("D21") | |
cell.put_value("Oceania") | |
cell = cells.get("D22") | |
cell.put_value("Oceania") | |
cell = cells.get("D23") | |
cell.put_value("Oceania") | |
cell = cells.get("D24") | |
cell.put_value("Oceania") | |
cell = cells.get("D25") | |
cell.put_value("Africa") | |
cell = cells.get("D26") | |
cell.put_value("Africa") | |
cell = cells.get("D27") | |
cell.put_value("Africa") | |
cell = cells.get("D28") | |
cell.put_value("Africa") | |
cell = cells.get("D29") | |
cell.put_value("Africa") | |
cell = cells.get("D30") | |
cell.put_value("Africa") | |
cell = cells.get("E2") | |
cell.put_value("China") | |
cell = cells.get("E3") | |
cell.put_value("India") | |
cell = cells.get("E4") | |
cell.put_value("Korea") | |
cell = cells.get("E5") | |
cell.put_value("India") | |
cell = cells.get("E6") | |
cell.put_value("France") | |
cell = cells.get("E7") | |
cell.put_value("France") | |
cell = cells.get("E8") | |
cell.put_value("Germany") | |
cell = cells.get("E9") | |
cell.put_value("Italy") | |
cell = cells.get("E10") | |
cell.put_value("France") | |
cell = cells.get("E11") | |
cell.put_value("U.S.") | |
cell = cells.get("E12") | |
cell.put_value("U.S.") | |
cell = cells.get("E13") | |
cell.put_value("Brazil") | |
cell = cells.get("E14") | |
cell.put_value("U.S.") | |
cell = cells.get("E15") | |
cell.put_value("U.S.") | |
cell = cells.get("E16") | |
cell.put_value("Canada") | |
cell = cells.get("E17") | |
cell.put_value("U.S.") | |
cell = cells.get("E18") | |
cell.put_value("Italy") | |
cell = cells.get("E19") | |
cell.put_value("France") | |
cell = cells.get("E20") | |
cell.put_value("Italy") | |
cell = cells.get("E21") | |
cell.put_value("New Zealand") | |
cell = cells.get("E22") | |
cell.put_value("Australia") | |
cell = cells.get("E23") | |
cell.put_value("Australia") | |
cell = cells.get("E24") | |
cell.put_value("New Zealand") | |
cell = cells.get("E25") | |
cell.put_value("S.Africa") | |
cell = cells.get("E26") | |
cell.put_value("S.Africa") | |
cell = cells.get("E27") | |
cell.put_value("S.Africa") | |
cell = cells.get("E28") | |
cell.put_value("Egypt") | |
cell = cells.get("E29") | |
cell.put_value("Egypt") | |
cell = cells.get("E30") | |
cell.put_value("Egypt") | |
cell = cells.get("F2") | |
cell.put_value(2000) | |
cell = cells.get("F3") | |
cell.put_value(500) | |
cell = cells.get("F4") | |
cell.put_value(1200) | |
cell = cells.get("F5") | |
cell.put_value(1500) | |
cell = cells.get("F6") | |
cell.put_value(500) | |
cell = cells.get("F7") | |
cell.put_value(1500) | |
cell = cells.get("F8") | |
cell.put_value(800) | |
cell = cells.get("F9") | |
cell.put_value(900) | |
cell = cells.get("F10") | |
cell.put_value(500) | |
cell = cells.get("F11") | |
cell.put_value(1600) | |
cell = cells.get("F12") | |
cell.put_value(600) | |
cell = cells.get("F13") | |
cell.put_value(2000) | |
cell = cells.get("F14") | |
cell.put_value(500) | |
cell = cells.get("F15") | |
cell.put_value(900) | |
cell = cells.get("F16") | |
cell.put_value(700) | |
cell = cells.get("F17") | |
cell.put_value(1400) | |
cell = cells.get("F18") | |
cell.put_value(1350) | |
cell = cells.get("F19") | |
cell.put_value(300) | |
cell = cells.get("F20") | |
cell.put_value(500) | |
cell = cells.get("F21") | |
cell.put_value(1000) | |
cell = cells.get("F22") | |
cell.put_value(1500) | |
cell = cells.get("F23") | |
cell.put_value(1500) | |
cell = cells.get("F24") | |
cell.put_value(1600) | |
cell = cells.get("F25") | |
cell.put_value(1000) | |
cell = cells.get("F26") | |
cell.put_value(1200) | |
cell = cells.get("F27") | |
cell.put_value(1300) | |
cell = cells.get("F28") | |
cell.put_value(1500) | |
cell = cells.get("F29") | |
cell.put_value(1400) | |
cell = cells.get("F30") | |
cell.put_value(1000) | |
# Adding a new sheet | |
sheet2 = workbook.worksheets[workbook.worksheets.add()] | |
# Naming the sheet | |
sheet2.name = "PivotTable" | |
# Getting the pivottables collection in the sheet | |
pivotTables = sheet2.pivot_tables | |
# Adding a PivotTable to the worksheet | |
index = pivotTables.add("=Data!A1:F30", "B3", "PivotTable1") | |
# Accessing the instance of the newly added PivotTable | |
pivotTable = pivotTables[index] | |
# Showing the grand totals | |
pivotTable.row_grand = True | |
pivotTable.column_grand = True | |
# Setting the PivotTable report is automatically formatted | |
pivotTable.is_auto_format = True | |
# Setting the PivotTable autoformat type. | |
pivotTable.auto_format_type = PivotTableAutoFormatType.REPORT6 | |
# Draging the first field to the row area. | |
pivotTable.add_field_to_area(PivotFieldType.ROW, 0) | |
# Draging the third field to the row area. | |
pivotTable.add_field_to_area(PivotFieldType.ROW, 2) | |
# Draging the second field to the row area. | |
pivotTable.add_field_to_area(PivotFieldType.ROW, 1) | |
# Draging the fourth field to the column area. | |
pivotTable.add_field_to_area(PivotFieldType.COLUMN, 3) | |
# Draging the fifth field to the data area. | |
pivotTable.add_field_to_area(PivotFieldType.DATA, 5) | |
# Setting the number format of the first data field | |
pivotTable.data_fields[0].number_format = "$#,##0.00" | |
# Saving the Excel file | |
workbook.save(dataDir + "pivotTable_test.out.xlsx") |
Hur man lägger till ett pivottabell-diagram med hjälp av Aspsoe.Cells för Python Excel-bibliotek
För att skapa ett pivotdiagram med Aspose.Cells för Python via .NET:
- Lägg till en graf.
- Ange grafens PivotSource så att den hänvisar till en befintlig pivot tabell i kalkylarket.
- Ange andra attribut.
from aspose.cells import SheetType, Workbook | |
from aspose.cells.charts import ChartType | |
# For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET | |
# The path to the documents directory. | |
dataDir = RunExamples.GetDataDir(".") | |
# Instantiating an Workbook object | |
# Opening the excel file | |
workbook = Workbook(dataDir + "pivotTable_test.xlsx") | |
# Adding a new sheet | |
sheet3 = workbook.worksheets[workbook.worksheets.add(SheetType.CHART)] | |
# Naming the sheet | |
sheet3.name = "PivotChart" | |
# Adding a column chart | |
index = sheet3.charts.add(ChartType.COLUMN, 0, 5, 28, 16) | |
# Setting the pivot chart data source | |
sheet3.charts[index].pivot_source = "PivotTable!PivotTable1" | |
sheet3.charts[index].hide_pivot_field_buttons = False | |
# Saving the Excel file | |
workbook.save(dataDir + "pivotChart_test_out.xlsx") |