Find and replace options

FindOptions controls how the find and replace methods match text. The search is case sensitive by default. WholeWord matches whole words only, UseRegularExpressions treats the search text as a regular expression and substitutes group references such as $1 in the replacement text, UseWildcards enables the wildcards *, ? and [ ], Scope restricts the search to parts of the workbook and MaxResults limits the number of matches. Formulas are only searched when SearchScope.FORMULAS is set explicitly.

MatchDisplayText also searches number, date and boolean cells by the text Excel displays, that is the value with its number format applied or TRUE and FALSE; a replacement is stored the way typed input would be, so a number stays a number and a date stays a date, while any other text turns the cell into a text cell. The cached result of a formula is found but never replaced. MatchEntireCell matches only when the whole text of the cell equals the search text, like "Match entire cell contents" in Excel. Worksheet restricts the search to one sheet and Range to a cell range such as B2:D10, A:A, 3:7 or 'Sheet 1'!A1:C5. Every result reports its row, column and reference.

import com.independentsoft.office.spreadsheet.FindOptions;
import com.independentsoft.office.spreadsheet.Workbook;

public class Example {

    public static void main(String[] args)
    {
        try
        {
            Workbook book = new Workbook("c:\\test\\input.xlsx");

            FindOptions options = new FindOptions();
            options.setMatchCase(false);
            options.setWholeWord(true);

            //"cat" is replaced, "catalog" is not
            int count = book.replace("cat", "dog", options);

            System.out.println(count + " occurrences replaced.");

            book.save("c:\\test\\output.xlsx", true);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

import com.independentsoft.office.spreadsheet.FindOptions;
import com.independentsoft.office.spreadsheet.Workbook;

public class Example {

    public static void main(String[] args)
    {
        try
        {
            Workbook book = new Workbook("c:\\test\\input.xlsx");

            FindOptions options = new FindOptions();
            options.setUseRegularExpressions(true);

            //"Smith, John" becomes "John Smith"
            book.replace("([A-Za-z]+), ([A-Za-z]+)", "$2 $1", options);

            book.save("c:\\test\\output.xlsx", true);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

import java.util.List;

import com.independentsoft.office.spreadsheet.FindOptions;
import com.independentsoft.office.spreadsheet.FindResult;
import com.independentsoft.office.spreadsheet.SearchScope;
import com.independentsoft.office.spreadsheet.Workbook;

public class Example {

    public static void main(String[] args)
    {
        try
        {
            Workbook book = new Workbook("c:\\test\\input.xlsx");

            FindOptions options = new FindOptions(SearchScope.COMMENTS | SearchScope.FORMULAS);
            options.setMaxResults(10);

            List<FindResult> results = book.find("Sheet1", options);

            for (FindResult result : results)
            {
                System.out.println(result.getStory() + ": " + result.getHostText());
            }
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

import java.util.List;

import com.independentsoft.office.spreadsheet.FindOptions;
import com.independentsoft.office.spreadsheet.FindResult;
import com.independentsoft.office.spreadsheet.Workbook;

public class Example {

    public static void main(String[] args)
    {
        try
        {
            Workbook book = new Workbook("c:\\test\\input.xlsx");

            FindOptions options = new FindOptions();
            options.setMatchDisplayText(true);
            options.setMatchEntireCell(true);
            options.setRange("B2:B100");

            //only the cells of B2:B100 whose whole text is 1234; the cell stays a number
            int count = book.replace("1234", "9999", options);

            System.out.println(count + " cells replaced.");

            List<FindResult> results = book.find("9999", options);

            for (FindResult result : results)
            {
                System.out.println(result.getReference() + ": " + result.getHostText());
            }

            book.save("c:\\test\\output.xlsx", true);
        }
        catch (Exception e)
        {
            System.out.println(e.getMessage());
            e.printStackTrace();
        }
    }
}

JSpreadsheet > Tutorial > Find and replace options

Need help? Ask our developers: