Technical Developer: September 2014

Apache POI is a useful Java API for Microsoft documents. It is a project run by the Apache Software Foundation providing Java libraries for both reading and writing files in MS Word, MS Excel, and Powerpoint. Many a times web application developers use the API to display an Excel sheet in the browser. For this purpose, we first need to populate the Excel sheet, read it using Apache POI and then display it using Servlets/JSP.

In this post, we will read a pre-populated Excel sheet and display the same using Servlets.

Pre-requisites: Eclipse IDE for Java EE developers, Apache Tomcat Server 6.0, Apache POI (download the binaries from over here)

Step 1: Create a new Dynamic Web project

Open Eclipse IDE and create a new Dynamic web project namedDisplayExcelWeb. Create a new Servlet class with package namecom.example as follows!

ExcelServlet.java

package com.example;

import java.io.IOException;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.File; 

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.util.Iterator;

import org.apache.poi.ss.usermodel.Cell;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**

 * Servlet implementation class ExcelServlet

 */

public class ExcelServlet extends HttpServlet 

{

  private static final long serialVersionUID = 1L;     

  XSSFWorkbook workbook; 

    public ExcelServlet() 

    {

        super();

        // TODO Auto-generated constructor stub

    }

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException

     {   

         try

          {          

            FileInputStream file = new FileInputStream(new File("C:\\programs\\excel\\source.xlsx"));

            //Get the xlsx workbook

            workbook = new XSSFWorkbook (file);

            //Get first sheet from the workbook

            XSSFSheet sheet = workbook.getSheetAt(0);

            //Get iterator to all cells of current row

            Iterator<Row> rowIterator = sheet.rowIterator();

         while(rowIterator .hasNext())

         {

             Row row = rowIterator.next();

            //For each row, iterate through each columns

            Iterator<org.apache.poi.ss.usermodel.Cell> cellIterator = row.cellIterator();

            while(cellIterator.hasNext()) 

                {

                    Cell cell = cellIterator.next();

                    switch(cell.getCellType())

                    {

                          case Cell.CELL_TYPE_BOOLEAN:

                          System.out.print(cell.getBooleanCellValue() + "\t\t");

                          break;

                          case Cell.CELL_TYPE_NUMERIC:

                          System.out.print(cell.getNumericCellValue() + "\t\t");

                          break;

                          case Cell.CELL_TYPE_STRING:

                          System.out.print(cell.getStringCellValue() + "\t\t");

                          break;

                    }     

                }               

                          System.out.println("");

               }    

                 file.close();

           } 

           catch (FileNotFoundException e) 

           {

             e.printStackTrace();

           } 

           catch (IOException e) 

           {

             e.printStackTrace();

           }

       response.setContentType("application/vnd.ms-excel");

       workbook.write(response.getOutputStream());

       response.getOutputStream().close();

    }

}

web.xml

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

<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd" id="WebApp_ID" version="2.5">

  <display-name>DisplayExcelWeb</display-name>

  <welcome-file-list>

    <welcome-file>index.html</welcome-file>

    <welcome-file>index.htm</welcome-file>

    <welcome-file>index.jsp</welcome-file>

    <welcome-file>default.html</welcome-file>

    <welcome-file>default.htm</welcome-file>

    <welcome-file>default.jsp</welcome-file>

  </welcome-file-list>

  <servlet>

    <description></description>

    <display-name>ExcelServlet</display-name>

    <servlet-name>ExcelServlet</servlet-name>

    <servlet-class>com.example.ExcelServlet</servlet-class>

  </servlet>

  <servlet-mapping>

    <servlet-name>ExcelServlet</servlet-name>

    <url-pattern>/ExcelServlet</url-pattern>

  </servlet-mapping>

</web-app>

Step 2: Add the following jar files inside the WEB-INF/lib folder

Step 3: Create JSP page

Add a new JSP page called index.jsp that will call the Servlet!

index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">

<title>Display Excel</title>

</head>

<body>

<a href="ExcelServlet">Show Excel</a>

</body>

</html>

Finally, run the project on the Server. Once you click on the link, you should be able to save and view the Excel file.

ref: http://karanbalkar.com/2013/09/save-excel-sheet-from-browser-using-apache-poi/

Technical Developer

Tuesday, September 23, 2014

Save Excel sheet from browser using Apache POI