Ads Inside Post

Search in This Blog

How to generate .XLSX file in Java using Apache POI

How to generate .XLSX file in Java using Apache POI


First you need to download from apache site:
Extract zip file and copy listed below file in you Libraries folder under NetBeans.
1)      poi-3.9-20121203.jar
2)      poi-examples-3.9-20121203.jar
3)      poi-excelant-3.9-20121203.jar
4)      poi-ooxml-3.9-20121203.jar
5)      poi-ooxml-schemas-3.9-20121203.jar
6)      poi-scratchpad-3.9-20121203.jar
7)      xmlbeans-2.3.0.jar
8)      dom4j-1.6.1.jar

Create “excel.jsp” as listed below file:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Excel Page</title>
    </head>
    <body>
        <h1>Excel!</h1>
        <form name="excelform" id="excelform"  action="/MyTest/TestExcel"   method="post">
            <input type="submit" value="submit"/>
        </form>    
    </body>
</html>


Create a servlet “TestExcel.java” as listed below file:
import java.io.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class TestExcel extends HttpServlet {
    //Processes requests for both HTTP
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        PrintWriter out = response.getWriter();
        String contextName = request.getContextPath().substring(1);
        HttpSession hs = request.getSession();
        String path = hs.getServletContext().getRealPath("/" + contextName);
       
        char pathSep = new File(path).separatorChar;
        path = path.substring(0, path.lastIndexOf(pathSep + contextName));
        path = path + pathSep;
        //String filename = "c:\\Send\\text.xlsx";
        String filename = path+"text.xlsx";
        String sheetName = "Sheet1";
        XSSFWorkbook wb= new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet(sheetName) ;
        int width = 30;
        sheet.setAutobreaks(true);
        sheet.setDefaultColumnWidth(width);
       
        XSSFRow rowHead = sheet.createRow((short) 0);
        rowHead.createCell((int) 0).setCellValue("SR No.");
        rowHead.createCell((int) 1).setCellValue("Name");
        rowHead.createCell((int) 2).setCellValue("Code");
        rowHead.createCell((int) 3).setCellValue("Salary");
        rowHead.createCell((int) 4).setCellValue("City");
        rowHead.createCell((int) 5).setCellValue("State");
       
        int i = 0,index=0;
        for(i=0;i<6;i++) {
            index++;
            XSSFRow row = sheet.createRow((short) index);
            row.createCell((int) 0).setCellValue(index);
            row.createCell((int) 1).setCellValue("Name -- "+index);
            row.createCell((int) 2).setCellValue("12345 -- "+index);
            row.createCell((int) 3).setCellValue("4500"+index);
            row.createCell((int) 4).setCellValue("City -- "+index);
            row.createCell((int) 5).setCellValue("State -- "+index);
        }
        FileOutputStream fileOut = new FileOutputStream(filename);
        wb.write(fileOut);
        fileOut.close();
        String disHeader = "Attachment;Filename=\"Report.xlsx\"";
        response.setHeader("Content-Disposition", disHeader);
        File desktopFile = new File(filename);
        PrintWriter pw = response.getWriter();
        FileInputStream fileInputStream = new FileInputStream(desktopFile);
        int j;
        //pw.flush();
        while ((j = fileInputStream.read()) != -1) {
            pw.write(j);
        }
        fileInputStream.close();
        response.flushBuffer();
        pw.flush();
        pw.close();
       
    }

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    @Override
    public String getServletInfo() {
        return "Short description";
    }

}