Export CSV and Excel from Java web apps With Data Pipeline

Updated: July 2021

This article will demonstrate how to download CSV (comma-separated values) and Excel data from Java web applications using Data Pipeline. It will also show one way to plug Data Pipeline into your JSPs.

Data Pipeline is often used in non-GUI applications to move data from one place/format to another. However, Data Pipeline can just as easily be plugged into your web, mobile, and desktop applications. Being as lightweight and Java-centric as it is, it is perfect for handling your data conversion and manipulation use-cases.

Overview

Here is a simple, read-only web app to display a list of events (conferences, meetings, appointments, etc.). The app’s functionality can be divided into 3 groups:

  1. Create and populate the database
  2. Read and transform data
  3. Export/render data

Export CSV and Excel from Java web apps With Data Pipeline

The overall design is based on the Model–View–Controller (MVC) architecture. Data Pipeline’s Record and RecordList classes will constitute the model. A single JSP will present the view. A RESTful resource (using JBoss’ RESTEasy framework) will be the controller.

The full source and configuration are included with the download for this blog.

1. Create and populate the database

The first step is to set up a database and fill it with test data. To save time, we use a HyperSQL, in-memory database with no persistence to disk.

event-table

The data model is minimal, just a single event table that we create and populate each time the app is loaded.

2. Read and transform events

Next, we read the events data using getEvents(). This method will be used internally by the data exporters and JSP render. Its job is to retrieve a stream of records from the database and enrich it with new fields. The retrieval step is done by instantiating a JdbcReader and passing it a database connection and a SQL query. The JdbcReader is then wrapped by several TransformingReaders that do the following:

  1. Copy the existing DURATION_MINUTES field to a new field (DURATION), then convert the new field from a number to an interval (as minutes). Data Pipeline intervals, like Oracle intervals, hold a quantity of time that can be used in datetime expressions.
  2. Create a new calculated field (END_TIME) by adding START_TIME and DURATION
  3. Copy START_TIME to START then convert it from a datetime to a formatted string
  4. copy END_TIME to END then convert it from a datetime to a formatted string

This demo uses several transformers for clarity, but the add(Transformer...) calls could just have easily been made on a single TransformingReader instance.

3. Export CSV

Next we serve up the first file using getEventsAsCsv(). This method takes the record stream from getEvents() and converts it to a CSV text stream on-the-fly. getEventsAsCsv() can be used to return huge datasets since the CSVWriter is writing records directly to the HTTP response stream without any buffering or batching.

4. Export Excel

The second file is served up using getEventsAsExcel(). This method takes the record stream from getEvents() and converts it to an in-memory Excel document before sending the entire thing to the HTTP response stream. Unlike getEventsAsCsv(), this method has to allocate memory to buffer all of the Excel document’s records.

5. Render JSP

The final step is to present the following data grid and download links for the CSV and Excel files. For simplicity’s sake, we’ll display all records in the table and omit any paging, sorting, or filtering functions.

Like previously, getEventsAsHtml() reads the record stream from getEvents(), but now collects them into a RecordList. The collected records are then passed onto the JSP for rendering.

The JSP renders the 2 download links followed by a data grid. Since the grid is built dynamically, it should handle most tables (as long as there’s data and any HTML in the fields are escaped).

Running the example

That’s it. To try the example app for yourself:

  1. Download the project.
  2. Download the latest Data Pipeline and unpack it into the project’s WEB-INF\lib folder.
  3. Run the ant build script.
  4. Deploy the generated war to your server of choice.
  5. Visit http://localhost:8080/events1/ (adjust the port as needed).

 

Happy coding!

About Dele Taylor

We make Data Pipeline — a lightweight ETL framework for Java. Use it to filter, transform, and aggregate data on-the-fly in your web, mobile, and desktop apps. Learn more about it at northconcepts.com.

6 thoughts on “Export CSV and Excel from Java web apps With Data Pipeline

Leave a Reply

Your email address will not be published. Required fields are marked *
You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">