Insert Image in Excel - Java POI Example Program

Insert Image into Excel Workbook - Introduction


In this tutorial, we will take you through a step by step guide, that will explain how to use Java and Apache POI library, to insert images into a XLS workbook. Each one of us likes pictures, and often you may find yourself with a requirement to add a small image in the Excel workbook you create. It could be a regular image or even a banner. Whatsoever it may be, this guide would be handy for you to know how to add images to XLS workbook using Apache POI.. Let us get started.

Let us use the following sample banner in this example. We will try and push this into our Javaxlised workbook.

Insert Image into XLS Document - Java Apache POI Example - Input Picture
Insert Image into XLS Document - Java Apache POI Example - Input Picture

Create Workbook and Image Worksheet


The first step is to create a workbook and a worksheet that will hold our image. We have discussed this many times. So, we will provide the code snippet right away.
                /* Create a Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("MyBanner");

addPicture - to Workbook


We do three things in this steps.
  • Read picture into InputStream from the file.
  • Convert the image into a byte array.  Helper methods are available in org.apache.poi.util.IOUtils to do the conversion.
  • Add the image to the workbook using addPicture method, and specify the type of image.
Simple steps. And only lines of code to do this. Here is a code fragment that reads the banner image and adds it to the workbook.

                /* Read the input image into InputStream */
                InputStream my_banner_image = new FileInputStream("sample.jpg");
                /* Convert Image to byte array */
                byte[] bytes = IOUtils.toByteArray(my_banner_image);
                /* Add Picture to workbook and get a index for the picture */
                int my_picture_id = my_workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                /* Close Input Stream */
                my_banner_image.close();

Note that the addPicture method, returns an integer which is the index of the picture with regard to the entire workbook. We need that index to position the image in the worksheet of our choice.

Create Drawing Object And Anchor Point


Now the main ones. Not complex. But needs understanding. We begin by creating a drawing object. Think this as a Patriarch / Canvas that will hold the image. We use org.apache.poi.hssf.usermodel.HSSFPatriarch class for this. The container will hold all shapes, and the picture is a shape for us.  There is no constructor for this class, and you have to access this via worksheet.createDrawingPatriarch method.

                /* Create the drawing container */
                HSSFPatriarch drawing = my_sheet.createDrawingPatriarch();

Next, we define an anchor point for the picture. Anchor point basically defines the row / column for Top left position. Anchor points are defined with the help of org.apache.poi.hssf.usermodel.HSSFClientAnchor class.

                /* Create an anchor point */
                ClientAnchor my_anchor = new HSSFClientAnchor();
                /* Define top left corner, and we can resize picture suitable from there */
                my_anchor.setCol1(2);
                my_anchor.setRow1(1);

Add Picture to Drawing



Now the easy step. We invoke createPicture method in HSSFPatriarch class, and pass the anchor point created in the last step and the index of the picture we obtained in step 1. (i.e. when adding the picture to a workbook). The call to createPicture returns an object back of type org.apache.poi.hssf.usermodel.HSSFPicture which we can use for futher manipulation.

                /* Invoke createPicture and pass the anchor point and ID */
                HSSFPicture  my_picture = drawing.createPicture(my_anchor, my_picture_id);
                /* Call resize method, which resizes the image */
                my_picture.resize();
Note, that we have called the resize method to resize the image to suit to our dimensions.

Finally, we write the workbook as a file to the outputstream. Our file with image is ready.

                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\excel_insert_image_example.xls"));
                my_workbook.write(out);
                out.close();

Insert Image to Excel Workbook - Complete Java Program Example


Putting it all together, the complete Java Program to insert an image into an XLS workbook is provided below:

import java.io.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
public class AddImageExample {  
        public static void main(String[] args) throws Exception{
                /* Create a Workbook and Worksheet */
                HSSFWorkbook my_workbook = new HSSFWorkbook();
                HSSFSheet my_sheet = my_workbook.createSheet("MyBanner");               
                /* Read the input image into InputStream */
                InputStream my_banner_image = new FileInputStream("sample.jpg");
                /* Convert Image to byte array */
                byte[] bytes = IOUtils.toByteArray(my_banner_image);
                /* Add Picture to workbook and get a index for the picture */
                int my_picture_id = my_workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                /* Close Input Stream */
                my_banner_image.close();                
                /* Create the drawing container */
                HSSFPatriarch drawing = my_sheet.createDrawingPatriarch();
                /* Create an anchor point */
                ClientAnchor my_anchor = new HSSFClientAnchor();
                /* Define top left corner, and we can resize picture suitable from there */
                my_anchor.setCol1(2);
                my_anchor.setRow1(1);           
                /* Invoke createPicture and pass the anchor point and ID */
                HSSFPicture  my_picture = drawing.createPicture(my_anchor, my_picture_id);
                /* Call resize method, which resizes the image */
                my_picture.resize();            
                /* Write changes to the workbook */
                FileOutputStream out = new FileOutputStream(new File("C:\\excel_insert_image_example.xls"));
                my_workbook.write(out);
                out.close();
        }
}

This program when executed, produces an Excel workbook with the picture embedded. A sample output screenshot is provided below:

XLS - Insert Image to XLS Document - Java POI Example Program - Output
XLS - Insert Image to XLS Document - Java POI Example Program - Output
You may get the following exception at runtime

Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/commons/codec/digest/DigestUtils
        at org.apache.poi.hssf.usermodel.HSSFWorkbook.addPicture(HSSFWorkbook.java:1580)
        at AddImageExample.main(AddImageExample.java:18)
Caused by: java.lang.ClassNotFoundException: org.apache.commons.codec.digest.DigestUtils
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.net.URLClassLoader$1.run(Unknown Source)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.net.URLClassLoader.findClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
        at java.lang.ClassLoader.loadClass(Unknown Source)
        ... 2 more

This means, you are missing the JAR file commons-codec-1.7.jar which is a part of Apache Commons Codec distribution. POI needs this JAR file to add the image to the workbook. So, make sure you have this JAR file (or equivalent version) in your classpath.

That completes a breezy tutorial to insert image to XLS document using Java and Apache POI. Hope this tutorial serves as a basics for you to get started with advanced examples. (we will see some of them as we move on). If you have any questions, you can post it in the comments section of this blog. Stay Connected.

2 comments:

  1. how to insert a images in a collumn ??

    ReplyDelete
  2. Hi, that not works for me, i'm using Netbeans as IDE, I follow this>

    Project > Propertires > Libraries > Compile and I've added the commons-codec-1.7.jar and nothing...

    too I've added the 1.6 / 1.5 / 1.9 and nothing...

    Please help me.

    thanks in advance

    ReplyDelete