Skapa pivottabeller och pivotdiagram

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

  1. Installera Aspose.Cells for Python via .NET från pypi, använd kommandot: $ pip install aspose-cells-python.
  2. 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:

  1. 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.
  2. Lägg till en pivot tabell till arbetsbladet genom att anropa PivotTables -samlingens add-metod (inkapslad i Worksheet-objektet).
  3. 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:

  1. Lägg till en graf.
  2. Ange grafens PivotSource så att den hänvisar till en befintlig pivot tabell i kalkylarket.
  3. 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")