Фильтрация данных

Microsoft Excel предоставляет хорошие функции для автоматического фильтрации данных листа. Aspose.Cells полностью поддерживает функции автоматического фильтра Excel. Эта статья объясняет, как использовать эти функции в Excel и как программировать их с помощью Aspose.Cells for Node.js via C++.

Автофильтрация данных

Автофильтрация - самый быстрый способ выбрать только те элементы с листа, которые вы хотите отображать в списке. Функция автофильтрации позволяет фильтровать элементы в списке в соответствии с набором критериев. Отфильтруйте на основе текста, чисел или дат.

Автофильтр в Microsoft Excel

Чтобы активировать функцию автофильтра в Microsoft Excel:

1. Щелкните строку заголовка на листе.
2. В меню Данные выберите Фильтр, а затем Автофильтр.

При применении автофильтра к листу появляются переключатели фильтра (черные стрелки) справа от заголовков столбцов.

1. Щелкните стрелку фильтра, чтобы увидеть список вариантов фильтра.

Некоторые из вариантов автофильтра:

Опции	Описание
All	Показать все элементы в списке один раз.
Custom	Настроить критерии фильтрации, такие как содержит/не содержит.
Filter by Color	Фильтрация на основе заполненного цвета.
Date Filters	Фильтрация строк на основе различных критериев по дате.
Number Filters	Несколько видов фильтров для чисел, таких как сравнение, средние значения и Топ-10 и т.д.
Text Filters	Различные фильтры, такие как начинается с, заканчивается на, содержит и т. д.
Blanks/Non Blanks	Эти фильтры могут быть применены с помощью пустого текстового фильтра.

Пользователи вручную фильтруют данные своего листа в Microsoft Excel, используя эти опции.

Автофильтр с Aspose.Cells for Node.js via C++

Aspose.Cells предоставляет класс, Workbook, который представляет файл Excel. Класс Workbook содержит коллекцию Worksheets, которая позволяет получить доступ к каждому рабочему листу в файле Excel.

Лист представлен классом Worksheet. Класс Worksheet предоставляет широкий спектр свойств и методов для управления листами. Для создания автофильтра используйте свойство AutoFilter класса Worksheet. Свойство AutoFilter является объектом класса AutoFilter, который предоставляет свойство Range для указания диапазона ячеек, образующих строку заголовка. Автофильтр применяется к диапазону ячеек, который является строкой заголовка.

Для каждого листа вы можете указать только один диапазон фильтра. Это ограничено Microsoft Excel. Для настраиваемой фильтрации данных используйте метод AutoFilter.Custom.

В приведённом ниже примере мы создали тот же автофильтр с помощью Aspose.Cells for Node.js via C++, что и в Microsoft Excel, описанном выше.

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new AsposeCells.Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Creating AutoFilter by giving the cells range of the heading row
worksheet.getAutoFilter().setRange("A1:B1");

// Saving the modified Excel file
workbook.save(dataDir + "output.out.xls");

Различные типы фильтров

Aspose.Cells предоставляет несколько вариантов применения различных типов фильтров, таких как Фильтр по цвету, Фильтр по дате, Фильтр по числам, Фильтр по тексту, Фильтры для заполненных ячеек и незаполненных ячеек.

Цвет заливки

Aspose.Cells предоставляет функцию AddFillColorFilter для фильтрации данных на основе цвета заливки ячеек. В приведенном ниже примере используется файл-шаблон, содержащий различные цвета заливки в первом столбце листа, для тестирования функции фильтрации по цвету. Образцы файлов можно скачать по следующим ссылкам.

1. ColouredCells.xlsx
2. FilteredColouredCells.xlsx

const AsposeCells = require("aspose.cells.node");
const path = require("path");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Source directory
const sourceDir = dataDir;

// Output directory
const outputDir = dataDir;

// Instantiating a Workbook object
// Opening the Excel file through the file stream
let workbook = new AsposeCells.Workbook(path.join(sourceDir, "ColouredCells.xlsx"));

// Instantiating a CellsColor object for foreground color
let clrForeground = workbook.createCellsColor();
clrForeground.color = AsposeCells.Color.fromArgb(255, 0, 0); // Red color

// Instantiating a CellsColor object for background color
let clrBackground = workbook.createCellsColor();
clrBackground.color = AsposeCells.Color.fromArgb(255, 255, 255); // White color

// Accessing the first worksheet in the Excel file
let worksheet = workbook.getWorksheets().get(0);

// Call AddFillColorFilter function to apply the filter
worksheet.getAutoFilter().addFillColorFilter(0, AsposeCells.BackgroundType.Solid, clrForeground, clrBackground);

// Call refresh function to update the worksheet
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(path.join(outputDir, "FilteredColouredCells.xlsx"));

Дата

Можно реализовать различные типы фильтров дат, например фильтрацию всех строк с датами в январе 2018 года. Следующий пример демонстрирует этот фильтр с помощью функции AddDateFilter. Примерные файлы приведены ниже.

1. Date.xlsx
2. FilteredDate.xlsx

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const sourceDir = path.join(__dirname, "data");
const outputDir = path.join(__dirname, "output");

// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new AsposeCells.Workbook(path.join(sourceDir, "Date.xlsx"));

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Call AddDateFilter function to apply the filter
worksheet.getAutoFilter().addDateFilter(0, AsposeCells.DateTimeGroupingType.Month, 2018, 1, 0, 0, 0, 0);

// Call refresh function to update the worksheet
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(path.join(outputDir, "FilteredDate.xlsx"));

Динамическая дата

Иногда требуются динамические фильтры на основе даты, например, все ячейки с датами в январе независимо от года. В этом случае используется функция DynamicFilter, как показано в приведенном ниже образце кода. Примеры файлов даны ниже.

1. Date.xlsx
2. FilteredDynamicDate.xlsx

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const sourceDir = path.join(__dirname, "data/");
const outputDir = path.join(__dirname, "output/");

// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new AsposeCells.Workbook(`${sourceDir}Date.xlsx`);

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Call DynamicFilter function to apply the filter
worksheet.getAutoFilter().dynamic_Filter(0, AsposeCells.DynamicFilterType.January);

// Call refresh function to update the worksheet
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(`${outputDir}FilteredDynamicDate.xlsx`);

Число

Пользовательские фильтры могут быть применены с использованием Aspose.Cells, например, выбор ячеек с числами в заданном диапазоне. В следующем примере демонстрируется использование функции Custom() для фильтрации чисел. Приведены примеры файлов.

1. Number.xlsx
2. FilteredNumber.xlsx

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const sourceDir = path.join(__dirname, "data");
const outputDir = path.join(__dirname, "data");

// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new AsposeCells.Workbook(path.join(sourceDir, "Number.xlsx"));

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Call Custom function to apply the filter
worksheet.getAutoFilter().custom(0, AsposeCells.FilterOperatorType.GreaterOrEqual, 5, true, AsposeCells.FilterOperatorType.LessOrEqual, 10);

// Call refresh function to update the worksheet
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(path.join(outputDir, "FilteredNumber.xlsx"));

Текст

Если столбец содержит текст, и необходимо выбрать ячейки с определённым текстом, можно использовать функцию Filter(). В следующем примере файл-шаблон содержит список стран, и необходимо выбрать строки с названием конкретной страны. Следующий код демонстрирует фильтрацию текста. Примерные файлы приведены ниже.

1. Text.xlsx
2. FilteredText.xlsx

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const sourceDir = path.join(__dirname, "data");
const outputDir = path.join(__dirname, "output");

// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new AsposeCells.Workbook(path.join(sourceDir, "Text.xlsx"));

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Call Filter function to apply the filter
worksheet.getAutoFilter().filter(0, "Angola");

// Call refresh function to update the worksheet
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(path.join(outputDir, "FilteredText.xlsx"));

Пустые

Если столбец содержит текст так, что некоторые ячейки пусты, и требуется выбрать только те строки, где присутствуют пустые ячейки, используйте функцию MatchBlanks(), как показано ниже. Примеры файлов даны ниже.

1. Пустой.xlsx
2. ОтфильтрованныйПустой.xlsx

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Source directory
const sourceDir = path.join(dataDir, "source/");

// Output directory
const outputDir = path.join(dataDir, "output/");

// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new AsposeCells.Workbook(sourceDir + "Blank.xlsx");

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Call MatchBlanks function to apply the filter
worksheet.getAutoFilter().matchBlanks(0);

// Call refresh function to update the worksheet
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(outputDir + "FilteredBlank.xlsx");

Не пустые

Когда нужно отфильтровать ячейки с любым текстом, используйте функцию фильтрации MatchNonBlanks, как показано ниже. Примеры файлов даны ниже.

1. Пустой.xlsx
2. ОтфильтрованныеНеПустой.xlsx

const path = require("path");
const AsposeCells = require("aspose.cells.node");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Source directory
const sourceDir = dataDir + "/"; // Assuming sourceDir is stored here
// Output directory
const outputDir = dataDir + "/"; // Assuming outputDir is stored here

// Instantiating a Workbook object
// Opening the Excel file through the file stream
const workbook = new AsposeCells.Workbook(sourceDir + "Blank.xlsx");

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Call MatchNonBlanks function to apply the filter
worksheet.getAutoFilter().matchNonBlanks(0);

// Call refresh function to update the worksheet
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(outputDir + "FilteredNonBlank.xlsx");

Пользовательский фильтр с содержит

Excel предоставляет пользовательские фильтры, такие как фильтрация строк, содержащих определенную подстроку. Эта функция доступна в Aspose.Cells и демонстрируется ниже путем фильтрации имен в примере файла. Приведены примеры файлов.

1. ИсходныйОбразецНазванийСтран.xlsx
2. outSourseSampleCountryNames.xlsx.

const AsposeCells = require("aspose.cells.node");
const path = require("path");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Instantiating a Workbook object containing sample data
let workbook = new AsposeCells.Workbook(path.join(dataDir, "sourseSampleCountryNames.xlsx"));

// Accessing the first worksheet in the Excel file
let worksheet = workbook.getWorksheets().get(0);

// Creating AutoFilter by giving the cells range
worksheet.getAutoFilter().setRange("A1:A18");

// Initialize filter for rows containing string "Ba"
worksheet.getAutoFilter().custom(0, AsposeCells.FilterOperatorType.Contains, "Ba");

// Refresh the filter to show/hide filtered rows
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(path.join(dataDir, "outSourseSampleCountryNames.xlsx"));

Пользовательский фильтр с не содержит

Excel предоставляет пользовательские фильтры, такие как фильтр строк, не содержащих определённую строку. Эта функция доступна в Aspose.Cells и продемонстрирована ниже на примере фильтрации имён в указанных ниже файлах.

1. sourseSampleCountryNames.xlsx.

const AsposeCells = require("aspose.cells.node");
const path = require("path");

// The path to the documents directory.
const dataDir = path.join(__dirname, "data");

// Instantiating a Workbook object containing sample data
const workbook = new AsposeCells.Workbook(path.join(dataDir, "sourseSampleCountryNames.xlsx"));

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Creating AutoFilter by giving the cells range
worksheet.getAutoFilter().setRange("A1:A18");

// Initialize filter for rows containing string "Ba"
worksheet.getAutoFilter().custom(0, AsposeCells.FilterOperatorType.NotContains, "Be");

// Refresh the filter to show/hide filtered rows
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(path.join(dataDir, "outSourseSampleCountryNames.xlsx"));

Пользовательский фильтр с начинается с

Excel предоставляет пользовательские фильтры, такие как фильтр строк с началом на определённую строку. Эта функция доступна в Aspose.Cells и показана ниже на примере фильтрации имён в приведённых файлах.

1. sourseSampleCountryNames.xlsx.

const AsposeCells = require("aspose.cells.node");
const path = require("path");

// The path to the documents directory.
const sourceDir = path.join(__dirname, "data/");
const outputDir = path.join(__dirname, "output/");

// Instantiating a Workbook object containing sample data
let workbook = new AsposeCells.Workbook(sourceDir + "sourseSampleCountryNames.xlsx");

// Accessing the first worksheet in the Excel file
let worksheet = workbook.getWorksheets().get(0);

// Creating AutoFilter by giving the cells range
worksheet.getAutoFilter().setRange("A1:A18");

// Initialize filter for rows starting with string "Ba"
worksheet.getAutoFilter().custom(0, AsposeCells.FilterOperatorType.BeginsWith, "Ba");

// Refresh the filter to show/hide filtered rows
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(outputDir + "outSourseSampleCountryNames.xlsx");

Пользовательский фильтр с EndsWith

Excel предоставляет пользовательские фильтры, такие как фильтрация строк, которые заканчиваются определенной строкой. Эта функция доступна в Aspose.Cells и демонстрируется ниже по фильтру имен в предоставленном образце файла.

1. sourseSampleCountryNames.xlsx.

const AsposeCells = require("aspose.cells.node");
const path = require("path");

// The path to the documents directory.
const sourceDir = path.join(__dirname, "data/");
const outputDir = path.join(__dirname, "output/");

// Instantiating a Workbook object containing sample data
const workbook = new AsposeCells.Workbook(sourceDir + "sourseSampleCountryNames.xlsx");

// Accessing the first worksheet in the Excel file
const worksheet = workbook.getWorksheets().get(0);

// Creating AutoFilter by giving the cells range
worksheet.getAutoFilter().setRange("A1:A18");

// Initialize filter for rows end with string "ia"
worksheet.getAutoFilter().custom(0, AsposeCells.FilterOperatorType.BeginsWith, "ia");

// Refresh the filter to show/hide filtered rows
worksheet.getAutoFilter().refresh();

// Saving the modified Excel file
workbook.save(outputDir + "outSourseSampleCountryNames.xlsx");

Продвинутые темы

• Применение расширенного фильтра Microsoft Excel для отображения записей, удовлетворяющих сложным критериям
• Получить все скрытые индексы строк после обновления автофильтра