This tutorial is part of the following reporting tutorial series that uses Jasper, DynamicJasper, and Apache POI:
Spring 3 - Apache POI - Hibernate: Creating an Excel Report Tutorial
Spring 3 - DynamicJasper - Hibernate Tutorial: Concatenating a DynamicReport
Spring 3 - DynamicJasper - Hibernate Tutorial: Concatenating a Subreport
Spring 3 - DynamicJasper - Hibernate Tutorial: Using Plain List
Spring 3 - DynamicJasper - Hibernate Tutorial: Using JRDataSource
Spring 3 - DynamicJasper - Hibernate Tutorial: Using HQL Query
All of these tutorials produce the same document, and all of them demonstrate different ways of creating the same report.
What is Apache POI?
The Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files using Java. In addition, you can read and write MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008).
Source: http://poi.apache.org/
Background
Before we start our application, let's preview first the final print document:Our document is a simple Excel document. It's a Sales Report for a list of power supplies. The data is retrieved from a MySQL database.
Domain
Notice that for each Power Supply entry there's a common set of properties:id brand model maximum power price efficiency
Development
Domain
We'll start our application by declaring the domain object PowerSupplyPowerSupply.java
package org.krams.tutorial.domain; import java.io.Serializable; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.GeneratedValue; import javax.persistence.Id; import javax.persistence.Table; /** * A simple POJO containing the common properties of a Power Supply * This is an annotated Hibernate entity. * * @author Krams at {@link http://krams915@blogspot.com} */ @Entity @Table(name = "POWER_SUPPLY") public class PowerSupply implements Serializable { private static final long serialVersionUID = 8634209606034270882L; @Id @Column(name = "ID") @GeneratedValue private Long id; @Column(name = "BRAND") private String brand; @Column(name = "MODEL") private String model; @Column(name = "MAXIMUM_POWER") private String maximumPower; @Column(name = "PRICE") private Double price; @Column(name = "EFFICIENCY") private Double efficiency; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getBrand() { return brand; } public void setBrand(String brand) { this.brand = brand; } public String getModel() { return model; } public void setModel(String model) { this.model = model; } public String getMaximumPower() { return maximumPower; } public void setMaximumPower(String maximumPower) { this.maximumPower = maximumPower; } public Double getPrice() { return price; } public void setPrice(Double price) { this.price = price; } public Double getEfficiency() { return efficiency; } public void setEfficiency(Double efficiency) { this.efficiency = efficiency; } }PowerSupply is a simple POJO containing six private fields. Each of these fields have been annotated with @Column and assigned with corresponding database column names.
ID BRAND MODEL MAXIMUM_POWER PRICE EFFICIENCY
Service
We'll be declaring a single service named DownloadService. This service is the heart of the application that will process and retrieve the report document.The service will run the following steps:
1. Create new workbook 2. Create new worksheet 3. Define starting indices for rows and columns 4. Build layout 5. Fill report 6. Set the HttpServletResponse properties 7. Write to the output stream
DownloadService.java
package org.krams.tutorial.service; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.hibernate.Query; import org.hibernate.Session; import org.hibernate.SessionFactory; import org.krams.tutorial.domain.PowerSupply; import org.krams.tutorial.report.FillManager; import org.krams.tutorial.report.Layouter; import org.krams.tutorial.report.Writer; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import javax.annotation.Resource; /** * Service for processing Apache POI-based reports * * @author Krams at {@link http://krams915@blogspot.com} */ @Service("downloadService") @Transactional public class DownloadService { private static Logger logger = Logger.getLogger("service"); @Resource(name="sessionFactory") private SessionFactory sessionFactory; /** * Processes the download for Excel format. * It does the following steps: * <pre>1. Create new workbook * 2. Create new worksheet * 3. Define starting indices for rows and columns * 4. Build layout * 5. Fill report * 6. Set the HttpServletResponse properties * 7. Write to the output stream * </pre> */ @SuppressWarnings("unchecked") public void downloadXLS(HttpServletResponse response) throws ClassNotFoundException { logger.debug("Downloading Excel report"); // 1. Create new workbook HSSFWorkbook workbook = new HSSFWorkbook(); // 2. Create new worksheet HSSFSheet worksheet = workbook.createSheet("POI Worksheet"); // 3. Define starting indices for rows and columns int startRowIndex = 0; int startColIndex = 0; // 4. Build layout // Build title, date, and column headers Layouter.buildReport(worksheet, startRowIndex, startColIndex); // 5. Fill report FillManager.fillReport(worksheet, startRowIndex, startColIndex, getDatasource()); // 6. Set the response properties String fileName = "SalesReport.xls"; response.setHeader("Content-Disposition", "inline; filename=" + fileName); // Make sure to set the correct content type response.setContentType("application/vnd.ms-excel"); //7. Write to the output stream Writer.write(response, worksheet); } /** * Retrieves the datasource as as simple Java List. * The datasource is retrieved from a Hibernate HQL query. */ @SuppressWarnings("unchecked") private List<PowerSupply> getDatasource() { // Retrieve session Session session = sessionFactory.getCurrentSession(); // Create query for retrieving products Query query = session.createQuery("FROM PowerSupply"); // Execute query List<PowerSupply> result = query.list(); // Return the datasource return result; } }This service is our download service for generating the report document. It should be clear what each line of code is doing.
The service has been divided into separate classes to encapsulate specific jobs.
The Layouter
The purpose of the Layouter is to layout the design of the report. Here's where we declare the dynamic columns and special properties of the document.Layouter.java
package org.krams.tutorial.report; import java.util.Date; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; /** * Builds the report layout, the template, the design, the pattern or whatever synonym you may want to call it. * * @author Krams at {@link http://krams915@blogspot.com} */ public class Layouter { private static Logger logger = Logger.getLogger("service"); /** * Builds the report layout. * <p> * This doesn't have any data yet. This is your template. */ public static void buildReport(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Set column widths worksheet.setColumnWidth(0, 5000); worksheet.setColumnWidth(1, 5000); worksheet.setColumnWidth(2, 5000); worksheet.setColumnWidth(3, 5000); worksheet.setColumnWidth(4, 5000); worksheet.setColumnWidth(5, 5000); // Build the title and date headers buildTitle(worksheet, startRowIndex, startColIndex); // Build the column headers buildHeaders(worksheet, startRowIndex, startColIndex); } /** * Builds the report title and the date header * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset */ public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the report title Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // Create cell style for the report title HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); // Create report title HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("Sales Report"); cellTitle.setCellStyle(cellStyleTitle); // Create merged region for the report title worksheet.addMergedRegion(new CellRangeAddress(0,0,0,5)); // Create date header HSSFRow dateTitle = worksheet.createRow((short) startRowIndex +1); HSSFCell cellDate = dateTitle.createCell(startColIndex); cellDate.setCellValue("This report was generated at " + new Date()); } /** * Builds the column headers * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset */ public static void buildHeaders(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the headers Font font = worksheet.getWorkbook().createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create cell style for the headers HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle(); headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headerCellStyle.setFillPattern(CellStyle.FINE_DOTS); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headerCellStyle.setWrapText(true); headerCellStyle.setFont(font); headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN); // Create the column headers HSSFRow rowHeader = worksheet.createRow((short) startRowIndex +2); rowHeader.setHeight((short) 500); HSSFCell cell1 = rowHeader.createCell(startColIndex+0); cell1.setCellValue("Id"); cell1.setCellStyle(headerCellStyle); HSSFCell cell2 = rowHeader.createCell(startColIndex+1); cell2.setCellValue("Brand"); cell2.setCellStyle(headerCellStyle); HSSFCell cell3 = rowHeader.createCell(startColIndex+2); cell3.setCellValue("Model"); cell3.setCellStyle(headerCellStyle); HSSFCell cell4 = rowHeader.createCell(startColIndex+3); cell4.setCellValue("Max Power"); cell4.setCellStyle(headerCellStyle); HSSFCell cell5 = rowHeader.createCell(startColIndex+4); cell5.setCellValue("Price"); cell5.setCellStyle(headerCellStyle); HSSFCell cell6 = rowHeader.createCell(startColIndex+5); cell6.setCellValue("Efficiency"); cell6.setCellStyle(headerCellStyle); } }
The FillManager
The purpose of the FillManager is to fill the Excel report with data from the data source.FillManager.java
package org.krams.tutorial.report; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.CellStyle; import org.krams.tutorial.domain.PowerSupply; public class FillManager { /** * Fills the report with content * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset * @param datasource the data source */ public static void fillReport(HSSFSheet worksheet, int startRowIndex, int startColIndex, List<PowerSupply> datasource) { // Row offset startRowIndex += 2; // Create cell style for the body HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle(); bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); bodyCellStyle.setWrapText(true); // Create body for (int i=startRowIndex; i+startRowIndex-2< datasource.size()+2; i++) { // Create a new row HSSFRow row = worksheet.createRow((short) i+1); // Retrieve the id value HSSFCell cell1 = row.createCell(startColIndex+0); cell1.setCellValue(datasource.get(i-2).getId()); cell1.setCellStyle(bodyCellStyle); // Retrieve the brand value HSSFCell cell2 = row.createCell(startColIndex+1); cell2.setCellValue(datasource.get(i-2).getBrand()); cell2.setCellStyle(bodyCellStyle); // Retrieve the model value HSSFCell cell3 = row.createCell(startColIndex+2); cell3.setCellValue(datasource.get(i-2).getModel()); cell3.setCellStyle(bodyCellStyle); // Retrieve the maximum power value HSSFCell cell4 = row.createCell(startColIndex+3); cell4.setCellValue(datasource.get(i-2).getMaximumPower()); cell4.setCellStyle(bodyCellStyle); // Retrieve the price value HSSFCell cell5 = row.createCell(startColIndex+4); cell5.setCellValue(datasource.get(i-2).getPrice()); cell5.setCellStyle(bodyCellStyle); // Retrieve the efficiency value HSSFCell cell6 = row.createCell(startColIndex+5); cell6.setCellValue(datasource.get(i-2).getEfficiency()); cell6.setCellStyle(bodyCellStyle); } } }
The Writer
The purpose of the Writer is to write the "exported" worksheet to the output stream. Once the document has been written to the stream, the user will receive the document ready to be downloaded.Writer.java
package org.krams.tutorial.report; import javax.servlet.ServletOutputStream; import javax.servlet.http.HttpServletResponse; import org.apache.log4j.Logger; import org.apache.poi.hssf.usermodel.HSSFSheet; /** * Writes the report to the output stream * * @author Krams at {@link http://krams915@blogspot.com} */ public class Writer { private static Logger logger = Logger.getLogger("service"); /** * Writes the report to the output stream */ public static void write(HttpServletResponse response, HSSFSheet worksheet) { logger.debug("Writing report to the stream"); try { // Retrieve the output stream ServletOutputStream outputStream = response.getOutputStream(); // Write to the output stream worksheet.getWorkbook().write(outputStream); // Flush the stream outputStream.flush(); } catch (Exception e) { logger.error("Unable to write report to the output stream"); } } }
Controller
We've completed the domain and service layer of the application. Since we're developing a Spring MVC web application, we're required to declare a controller that will handle the user's request.DownloadController.java
package org.krams.tutorial.controller; import javax.annotation.Resource; import javax.servlet.http.HttpServletResponse; import org.apache.log4j.Logger; import org.krams.tutorial.service.DownloadService; import org.springframework.stereotype.Controller; import org.springframework.ui.Model; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; /** * Handles download requests * * @author Krams at {@link http://krams915@blogspot.com} */ @Controller @RequestMapping("/download") public class DownloadController { private static Logger logger = Logger.getLogger("controller"); @Resource(name="downloadService") private DownloadService downloadService; /** * Downloads the report as an Excel format. * <p> * Make sure this method doesn't return any model. Otherwise, you'll get * an "IllegalStateException: getOutputStream() has already been called for this response" */ @RequestMapping(value = "/xls", method = RequestMethod.GET) public void getXLS(HttpServletResponse response, Model model) throws ClassNotFoundException { logger.debug("Received request to download report as an XLS"); // Delegate to downloadService. Make sure to pass an instance of HttpServletResponse downloadService.downloadXLS(response); } }DownloadController is a simple controller that handles download requests. It delegates report generation to the DownloadService. Notice we're required to pass the HttpServletResponse to the service.
Database Configuration
We've completed the MVC module of the application. However we haven't created yet the Hibernate configuration and the MySQL database.Our first task is to create an empty MySQL database.
Here are the steps:
1. Run MySQL
2. Open MySQL admin
3. Create a new database mydb
In this tutorial I've setup a local MySQL database and used phpmyadmin to administer it.
Next, we'll be declaring a hibernate-context.xml configuration file. Its purpose is to contain all of Spring-related configuration for Hibernate.
hibernate-context.xml
This configuration requires two external configurations further:
spring.properties
# database properties app.jdbc.driverClassName=com.mysql.jdbc.Driver app.jdbc.url=jdbc:mysql://localhost/mydb app.jdbc.username=root app.jdbc.password= #hibernate properties hibernate.config=/WEB-INF/hibernate.cfg.xml
hibernate.cfg.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd"> <hibernate-configuration> <session-factory> <!-- We're using a MySQL database so the dialect needs to be MySQL as well --> <!-- Also we want to use MySQL's InnoDB engine --> <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property> <!-- Enable this to see the Hibernate generated SQL statements in the logs --> <property name="show_sql">false</property> <!-- Setting this to 'create' will drop our existing database and re-create a new one. This is only good for testing. In production, this is a bad idea! --> <property name="hbm2ddl.auto">create</property> </session-factory> </hibernate-configuration>
The Import.SQL
After declaring all the Hibernate-related configuration, let's now declare a SQL script that will populate our database with a sample data automatically.import.sql
insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Corsair', 'CMPSU-750TX', '750W', '109.99', '0.80') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Antec', 'NEO ECO 620C', '620W', '69.99', '0.80') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'OCZ', 'OCZ700MXSP', '700W', '89.99', '0.86') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Thermaltake', 'W0070RUC', '430W', '43.99', '0.65') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'COOLER MASTER', 'RS-460-PSAR-J3', '460W', '29.99', '0.70') insert into POWER_SUPPLY (ID, BRAND, MODEL, MAXIMUM_POWER, PRICE, EFFICIENCY) values (null, 'Rosewill', 'RG530-S12', '530W', '54.99', '0.80')Make sure to place this document under the classpath. Hibernate will automatically import the contents of this document everytime your start the application. This is dictated by the hbm2ddl.auto setting we declared in the hibernate.cfg.xml earlier.
We're not required to create this import.sql file. We could of course create a MySQL SQL script and import it directly to the database, or add the data manually in the database. I just believe this is convenient for development purposes.
Spring MVC Configuration
We've declared all the necessary classes and Hibernate-related configuration of the application. However, we haven't created yet the required Spring MVC configuration.Let's begin with the web.xml
web.xml
<servlet> <servlet-name>spring</servlet-name> <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class> <load-on-startup>1</load-on-startup> </servlet> <servlet-mapping> <servlet-name>spring</servlet-name> <url-pattern>/krams/*</url-pattern> </servlet-mapping> <listener> <listener-class>org.springframework.web.context.ContextLoaderListener</listener-class> </listener>Take note of the URL pattern. When accessing any pages in our MVC application, the host name must be appended with
/kramsIn the web.xml we declared a servlet-name spring. By convention, we must declare a spring-servlet.xml.
spring-servlet.xml
<!-- Declare a view resolver for resolving JSPs --> <bean id="viewResolver" class="org.springframework.web.servlet.view.InternalResourceViewResolver" p:prefix="/WEB-INF/jsp/" p:suffix=".jsp" />By convention, we must declare an applicationContext.xml as well.
applicationContext.xml
<!-- Activates various annotations to be detected in bean classes --> <context:annotation-config /> <!-- Scans the classpath for annotated components that will be auto-registered as Spring beans. For example @Controller and @Service. Make sure to set the correct base-package--> <context:component-scan base-package="org.krams.tutorial" /> <!-- Configures the annotation-driven Spring MVC Controller programming model. Note that, with Spring 3.0, this tag works in Servlet MVC only! --> <mvc:annotation-driven /> <!-- Loads Hibernate related configuration --> <import resource="hibernate-context.xml" />
Run the Application
We've completed the application. Our last task is to run the application and download the report.To run the application, open your browser and enter the following URL:
http://localhost:8080/spring-poi-hibernate/krams/download/xlsThis will automatically download the report document. Again, here's the final screenshot of the document:
Conclusion
That's it. We've managed to build a simple Spring MVC 3 application with reporting capabilities. We used Apache POI to generate the dynamic Excel reports and Hibernate for the ORM framework. Lastly, we used a plain Java List as the data source where the data is retrieved from a MySQL database.Download the project
You can access the project site at Google's Project Hosting at http://code.google.com/p/spring-poi-integration-tutorial/
You can download the project as a Maven build. Look for the spring-poi-hibernate.zip in the Download sections.
You can run the project directly using an embedded server via Maven.
For Tomcat: mvn tomcat:run
For Jetty: mvn jetty:run
If you want to learn more about Spring MVC and integration with other technologies, feel free to read my other tutorials in the Tutorials section.
Share the joy:
|
Subscribe by reader Subscribe by email Share
does this work with spring 2.5.6?
ReplyDeletecould you please tell how to send this excel as an attachmet in mail.
ReplyDeleteSantosh
try this...
ReplyDeleteon picture...
http://www.megaupload.com/?d=JT3CQSDH
Awesome example! Thanks! you saved me a lot of time!
ReplyDeleteHi i need to store excel data into oracle using jsp-servlet or hibernet
ReplyDeleteplease help me in this
This comment has been removed by a blog administrator.
ReplyDeleteI have tried this but its not displaying the report in the browser instead its prompting to Open/Save as ( windows promt). Any ideas?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThanks. It's presented in a very organized way and better than some other examples I came across. I had to insert this filter in web.xml to make the session factory work.
ReplyDeletehibernateFilter
org.springframework.orm.hibernate4.support.OpenSessionInViewFilter
sessionFactoryBeanName
sessionFactory
hibernateFilter
/*
Great work !!
ReplyDeleteGreat. Appreciate if you can create a tutorial also regarding uploading an excel file and at the same time inserting the excel data to a database.
ReplyDeleteHi,
ReplyDeleteI just want to export mysql data into excel. I am completely new on these technology. Could you please guide me how to do that and what line of code/conf I have to remove.
I need to create footer , can you please tell me how do i create footer section?
ReplyDeleteThanks a lot for this post, very nicely explained. Helped a lot.
ReplyDeleteSee simple example for create excel file in java here http://www.javaproficiency.com/2015/03/create-xls-file-in-java.html
ReplyDeleteSimple CRUD with JSF and Hibernate - GeekOnJava:
ReplyDeleteHibernate CRUD example using HQL query language:
Solve QuerySyntaxException in Hibetnate:
Hello - Could you please update your projects with latest dependencies? Your all tutorials/project are great. I like the great people. Now your tutorials has become somewhat old which needs to be upgraded. Please do some needful.
ReplyDeleteRegards,
Savani
I have read your blog its very attractive and impressive. I like it your blog.
ReplyDeleteSpring online training Spring online training Spring Hibernate online training Spring Hibernate online training Java online training
spring training in chennai spring hibernate training in chennai
I have read your blog its very attractive and impressive. I like it your blog.
ReplyDeleteSpring online training Spring online training Spring Hibernate online training Spring Hibernate online training Java online training
spring training in chennai spring hibernate training in chennai
Thank for this tutorial:
ReplyDeleteSee my page: ao thun nam gia si