to_spreadsheet lets your Rails 3+ app render Excel files using the existing slim/haml/erb/etc views.
Add it to your Gemfile:
gem 'to_spreadsheet'In the controller:
# my_thingies_controller.rb
class MyThingiesController < ApplicationController
respond_to :xlsx, :html
def index
@my_items = MyItem.all
respond_to do |format|
format.html
format.xlsx { render xlsx: :index, filename: "my_items_doc" }
end
end
endIn the 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 the XLSX view:
# index.xlsx.haml
= render 'my_items', my_items: @my_itemsIn the HTML view:
# index.html.haml
= link_to 'Download spreadsheet', my_items_url(format: :xlsx)
= render 'my_items', my_items: @my_itemsEvery 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.
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', bg_color: lambda { |row| 'ddffdd' if row.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
endFor the full list of supported properties head here: https://rubydoc.info/github/randym/axlsx/Axlsx/Styles#add_style-instance_method In addition, for column formats, Axlsx columnInfo properties are also supported
to_spreadsheet associates HTML nodes with Axlsx objects as follows:
| HTML tag | Axlsx object |
|---|---|
| table | worksheet |
| tr | row |
| td, th | cell |
For example, to directly manipulate a worksheet:
format_xls do
format 'table' do |worksheet|
worksheet.add_chart ...
# to get the associated Nokogiri node:
el = context.to_xml_node(worksheet)
end
endYou can define themes, i.e. blocks of formatting code:
ToSpreadsheet.theme :zebra do
format 'tr', bg_color: lambda { |row| 'ddffdd' if row.row_index.odd? }
endAnd then use them:
format_xls 'table.zebra', ToSpreadsheet.theme(:zebra)If you are using axlsx-rails, :xlsx renderer might have already been defined. In that case define a custome renderer using
# app/config/application.rb
config.to_spreadsheet.renderer = :html2xlsxAnd then in controller
respond_to do |format|
format.html2xlsx
endThe 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) |