[Java] Replace the specified text in Excel worksheets with images

Requirement

Here I have an Excel worksheet, and now I want to replace the following text in the second column with images using Java codes with the help of a free third-party library.

Solution

I used Free Spire.XLS for Java to finish the operation above. First of all, we need to create a development environment – downloading and installing JDK and Intellij IDEA. Then we can get the package of the third-party library from the link, find Spire.Xls.jar in the “lib” folder and finally add it to IDEA. Or we can reference the Jar file by using the following Maven configurations.

<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.com/nexus/content/groups/public/</url>
</repository>
</repositories>
<dependencies>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>

Using the code

You can replace the specified text with images through several steps as follows.
Step 1: Create a Workbook instance and load an Excel document.
Step 2: Get the worksheet we’ll manipulate by index.
Step 3: Find the text string and set its content as null.
Step 4: Get the location of the cell and add an image to it.
Step 5: Save the resulting document to the specified path.
import com.spire.xls.*;

public class ReplaceTextWithImage {
    public static void main(String[] args) {
        //Load an Excel document
        Workbook workbook = new Workbook();
        workbook.loadFromFile("C:\\Users\\Test1\\Desktop\\Sample.xlsx");
        //Get the first worksheet
        Worksheet worksheet = workbook.getWorksheets().get(0);

        //Find the text string "Chinese flag"
        CellRange[] ranges1 = worksheet.findAllString("Chinese flag", false, false);
        for (CellRange range1 : ranges1) {
            //set the text as null
            range1.setText("");

            //get the row and column of the searched range
            int row1 = range1.getRow();
            int column1 = range1.getColumn();
            //Add the image to the searched range
            worksheet.getPictures().add(row1, column1, "C:\\Users\\Test1\\Desktop\\logo1.png", ImageFormatType.Png);

            //Find the text string "American flag"
            CellRange[] ranges2 = worksheet.findAllString("American flag", false, false);
            for (CellRange range2 : ranges2) {
                //set the text as null
                range2.setText("");

                //get the row and column of the searched range
                int row2 = range2.getRow();
                int column2 = range2.getColumn();
                //Add the image to the searched range
                worksheet.getPictures().add(row2, column2, "C:\\Users\\Test1\\Desktop\\logo2.jpg", ImageFormatType.Jpeg);

                //Find the text string "English flag"
                CellRange[] ranges3 = worksheet.findAllString("English flag", false, false);
                for (CellRange range3 : ranges3) {
                    //set the text as null
                    range3.setText("");

                    //get the row and column of the searched range
                    int row3 = range3.getRow();
                    int column3 = range3.getColumn();
                    //Add the image to the searched range
                    worksheet.getPictures().add(row3, column3, "C:\\Users\\Test1\\Desktop\\logo3.jpg", ImageFormatType.Jpeg);

                    //Save the document to file
                    workbook.saveToFile("output/ReplaceTextWithImage.xlsx", ExcelVersion.Version2013);
                }
            }
        }
    }
}

Output