Crear tablas dinámicas y gráficos dinámicos
Contents
[
Hide
]
Una tabla dinámica es un resumen interactivo de registros. Por ejemplo, puede tener cientos de entradas de factura en una lista en una hoja de cálculo. Una tabla dinámica puede totalizar las facturas por cliente, producto o fecha. Con Microsoft Excel es posible reorganizar rápidamente la información en la tabla dinámica arrastrando botones a una nueva posición.
Un gráfico dinámico es una representación gráfica interactiva de los datos en una tabla dinámica. Los gráficos dinámicos se introdujeron en Excel 2000. El uso de un gráfico dinámico facilita aún más la comprensión de los datos, ya que la tabla dinámica crea subtotales y totales automáticamente.
Aspose.Cells para Python via .NET soporta tablas dinámicas y gráficos dinámicos.
Agregar Tablas Dinámicas y Gráficos utilizando la Biblioteca de Aspose.Cells para Python Excel
Aspose.Cells para Python via .NET provee un conjunto especial de clases utilizadas para crear tablas dinámicas. Estas clases se utilizan para crear y establecer objetos PivotTable, que actúan como los elementos básicos de construcción de un objeto de tabla dinámica:
- PivotField, un campo en un informe de tabla dinámica.
- PivotFields, una colección de todos los objetos PivotField en una tabla dinámica.
- PivotTable, un informe de tabla dinámica en una hoja de cálculo.
- PivotTables, una colección de todos los objetos PivotTable en la hoja de cálculo.
Prepararse para utilizar Aspose.Cells para Python via .NET
- Instalar Aspose.Cells para Python via .NET desde pypi, utilizando el comando: $ pip install aspose-cells-python.
- También puedes seguir las instrucciones paso a paso sobre cómo instalar “Aspose.Cells para Python via .NET” en tu entorno de desarrollo.
Cómo Añadir una Tabla Dinámica Utilizando la Biblioteca de Aspose.Cells para Python Excel
Para crear una tabla dinámica utilizando Aspose.Cells para Python via .NET:
- Añadir algunos datos a las celdas de una hoja de cálculo utilizando el método put_value de un objeto Celda. También puedes utilizar un archivo de plantilla ya lleno de datos. Los datos se utilizarán como fuente de datos de la tabla dinámica.
- Agregue una tabla dinámica a la hoja de cálculo llamando al método add de la colección PivotTables (encapsulada en el objeto Worksheet).
- Accede al nuevo objeto PivotTable desde la colección PivotTables pasando su índice. # Usa cualquiera de los objetos de tabla dinámica encapsulados en el objeto PivotTable para gestionar la tabla.
A continuación se muestran ejemplos de código.
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") |
Cómo agregar un gráfico dinámico usando Aspsoe.Cells para la biblioteca de Excel de Python
Para crear un PivotChart utilizando Aspose.Cells para Python via .NET:
- Agregue un gráfico.
- Establezca el PivotSource del gráfico para hacer referencia a una tabla dinámica existente en la hoja de cálculo.
- Establezca otros atributos.
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") |