Ekuseru

Ekuseru is a gem to generate Microsoft Excel documents with Rails. This gem provides templating abilities to create excel documents.

Installation

Rails 3

  • Edit your Gemfile, add:

    gem “ekuseru”

  • then run

    bundle install

Rails 2

Ekuseru master branch now support rails 3 and is NOT backward compatible. To use ekuseru in rails 2, install it as plugin :

  • cd vendor/plugins

  • git clone git://github.com/xinuc/ekuseru.git

  • git checkout remotes/origin/rails2 -b rails2

Usage

Controller

To generate xls document, add respond_to :xls to your controller.

Example:

class ProductsController < ApplicationController
  respond_to :html, :xls

  def index
    @products = Product.all
    respond_with @products
  end

  ...

end

Template

Ekuseru will use .eku files as the template. So, with the example above, we will need to create ‘index.xls.eku’ in app/views/products/. Basically it’s just an ordinary ruby file.

In the template, we will get a xls variable which is a Spreadsheet::Workbook object ready to be modified like whatever we want.

Consult the Spreadsheet documentation to create the template.

spreadsheet.rubyforge.org/GUIDE_txt.html

You can set the filename sent to the user with __filename variable.

In the template :

# set the filename sent to the user with __filename variable
# this is optional, if you don't set it, the name will be like products.xls

__filename = "Products Catalog.xls"

# we get 'xls' variable which is a Workbook object
# then we can create some worksheet to work with, with create_worksheet method

sheet1 = xls.create_worksheet

# fill the [0, 0] cell

sheet1[0, 0] = "Products Catalog"

# Worksheet#row will return a Row object. We can modify it just like an Array.
# this code will return the second row and fill the cells.

sheet1.row(1).concat ["Name", "Price", "Stock", "Description"]

# we can access the instance variable we set in the controller, just like
# in erb template

@products.each_with_index do |p, i|
  sheet1.update_row i+2, p.name, p.price, p.stock, p.description
end

# we can add some formatting using Spreadsheet::Format object

title_format = Spreadsheet::Format.new(:color => :blue, :weight => :bold, :size => 18)
sheet1.row(0).set_format(0, title_format)

bold = Spreadsheet::Format.new(:weight => :bold)
sheet1.row(1).default_format = bold

That’s it. Then you can create a link to the xls file if you want, like:

<%= link_to 'Download as Excel', products_path(:format => :xls) %>

Credits