Создание сводных таблиц и сводных графиков
Сводная таблица - это интерактивная сводка записей. Например, у вас может быть сотни записей о счетах-фактурах в списке на листе. Сводная таблица может подсчитать счета-фактуры по клиенту, продукту или дате. С помощью Microsoft Excel можно быстро переставить информацию в сводной таблице, перетаскивая кнопки на новую позицию.
Сводный график - это интерактивное графическое представление данных в сводной таблице. Сводные графики были введены в Excel 2000. Использование сводного графика делает понимание данных еще проще, поскольку сводная таблица автоматически создает итоги и подитоги.
Aspose.Cells для Python via .NET поддерживает сводные таблицы и сводные диаграммы.
Добавление сводных таблиц и диаграмм с использованием Aspose.Cells для Python Excel Library.
Aspose.Cells для Python via .NET предоставляет особый набор классов, используемых для создания сводных таблиц. Эти классы используются для создания и установки объектов PivotTable, которые являются основными строительными блоками объекта PivotTable:
- PivotField, поле в отчете сводной таблицы.
- PivotFields, коллекция всех объектов PivotField в сводной таблице.
- PivotTable, отчет PivotTable на листе.
- PivotTables, коллекция всех объектов PivotTable на листе.
Подготовка к использованию Aspose.Cells для Python via .NET
- Установите Aspose.Cells для Python via .NET с pypi, используя команду: $ pip install aspose-cells-python.
- Вы также можете следовать пошаговым инструкциям о том, как установить “Aspose.Cells для Python via .NET” в свою среду разработки.
Как добавить сводную таблицу, используя Aspose.Cells для Python Excel Library
Чтобы создать сводную таблицу с помощью Aspose.Cells для Python via .NET:
- Добавьте некоторые данные в ячейки листа книги Excel с помощью метода put_value объекта Cell. Вы также можете использовать файл-шаблон, уже заполненный данными. Данные будут использоваться в качестве источника данных для сводной таблицы.
- Добавьте сводную таблицу на лист с помощью метода add коллекции PivotTables (инкапсулированной в объекте Worksheet).
- Обратитесь к новому объекту PivotTable из коллекции PivotTables, передав его индекс. # Используйте любые из инкапсулированных объектов сводной таблицы в объекте PivotTable для управления таблицей.
Приведены примеры кода ниже.
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") |
Как добавить сводную диаграмму с использованием Aspose.Cells для Python Excel Library
Чтобы создать сводную диаграмму с помощью Aspose.Cells для Python via .NET:
- Добавьте диаграмму.
- Установите источник данных диаграммы так, чтобы он ссылался на существующую сводную таблицу в электронной таблице.
- Задайте другие атрибуты.
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") |