Add image in Excel cell using Java poi Example

In this article, you will see how can you Add/Insert an image in the Excel cell using Java. Here you will see an example program to add an image in XLS or XLSX files in Java using Apache POI.

The Apache POI is a Java library that helps in working with various Microsoft documents such as Excel, Word, etc.

Required dependency and Project Setup

Here we use the Apache POI library to develop this Java application, so we should have the POI dependencies available in the Project setup. POI can be used to read, write, and modify the Excel spreadsheet. It supports both excel file formats XLS and XLSX.

Apache poi Maven dependencies

The below maven dependencies are required to develop the Apache POI application. We have to add these dependencies in the pom.xml file. Please note that we are going to use Apache POI-4.0.1 version to develop this example application.

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.0.1</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.0.1</version>
</dependency>

NOTE: The only dependency poi-4.0.1 is enough if you are working with an XLS file format, whereas both dependencies (poi-4.0.1 and poi-ooxml-4.0.1) are required to work with an XLSX file format.

Apache poi jars

If you want to create a standalone java application for Apache poi, then you can add the followings poi jars file into the classpath:

  1. poi-4.0.1.jar
  2. poi-ooxml-4.0.1.jar
  3. poi-ooxml-schemas-4.0.1.jar
  4. xmlbeans-3.0.2.jar
  5. curvesapi-1.05.jar
  6. commons-codec-1.11.jar
  7. commons-collections4-4.2.jar
  8. commons-compress-1.18.jar
  9. commons-math3-3.6.1.jar

If you want to download all the above jars and are required to setups the application in Eclipse IDE, visit another article Apache POI – Getting Started.

How to insert an image in an Excel cell using Java?

You can follow the basic steps to add or insert an image in the Excel cell. Let’s see the below steps.

Steps to add an image in XLSX using Java POI

  1. Create an XSSFWorkbook object.
  2. Create a Sheet object. eg- workbook.createSheet(“sheetname”);
  3. Create a Row and then create Cells into the row.
  4. Load the image as an InputStream object.
  5. Convert the input stream into a byte array. eg- IOUtils.toByteArray(is);
  6. Add picture to workbook. eg- workbook.addPicture(inputImageBytes, Workbook.PICTURE_TYPE_PNG);
  7. Create an XSSFClientAnchor object and set the row1, row2, col1, col2 value using appropriate method of client anchor.
  8. Create a drawing object. eg- sheet.createDrawingPatriarch();
  9. Then finally, insert the image using a drawing object. eg- drawing.createPicture(anchor, inputImagePictureID);

Add image in excel using java poi Example

Now let’s see the complete example for adding the images into the Excel file using apache poi in java. This example program will work for both XLS and XLSX file formats of Excel.

package com.javacodepoint.example;

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

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Row;
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.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 
 * @author javacodepoint.com
 *
 */
public class AddImageInExcel {

	/**
	 * This Method loads the image from application resource and insert into the
	 * Cell
	 */
	private static void insertImageToCell(Workbook workbook, String fileType, int rowNum, Drawing drawing,
			String imageName) throws IOException {
		
		//Loading image from application resource
		InputStream is = AddImageInExcel.class.getClassLoader().getResourceAsStream(imageName);
		
		//Converting input stream into byte array
		byte[] inputImageBytes = IOUtils.toByteArray(is);
		int inputImagePictureID = workbook.addPicture(inputImageBytes, Workbook.PICTURE_TYPE_PNG);
		is.close();

		ClientAnchor anchor = null;
		//Creating the client anchor based on file format
		if (fileType.equals("xls")) {
			anchor = new HSSFClientAnchor();
		} else {
			anchor = new XSSFClientAnchor();
		}
		anchor.setCol1(1);
		anchor.setCol2(2);
		anchor.setRow1(rowNum - 1);
		anchor.setRow2(rowNum);
		drawing.createPicture(anchor, inputImagePictureID);
		
	}

	public static void main(String[] args) {
		
		Workbook workbook = null;
		FileOutputStream fos = null;
		try {
			File file = new File("E:/company-logo.xlsx");
			String fileType = null;
			
			// Creating workbook object based on excel file format
			if (file.getName().endsWith(".xls")) {
				workbook = new HSSFWorkbook();
				fileType = "xls";
			} else if (file.getName().endsWith(".xlsx")) {
				workbook = new XSSFWorkbook();
				fileType = "xlsx";
			} else {
				System.err.println("File format should be XLS or XLSX only.");
				return;
			}

			Sheet sheet = workbook.createSheet("Company Logo");

			// Creating drawing object
			Drawing drawing = sheet.createDrawingPatriarch();

			//Adding first row data
			Row row1 = sheet.createRow(0);
			row1.setHeight((short) 1000);
			row1.createCell(0).setCellValue("Apple");
			insertImageToCell(workbook, fileType, 1, drawing, "apple_logo.png");

			//Adding second row data
			Row row2 = sheet.createRow(1);
			row2.setHeight((short) 1000);
			row2.createCell(0).setCellValue("Google");
			insertImageToCell(workbook, fileType, 2, drawing, "google_logo.png");

			//Adding third row data
			Row row3 = sheet.createRow(2);
			row3.setHeight((short) 1000);
			row3.createCell(0).setCellValue("Facebook");
			insertImageToCell(workbook, fileType, 3, drawing, "facebook_logo.png");

			// Resize all columns to fit the content size
			for (int i = 0; i < 3; i++) {
				sheet.autoSizeColumn(i);
			}
			
			// Write the workbook to the excel file
			fos = new FileOutputStream(file);
			workbook.write(fos);
			System.out.println("Images have been added successfully.");

		} catch (IOException e) {
			System.err.println(e.getMessage());
			e.printStackTrace();
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (fos != null) {
				try {
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

add image in excel cell using java poi
OUTPUT OF THE ABOVE EXAMPLE

Conclusion

In this article, you have seen how to add an image to Excel (XLS and XLSX) using Apache POI in Java. You can download the complete maven application for the above example from the below download link.

Learn how to read and write an Excel file using java, Apache POI – Read and Write Excel files in java.

Share with friends

Leave a Comment

Your email address will not be published.