How to Insert Image on XLS Using Apache POI

Hi.. on this article I will give you simple example about how to insert image on xls using apache poi. We need some libraries to help us and insert it to the project. The libraries are apache-commons-io, apache-commons-codec, and ofcourse, apache-poi lib. The result will be look like this
imgxls

I’ve created simple web for this sample. I put the image on WEB-INF folder and start to create web interface and java class just like this.

Interface:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Generate XLS</title>
    </head>
    <body>
        <h1>Hello..!</h1>
        <form action="genreport" target="_blank" name="form1" id="form1" method="post">
            <button value="submit" type="submit">Generate XLS</button>
        </form>
    </body>
</html>

Here is the controller

package com.ariestania.servlet;

import java.io.FileInputStream;
import java.io.IOException;
import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.Workbook;

/**
 *
 * @author ariestania.winda
 */
public class genreport extends HttpServlet {

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        //get my image
        ServletContext servletContext = req.getSession().getServletContext();
        String imgLoc = servletContext.getRealPath("/WEB-INF");
        try (FileInputStream fis = new FileInputStream(imgLoc + "/awlogo.png"); ServletOutputStream os = resp.getOutputStream();) {
            byte[] imgBytes = IOUtils.toByteArray(fis);

            //create workbook
            Workbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet) workbook.createSheet();

            //create title
            HSSFRow row = sheet.createRow(0);
            HSSFCell cell = row.createCell(1);
            cell.setCellValue("Hi .. this is sample insert image to XLS");

            //create helper and set position image
            CreationHelper helper = workbook.getCreationHelper();
            ClientAnchor anchor = helper.createClientAnchor();
            anchor.setCol1(1);
            anchor.setRow1(2);

            //drawing image
            Drawing imgDrawing = sheet.createDrawingPatriarch();
            int imgId = workbook.addPicture(imgBytes, Workbook.PICTURE_TYPE_PNG);
            Picture imgPic = imgDrawing.createPicture(anchor, imgId);
            imgPic.resize();

            String attch = "attachment; filename=\"tesarisimg.xls\"";
            resp.setContentType("application/vnd.ms-excel");
            resp.setHeader("Content-Disposition", attch);
            workbook.write(os);

            os.flush();
        }
    }
}

Do not forget the web.xml for the servlet config

<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
	 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
	 version="3.1">
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
    <servlet>
        <servlet-name>genreport</servlet-name>
        <servlet-class>com.ariestania.servlet.genreport</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>genreport</servlet-name>
        <url-pattern>/genreport</url-pattern>
    </servlet-mapping>
</web-app>

Hope this simple article can help 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s