Find And Highlight Data In Excel Using C# & SPIRE.XLS

Introduction

 

Hi Everyone..!! Recently I got an opportunity from a China-Based company, E-iceblue, to review one of their products. So I selected Spire.Pdf from E-iceblue.The article was Convert pdf to other format using Spire.Pdf. Now I am going introduce Spire.XLS. The Spire.XLS for .NET is a professional Excel .NET component that can be used to any type of .NET 2.0, 3.5, 4.0 or 4.5 framework application, both ASP.NET web sites and Windows Forms applications. Spire.XLS for .NET offers object model Excel API for speeding up Excel programming in .NET platform – create new Excel documents from template, edit existing Excel documents, and convert Excel files.

 

Namespace :

 

The following code contain “Spire.Xls”. This library contains the functionality of highlight data in excel,Excel Version,Excel Location,etc. 

using Spire.Xls;
using System.Drawing;

Code :

using Spire.Xls;
using System.Drawing;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //load an excel file from system
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"C:\Users\RAJEESH\Desktop\TestSpireXls.xlsx", ExcelVersion.Version2013);

            //find and highlight excel data
            Worksheet sheet = workbook.Worksheets[0];
            foreach (CellRange range in sheet.FindAllString("Palakkad",true,true))
            {
                range.Style.Color = Color.Green;
            }
            //save and launch the project
            workbook.SaveToFile("NewProduct.xlsx", ExcelVersion.Version2013);
            System.Diagnostics.Process.Start("NewProduct.xlsx");
        }
    }
}

Creating the instance of PdfDocument

Workbook workbook = new Workbook();

Load the file location & Selecting the version of Excel

 

Loading the location of excel file for “TestSpireXls.xlsx“. We need to specify the version of excel file on loading the process.

workbook.LoadFromFile(@"C:\Users\RAJEESH\Desktop\TestSpireXls.xlsx", ExcelVersion.Version2013);

The following excel versions are contain in Spire.Xls.

• ODS
• Version2007
• Version2010
• Version2013
• Version97to2003
• Xlsb2007
• Xlsb2010

Find and Highlight Excel Data

 

The following codeis  finding string as “Palakkad” in excel sheet. So we can change this based on our requirement. Other finding options and Color options are specified in Pic 1 & Pic 2 respectively. 

Worksheet sheet = workbook.Worksheets[0];
foreach (CellRange range in sheet.FindAllString("Palakkad",true,true))
{
    range.Style.Color = Color.Green;
}

Pic 1:

 

 

 

Pic 2 :

 

 

 

Save and Launch The Project 

 

Highlighted data saved into the new excel sheet “NewProduct.xlsx“. 

workbook.SaveToFile("NewProduct.xlsx", ExcelVersion.Version2013);
System.Diagnostics.Process.Start("NewProduct.xlsx");

Before Highlighting Data In Excel :

 

 

 

OutPut :

 

 

 

Pros : 

• Very fast conversion
• Easy to handle code
• Less code with quality output

Reference : 

• SpireXls 
• Tech Blog 

Summary

 

We learned how to highlight data in Excel Using C# & Spire.Xls. I hope this article is useful for all beginners.