How to add a PivotChart using Aspose.Cells for Python via .NET
Contents
[
Hide
]
What is PivotChart
A pivot chart is a visual representation of the data in a pivot table. Pivot charts provide a way to summarize, analyze, explore, and present summary data. Here are some key features and aspects of pivot charts:
-
Dynamic Data Representation: Pivot charts automatically update to reflect changes in the pivot table. If you add or remove fields in the pivot table, the pivot chart updates accordingly.
-
Interactive: Pivot charts are interactive, allowing users to filter, sort, and drill down into data. This makes it easy to explore different aspects of the data set.
-
Flexible Layout: Users can change the layout of the pivot chart by dragging and dropping fields, which offers flexibility in how data is visualized.
-
Various Chart Types: Pivot charts can be created using various chart types such as bar charts, line charts, pie charts, and more, depending on the nature of the data and the insights you wish to gain.
-
Summarization: Pivot charts summarize large amounts of data and can show totals, averages, counts, or other summary statistics.
-
Filtering: They provide filtering capabilities, allowing you to display only the data that meets certain criteria.
Pivot charts are commonly used in business intelligence and data analysis to provide a clear and concise visual summary of complex data sets. They are a powerful tool for making data-driven decisions.
How to add a PivotChart using Aspose.Cells for Python Excel Library
Adding a Pivot Table
To create a pivot table using Aspose.Cells for Python via .NET:
- Add some data to a worksheet cells using a Cell object’s PutValue/setValue method. You also use a template file already filled with data. The data will be used as the pivot table’s data source.
- Add a pivot table to the worksheet by calling the PivotTables collection’s add method (encapsulated in the Worksheet object).
- Access the new PivotTable object from the PivotTables collection by passing its index. # Use any of the pivot table objects encapsulated in the PivotTable object to manage the table.
Code examples are given below.
This file contains 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
| 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") |
Adding a Pivot Chart
To create a PivotChart using Aspose.Cells for Python via .NET:
- Add a chart.
- Set the PivotSource of the chart to refer to an existing pivot table in the spreadsheet.
- Set other attributes.
This file contains 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
| 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") |