Key Takeaways
- jXLS, a popular Java library, can be utilized in Rails thanks to JRuby to generate Excel files from template Excel files. This provides a solution for creating complex Excel reports without resorting to programming, which can be complicated and tiresome.
- The process of integrating jXLS into Rails involves creating a basic Rails application, creating an Excel report, and then integrating jXLS. This includes downloading the jXLS files and dependencies, setting up the directory structure, creating a jxls.rb file in the lib directory, and adding an initializer for loading the JXLS class in Rails.
- jXLS provides more advanced options for complex reports such as formulae, graphs, macros, etc. However, these more advanced features are not covered in this guide. This guide focuses on creating a basic Excel report with jXLS and Rails.
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
How can I get started with JXLS in Rails?
To get started with JXLS in Rails, you first need to add the JXLS gem to your Gemfile. Run the ‘bundle install’ command to install the gem. Next, create a new Excel template file in your Rails application. You can use the ‘xlsx’ gem to create this file. Once the template is created, you can use the JXLS library to populate the template with data from your Rails application. The JXLS library provides a simple and intuitive API for manipulating Excel files.
How can I use Excel formulas with JXLS?
JXLS supports Excel formulas. You can define formulas in your Excel template file and JXLS will evaluate them when generating the report. To define a formula, use the standard Excel formula syntax. For example, to calculate the sum of a column, you can use the formula ‘=SUM(A1:A10)’. JXLS will replace the cell references with the actual data when generating the report.
How can I generate Excel documents using JXLS templates?
To generate an Excel document using a JXLS template, you first need to create a template file. This file should contain placeholders for the data that you want to insert. Next, use the JXLS library to populate the template with data. The library provides a simple API for this purpose. You can specify the data as a hash or an array, and JXLS will insert it into the appropriate placeholders in the template.
How can I contribute to the JXLS project?
The JXLS project is open source and welcomes contributions from the community. You can contribute by submitting pull requests on GitHub. Before submitting a pull request, make sure to read the project’s contribution guidelines. You can also contribute by reporting bugs, suggesting new features, or improving the documentation.
How can I read Excel files with JXLS?
JXLS provides a reader API for reading Excel files. You can use this API to read data from an Excel file and convert it into a format that your Rails application can use. The reader API supports both .xls and .xlsx file formats. To read an Excel file, you first need to create a reader object. Then, you can use the ‘read’ method to read the data from the file. The data is returned as a hash or an array, depending on the structure of the Excel file.
Devdatta Kane is a software developer and designer based in Pune, India. He works with Radinik Technologies building traceability solutions for a variety of industries. He is also the lead developer of refers2, a CRM for small businesses. He works in Ruby on Rails, but likes to dabble with various new technologies as well. An aspiring photographer and passionate traveler, he loves traveling on his motorcycle, capturing experiences through camera.