Introduction

In this tutorial we will see an example on how to add images to excel file using Apache POI in Java programming language. Using Apache POI library it becomes easy to insert or add images into excel file. Images are part of the drawing support. To add an image just call createPicture() on the drawing patriarch.

In this example we will insert or add two images of types png and jpg/jpeg. I am going to show you example using both maven and gradle build tools. Here I am using Apache POI version 4.1.1.

Prerequisites

Eclipse 4.12, At least JDK 1.8, maven 3.6.1, gradle 5.6, Apache POI 4.1.1

Creating Project

You can create a maven or gradle based project in Eclipse. The name of the project is apache-poi-excel-add-images.

If you are creating gradle based project then you can use the following build.gradle script to build your project.

plugins {
    id 'java-library'
}

sourceCompatibility = 12
targetCompatibility = 12

repositories {
    jcenter()
}

dependencies {
    implementation 'org.apache.poi:poi-ooxml:4.1.1'
    //required only for jdk 9 or above
    implementation('com.fasterxml.jackson.core:jackson-databind:2.10.1')
}

If you are creating maven based project in Eclipse then you can use the following pom.xml file:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	
	<groupId>com.roytuts</groupId>
	<artifactId>apache-poi-excel-add-images</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<packaging>jar</packaging>
	
	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<java.version>at least 1.8</java.version>
		<poi.version>4.1.1</poi.version>
	</properties>
	
	<dependencies>
		<!-- apache poi for xlsx, docx etc reading/writing -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>${poi.version}</version>
		</dependency>
	</dependencies>
	
	<build>
		<plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
                <configuration>
					<source>${java.version}</source>
					<target>${java.version}</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Insert or Add Images

Now we will see how to insert images into excel file using Java program with the help of Apache POI library.

We have already put comments between the source codes to make you understand what it does.

package com.roytuts.apache.poi.excel.add.images;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Picture;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class AddImagesToExcelFile {

	public static void main(String[] args) throws IOException {
		addImages();
	}

	public static void addImages() throws IOException {
		// create a new workbook
		Workbook wb = new XSSFWorkbook(); // or new HSSFWorkbook();

		// add jpg/jpeg and png formats picture data to this workbook.
		InputStream is = new FileInputStream("sample.jpg");
		byte[] bytes = IOUtils.toByteArray(is);
		int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
		is.close();

		CreationHelper helper = wb.getCreationHelper();

		// create sheet
		Sheet sheet = wb.createSheet();

		// Create the drawing patriarch. This is the top level container for all shapes.
		Drawing<?> drawing = sheet.createDrawingPatriarch();

		// add a picture shape
		ClientAnchor anchor = helper.createClientAnchor();

		// set top-left corner of the picture,
		// subsequent call of Picture#resize() will operate relative to it
		anchor.setCol1(3);
		anchor.setRow1(2);
		Picture pict = drawing.createPicture(anchor, pictureIdx);

		// auto-size picture relative to its top-left corner
		pict.resize();

		is = new FileInputStream("sample.png");
		bytes = IOUtils.toByteArray(is);
		pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
		is.close();

		anchor = helper.createClientAnchor();
		anchor.setCol1(3);
		anchor.setRow1(14);
		pict = drawing.createPicture(anchor, pictureIdx);

		// auto-size picture relative to its top-left corner
		pict.resize();

		// save workbook
		String fileName = "excel_image.xls";

		if (wb instanceof XSSFWorkbook) {
			fileName += "x";
		}

		try (OutputStream fileOut = new FileOutputStream(new File(fileName))) {
			wb.write(fileOut);
		}

		wb.close();
	}

}

In the above code snippets the images are put into the current project’s root directory to read the images.

We generate the excel file finally in the project’s root directory. We check whether you are creating XSSF or HSSF object and accordingly we generate file extension as xlsx or xls.

Thanks for reading.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *