Find and Replace Data

In a spreadsheet with hundreds of rows and columns, it becomes difficult to look for specific chunks of data across the entire worksheet and even more cumbersome to edit this information. The find and replace feature makes it easy for users to locate information and replace it within seconds, thereby saving both time and efforts.

DsExcel Java enables users to locate data in a cell range, find specific information (and all its occurrences) across the worksheet and replace it with the desired information. Using this feature, you can find and replace specific values and formulas in a range as per custom requirements and preferences with the help of the following methods.

The find method of the IRange interface can be used to find the first, next or the previously matched cell range.
The replace method of the IRange interface can be used to replace the data within the cell range.

Users can find basic information, locate cells with different formats, search data using various options, enumerate all occurences across the worksheet, match the number of bytes occupied by the data and look for specific data in different places including comments, formula and text. Further, you can replace the basic information, replace via executing the search operation in loop and also replace using several options (like match case, match whole word and match byte).

Refer to the following example code in order to find cells in a target range starting from multiple positions and replace it with the desired information.

// Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);

Object data = new Object[][] { 
{ "Name", "City", "Birthday", "Sex", "Weight", "Height", "Age" },
{ "Bob", "newyork", new GregorianCalendar(1968, 6, 8), "male", 80, 180, 56 },
{ "Betty", "newyork", new GregorianCalendar(1972, 7, 3), "female", 72, 168, 45 },
{ "Gary", "NewYork", new GregorianCalendar(1964, 3, 2), "male", 71, 179, 50 },
{ "Hunk", "Washington", new GregorianCalendar(1972, 8, 8), "male", 80, 171, 59 },
{ "Cherry", "Washington", new GregorianCalendar(1986, 2, 2), "female", 58, 161, 34 },
{ "Coco", "Virginia", new GregorianCalendar(1982, 12, 12), "female", 58, 181, 45 },
{ "Lance", "Chicago", new GregorianCalendar(1962, 3, 12), "female", 49, 160, 57 },
{ "Eva", "Washington", new GregorianCalendar(1993, 2, 5), "female", 71, 180, 81 } };

// Set data
worksheet.getRange("A1:G9").setValue(data);
worksheet.getRange("I10:P19").setValue(data);
worksheet.getRange("A21:G29").setValue(data);

String what = "newyork";
String replacement = "NewYork";
ReplaceOptions ro = new ReplaceOptions();
ro.setMatchCase(true);

// Specify range to search in
IRange searchRange = worksheet.getRange("A1:G9,I10:P19");
        
// Using Replace method to replace content in a specific range
searchRange.replace(what, replacement, ro);

// Saving workbook to xlsx
workbook.save("FindAndReplaceData.xlsx", SaveFileFormat.Xlsx);

Refer to the following example code in order to find cells with the formula "SUM" and replace it with another formula "PRODUCT" simultaneously.

// Initialize workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);
        
// Set formulas
worksheet.getRange("A1:H5").setFormula("SUM(6,10)");

FindOptions fo = new FindOptions();
fo.setLookIn(FindLookIn.Formulas);

IRange range = null;
        
// Specify range to search in formulas
IRange searchRange = worksheet.getRange("A1:B4");
do 
{
 range = searchRange.find("SUM", range, fo);
 if (range != null) 
 {
  // Using Replace method to replace formula in searched range
  range.setFormulaArray(range.getFormula().replace("SUM", "PRODUCT"));
 }
} while (range != null);

// Saving workbook to xlsx
workbook.save("FindAndReplaceFormulas.xlsx", SaveFileFormat.Xlsx);

Find Pictures in Cells

When a cell contains a picture, the behavior of the Find operation depends on the LookAt mode:

LookAt.Part: The Find operation succeeds if the search string is contained within the picture’s alt text.
LookAt.Whole: The Find operation always returns no match, because the rich data in the cell is not a plain string.

To retrieve all cells that contain in-cell pictures, use the SpecialCellType.CellPicture enumeration. For more details, see Find Cells with In-Cell Pictures.

Refer to the following example code to find pictures in cells.

// Create a new workbook.
Workbook workbook = new Workbook();
IWorksheet sheet = workbook.getActiveSheet();

try {
    sheet.getRange("A1").setValue("Pictures");

    // Add pictures in cells with alt text.    
    byte[] imageData = Files.readAllBytes(Paths.get("Picture1.png"));
    sheet.getRange("A2").setCellPicture(new CellPicture(imageData, "Dress"));

    byte[] imageData2 = Files.readAllBytes(Paths.get("Picture2.png"));
    sheet.getRange("A3").setCellPicture(new CellPicture(imageData2, "T-shirt"));

    byte[] imageData3 = Files.readAllBytes(Paths.get("Picture4.png"));
    sheet.getRange("A4").setCellPicture(new CellPicture(imageData3));

    byte[] imageData4 = Files.readAllBytes(Paths.get("Picture5.png"));
    sheet.getRange("A5").setCellPicture(new CellPicture(imageData4));

    // Find text within the picture alt text using LookAt.Part mode.    
    FindOptions findOptions = new FindOptions();
    findOptions.setLookAt(LookAt.Part);
    IRange result = sheet.getRange("A1:D10").find("Dress", findOptions);

    if (result != null) {
        System.out.println("Found at " + result.getAddress()); // Output: Found at $A$2  
    }
} catch (IOException e) {
    throw new RuntimeException("Error loading image resource", e);