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

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

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

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

Автофильтр в 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

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

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

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

В приведенном ниже примере мы создали тот же AutoFilter, используя Aspose.Cells, что и создали с помощью Microsoft Excel в предыдущем разделе.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Instantiating a Workbook object
Workbook workbook = new Workbook("AFData.xls");

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

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

// Saving the modified Excel file
workbook.save("AFData_out.xls");

// Print message
System.out.println("Process completed successfully");

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

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

Цвет заливки

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

1. ColouredCells.xlsx
2. FilteredColouredCells.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook("ColouredCells.xlsx");

// Instantiating a CellsColor object for foreground color
CellsColor clrForeground = workbook.createCellsColor();
clrForeground.setColor(Color.getRed());

// Instantiating a CellsColor object for background color
CellsColor clrBackground = workbook.createCellsColor();
clrBackground.setColor(Color.getWhite());

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

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

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

// Saving the modified Excel file
workbook.save("FilteredColouredCells.xlsx");

// Print message
System.out.println("Process completed successfully");

Дата

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

1. Date.xlsx
2. FilteredDate.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook("Date.xlsx");

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

// Call AddDateFilter function to apply the filter
worksheet.getAutoFilter().addDateFilter(0, 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("FilteredDate.xlsx");

// Print message
System.out.println("Process completed successfully");

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

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

1. Date.xlsx
2. FilteredDynamicDate.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook("Date.xlsx");

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

// Call DynamicFilter function to apply the filter
worksheet.getAutoFilter().dynamicFilter(0, DynamicFilterType.JANUARY);

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

// Saving the modified Excel file
workbook.save("FilteredDynamicDate.xlsx");

Число

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

1. Number.xlsx
2. FilteredNumber.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook("Date.xlsx");

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

// Call DynamicFilter function to apply the filter
worksheet.getAutoFilter().dynamicFilter(0, DynamicFilterType.JANUARY);

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

// Saving the modified Excel file
workbook.save("FilteredDynamicDate.xlsx");

Текст

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

1. Text.xlsx
2. FilteredText.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

// Instantiating a Workbook object
// Opening the Excel file through the file stream
Workbook workbook = new Workbook("Text.xlsx");

// Accessing the first worksheet in the Excel file
Worksheet 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("FilteredText.xlsx");

Пустые

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

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

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

// Accessing the first worksheet in the Excel file
Worksheet 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("FilteredBlank.xlsx");

Не пустые

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

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

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

// Accessing the first worksheet in the Excel file
Worksheet 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("FilteredBlank.xlsx");

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

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

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

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

// Accessing the first worksheet in the Excel file
Worksheet 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, FilterOperatorType.CONTAINS, "Ba");

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

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

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

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

1. sourseSampleCountryNames.xlsx.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

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

// Accessing the first worksheet in the Excel file
Worksheet 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, FilterOperatorType.NOT_CONTAINS, "Ba");

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

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

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

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

1. sourseSampleCountryNames.xlsx.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

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

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Creating AutoFilter by giving the cells range
worksheet.AutoFilter.Range = "A1:A18";

// Initialize filter for rows starting with string "Ba"
worksheet.AutoFilter.Custom(0, FilterOperatorType.BeginsWith, "Ba");

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

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

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

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

1. sourseSampleCountryNames.xlsx.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java

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

// Accessing the first worksheet in the Excel file
Worksheet 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, FilterOperatorType.ENDS_WITH, "ia");

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

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

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

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