Home > Code > C# > How to Search & Replace Data in a Range of Excel Workbook inside .NET Applications

How to Search & Replace Data in a Range of Excel Workbook inside .NET Applications

by sher azam   on Nov 04, 2015   Category: C#   |  Views: 571    |  Points: 25   |  Gold 


This technical tip shows how to Search and Replace Data in a Range in Excel workbooks inside .NET Applications. Sometime, you need to search for and replace specific data in a range, ignoring any cell values outside the desired range. Aspose.Cells allows you to limit a search to a specific range. This article explains how. Aspose.Cells provides the FindOptions.SetRange() method for specifying a range when searching data. Suppose you want to search for the string "search" and replace it with "replace" in the range E3:H6. In the screenshot on blog pag, the string "search" can be seen in several cells but we want to replace it only in a given range, here highlighted in yellow. After the execution of the code, the output file looks like the below. All "search" strings within the range have been replaced with "replace".

//[C# Code Example]

string filePath = @"F:\source.xlsx";

Workbook workbook = new Workbook(filePath);

Worksheet worksheet = workbook.Worksheets[0];

//Specify the range where you want to search
//Here the range is E3:H6
CellArea area = CellArea.CreateCellArea("E3", "H6");

//Specify Find options
FindOptions opts = new FindOptions();
opts.LookInType = LookInType.Values;
opts.LookAtType = LookAtType.EntireContent;
opts.SetRange(area);

Cell cell = null;

do
{
//Search the cell with value search within range
cell = worksheet.Cells.Find("search", cell, opts);

//If no such cell found, then break the loop
if (cell == null)
break;

//Replace the cell with value replace
cell.PutValue("replace");

} while (true);

//Save the workbook
workbook.Save("output.xlsx");


[VB C.NETode Example]


Dim filePath As String = "F:\source.xlsx"

Dim m_workbook As Workbook = New Workbook(filePath)

Dim m_worksheet As Worksheet = m_workbook.Worksheets(0)

'Specify the range where you want to search
'Here the range is E3:H6
Dim area As CellArea = CellArea.CreateCellArea("E3", "H6")

'Specify Find options
Dim opts As FindOptions = New FindOptions()
opts.LookInType = LookInType.Values
opts.LookAtType = LookAtType.EntireContent
opts.SetRange(area)

Dim m_cell As Cell = Nothing

Do
'Search the cell with value "search" within range
m_cell = m_worksheet.Cells.Find("search", m_cell, opts)

'If no such cell found, then break the loop
If m_cell Is Nothing Then
Exit Do
End If

'Replace the cell with value "replace"
m_cell.PutValue("replace")

Loop While True

'Save the workbook
m_workbook.Save("F:\output.xlsx")



More about Aspose.Cells for .NET

- Homepage of Aspose.Cells for .NET: http://www.aspose.com/categories/.net-components/aspose.cells-for-.net/default.aspx

- Read More Technical Tips by Aspose.Cells for .NET at: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/technical-articles.html



Post Code  |  Code Snippet Home

User Responses


No response found, be the first to review this code snippet.

Submit feedback about this code snippet

Please sign in to post feedback

Latest Posts