Search and Replace Data in a Range
Aspose.Cells provides the FindOptions.setRange() method for specifying a range when searching for data.
Suppose you want to search for the string “search” and replace it with “replace” in the range E3:H6. In the screenshot below, the string “search” can be seen in several cells but we want to replace it only in a given range, here highlighted in yellow.
Input file
After the execution of the code, the output file looks like the below. All “search” strings within the range have been replaced with “replace”.
Output file
// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java | |
// The path to the documents directory. | |
String dataDir = Utils.getDataDir(SearchReplaceDataInRange.class); | |
Workbook workbook = new Workbook(dataDir + "input.xlsx"); | |
Worksheet worksheet = workbook.getWorksheets().get(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.setLookInType(LookInType.VALUES); | |
opts.setLookAtType(LookAtType.ENTIRE_CONTENT); | |
opts.setRange(area); | |
Cell cell = null; | |
do { | |
// Search the cell with value search within range | |
cell = worksheet.getCells().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(dataDir + "output.xlsx"); |