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: 881    |  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

Recent Posts

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