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

Введение

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

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

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

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

;
using System.IO;
using Aspose.Cells;
using Aspose.Cells.Pivot;

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

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

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

// Получить доступ к сводной таблице по индексу
PivotTable pivotTable = worksheet.PivotTables[0];

// Получить первое поле строки PivotField
PivotField rowField = pivotTable.RowFields[0];

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

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

// Сохранить книгу обратно на диск
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 передаются начальная и конечная даты). В следующем примере загружается рабочая книга со сводной таблицей, в области строк которой находится поле даты, применяется фильтр по дате, ограничивающий видимые элементы определённым диапазоном дат, обновляется сводная таблица и сохраняется рабочая книга.

using System;
using System.IO;
using Aspose.Cells;
using Aspose.Cells.Pivot;

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

if (!File.Exists(inputPath))
{
    throw new FileNotFoundException("Source workbook not found.", inputPath);
}

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

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

// Получаем сводную таблицу по индексу
var pivotTable = worksheet.PivotTables[0];

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

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

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

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

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

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

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

using Aspose.Cells;
using Aspose.Cells.Pivot;

var workbook = new Workbook("sample.xlsx");
var worksheet = workbook.Worksheets[0];
var pivotTable = worksheet.PivotTables[0];

var rowField = pivotTable.RowFields[0];
var dataField = pivotTable.DataFields[0];

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

if (dataFieldIndex >= 0)
{
    rowField.FilterByValue(dataFieldIndex, PivotFilterType.ValueGreaterThan, 5000, double.MaxValue);
}

pivotTable.PivotCache.Refresh();

workbook.Save("output.xlsx");

Топ-10 фильтр

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

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

using System;
using System.IO;
using Aspose.Cells;
using Aspose.Cells.Pivot;

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

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

// Получить доступ к сводной таблице по индексу
PivotTable pivotTable = worksheet.PivotTables[0];

// Убедиться, что в области данных есть хотя бы одно поле значений PivotField
if (pivotTable.DataFields.Count == 0)
{
    throw new InvalidOperationException("Сводная таблица не имеет поля значений (данных) PivotField.");
}
PivotField valueField = pivotTable.DataFields[0];

// Получить целевое строковое поле PivotField (поле, к которому нужно применить Top 10)
PivotField rowField = pivotTable.RowFields[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.PivotCache.Refresh();

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

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

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

System;
using System.IO;
using Aspose.Cells;
using Aspose.Cells.Pivot;

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

// Получаем доступ к первому листу, который содержит сводную таблицу
Worksheet sheet = workbook.Worksheets[0];

// Получаем доступ к сводной таблице по индексу (первая сводная таблица на листе)
PivotTable pivotTable = sheet.PivotTables[0];

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

// Перебираем коллекцию PivotItems выбранного поля сводной таблицы
int itemCount = pivotField.PivotItems.Count;
for (int i = 0; i < itemCount; i++)
{
    PivotItem item = pivotField.PivotItems[i];

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

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

// Обновляем и пересчитываем сводную таблицу, чтобы изменения вступили в силу
pivotTable.PivotCache.Refresh();

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

Резюме

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

Указание абсолютного положения элемента сводной таблицы