Expand Authors Top

If you have a few years of experience in the Java ecosystem and you’d like to share that with the community, have a look at our Contribution Guidelines.

Expanded Audience – Frontegg – Security (partner)
announcement - icon User management is very complex, when implemented properly. No surprise here.

Not having to roll all of that out manually, but instead integrating a mature, fully-fledged solution - yeah, that makes a lot of sense.
That's basically what Frontegg is - User Management for your application. It's focused on making your app scalable, secure and enjoyable for your users.
From signup to authentication, it supports simple scenarios all the way to complex and custom application logic.

Have a look:

>> Elegant User Management, Tailor-made for B2B SaaS

November Discount Launch 2022 – Top
We’re finally running a Black Friday launch. All Courses are 30% off until end-of-day today:

>> GET ACCESS NOW

November Discount Launch 2022 – TEMP TOP (NPI)
We’re finally running a Black Friday launch. All Courses are 30% off until end-of-day today:

>> GET ACCESS NOW

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>3.15</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");

3.2. Convert Image InputStream Into a Byte Array

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:

xlavengers

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.

November Discount Launch 2022 – Bottom
We’re finally running a Black Friday launch. All Courses are 30% off until end-of-day today:

>> GET ACCESS NOW

Generic footer banner
Comments are closed on this article!