It's breeze to create excel reports using Play framework. Let me run thru an example.
Let's create a sample project - excel-example.
$ play new excel-example
Then eclipsify and import into eclipse.
$ play eclipsify excel-example
Edit the dependencies.yml and include the
excel module
# Application dependencies
require:
- play 1.2.3
- play -> excel 1.2.3
Run the play dependencies command. Play connects to the web and downloads the excel-module into the modules directory
$ play deps excel-example
~ _ _
~ _ __ | | __ _ _ _| |
~ | '_ \| |/ _' | || |_|
~ | __/|_|\____|\__ (_)
~ |_| |__/
~
~ play! 1.2.3, http://www.playframework.org
~
~ Resolving dependencies using /Users/play/excel-example/conf/dependencies.yml,
~
~ play->excel 1.2.3 (from playLocalModules)
~
~ Installing resolved dependencies,
~
~ modules/excel-1.2.3 -> /Users/play/frameworks/play-1.2.3/modules/excel-1.2.3
~
~ Done!
~
Plays excel module uses
jxls internally which uses
Apache POI library - the one and only one library in Java world for excel manipulation. All the dependencies of jxls are bundled with excel-module and dependencies managed.
However there is one
additional library that would be required to ouptut xslx excel formats - which is the newer format depending on Office Open XML documents. This is because xslx is basically a zip format.
Download the
ooxml-schemas-1.1.jar from the maven repository and put it in the lib directory of the excel-example project.
Then eclipsify again so that eclispe classpath reflects the new dependencies.
$ play eclipsify excel-example
Refresh the Project in eclipse.
Start the play server.
$ play run excel-example
~ _ _
~ _ __ | | __ _ _ _| |
~ | '_ \| |/ _' | || |_|
~ | __/|_|\____|\__ (_)
~ |_| |__/
~
~ play! 1.2.3, http://www.playframework.org
~
~ Ctrl+C to stop
~
JPDA port 8000 is already used. Will try to use any free port for debugging
Listening for transport dt_socket at address: 50675
00:54:21,975 INFO ~ Starting /Users/play/excel-example
00:54:21,979 INFO ~ Module excel is available (/Users/play/frameworks/play-1.2.3/modules/excel-1.2.3)
00:54:22,603 WARN ~ You're running Play! in DEV mode
00:54:22,769 INFO ~ Listening for HTTP on port 9000 (Waiting a first request to start) ...
check at localhost:9000, you should see a welcome page (I'm just using command line to do a quick check).
$ curl http://localhost:9000
The next step is to Create Sample Excel template file
I have created a simple xslx file which shows displays the list of customers and their phone numbers. You can get the file
here. Below is an image for easy reference. Using jxls is a great advantage. Your excel template can contain all required formatting, formulas, macros etc which will be preserved intact. You need to use specific notation (common these days in groovy, ruby language) to indicate placement of data. Refer the below image.
Save this file under under
excel-example/app/views/Application/customerphonelist.xlsx
Lets edit the default index.html which is under excel-example/app/views/Application/
The home page contains just one link to download the excel report as shown below
#{extends 'main.html' /}
#{set title:'Home' /}
<a href="@{customerphonelist()}"/> Download Customer Phone Numbers Excel Report </a>
Edit the controller
Application.java and add a method -
customerphonelist() which outputs the excel file as shown below:
package controllers;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import org.apache.commons.lang.RandomStringUtils;
import models.Customer;
import play.Logger;
import play.modules.excel.RenderExcel;
import play.mvc.Controller;
import play.mvc.With;
@With(ExcelControllerHelper.class)
public class Application extends Controller {
public static void index() {
render();
}
public static void customerphonelist(){
Logger.info("Generating Customer Phone List Excel report ");
request.format = "xlsx";
Date date = new Date();
String user = "Bob";
List customers = new ArrayList();
for (int i = 0 ;i < 10; i++){
customers.add(new Customer("Mr", RandomStringUtils.randomAlphabetic(15), RandomStringUtils.randomNumeric(10)));
}
renderArgs.put("date", date);
renderArgs.put("user", user);
renderArgs.put("customers", customers);
renderArgs.put(RenderExcel.RA_ASYNC, true);
renderArgs.put(RenderExcel.RA_FILENAME, "customer_list_report.xlsx");
render();
Logger.info("Completed Customer Phone List Excel report ");
}
}
Above at line no 11 -
@With(ExcelControllerHelper.class)
annotation is used. ExcelControllerHelper class is part of play excel module.
@With is an interceptor. This interceptor loads the excel template.
Line No 22 to 25 creates a Customer model class and populates it with some random data.
Line No 26 to 30 puts the objects in to the renderArgs map. These objects would be available as part of the actual excel file which can be used to render the dynamic content.
Line No 29 is of
special interest which sets the ASYNC to true. This is a very powerful feature of Play framework. This helps in transparent asynchronous rendering of the excel. You don't have to deal with Future, Promise, await, request.new etc and you get the power for nearly free. The existing HTTP request is suspended and then when the excel report generation is done, this thread is resumed and the response sent back to the client. Think about the fact that a large Excel table rendering might take as long as 500ms and blocking request handling thread for half second is NOT acceptable in a high performance web server like play which use only limited thread (N+1) to handle web requests. Read more about this
here.
Last step :), Open
http://localhost:9000
and click on the
Download Customer Phone Numbers Excel Report link. A excel report should be generated and should look some thing like below.
That completes the example.
