How to generate excel files on GAE?

Generating an Excel File in a standard Java EE way is a common and simply development task. You had probably achieved this work by using Apache POI. Unfortunately, use it on Google App Engine will result in a lot of errors because it uses some JRE Classes that aren’t supported by Google’s Cloud! This post is a step by step guide in order to quickly implement the mechanism for generating Excel files on Google App Engine. I also added a way to download generated files using GWT

Alternative to Apache POI

JExcelAPI is an open source Java API which allows Java developers to read Excel spreadsheets and to generate Excel spreadsheets dynamically.
In addition, it contains a mechanism which allows java applications to read in a spreadsheet, modify some cells and write out the new spreadsheet.

Features

  • Reads data from Excel 95, 97, 2000 workbooks
  • Reads and writes formulas (Excel 97 and later only)
  • Generates spreadsheets in Excel 2000 format
  • Supports font, number and date formatting
  • Supports shading and colouring of cells
  • Modifies existing worksheets
  • Supports image creation
  • Preserves macros on copy
  • Customizable logging

Limitations

  • JExcelApi does not generate or chart, graph or macro information. This information is however preserved when spreadsheets are copied
  • When adding images to a sheet, only PNG image formats are supported

Proof of Concept

We will create a small application that exports a list of users in an excel file.
A user is defined by two attributes : a first name and a last name.

Step 1 : Add JExcel API in the Classpath’s Application

If you don’t use Maven 2, just add the jar in WEB-INF/lib folder of your application and go to the next step.

  • Add JExcelAPI as Maven 2 Dependency :
    <dependency>
        <groupId>net.sourceforge.jexcelapi</groupId>
        <artifactId>jxl</artifactId>
        <version>2.6.10</version>
    </dependency>
    
  • Add MvnRepository.com as Maven 2 Repository :
    <repository>
         <id>mvnrepository.com</id>
         <name>MvnRepository</name>
         <url>http://www.mvnrepository.com</url>
    </repository>
    

Step 2 : Generate your Excel Export

  • Model object :
    public class User {
    
    	private String firstName;
    	private String lastName;
    
           /* ... Getters / Setters / Constructors ... */
    }
    
  • Class that generate the Excel File :
    	public ByteArrayOutputStream generateExcelReport() throws IOException, WriteException {
    
    		// Stream containing excel data
    		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    
    		// Create Excel WorkBook and Sheet
    		WritableWorkbook workBook = Workbook.createWorkbook(outputStream);
    		WritableSheet sheet = workBook.createSheet("User List", 0);
    
    		// Generates Headers Cells
    		WritableFont headerFont = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.BOLD);
    		WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
    		headerCellFormat.setBackground(Colour.PALE_BLUE);
    		sheet.addCell(new Label(1, 1, "LastName", headerCellFormat));
    		sheet.addCell(new Label(2, 1, "FirstName", headerCellFormat));
    
    		// Generates Data Cells
    		WritableFont dataFont = new WritableFont(WritableFont.TAHOMA, 12);
    		WritableCellFormat dataCellFormat = new WritableCellFormat(dataFont);
    		int currentRow = 2;
    		for (User user : getUsers()) {
    			sheet.addCell(new Label(1, currentRow, user.getLastName(),dataCellFormat));
    			sheet.addCell(new Label(2, currentRow, user.getFirstName(),dataCellFormat));
    			currentRow++;
    		}
    
    		// Write & Close Excel WorkBook
    		workBook.write();
    		workBook.close();
    
    		return outputStream;
    	}
    

Step 3 : Create a simple Servlet to download the generated Excel Export

public class ExportServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
		try {
			// Get Excel Data
			ByteArrayOutputStream bytes = new ExportService().generateExcelReport();

			// Initialize Http Response Headers
			response.setHeader("Content-disposition", "attachment; filename=exportUsers.xls");
			response.setContentType("application/vnd.ms-excel");

			// Write data on response output stream
			if (bytes != null) {
				response.getOutputStream().write(bytes.toByteArray());
			}
		} catch (WriteException e) {
			response.setContentType("text/plain");
			response.getWriter().print("An error as occured");
		}
	}
}

Step 4 : Expose your Servlet

In your web.xml, just add the following servlet declaration :

	<servlet>
		<servlet-name>export</servlet-name>
		<servlet-class>org.company.application.ExportServlet</servlet-class>
	</servlet>

	<servlet-mapping>
		<servlet-name>export</servlet-name>
		<url-pattern>/export</url-pattern>
	</servlet-mapping>

Step 5 : Launch Excel Generation using GWT (Google Web Toolkit) ?

Some people asked me how to launch excel generation using GWT.
In fact, this is really simple :

  • If you are using GWT 2.X :
    @UiHandler("myButton")
    void onMyButtonClick(ClickEvent e) {
        Window.Location.replace("/myApplication/path/to/my/servlet");
    }
    
  • If you are using GWT 1.X :
    Button myButton = new Button("Generate");
    
    myButton.addClickHandler(
         new ClickHandler() {
             @Override
             public void onClick(ClickEvent event) {
                 Window.Location.replace("/myApplication/path/to/my/servlet");
             }
        }
    );
    

Check out this sample

svn co http://z80-repository.googlecode.com/svn/trunk/gae-excel-export gae-excel-export

Evaluer l'article :


12345678910 (8 votes, moyenne: 8,13 sur 10)
Chargement ... Chargement ...

Partager l'article :


Découvrir d'autres articles sur le même thème :


5 réponses au billet “How to generate excel files on GAE?”


  • I have seen your article regarding excel generation in GWT..

    Excellent one that it was.. this was the thing I was really searching for..

    But one thing I missed in this article was how to generate excel in the client code?

    Whats the code I have to use in the client code? How can I display the excel sheet in the client?

    Thanks

    Bhanu Lakshmi

  • Thank you for your encouragement regarding my work.

    In fact, this article concerns only excel generation on Google App Engine due to platform limitations.

    I’ve just updated it to show you how to call it with GWT (1.X and 2.X)

  • This article helped a bunch in my website migration to google app engine. Thanks for the post

  • Hello, in this part of your code

    for (User user : getUsers()) {
    sheet.addCell(new Label(1, currentRow, user.getLastName(),dataCellFormat));
    sheet.addCell(new Label(2, currentRow, user.getFirstName(),dataCellFormat));
    currentRow++;
    }

    Where I am suposed to get the method getUsers; If I need to implement it, what should it return
    regards

  • Hi Sebas!

    This article is focused on how to generate excel files and getUsers() is just a simple method that returns a list of User.

    If you would like more informations, take a look at source code!

    Regards


Laisser un commentaire


WARNING

Your browser does not support JavaScript or has JavaScript disabled!

This will not compromise the possibility to leave a comment, although the automatic insertion of both markup tags and emoticons will not work.

Smile Grin Sad Surprised Shocked Confused Cool Mad Razz Neutral Wink Lol Red Face Cry Evil Twisted Roll Exclaim Question Idea Arrow Mr Green