1. Overview
In this tutorial, we’ll learn how to add an image to a cell in an Excel file with Java.
We’ll create an Excel file dynamically and add an image to the cell, using apache-poi.
2. Project Setup and Dependencies
Java applications can use apache-poi to read, write, and modify the contents of an Excel spreadsheet dynamically. It supports both .xls and .xlsx Excel formats.
2.1. Maven Dependency for Apache Poi API
First, let’s add the poi dependency to our project:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.5</version>
</dependency>
2.2. Excel Workbook Creation
First, let’s create a workbook and sheet to write in. We can choose either XSSFWorkbook, which works with .xlsx files, or HSSFWorkbook, which works with .xls files. Let’s use XSSFWorkbook:
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Avengers");
Row row1 = sheet.createRow(0);
row1.createCell(0).setCellValue("IRON-MAN");
Row row2 = sheet.createRow(1);
row2.createCell(0).setCellValue("SPIDER-MAN");
Here, we’ve created an Avengers sheet and filled the A1 and A2 cells with two names. Next, we’ll add the Avengers’ images to cells B1 and B2.
3. Insert Image in the Workbook
3.1. Read Image From Local File
To add the images, we’ll first need to read them from our project directory. For our project, we have two images in the resources directory:
- /src/main/resources/ironman.png
- /src/main/resources/spiderman.png
InputStream inputStream1 = TestClass.class.getClassLoader()
.getResourceAsStream("ironman.png");
InputStream inputStream2 = TestClass.class.getClassLoader()
.getResourceAsStream("spiderman.png");
Next, let’s convert the images into byte arrays. Here, we’ll use IOUtils from apache-poi:
byte[] inputImageBytes1 = IOUtils.toByteArray(inputStream1);
byte[] inputImageBytes2 = IOUtils.toByteArray(inputStream2);
3.3. Add Picture in the Workbook
Now, we’ll use the byte array to add a picture to our workbook. The supported picture types are PNG, JPG, and DIB. We’re using PNG here:
int inputImagePictureID1 = workbook.addPicture(inputImageBytes1, Workbook.PICTURE_TYPE_PNG);
int inputImagePictureID2 = workbook.addPicture(inputImageBytes2, Workbook.PICTURE_TYPE_PNG);
As a result of this step, we’ll obtain an index of each picture that we’ll use for creating Drawing objects.
3.4. Create a Drawing Container
The drawing patriarch is the top-level container for all shapes. This will return a Drawing interface – in our case, the XSSFDrawing object. We’ll use this object to create pictures that we will put into our defined cell.
Let’s create the drawing patriarch:
XSSFDrawing drawing = (XSSFDrawing) sheet.createDrawingPatriarch();
4. Add Image in a Cell
Now, we’re ready to add the images into our cells.
4.1. Create Anchor Object
First, we’ll create a client anchor object, which is attached to an Excel worksheet and is used to set the position of the image in an Excel sheet. It anchors against a top-left and bottom-right cell.
We’ll create two anchor objects, one for each image:
XSSFClientAnchor ironManAnchor = new XSSFClientAnchor();
XSSFClientAnchor spiderManAnchor = new XSSFClientAnchor();
Next, we need to specify the relative positions of the images to our anchor objects.
Let’s place our first image in cell B1:
ironManAnchor.setCol1(1); // Sets the column (0 based) of the first cell.
ironManAnchor.setCol2(2); // Sets the column (0 based) of the Second cell.
ironManAnchor.setRow1(0); // Sets the row (0 based) of the first cell.
ironManAnchor.setRow2(1); // Sets the row (0 based) of the Second cell.
In the same way, we’ll place the second image in cell B2:
spiderManAnchor.setCol1(1);
spiderManAnchor.setCol2(2);
spiderManAnchor.setRow1(1);
spiderManAnchor.setRow2(2);
4.2. Add Anchor Object and Picture Index to Drawing Container
Now, let’s call createPicture on the drawing patriarch to add an image. We’ll use the previously created anchor object and picture index of our images:
drawing.createPicture(ironManAnchor, inputImagePictureID1);
drawing.createPicture(spiderManAnchor, inputImagePictureID2);
5. Save Workbook
Before we save, let’s make sure the cells are wide enough for the pictures we’ve added by using autoSizeColumn:
for (int i = 0; i < 3; i++) {
sheet.autoSizeColumn(i);
}
Finally, let’s save the workbook:
try (FileOutputStream saveExcel = new FileOutputStream("target/baeldung-apachepoi.xlsx")) {
workbook.write(saveExcel);
}
The resulting Excel sheet should look like this:
6. Conclusion
In this article, we learned how to add an image to the cell of an Excel worksheet in Java using the apache-poi library.
We needed to load the image, convert it into bytes, attach it to the sheet, and then use the drawing tools to locate the image in the correct cells. Finally, we were able to resize the columns and save our workbook.
As always, the example code for this article is available over on GitHub.
