How to Search & Replace Data in a Range of Excel Workbook inside .NET Applicatio
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 to achieve it. 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 page, 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".
//your code here...//[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")
Url: http://www.aspose.com/.net/excel-component.aspx
Language: C# | User: Sheraz Khan | Created: Nov 4, 2015 | Tags: Search Data in a Range in Excel, Replace Data in a Range in Excel, replace specific data in a range, .NET Excel Component, specify range when searching data, search data in spreadsheet using .NET Search Data in a Range in Excel Replace Data in a Range in Excel replace specific data in a range .NET Excel Component specify range when searching data search data in spreadsheet using .NET