In Excel, cells can be filtered based on the cell color. This article is going to show you how to filter rows by cell color using Spire.XLS.
The example Excel file:
Detail steps:
Step 1: Instantiate a Workbook object and load the Excel file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Step 2: Get the first worksheet.
Worksheet sheet = workbook.Worksheets[0];
Step 3: Add a color filter to filter cells based on cell color.
//Create an auto filter in the sheet and specify the range to be filterd sheet.AutoFilters.Range = sheet.Range["A1:A9"]; //Get the coloumn to be filterd FilterColumn filtercolumn = (FilterColumn)sheet.AutoFilters[0]; //Add a color filter to filter the column based on cell color sheet.AutoFilters.AddFillColorFilter(filtercolumn, Color.Red);
Step 4: Filter the data.
sheet.AutoFilters.Filter();
Step 5: Save the file.
workbook.SaveToFile("ColorFilter.xlsx", ExcelVersion.Version2013);
Screenshot:
Full code:
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet.AutoFilter;
namespace FilterCells
{
class Program
{
static void Main(string[] args)
{
//Instantiate a Workbook object
Workbook workbook = new Workbook();
//Load the Excel file
workbook.LoadFromFile("sample.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Create an auto filter in the sheet and specify the range to be filterd
sheet.AutoFilters.Range = sheet.Range["A1:A9"];
//Get the coloumn to be filterd
FilterColumn filtercolumn = (FilterColumn)sheet.AutoFilters[0];
//Add a color filter to filter the column based on cell color
sheet.AutoFilters.AddFillColorFilter(filtercolumn, Color.Red);
//Filter the data
sheet.AutoFilters.Filter();
//Save the file
workbook.SaveToFile("ColorFilter.xlsx", ExcelVersion.Version2013);
}
}
}
