to_spreadsheet is a gem that lets you render xls from your existing haml/erb views from Rails (>= 3.0).
Installation
Add it to your Gemfile:
gem 'to_spreadsheet'
Usage
In your controller:
# my_thingies_controller.rb
class MyThingiesController < ApplicationController
respond_to :xls, :html
def index
@my_items = MyItem.all
respond_to do |format|
format.html
format.xlsx { render xlsx: :index, filename: "my_items_doc" }
end
end
end
In your view partial:
# _my_items.haml
%table
%caption My items
%thead
%tr
%td ID
%td Name
%tbody
- my_items.each do |my_item|
%tr
%td.number= my_item.id
%td= my_item.name
%tfoot
%tr
%td(colspan="2") #{my_items.length}
In your index.xls.haml:
# index.xls.haml
= render 'my_items', my_items: @my_items
In your index.html.haml:
# index.html.haml
= link_to 'Download spreadsheet', my_items_url(format: :xlsx)
= render 'my_items', my_items: @my_items
Worksheets
Every table in the view will be converted to a separate sheet. The sheet title will be assigned to the value of the table’s caption element if it exists.
Formatting
You can define formats in your view file (local to the view) or in the initializer
format_xls 'table.my-table' do
workbook use_autowidth: true
sheet orientation: landscape
format 'th', b: true # bold
format 'tbody tr', color: lambda { |row| 'ddffdd' if row.index.odd? }
format 'A3:B10', i: true # italic
format column: 0, width: 35
format 'td.custom', lambda { |cell| modify cell somehow.}
# default value (fallback value when value is blank or 0 for integer / float)
default 'td.price', 10
For the full list of supported properties head here: http://rubydoc.info/github/randym/axlsx/Axlsx/Cell In addition, for column formats, Axlsx columnInfo properties are also supported
Themes
You can define “themes” – blocks of formatting code:
ToSpreadsheet.theme :zebra do
format 'tr', color: lambda { |row| 'ddffdd' if row.index.odd? }
And then use them:
format_xls 'table.zebra', ToSpreadsheet.theme(:zebra)
Types
The default theme uses class names on td/th to cast values. Here is the list of class to type mapping:
CSS class | Format |
---|---|
decimal or float | Decimal |
num or int | Integer |
datetime | DateTime (Chronic.parse) |
date | Date (Date.parse) |
time | Time (Chronic.parse) |