Export to Excel

Export to Excel a Report on Web

Exporting a report in Excel format is usually a requirement in most of the web application now a days. The administrator or the manager needs to generate different reports in excel, pdf or word format. Here is a common utility to generate report in excel.

• Export report in Excel format.
• Generic way to generate report with MxN rows & columns

Download the source code here:

Technologies and tools used:

• Java 1.6 or above
• Spring 3.1
• Eclipse (with dynamic web application support)

Step 1:

Set up a Java EE based web application with MVC support. I have used Spring 3.1 MVC in this example.

Step 2: 

Create a controller class and a method (end point) to serve the request for export. Usually the method accept POST request to submit a form based on which the report should be generated. In this example, the generated report contains static data of employees. The controller retrieves (static) data from business layer and invokes the utility to generate report.

@Controller @RequestMapping("/excel") public class ExcelController { protected ExcelService excelService = new ExcelService(); @RequestMapping(value = "/export") public ModelAndView save(HttpServletResponse response) throws IOException { System.out.println("Export called"); List<Employee> employeeList = excelService.getAllEmployees(); …... …... …... return null; } }

Step 3:

Iterate the list of employees (Collection of domain objects) and convert each employee's (domain object's) details into list of String (List<String>). Finally prepare List of List of String.

List<List<String>> employeeDetailsMasterList = convertToListOfList(employeeList); private List<List<String>> convertToListOfList(List<Employee> employeeList) { List<List<String>> masterList = new ArrayList<List<String>>(); ListIterator<Employee> iterator = employeeList.listIterator(); while(iterator.hasNext()) { Employee emp = iterator.next(); List<String> empDetails = new ArrayList<String>(); empDetails.add(iterator.previousIndex()+1+""); empDetails.add(emp.getName()); empDetails.add(emp.getDept()); empDetails.add(emp.getDoj()); masterList.add(empDetails); } return masterList; }

Step 4:

Prepare a list of name of column / header. Add this list in the list prepared in step 3 above at zeroth positions.

List<String> myReportHeader = getTrainingReportHeader(); employeeDetailsMasterList.add(0, myReportHeader);

Step 5:

Prepare the workBook object from the List of List of String. Use the POI api to generate the work book object as shown below

HSSFWorkbook workBook = ExcelUtils.prepareWorkBook(employeeDetailsMasterList, "Test Report");

Step 6:

Finally write the work book on the response object. This is allow the user to download the report in excel format.

ExcelUtils.generateReport(response, workBook, "Test Report");

Putting all the above steps together, we have following three important methods:

ExcelController.java

@Controller @RequestMapping("/excel") public class ExcelController { protected ExcelService excelService = new ExcelService(); @RequestMapping(value = "/export") public ModelAndView save(HttpServletResponse response) throws IOException { System.out.println("Export called"); List<Employee> employeeList = excelService.getAllEmployees(); List<List<String>> employeeDetailsMasterList = convertToListOfList(employeeList); List<String> myReportHeader = getTrainingReportHeader(); employeeDetailsMasterList.add(0, myReportHeader); HSSFWorkbook workBook = ExcelUtils.prepareWorkBook(employeeDetailsMasterList, "Test Report"); ExcelUtils.generateReport(response, workBook, "Test Report"); return null; } }

ExcelUtils.java

public static void generateReport(HttpServletResponse response, HSSFWorkbook workBook, String workSheetName) throws IOException { // set specific response content type for xls files. response.setContentType("application/vnd.ms-excel; charset=utf-8"); response.setHeader("Content-Disposition", "attachment; filename=" + workSheetName + ".xls"); /* * With this we let the browser know what the default name should be for * the user to save this file. */ ServletOutputStream servletOutputStream = response.getOutputStream(); workBook.write(servletOutputStream); }

ExcelUtils.java

public static HSSFWorkbook prepareWorkBook(List<List<String>> reportList, String workSheetName) { final String METHOD_NAME = "convertListToExcel"; System.out.println("Entered " + METHOD_NAME); if (StringUtils.isNullOrBlank(workSheetName)) { workSheetName = REPORT_EXCEL_SHEET_NAME_DEFAULT; } int lengthOfColumns = ((List<String>) reportList.get(0)).size(); // Do the POI Things to generate the Excel File create a new workbook HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet(workSheetName); /******************************************* * This Part Contains The Cell Formatting Stuff. * ********************************************/ // Create a Font For Header HSSFFont headerFont = workBook.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName(DEFAULT_FONT_FACE); // create a style for the header Columns HSSFCellStyle columnHeaderStyle = workBook.createCellStyle(); columnHeaderStyle.setFont(headerFont); // Create a Font For Data Rows HSSFFont rowFont = workBook.createFont(); rowFont.setFontName(DEFAULT_FONT_FACE); // create a style for the Records In Next Rows HSSFCellStyle rowCellStyle = workBook.createCellStyle(); rowCellStyle.setFont(rowFont); /**********************************************/ HSSFRow[] headerRow = new HSSFRow[reportList.size()]; ListIterator<List<String>> outerListIterator = reportList .listIterator(); // Create Header Row if (outerListIterator.hasNext()) { int rowIndex = outerListIterator.nextIndex(); List<String> innerList = outerListIterator.next(); headerRow[rowIndex] = sheet.createRow((short) rowIndex); HSSFCell[] headerColumns = new HSSFCell[lengthOfColumns]; ListIterator<String> innerListIterator = innerList.listIterator(); while (innerListIterator.hasNext()) { int columnIndex = innerListIterator.nextIndex(); headerColumns[columnIndex] = headerRow[rowIndex] .createCell(columnIndex); String cellValue = innerListIterator.next(); headerColumns[columnIndex].setCellValue(cellValue); headerColumns[columnIndex].setCellStyle(columnHeaderStyle); } } // Create other Row(s) while (outerListIterator.hasNext()) { int rowIndex = outerListIterator.nextIndex(); List<String> innerList = outerListIterator.next(); headerRow[rowIndex] = sheet.createRow((short) rowIndex); HSSFCell[] headerColumns = new HSSFCell[innerList.size()]; // Create Data for the Header Row ListIterator<String> innerListIterator = innerList.listIterator(); while (innerListIterator.hasNext()) { int columnIndex = innerListIterator.nextIndex(); headerColumns[columnIndex] = headerRow[rowIndex] .createCell(columnIndex); String cellValue = innerListIterator.next(); headerColumns[columnIndex].setCellValue(cellValue); headerColumns[columnIndex].setCellStyle(rowCellStyle); } } return workBook; }

Grid with Multiple Row, Multiple Action Form Submit using List of Beans

Recently I worked on a web application with following requirements:

• Display a grid / table of records from database.
• Provide pagination with configurable records per page & page per blacks.
• Pagination with sorting on one or more column.
• Option to select zero or more records (check-box) and perform any action (add / edit / delete / modify / status update etc)
• Perform search based on some criteria and display result in same grid. All above options to be available in search result.

The grid / table would look like following:

Technologies and tools used:

• Java 1.6 or above
• Spring 3.1
• Any persistence framework (JPA used in this example)
• Eclipse (with dynamic web application support)
• Jquery

Steps for creating web application with above requirements:

Step 1: Create Web Application

Open eclipse and create a Dynamic Web Project

Enter project name as GridWeb

Step 2: Copy required JAR files

After the dynamic web application is created, copy the following JAR files in WEB-INF/lib folder:

• All Spring 3.1 related JAR files
• Commons libs (beanutil, cli, collections, digester, discovery, file-upload, io, lang, logging.
• Servlets & JSP api jars
• jstl, antlr,
• persistence framework related jars (in this case JPA related jar)

Step 3: Provide Spring MVC support

After the JAR files are copied, provide the support for Spring MVC.

a) Add dispatcher servlet in web.xml

<context-param> <param-name>contextConfigLocation</param-name> <param-value>/WEB-INF/config/grid-service.xml /WEB-INF/config/grid-persistence.xml </param-value> </context-param> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener> <servlet> <servlet-name>grid</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <init-param> <param-name>contextConfigLocation</param-name> <param-value> /WEB-INF/config/grid-servlet.xml </param-value> </init-param> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>grid</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping>

Step 4: Add Following spring config files:

• grid-servlet.xml
• grid-service.xml
• grid-persistence.xml
• jdbc.properties
• log4j.properties

Step 5: Add Spring Controller and Form Classes:

File: GridController.java

package com.test.demo.grid.web; @Controller @RequestMapping("/grid") public class GridController { private GridService myGridService; @Autowired private MessageSource messageSource; @Inject public GridController(@Named("gridService") GridService gridService) { this.myGridService = gridService; } public GridService getGridService() { return myGridService; } @RequestMapping(value = "gridHome", method = RequestMethod.GET) public String findGrid(HttpServletRequest request) { System.out.println("Find Grid Invoked"); if(null==PaginationTag.getContextPath()) { String contextPathVar = request.getContextPath(); PaginationTag.setContextPath(contextPathVar); } return "grid/gridHome"; } @RequestMapping(value = "gridHomeAjax") public String empHomeAjax(@ModelAttribute("baseBean") BaseBean baseBean, HttpServletRequest request, Model model) { System.out.println("************ gridHomeAjax requested ***********"); // Update PaginationDetails from Properties File PaginationBean pb = baseBean.getPaginationBean(); pb.setMaxResult(Integer.parseInt(messageSource.getMessage(ApplicationConstants.Pagination.KEY_RECORD_PER_PAGE, null, Locale.ENGLISH))); pb.setFirstResult(); if (StringUtils.isNullOrBlank(pb.getSortColumn())) { // Provide default sorting order & column if not already present pb.setNavigationProperties("1", "C1"); } baseBean.setPaginationBean(pb); // Do something to interact with database baseBean = getGridService().getPlayers(baseBean); model.addAttribute("baseBean", baseBean); return "grid/ajax/gridHomeAjax"; } }

File: BaseBean.java

package com.test.demo.grid.web.beans; public class BaseBean { private User loggedInUser; private PaginationBean paginationBean = new PaginationBean(); private SearchBean searchBean = new SearchBean(); private List pageItemList = new ArrayList(0); private String actionName; public String toString() { String message = ""; try { message = BeanUtils.describe(this).toString(); } catch (Exception e) { // Eat Exception Do Nothing } return message; } /*** All getters & setters */ }

Notes: The getter for the domain object list must be type specific. Check below the text in bold.

public List<Player> getPageItemList() {

return pageItemList;

}

• In above sample code, the getter has a return type of List<Player>, where Player is the domain object whose list will be displayed in the grid.
• If we have to display the grid of two different types (domain objects) on two different pages, we need two lists in the BaseBean.
• For example:

private List pageItemList = new ArrayList(0); // For playerList private List pageItemList2 = new ArrayList(0); // For employeeList public List<Player> getPageItemList() { return pageItemList; } public List<Employee> getPageItemList2() { return pageItemList2; }

Step 6: Add JSP files for viewing grid:

File: gridHomeAjax.jsp

<c:forEach items="${baseBean.pageItemList}" var="pageItem" varStatus="status"> <tr align="center" class="courseRow"> <td><input name="pageItemList[${status.index}].id" value="${pageItem.id}" type="checkbox" class="case"/></td> <td>${pageItem.id}</td> <td>${pageItem.firstName} ${pageItem.lastName}</td> <td><input name="pageItemList[${status.index}].email" value="${pageItem.email}" type="text" class="case"/></td> <td><input name="pageItemList[${status.index}].team" value="${pageItem.team}" type="text" class="case"/></td> <td>${pageItem.status}</td> <td>&nbsp;</td> </tr> </c:forEach>

In above JSP, we will be displaying a list of Player details in grid / table format. Reach row will represent one player. Each column will represent different property of player (name, email, team etc...)

Notice that modelAttribute="baseBean" is defined in the <form:form> tag of spring. On submission of form spring will parse the values from HttpServletRequest object, and populate the BaseBean obj and pass it to the Spring Controller.

One way to define the player details as form input (so that these input fields are populated in the appropriate bean/list object) is:

CHECKBOX: <input name="pageItemList[${status.index}].id" value="${pageItem.id}" type="checkbox" />

TEXT BOX: <input name="pageItemList[${status.index}].email" value="${pageItem.email}" type="text" />

Understanding the Framework: The core of this framework is the bean class that holds all the form data. The implementation of this form class in struts is ActionForm or DynaActionForm and in Spring is Command object. In the latest versions of almost all web frameworks, any POJO can be used Form.

I am using BaseBean as the core component which is a POJO. The baseBean holds following details:

PaginationBean paginationBean:

Hold the pagination details (pageNO,totalRecord,sort column,order etc)

SearchBean searchBean;

Holds the fields on which search can be made.

List pageItemList;

Contains list of beans representing rows in the grid.

String actionName;

Keeps the data (value from a drop-down for example) on which a particular action could be taken.

User loggedInUser;

If the application is user based and registration+login is required and the grid is to be populated based on the logged in user's access right / details.

For every request related to pagination (navigation to any page, sorting any column), clicking any button after selecting one or more checkbox(es), applying search etc the complete base bean is submitted as POST request.

JSP Form Tag: gridHomeAjax.jsp

<form:form method="post" modelAttribute="baseBean" id="gridForm" action="${pageContext.request.contextPath}/grid/gridHomeAjax"> <!-- Form Elements for Search --> <!-- Iteration Login to iterate pageItemList of BaseBean --> <!-- Each Row of Grid --> <!-- Iteration Closed --> <!-- Differnt Action Buttons: onclick= doSearchSubmit() --> <!-- Pagination Elements as hidden form fields --> </form:form>

AJAX / Jquery Code for form Submit: gridHome.jsp

function doSearchSubmit() { var url="${pageContext.request.contextPath}/grid/gridHomeAjax"; $.ajax({type:'POST', url: url, data:$('#gridForm').serialize(), success: function(response) { $('#grid').html(response); }}); return false; }

Controller method to accept the submitted form: GridController.java

@RequestMapping(value = "gridHomeAjax") public String empHomeAjax(@ModelAttribute("baseBean") BaseBean baseBean, HttpServletRequest request, Model model) { System.out.println("************ gridHomeAjax requested ***********"); // Do Something model.addAttribute("baseBean", baseBean); return "grid/ajax/gridHomeAjax"; }

Implementing the Pagination: I have written a tld to display the pagination part.

Step 1: Write/use class file to generate the pagination. PaginationTag.java

Step 2: Provide the config details of PaginationTag in grid-tags.tld

Step 3: Access the pagination tag in JSP:

<%@ taglib prefix="grid" uri="/tags/grid-tags"%>

<grid:pagination totalRecord="50"

currentPageIndex="2"

recordsPerPage="5"

pagesPerBlock="3" />

Developer need to pass the totalRecord & currentPageIndex only as other properties are optional & take default value if not supplied.

Above given values should be get from baseBean which is already set as request attribute in the controller method. See gridHomeAjax.jsp

Using Pagination:

Step 1: Keep the pagination properties in form. We have PaginationBean object in BaseBean form. PaginationBean contains all the required properties of pagination.

Refer BaseBean.java

Step 2: Use the pagination properties as hidden form field in JSP. Refer gridHomeAjax.jsp

<input type="hidden" name="paginationBean.sortColumn" id="sortColumn" value="${baseBean.paginationBean.sortColumn}"> <input type="hidden" name="paginationBean.sortIndicator" id="sortIndicator" value="${baseBean.paginationBean.sortIndicator}"> <input type="hidden" name="paginationBean.currentPageIndex" id="currentPageIndex" value="${baseBean.paginationBean.currentPageIndex}"> <input type="hidden" name="paginationBean.currentBlockIndex" id="currentBlockIndex" value="${baseBean.paginationBean.currentBlockIndex}">

Step 3: Update the respective pagination properties whenever required before form submit.

Click on a particular page no: Update the currentPageIndex to the required page no in JavaScript.

function setPageIndexSubmit(nextPageIndex) { $('#currentPageIndex').val(nextPageIndex); doSearchSubmit(); return false; }

Database Code (in JPA) to get selected records for a particular page: GridDAOImpl

public BaseBean getPlayers(BaseBean baseBean) { PaginationBean pb = baseBean.getPaginationBean(); StringBuilder queryString = new StringBuilder().append("SELECT e FROM Player e where e.activeInd=1"); Query query = getEntityManager().createQuery(queryString.toString()); // Apply Pagination Details query.setFirstResult(pb.getFirstResult()); query.setMaxResults(pb.getMaxResult()); List playerList = query.getResultList(); }

Sorting on a column::

Click on a particular Column for sorting: Update the sortColumn and sortIndicator to the required column name & sorting order in a JavaScript. Again update the currentPageIndex to 1 to reset the page no.

JSP Code for Sorting: gridHomeAjax.jsp

<td><a class="link" href="#" onclick="sortColumnSubmit('C1', '${baseBean.paginationBean.toggledSortIndicator}')">Full Name <%=pb.getSortImage("C1")%></a></td>

Above will allow sorting the grid on the column “Full Name”. The below JS will set the value of column to be sorted.

JS Code to submit form for given sorted column: gridHome.jsp

function sortColumnSubmit(sortColumn, sortIndicator) { $('#sortColumn').val(sortColumn); // Set column to be sorted $('#sortIndicator').val(sortIndicator); // set sort order $('#currentPageIndex').val(1); // reset page no to 1 doSearchSubmit(); return false; }

Java Code to apply column based sorting (using order by clause):

Storing code for sorting order (asc/desc): PaginationBean.java

private static Map<String, String> sortIndicatorMapping = new HashMap<String, String>(); static { sortIndicatorMapping.put("1", "asc"); sortIndicatorMapping.put("0", "DESC"); }

Storing code for sorting column name in db: in respective DAOImpl class

public class GridDAOImpl implements GridDAO { private static Map<String, String> sortIndicatorMapping = new HashMap<String, String>(); private static Map<String, String> sortColumnMapping = new HashMap<String, String>(); static { sortIndicatorMapping.put("1", "asc"); sortIndicatorMapping.put("0", "DESC"); sortColumnMapping.put("C1", ApplicationConstants.Pagination.COLUMN_PLAYER_NAME); sortColumnMapping.put("C2", ApplicationConstants.Pagination.COLUMN_PLAYER_EMAIL); sortColumnMapping.put("C3", ApplicationConstants.Pagination.COLUMN_PLAYER_TEAM); } public BaseBean getPlayers(BaseBean baseBean) { PaginationBean pb = baseBean.getPaginationBean(); StringBuilder queryString = new StringBuilder().append("SELECT e FROM Player e where e.activeInd=1"); // apply sorting of a column queryString.append(" order by "+getSortColumnValue(pb.getSortColumn()) + " " + getSortIndicatorValue(pb.getSortIndicator())); Query query = getEntityManager().createQuery(queryString.toString()); // Execute query ultimateResultList = query.getResultList(); baseBean.setPageItemList(ultimateResultList); return baseBean; }

Running the grid framework with pagination

Start the web application in Tomcat container.

Open the link http://localhost:9080/GridWeb/ and click on the GridExample provided on left side.

Frequently Asked Questions:

Q. I want to apply sorting on a different page (and different db column), for which db query is in the same DAOImpl (GridDAOImpl for example). How would I apply sorting as the sorting column logical name “C1” is refering to another column in another table?

Ans: For different pages under the same module you can use different logical columns. For example C1-C5 for page one, C6-C10 for page two in a module.

public class GridDAOImpl implements GridDAO { private static Map<String, String> sortColumnMapping = new HashMap<String, String>(); static { // Mapping for Page1: PlayerList sortColumnMapping.put("C1", ApplicationConstants.Pagination.COLUMN_PLAYER_NAME); sortColumnMapping.put("C2", ApplicationConstants.Pagination.COLUMN_PLAYER_EMAIL); sortColumnMapping.put("C3", ApplicationConstants.Pagination.COLUMN_PLAYER_TEAM); // Mapping for Page2: EmployeeList sortColumnMapping.put("C6", ApplicationConstants.Pagination.COLUMN_EMP_NAME); sortColumnMapping.put("C7", ApplicationConstants.Pagination.COLUMN_EMP_EMAIL); sortColumnMapping.put("C8", ApplicationConstants.Pagination.COLUMN_EMP_TEAM); } }

If you want to implement sorting on different columns in different modules, you can use same logical column name C1, and provide the actual column name in respective module's DAOImpl classes.

Q. Is it necessary to have <Player> in the getter of my pageItemList [public List<Player> getPageItemList()]?

Ans: If you want to pass more than one property (id for example) of Player (your domain object) to the server, you need to return List<Player>. In this case your field name in html will be pageItemList[i].<propertyName>

<c:forEach items="${baseBean.pageItemList}" var="pageItem" varStatus="status"> <tr align="center" > <td><input name="pageItemList[${status.index}].id" value="${pageItem.id}" type="checkbox" class="case"/></td> <td><input name="pageItemList[${status.index}].email" value="${pageItem.email}" type="text" class="case"/></td> <td><input name="pageItemList[${status.index}].team" value="${pageItem.team}" type="text" class="case"/></td> </tr> </c:forEach>

If you want to pass only one property (id for example, it will help you identify different objects of your domain class), you must return only List. In this case your field name in html will be pageItemList[i]

<c:forEach items="${baseBean.pageItemList}" var="player" varStatus="status"> <tr align="center" > <td><input name="pageItemList[${status.index}]" value="${player.id}" type="checkbox" /></td> <td>${player.firstName}</td> </tr> </c:forEach>

Q. Is it mandatory to use List only as the collection interface? Cannot I use Set or Collection instead?

Ans: It is mandatory to use List only to store the group of domain objects. If you are using any other collection API (Set or Collection interface) in the form, spring will not be able to convert the html form fields into populated command object.