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

Введение

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

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

Фильтр по подписи

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

import com.aspose.cells.*;

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

// Загрузить существующую книгу, содержащую сводную таблицу
Workbook workbook = new Workbook(fileName);

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

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

// Получить первое строковое поле PivotField
PivotField rowField = pivotTable.getRowFields().get(0);

// Применить фильтр по метке - показывать только строковые элементы, метки которых начинаются с указанного префикса
rowField.filterByLabel(PivotFilterType.CAPTION_BEGINS_WITH, prefix, "");

// Обновить и пересчитать данные сводной таблицы, чтобы фильтр вступил в силу
pivotTable.refreshData();

// Сохранить книгу обратно на диск
workbook.save(fileName);

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

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

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

import java.io.File;
import java.io.FileNotFoundException;

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

if (!new File(inputPath).exists())
{
    throw new FileNotFoundException("Source workbook not found: " + inputPath);
}

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

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

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

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

// Определить критерий даты для фильтра Between
DateTime startDate = new DateTime(2020, 1, 1);
DateTime endDate = new DateTime(2020, 12, 31);

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

// Обновить и пересчитать сводную таблицу, чтобы фильтр вступил в силу
pivotTable.refreshData();

// Сохранить книгу
workbook.save(outputPath);

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

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

import com.aspose.cells.*;

Workbook workbook = new Workbook("sample.xlsx");
Worksheet worksheet = workbook.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);

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

// Находим индекс поля данных вручную, так как PivotFieldCollection не имеет IndexOf
int dataFieldIndex = -1;
for (int i = 0; i < pivotTable.getDataFields().getCount(); i++)
{
    if (pivotTable.getDataFields().get(i) == dataField)
    {
        dataFieldIndex = i;
        break;
    }
}

if (dataFieldIndex >= 0)
{
    rowField.filterByValue(dataFieldIndex, PivotFilterType.VALUE_GREATER_THAN, 5000, Double.MAX_VALUE);
}

pivotTable.refreshData();

workbook.save("output.xlsx");

Фильтр первых 10

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

Aspose.Cells предоставляет фильтрацию первых 10 через метод PivotField.filterTop10(int itemCount, boolean isTop, PivotField valueField, PivotFilterType filterType). Параметр itemCount определяет, сколько элементов нужно оставить, isTop указывает, сохранять ли верхние элементы (true) или нижние (false), valueField ссылается на поле данных, используемое для ранжирования, а filterType управляет способом вычисления значения (обычно Sum, а также Count и Percent). В следующем примере загружается рабочая книга со сводной таблицей, содержащей поле значений, применяется фильтр первых 10, оставляющий только 10 наибольших элементов по сумме продаж, после чего сводная таблица обновляется и рабочая книга сохраняется.

import com.aspose.cells.*;

// Загружаем существующую книгу, содержащую сводную таблицу
String inputPath = "input.xlsx";
String outputPath = "output.xlsx";
Workbook workbook = new Workbook(inputPath);

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

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

// Проверяем, что в области данных есть хотя бы одно поле значений PivotField
if (pivotTable.getDataFields().getCount() == 0)
{
    throw new RuntimeException("Сводная таблица не содержит поле значений (данных) PivotField.");
}
PivotField valueField = pivotTable.getDataFields().get(0);

// Получаем целевое строковое PivotField (поле, к которому мы хотим применить Top 10)
PivotField rowField = pivotTable.getRowFields().get(0);

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

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

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

// Сохраняем книгу
workbook.save(outputPath);

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

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

import com.aspose.cells.*;

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

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

// Доступ к сводной таблице по индексу (первая сводная таблица на листе)
PivotTable pivotTable = sheet.getPivotTables().get(0);

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

// Перебор коллекции PivotItems выбранного PivotField
int itemCount = pivotField.getPivotItems().getCount();
for (int i = 0; i < itemCount; i++)
{
    PivotItem item = pivotField.getPivotItems().get(i);

    // Скрытие элементов сводной таблицы, соответствующих определённому имени/критерию
    if (item.getName() == "Item1" || item.getName() == "Item2")
    {
        item.setHidden(true);
    }

    // Демонстрация отображения: повторное отображение ранее скрытого элемента сводной таблицы
    if (item.getName() == "Item3")
    {
        item.setHidden(false);
    }
}

// Обновление и пересчёт сводной таблицы для применения изменений
pivotTable.refreshData();

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

Заключение

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

Пользовательская сортировка в сводной таблице