The Find function in Excel is one of the most commonly used functions for quickly locating specified data, and users can also highlight the data to make it more obvious. In this article, you will learn how to programmatically find and highlight cells with a specific value using Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Find and Highlight Data in Excel
The detailed steps are as follows.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Find all cells with matching text using Worksheet.FindAllString(string stringValue, bool formula, bool formulaValue) method.
- Set color to highlight the cells using CellRange.Style.Color property.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using System.Drawing;
namespace FindHighlight
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel file
workbook.LoadFromFile("Test.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Find all cells with the text "Regulator System"
foreach (CellRange range in sheet.FindAllString("Regulator System", true, true))
{
//Set color to highlight the cells
range.Style.Color = Color.Yellow;
}
//Save the result file
workbook.SaveToFile("FindHighlight.xlsx", ExcelVersion.Version2016);
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
