Search and Replace Data in a Range

Possible Usage Scenarios

Sometimes you need to search for and replace specific data in a range ignoring any cell values outside the desired range. Aspose.Cells for Python via .NET allows you to limit a search to a specific range. This article explains how to search and replace data in a range in Excel using Python code.

How to Search and Replace Data in a Range using Aspose.Cells for Python Excel Library

Aspose.Cells for Python via .NET provides the FindOptions.set_range() method for specifying a range when searching data. Below code sample search and replace data in a range.

from aspose.cells import CellArea, FindOptions, LookAtType, LookInType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  The path to the documents directory.
dataDir = RunExamples.GetDataDir(".")
filePath = dataDir + "input.xlsx"
workbook = Workbook(filePath)
worksheet = workbook.worksheets[0]
#  Specify the range where you want to search
#  Here the range is E3:H6
area = CellArea.create_cell_area("E9", "H15")
#  Specify Find options
opts = FindOptions()
opts.look_in_type = LookInType.VALUES
opts.look_at_type = LookAtType.ENTIRE_CONTENT
opts.set_range(area)

cell = None
while True:
    cell = worksheet.cells.find("search", cell, opts)
    if cell:
        cell.put_value("replace")
    else:
        break

#  Save the workbook
workbook.save(dataDir + "output.out.xlsx")