Replace text

The example shows you how to open a workbook and replace text. Replace searches the cells of all worksheets, the rich text runs inside them, the comments, the headers, the footers and the text of shapes, and returns the number of replaced occurrences. The format of the cell and the properties of all runs remain unchanged. Formulas are not searched by default because a replacement could make a formula invalid.

import com.independentsoft.office.spreadsheet.Workbook;

public class Example {

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

            int count = book.replace("2025", "2026");

            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.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");

            //the comments, headers, footers and shapes are not searched
            FindOptions options = new FindOptions(SearchScope.CELLS);

            book.replace("2025", "2026", options);

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

import com.independentsoft.office.spreadsheet.Run;
import com.independentsoft.office.spreadsheet.Text;
import com.independentsoft.office.spreadsheet.Workbook;

public class Example {

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

            Run run = new Run();
            run.setText(new Text("overdue"));
            run.setBold(true);

            //a cell holding plain text is converted into a rich text cell,
            //the text before and after the match keeps its format
            book.replace("{status}", run);

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

JSpreadsheet > Tutorial > Replace text

Need help? Ask our developers: