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

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

Данные автофильтра

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

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

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

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

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

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

Некоторые параметры автофильтра:

Параметры	Описание
Все	Показать все элементы в списке один раз.
Обычай	Настройте критерии фильтра, такие как содержит/не содержит
Фильтр по цвету	Фильтры на основе заполненного цвета
Фильтры даты	Фильтрует строки по разным критериям по дате
Числовые фильтры	Различные типы фильтров по числам, такие как сравнение, средние значения, 10 лучших и т. д.
Текстовые фильтры	Различные фильтры, такие как начинается с, заканчивается, содержит и т. д.
Пустые/не пустые	Эти фильтры могут быть реализованы с помощью пустого текстового фильтра.

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

Автофильтр с Aspose.Cells

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

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

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

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

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

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

// Creating AutoFilter by giving the cells range of the heading row
worksheet.AutoFilter.Range = "A1:B1";

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

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

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

Цвет заливки

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

1. ColoredCells.xlsx
2. FilteredColoredCells.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

//Output directory
string outputDir = RunExamples.Get_OutputDirectory();

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

// Instantiating a CellsColor object for foreground color
CellsColor clrForeground = workbook.CreateCellsColor();
clrForeground.Color = Color.Red;

// Instantiating a CellsColor object for background color
CellsColor clrBackground = workbook.CreateCellsColor();
clrBackground.Color = Color.White;

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

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

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

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

Датировать

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

1. Дата.xlsx
2. Фильтредате.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.

//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

//Output directory
string outputDir = RunExamples.Get_OutputDirectory();

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

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

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

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

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

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

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

1. Дата.xlsx
2. FilteredDynamicDate.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.

//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

//Output directory
string outputDir = RunExamples.Get_OutputDirectory();

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

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

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

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

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

Число

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

1. Номер.xlsx
2. Фильтредномер.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.

//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

//Output directory
string outputDir = RunExamples.Get_OutputDirectory();

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

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

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

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

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

Текст

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

1. Текст.xlsx
2. Фильтрованный текст.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.

//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

//Output directory
string outputDir = RunExamples.Get_OutputDirectory();

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

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

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

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

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

Бланки

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

1. Пустой.xlsx
2. FilteredBlank.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.

//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

//Output directory
string outputDir = RunExamples.Get_OutputDirectory();

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

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

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

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

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

Не пустые

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

1. Пустой.xlsx
2. Фильтреднонбланк.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.

//Source directory
string sourceDir = RunExamples.Get_SourceDirectory();

//Output directory
string outputDir = RunExamples.Get_OutputDirectory();

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

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

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

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

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

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

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

1. sourceSampleCountryNames.xlsx
2. аутсаурсесамплекатринамес.xlsx.

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

// Instantiating a Workbook object containing sample data
Workbook workbook = new Workbook("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 containing string "Ba"
worksheet.AutoFilter.Custom(0, FilterOperatorType.Contains, "Ba");

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

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

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

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

1. sourceSampleCountryNames.xlsx.

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

// Instantiating a Workbook object containing sample data
Workbook workbook = new Workbook("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 containing string "Ba"
worksheet.AutoFilter.Custom(0, FilterOperatorType.NotContains, "Be");

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

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

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

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

1. sourceSampleCountryNames.xlsx.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// 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. sourceSampleCountryNames.xlsx.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// 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 end with string "ia"
worksheet.AutoFilter.Custom(0, FilterOperatorType.BeginsWith, "ia");

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

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

Предварительные темы

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