Struts2 export excel Apache POI

Apache POI library is Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2).OLE2 files include most Microsoft Office files such as XLS, DOC, and PPT. You can read and write MS Excel , MS Word , MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008).

Some of acronym use here :-

1. HSSF (Horrible SpreadSheet Format) – reads and writes Microsoft Excel (XLS) format files.


In this example we will use Apache POI library to perform Microsoft Excel spreadsheet export operation via Struts2. Download poi.jar & add to lib folder either add Maven as dependency in pom.xml

 org.apache.poi
 poi
 3.9

Create a web project with required struts2 jar & also poi.jar.

struts.xml

 


 
 

  
   /exporttoexcel.jsp
  

  
   
    attachment;filename="${reportFile}"application/vnd.ms-excelinputStream1024
  
 
 

web.xml

 ApachePOIExport


 
  struts2
  org.apache.struts2.dispatcher.FilterDispatcher
 

 
  struts2
  /*
 

 
  index.jsp
 

StudentBean.java

package com.struts2.javastoreroom;

public class StudentBean {

 private int id;
 private String name;

 public StudentBean(int id, String name) {
  this.id = id;
  this.name = name;
 }

 public int getId() {
  return id;
 }

 public void setId(int id) {
  this.id = id;
 }

 public String getName() {
  return name;
 }

 public void setName(String name) {
  this.name = name;
 }

}

RetrieveAction.java

package com.struts2.javastoreroom;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

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.Cell;
import org.apache.poi.ss.usermodel.Row;

import com.opensymphony.xwork2.ActionSupport;

@SuppressWarnings("serial")
public class RetrieveAction extends ActionSupport {

 /**
  * @author Arun
  */

 private List nameList = new ArrayList();
 private InputStream inputStream;
 private String reportFile;

 public String fetchName() {

  return SUCCESS;
 }

 /**
  * student data to export , get from database if you export from database
  * 
  * @return {@link List}
  */

 private List fetchList() {

  {
   nameList.add(new StudentBean(1, "Arun"));
   nameList.add(new StudentBean(2, "Anu"));
   nameList.add(new StudentBean(3, "Aryan"));
   nameList.add(new StudentBean(4, "Anu"));
   nameList.add(new StudentBean(5, "Aranv"));
   nameList.add(new StudentBean(6, "Arun"));
  }
  return nameList;
 }

 /**
  * create HSSF read and write in spreadsheets
  * 
  * @return
  */

 public String exportEmployeeDetails() {
  HSSFWorkbook myWorkBook = new HSSFWorkbook();
  HSSFSheet mySheet = myWorkBook.createSheet();
  try {
   Calendar calendar = Calendar.getInstance();
   List studentBeans = fetchList();
   reportFile = "Student Report" + calendar.get(Calendar.YEAR)
     + "".concat(".xls");

   Row headerRow = mySheet.createRow(0);
   headerRow.setHeightInPoints(50);
   Cell titleCell = headerRow.createCell(0);
   titleCell.setCellValue("Student Report "
     + calendar.get(Calendar.YEAR) + "");

   setStudentAllInfo(mySheet, studentBeans);

   try {
    ByteArrayOutputStream boas = new ByteArrayOutputStream();
    myWorkBook.write(boas);
    setInputStream(new ByteArrayInputStream(boas.toByteArray()));
   } catch (IOException e) {
    e.printStackTrace();
   }
  } catch (Exception e) {
   e.printStackTrace();
  }
  return SUCCESS;
 }

 /**
  * setting field value as you want to export in .xls
  * 
  * @param mySheet
  * @param studentBeans
  */

 private void setStudentAllInfo(HSSFSheet mySheet,
   List studentBeans) {
  int rowNum = 2;
  HSSFRow myRow = null;
  final String[] errorSource = { "Id", "Name" };
  try {
   Row header = mySheet.createRow(1);
   for (int i = 0; i < errorSource.length; i++) {
    Cell monthCell = header.createCell(i);
    monthCell.setCellValue(errorSource[i]);
   }
   for (StudentBean studentBean : studentBeans) {
    myRow = mySheet.createRow(rowNum++);
    myRow.createCell(0).setCellValue(studentBean.getId());
    myRow.createCell(1).setCellValue(studentBean.getName());
   }
  } catch (Exception e) {
   e.printStackTrace();
  }

 }

 public String execute() {
  return SUCCESS;
 }

 public String getReportFile() {
  return reportFile;
 }

 public void setReportFile(String reportFile) {
  this.reportFile = reportFile;
 }

 public InputStream getInputStream() {
  return inputStream;
 }

 public void setInputStream(InputStream inputStream) {
  this.inputStream = inputStream;
 }

 public List getNameList() {
  return nameList;
 }

 public void setNameList(List nameList) {
  this.nameList = nameList;
 }

}

index.jsp

<%response.sendRedirect("fetchName.action");%> 

exporttoexcel.jsp

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







  
  Export To Excel

While executing the project below output shown click on export link and dialog box shown where you save file :-