In Microsoft Excel, we can easily find the data we want by using the find function. But how to achieve this programmatically? With Spire.XLS for WPF, not only can we find the specific data in excel with high-efficiency, but also we can highlight it with different color.
In this article, we’ll demonstrate how to find and highlight excel data using Spire.XLS for WPF in C# and VB.NET.
At first, please download Spire.XLS and install it correctly, then add Spire.XLS.Wpf.dll and Spire.License.dll from the installation folder as reference.
Below is the effective screenshot:
Detail steps:
Use namespace:
using System.Windows; using Spire.Xls; using System.Drawing;
Step 1: Initialize a new Workbook instance and load the sample excel document from file.
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
Step 2: Get its first worksheet, then find and highlight the specific number 1502 in worksheet 1.
Worksheet sheet = workbook.Worksheets[0];
foreach (CellRange range in sheet.FindAllNumber(1502,true))
{
range.Style.Color = Color.LawnGreen;
}
Apart from finding number, Spire.XLS also supports us to find string, datetime, bool, etc.
Step 3: Save and launch the file.
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
Full codes:
private void button1_Click(object sender, RoutedEventArgs e)
{
//load the sample excel document from file
Workbook workbook = new Workbook();
workbook.LoadFromFile("Sample.xlsx");
//find and highlight excel data
Worksheet sheet = workbook.Worksheets[0];
foreach (CellRange range in sheet.FindAllNumber(1502,true))
{
range.Style.Color = Color.LawnGreen;
}
//save and launch the file
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
}
Private Sub button1_Click(sender As Object, e As RoutedEventArgs)
'load the sample excel document from file
Dim workbook As New Workbook()
workbook.LoadFromFile("Sample.xlsx")
'find and highlight excel data
Dim sheet As Worksheet = workbook.Worksheets(0)
For Each range As CellRange In sheet.FindAllNumber(1502,True)
range.Style.Color = Color.LawnGreen
Next
'save and launch the file
workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("result.xlsx")
End Sub
