Easily Provide Excel Reports with Rails and jXLS

Reporting is one the most important requirements in any application and, if we are dealing with a business application, the requirements can take on even more importance. However, providing on-screen reports or pretty HTML or PDF reports doesn’t always satisfy the user. Sometimes users require Excel reports so that they can work further on the data to get exactly the results they want.

Ruby fortunately provides many Excel related libraries, but most of them use programmatic way to create Excel, which can quickly get complicated and tiresome for the more complex reports. But we have a solution for that: jXLS. It’s a popular Java library used to generate Excel files from template Excel files. Thanks to JRuby we can use this excellent library in Rails. Without further ado let’s get started.

Creating a Basic Rails Application

We will create a basic Rails application with an Invoice model and CRUD actions. The application will use JRuby, Rails 4.2, and SQLite to keep things simple. First of all, install JRuby with RVM or rbenv.

Switch to JRuby and gem install rails to get the latest Rails gem. Now create a new Rails application, like so:

rails new jxls_rails -T

After the application is generated, create a Invoice scaffold, which has a model and the requisite CRUD resource operations:

cd jxls_rails
rails g scaffold Invoice invoice_number:string invoice_date:string customer:string total_value:decimal

Now migrate the database:

rake db:migrate

Let’s check how its working:

rails s

Point your favorite browser to http://localhost:3000/invoices and make sure everything is working properly. Create some records so we can use those later.

Creating a Excel Report

First, let’s create an Excel spreadsheet to act as the report template. I am using LibreOffice since I’m on Ubuntu. Save it as invoices.xls in the app/reports directory. Add fields and comments as shown in the screen shot below.

We are defining the template for the report by adding headers, placeholders for data, and comments for jXLS related metadata. As you can see, we can format the cells as we like in the template itself so there is no need for programmatically managing that.

We have basically set placeholders for data, which look like ${invoice.invoice_number} and will be replaced by actual data. The first comment, jx:area(lastCell="D2"), sets the work area for the report by defining the last cell in the report’s scope. The other comment, jx:each(items="jdbc.query(query)" var="invoice" lastCell="D2"), specifies the iterator tag that moves through all records and populates the rows by replacing the placeholders. Here items is the row collection for the report. We have designed the report to direct execute the provided SQL query and populate the report by using jdbc.query(query) as items. We could pass in a collection of objects, as well.

We have created a simple Excel spreadsheet now. There are some advanced options, which can be found in the jXLS documentation.

Integrating jXLS into Rails

First, download the jXLS files from the jXLS SourceForge page. We are using the 2.2.9 version, so download jxls-2.2.9.zip and unzip the file. Copy all files from the dist folder into lib/jxls in our application.

Also, download all the dependencies of jXLS from this link. Unzip and copy all files in the lib folder into lib/jxls in our application.

Our final directory structure looks as follows:

jxls_rails
  - app
  ...
  - lib
    - jxls
      - jxls-2.2.9.jar
      ...

Now we have all required files in place. Let’s create a file named jxls.rb in the lib directory with following code:

Dir.entries("#{Rails.root}/lib/jxls").each do |lib|
  require "jxls/#{lib}" if lib =~ /\.jar$/
end

require 'java'

java_import Java::org.jxls.common.Context
java_import Java::org.jxls.util.JxlsHelper
java_import Java::org.jxls.jdbc.JdbcHelper
java_import Java::java.io.BufferedInputStream
java_import Java::java.io.FileInputStream
java_import Java::java.io.ByteArrayOutputStream
java_import Java::org.jxls.util.TransformerFactory
java_import Java::org.jxls.transform.poi.PoiTransformer

class Jxls
  DIR = "#{Rails.root}/app/reports"

  def initialize(report, query)
    @filename = report
    @conn = ActiveRecord::Base.connection.jdbc_connection
    @query = query
  end

  def to_xls
    beans = {}
    report_source = BufferedInputStream.new(FileInputStream.new("#{DIR}/#{@filename}.xls"))
    raise ArgumentError, "#@filename does not exist." unless File.exist?("#{DIR}/#{@filename}.xls")
    bos = ByteArrayOutputStream.new
    context = Context.new
    jdbc_helper = JdbcHelper.new(@conn)
    context.put_var("jdbc", jdbc_helper)
    context.put_var("query", @query)
    JxlsHelper.getInstance().processTemplate(report_source, bos, context)
    bos.close
    bytes = bos.toByteArray
    return String.from_java_bytes(bytes)
  end
end

OK, let’s walk through this code and figure out what it actually does. First, we require all the jXLS files into our class, like so:

Dir.entries("#{Rails.root}/lib/jxls").each do |lib|
  require "jxls/#{lib}" if lib =~ /\.jar$/
end

Add the Java import declarations for jXLS:

require 'java'

java_import Java::org.jxls.common.Context
java_import Java::org.jxls.util.JxlsHelper
java_import Java::org.jxls.jdbc.JdbcHelper
java_import Java::java.io.BufferedInputStream
java_import Java::java.io.FileInputStream
java_import Java::java.io.ByteArrayOutputStream
java_import Java::org.jxls.util.TransformerFactory
java_import Java::org.jxls.transform.poi.PoiTransformer

Define the location where all reports will be stored:

DIR = "#{Rails.root}/app/reports"

Add the initialization code in the class constructor:

def initialize(report, query)
  @filename = report
  @query = query
  @conn = ActiveRecord::Base.connection.jdbc_connection
end

As a first step, we initialized all the required variables:

• The report’s file name – report
• The report’s SQL query – query

@conn is a JDBC connection from the ActiveRecord connection pool, since jXLS will require a JDBC connection to execute the query specified on the report. If we intend to pass a collection of objects, then this connection may not be needed.

Finally, we have added a method to fill and export the report to the Excel format:

def to_xls
  report_source = BufferedInputStream.new(FileInputStream.new("#{DIR}/#{@filename}.xls"))
  raise ArgumentError, "#@filename does not exist." unless File.exist?("#{DIR}/#{@filename}.xls")
  bos = ByteArrayOutputStream.new
  context = Context.new
  jdbc_helper = JdbcHelper.new(@conn)
  context.put_var("jdbc", jdbc_helper)
  context.put_var("query", @query)
  JxlsHelper.getInstance().processTemplate(report_source, bos, context)
  bos.close
  bytes = bos.toByteArray
  return String.from_java_bytes(bytes)
end

The report (invoices.xls) file created earlier is set as the report_source. Next we add jdbc_helper and @query into the context object to be passed to the report. Lastly, we invoke the JxlsHelper.getInstance().processTemplate to fill and export the report into an Excel bytestream which is returned using String.from_java_bytes.

We now have the report as a Excel bytestream, but we’ve no way to send it to user, yet. So, add a small helper method in application_controller.rb as follows:

def respond_to_report(name, query, filename, download = false)
  @report = Jxls.new(name, query)
  disposition = (download.nil? || download == false) ? 'inline' : 'attachment'
  send_data @report.to_pdf, :filename => filename, :type => :xls, :disposition => disposition
end

This helper method simplifies the report invocation and sends the response back to the user. There are also added options for providing a filename, along with a disposition option to open the file within the browser or download it as an attachment.

One more thing we need to do is to add an initializer for loading our JXLS class in Rails. Create a file called config/initializers/jxls.rb with following code –

require 'jxls'

Now, we will to add an action to the InvoicesController that calls the report. Add the following code to app/controllers/invoices_controller.rb:

def report
  respond_to_report('invoices', 'select * from invoices', 'invoices.xls')
end

As we have no parameters to pass and want to open the report in browser, we have omitted the download and report_params from the method call.

Update routes.rb to add the new action:

resources :invoices do
  get :report, on: :collection
end

Add a link for the report in the invoices/index.html.erb view:

...
<h1>Listing Invoices</h1>

<%= link_to 'Download as Excel', report_invoices_path %>
...

Fire up the server and go to http://localhost:3000/invoices. Click on ‘Download as Excel’. You should now see an Excel file with all invoices listed.

Wrapping Up

Today, we got a quick overview of how to use jXLS with a Rails application to generate Excel reports quickly. jXLS provides more advanced options for complex reports such as formulae, graphs, macros, etc. but that’s for another day.

Your comments and insights are always welcome.

Frequently Asked Questions (FAQs) about Excel Reports with Rails and JXLS