Blog Archive

Apache POI Excel Insert an Image

Lot of times we have to generate Excel file with some information and company Logo. In this example we are going to use Apache POI to generate an Excel and insert an image at Column 2 and Row 3. To add an image just call createPicture() on the drawing patriarch.

Apache poi Excel insert an image
package com.as400samplecode;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
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;


public class ExcelWithImage {

 public static void main(String[] args) {

  try {

   Workbook wb = new XSSFWorkbook();
   Sheet sheet = wb.createSheet("My Sample Excel");

   //FileInputStream obtains input bytes from the image file
   InputStream inputStream = new FileInputStream("images/myLogo.png");
   //Get the contents of an InputStream as a byte[].
   byte[] bytes = IOUtils.toByteArray(inputStream);
   //Adds a picture to the workbook
   int pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
   //close the input stream
   inputStream.close();

   //Returns an object that handles instantiating concrete classes
   CreationHelper helper = wb.getCreationHelper();

   //Creates the top-level drawing patriarch.
   Drawing drawing = sheet.createDrawingPatriarch();

   //Create an anchor that is attached to the worksheet
   ClientAnchor anchor = helper.createClientAnchor();
   //set top-left corner for the image
   anchor.setCol1(1);
   anchor.setRow1(2);

   //Creates a picture
   Picture pict = drawing.createPicture(anchor, pictureIdx);
   //Reset the image to the original size
   pict.resize();

   //Write the Excel file
   FileOutputStream fileOut = null;
   fileOut = new FileOutputStream("data/myFile.xlsx");
   wb.write(fileOut);
   fileOut.close();

  }
  catch (Exception e) {
   System.out.println(e);
  }


 }

}

References


15 comments :

  1. Hi
    It throws error class definition not found for the class java.awt.Dimension

    ReplyDelete
  2. Try this Excel Converter , it not only converts excel documents to other formats but also offers many additional features like adding images into your excel file even based on cell reference and it will do all that in java language.

    ReplyDelete
  3. How did you get over the NoClassDefFound of java.awt.Dimension ?

    ReplyDelete
  4. Replies
    1. you can switch the values of (setCol1 and setCol2 ) and / or (setRow1 and setRow2) to flip the image

      Delete
  5. I am getting a FileNotFoundException.
    Should i store my logo on some specific location inside a project or there is some permission i need to add or something ?

    ReplyDelete
  6. How do you add ALT TEXT to the image?

    ReplyDelete
  7. how do you add it to a particular cell?

    ReplyDelete
  8. Thanks for your help

    ReplyDelete
  9. Which location will i store my image

    ReplyDelete
  10. Hi is it possible add check box like this??

    Thanks

    ReplyDelete