Advertisements

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 code is 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:

SpireXls

SpireXls

Pic 2 :

Test2

SpireXls

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 :

Test5

OutPut :

Test4

Pros :

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

Reference : Spire.Xls

Summary

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: