Фильтрация сводных таблиц по метке или значению

Введение

Сводные таблицы — это мощный аналитический инструмент, однако необработанные сводки часто содержат значительно больше информации, чем требуется для представления. Фильтрация является основным механизмом сужения сводной таблицы до строк, столбцов или значений, важных для конкретного отчёта. Aspose.Cells for Python via Java повторяет возможности фильтрации, доступные в Microsoft Excel, предоставляя их программно, чтобы формирование отчётов могло быть полностью автоматизировано. В данной статье рассматриваются следующие стратегии фильтрации:

  1. Фильтр по метке — фильтрует элементы поля строки или столбца на основе их текстовых меток.
  2. Фильтр по дате — фильтрует поля строк или столбцов, которые содержат только значения даты и времени (или пустые значения).
  3. Фильтр по значению — фильтрует элементы на основе агрегированных значений поля данных.
  4. Фильтр «первые 10» — показывает только верхние или нижние N элементов, ранжированных по полю значений.
  5. Скрытие/отображение элементов сводной таблицы — позволяет вручную управлять видимостью каждого отдельного элемента в поле. Каждый подход использует свой метод класса PivotField или свойство класса PivotItem. После применения любого фильтра необходимо вызвать refresh_data() и calculate_data() для сводной таблицы, чтобы кэшированные данные и вычисленные значения отражали новое состояние фильтра.

Фильтр по метке

Фильтр по метке позволяет фильтровать элементы поля строки или столбца путём сравнения их текстовых подписей с шаблоном. Это полезно, когда требуется отобразить только те продукты, названия которых начинаются с определённой буквы, содержат конкретное слово или соответствуют другому критерию, основанному на подписи. Aspose.Cells предоставляет фильтрацию по меткам через метод PivotField.filter_by_label(PivotFilterType, str). Перечисление PivotFilterType включает такие значения, как CaptionBeginsWith, CaptionContains, CaptionEndsWith, CaptionDoesNotContain, CaptionIsNotBlank, CaptionIsBlank и другие. Второй аргумент задаёт строку метки, используемую для сравнения. В следующем примере загружается рабочая книга, содержащая существующую сводную таблицу, применяется фильтр по метке, чтобы остались видны только элементы, подписи которых начинаются с указанного префикса, сводная таблица обновляется и результат сохраняется.

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook
from asposecells.api import Workbook, PivotFilterType

fileName = "sample.xlsx"
prefix = "B"

# Загружаем существующую рабочую книгу, содержащую сводную таблицу
workbook = Workbook(fileName)

# Получаем доступ к листу по индексу (первый лист)
worksheet = workbook.getWorksheets().get(0)

# Получаем доступ к сводной таблице по индексу
pivotTable = worksheet.getPivotTables().get(0)

# Получаем первое поле строки PivotField
rowField = pivotTable.getRowFields().get(0)

# Применяем фильтр по метке — отображаем только элементы строк, метки которых начинаются с указанного префикса
rowField.filterByLabel(PivotFilterType.CaptionBeginsWith, prefix, "")

# Обновляем и пересчитываем данные сводной таблицы, чтобы фильтр вступил в силу
pivotTable.getPivotCache().refresh()

# Сохраняем рабочую книгу обратно на диск
workbook.save(fileName)

jpype.shutdownJVM()

Фильтр по дате

Фильтры по дате позволяют сузить сводную таблицу по критериям, основанным на дате, таким как сегодня, прошлая неделя, этот месяц, следующий квартал или конкретный диапазон дат. Это специализированные фильтры, которые работают только с полями, содержащими информацию о дате и времени.

Aspose.Cells предоставляет фильтрацию по дате через метод PivotField.filter_by_date(PivotFilterType, values). Перечисление PivotFilterType содержит специальные значения для дат, такие как Today, Yesterday, LastWeek, ThisWeek, NextWeek, LastMonth, ThisMonth, NextMonth, LastQuarter, ThisQuarter, NextQuarter, LastYear, ThisYear, NextYear и Between. В зависимости от выбранного типа фильтра передаётся одно или два значения DateTime (для Between передаются начальная и конечная даты). В следующем примере загружается рабочая книга со сводной таблицей, в области строк которой находится поле даты, применяется фильтр по дате, ограничивающий видимые элементы определённым диапазоном дат, сводная таблица обновляется и рабочая книга сохраняется.

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook
from asposecells.api import Workbook, PivotFilterType

inputPath = "sample.xlsx"
outputPath = "output_filtered.xlsx"

if not os.path.exists(inputPath):
    raise FileNotFoundError(f"Source workbook not found: {inputPath}")

# Загрузка существующей книги, содержащей сводную таблицу
workbook = Workbook(inputPath)

# Доступ к листу, содержащему сводную таблицу (по индексу)
worksheet = workbook.getWorksheets().get(0)

# Доступ к сводной таблице по индексу
pivotTable = worksheet.getPivotTables().get(0)

# Получение поля даты из области строк
# (Фильтр по дате работает только когда область строк/столбцов содержит только ячейки с датой-временем или пустые ячейки)
dateField = pivotTable.getRowFields().get(0)

# Определение критерия даты для фильтра "Между"
Date = jpype.JClass("java.util.Date")
startDate = Date(2020 - 1900, 0, 1)
endDate = Date(2020 - 1900, 11, 31)

# Применение фильтра по дате к полю сводной таблицы
dateField.filterByDate(PivotFilterType.DateBetween, startDate, endDate)

# Обновление и пересчёт сводной таблицы, чтобы фильтр вступил в силу
pivotTable.getPivotCache().refresh()

# Сохранение книги
workbook.save(outputPath)

jpype.shutdownJVM()

Фильтр по значению

Фильтры по значениям работают с агрегированными значениями, которые сводная таблица вычисляет в области данных. Вместо сопоставления текстовых меток они сравнивают числовые итоги с пороговым значением. Типичные варианты использования включают отображение только тех продуктов, сумма продаж которых превышает целевую сумму, или только тех регионов, количество транзакций в которых попадает в определённый диапазон. Aspose.Cells предоставляет фильтрацию по значениям через метод PivotField.filter_by_value(value_field, filter_type, values). Параметр filter_type использует такие значения, как ValueGreaterThan, ValueLessThan, ValueBetween, ValueEqual, ValueNotEqual, ValueGreaterThanOrEqual и ValueLessThanOrEqual. Параметр value_field указывает, какое поле данных должно оцениваться, а последний аргумент (или аргументы) задаёт пороговое значение (или значения). В следующем примере загружается рабочая книга со сводной таблицей, применяется фильтр по значению, который оставляет только элементы, агрегированные продажи которых превышают числовой порог, сводная таблица обновляется и рабочая книга сохраняется.

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook
from asposecells.api import Workbook, PivotFilterType

workbook = Workbook("sample.xlsx")
worksheet = workbook.getWorksheets().get(0)
pivotTable = worksheet.getPivotTables().get(0)

rowField = pivotTable.getRowFields().get(0)
dataField = pivotTable.getDataFields().get(0)

# Найти индекс поля данных вручную, так как PivotFieldCollection не имеет IndexOf
dataFieldIndex = -1
for i in range(pivotTable.getDataFields().getCount()):
    if pivotTable.getDataFields().get(i) == dataField:
        dataFieldIndex = i
        break

if dataFieldIndex >= 0:
    rowField.filterByValue(dataFieldIndex, PivotFilterType.VALUE_GREATER_THAN, 5000, float('inf'))

pivotTable.getPivotCache().refresh()

workbook.save("output.xlsx")

jpype.shutdownJVM()

Фильтр «первые 10»

Фильтр «первые 10» — это специализированная форма фильтра по значению, который сохраняет только верхние или нижние N элементов на основе выбранного поля значений. Он часто используется в отчётах с ранжированием, таких как «10 лучших продуктов по выручке» или «5 худших регионов по количеству продаж».

Aspose.Cells предоставляет фильтрацию «первые 10» через метод PivotField.filter_top10(item_count, is_top, value_field, filter_type). Параметр item_count определяет, сколько элементов следует оставить, is_top указывает, оставлять ли верхние элементы (true) или нижние (false), value_field ссылается на поле данных, используемое для ранжирования, а filter_type управляет способом вычисления значения (обычно Sum, а также Count и Percent). В следующем примере загружается рабочая книга со сводной таблицей, содержащей поле значений, применяется фильтр «первые 10», чтобы оставить только 10 лучших элементов по сумме продаж, сводная таблица обновляется и рабочая книга сохраняется.

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook
from asposecells.api import Workbook, Worksheet, PivotTable, PivotField, PivotFilterType

# Загрузить существующую рабочую книгу, содержащую сводную таблицу
inputPath = "input.xlsx"
outputPath = "output.xlsx"
workbook = Workbook(inputPath)

# Получить доступ к листу, на котором находится сводная таблица (индекс 0)
worksheet = workbook.getWorksheets().get(0)

# Получить доступ к сводной таблице по индексу
pivotTable = worksheet.getPivotTables().get(0)

# Убедиться, что в области данных есть хотя бы одно поле значений (value PivotField)
if pivotTable.getDataFields().getCount() == 0:
    raise Exception("Сводная таблица не имеет поля значений (data) PivotField.")
valueField = pivotTable.getDataFields().get(0)

# Получить целевое поле строки (row PivotField), к которому применяется фильтр Top 10
rowField = pivotTable.getRowFields().get(0)

# Первое (и единственное) поле данных находится по индексу 0; Top 10 ранжирует по нему
valueFieldIndex = 0

# Применить фильтр Top 10 к полю строки:
#   - itemCount   = 10
#   - filterType  = PivotFilterType.Sum
#   - isTop       = true (верхние N; false означало бы нижние N)
#   - valueFieldIndex = индекс поля данных, используемого для ранжирования элементов
rowField.filterTop10(10, PivotFilterType.Sum, True, valueFieldIndex)

# Обновить данные сводной таблицы и пересчитать её, чтобы фильтр вступил в силу
pivotTable.getPivotCache().refresh()

# Сохранить рабочую книгу
workbook.save(outputPath)

jpype.shutdownJVM()

Фильтрация путём скрытия или отображения элементов сводной таблицы

Помимо структурированных API фильтрации, Aspose.Cells позволяет напрямую управлять видимостью каждого отдельного элемента сводной таблицы. Перебирая коллекцию PivotItems объекта PivotField и переключая свойство is_hidden, можно выборочно подавлять определённые элементы без применения фильтра на основе формулы. Установка is_hidden = True скрывает элемент из сводной таблицы; установка is_hidden = False отображает его снова. Этот подход полезен, когда правило фильтрации является нерегулярным или специфичным для элемента, например при сокрытии небольшого числа именованных категорий, которые не должны отображаться в конкретном отчёте. В примере ниже загружается сводная таблица, скрывается определённый элемент по имени, демонстрируется, как его снова отобразить, сводная таблица обновляется и рабочая книга сохраняется.

import jpype
import asposecells
jpype.startJVM()
from asposecells.api import Workbook
from asposecells.api import Workbook, Worksheet, Cells, Range, SaveFormat, PivotTable, PivotField, PivotItem

# Загружаем существующую книгу, содержащую сводную таблицу
workbook = Workbook("pivot_table_sample.xlsx")

# Получаем доступ к первому листу, содержащему сводную таблицу
sheet = workbook.getWorksheets().get(0)

# Получаем доступ к сводной таблице по индексу (первая сводная таблица на листе)
pivotTable = sheet.getPivotTables().get(0)

# Получаем целевое поле PivotField (первое поле строки, в котором будем скрывать/отображать элементы)
pivotField = pivotTable.getRowFields().get(0)

# Перебираем коллекцию PivotItems выбранного PivotField
itemCount = pivotField.getPivotItems().getCount()
for i in range(itemCount):
    item = pivotField.getPivotItems().get(i)

    # Скрываем элементы сводной таблицы, соответствующие определённому имени/критерию
    if item.getName() == "Item1" or item.getName() == "Item2":
        item.setIsHidden(True)

    # Демонстрация отображения: повторно показываем ранее скрытый элемент сводной таблицы
    if item.getName() == "Item3":
        item.setIsHidden(False)

# Обновляем и пересчитываем сводную таблицу, чтобы изменения вступили в силу
pivotTable.getPivotCache().refresh()

# Сохраняем книгу — скрытые элементы остаются в исходных данных
# но исключаются из отображаемой сводной таблицы
workbook.save("output_pivot_filtered.xlsx")

jpype.shutdownJVM()

Резюме

Aspose.Cells for Python via Java предоставляет полный набор возможностей фильтрации сводных таблиц, соответствующий возможностям Microsoft Excel. Фильтры по меткам, дате и значениям охватывают наиболее распространённые аналитические сценарии, тогда как фильтр «первые 10» предназначен для отчётов с ранжированием. Когда правило фильтрации является нерегулярным, свойство PivotItem.is_hidden предлагает гибкий резервный вариант на уровне отдельных элементов. Комбинирование этих стратегий — например, применение фильтра по метке с последующим скрытием определённых элементов — позволяет формировать точно нацеленные отчёты сводных таблиц полностью из кода.