神刀安全网

Easily Provide Excel Reports with Rails and jXLS

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 Contact 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.

Easily Provide Excel Reports with Rails and jXLS

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.

Easily Provide Excel Reports with Rails and jXLS

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.

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Easily Provide Excel Reports with Rails and jXLS

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址