Ekuseru

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

Having a template is very convenient, because we can define how our file looks like in the views, and we can access all helper methods defined in rails.

Installation

Spreadsheet gem

You will need to install Spreadsheet gem to use this plugin.

• Edit your config/environtment.rb, add:

  config.gem "spreadsheet"

• Install Spreadsheet gem if you haven’t, run:

  rake gems:install

• or just install it with:

  sudo gem install spreadsheet

Ekuseru plugin

Run the following command in your RAILS_ROOT:

  ./script/plugin install git://github.com/xinuc/ekuseru.git

Or, simply get the tarball at:

  http://github.com/xinuc/ekuseru/tarball/master

extract it to your vendor/plugins and rename it to ‘ekuseru’

Usage

Controller

To generate xls document, add format.xls in your controller.

Example:

  class ProductsController < ApplicationController
    def index
      @products = Product.all

      respond_to do |format|
        format.html
        format.xls # add this line to generate xls document
      end
    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/files/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 'Excel', products_path(:format => :xls) %>

Copyright © 2009 Nugroho Herucahyono, released under the MIT license