Spreadsheet Architect

Spreadsheet Architect is a library that allows you to create XLSX, ODS, or CSV spreadsheets super easily from ActiveRecord relations, plain Ruby objects, or tabular data.

Key Features:

• Dead simple custom spreadsheets with custom data
• Data Sources: Tabular Data from an Array, ActiveRecord relations, or array of plain Ruby object instances
• Easily style and customize spreadsheets
• Create multi sheet spreadsheets
• Setting Class/Model or Project specific defaults
• Simple to use ActionController renderers for Rails
• Plain Ruby (without Rails) completely supported

Install

gem 'spreadsheet_architect'

General Usage

Tabular (Array) Data

The simplest and preferred usage is to simply create the data array yourself.

headers = ['Col 1','Col 2','Col 3']
data = [[1,2,3], [4,5,6], [7,8,9]]
SpreadsheetArchitect.to_xlsx(headers: headers, data: data)
SpreadsheetArchitect.to_ods(headers: headers, data: data)
SpreadsheetArchitect.to_csv(headers: headers, data: data)

Using this style will allow you to utilize any custom performance optimizations during your data generation process. This will come in handy when the spreadsheets get large and any loss in performance starts to matter.

Rails Relations or an Array of plain Ruby object instances

If you would like to add the methods to_xlsx, to_ods, to_csv, to_axlsx_package, to_rodf_spreadsheet to some class, you can simply include the SpreadsheetArchitect module to whichever classes you choose. For example:

class Post < ApplicationRecord
  include SpreadsheetArchitect
end

When using on an AR Relation or using the :instances option, SpreadsheetArchitect requires an instance method to be defined on the class to generate the data. By default it looks for the spreadsheet_columns method on the class. If you are using on an ActiveRecord model and that method is not defined, it would fallback to the models column_names method. If using the :data option this is completely ignored.

class Post
  include SpreadsheetArchitect

  def spreadsheet_columns
    ### Column format is: [Header, Cell Data / Method (if symbol) to Call on each Instance, (optional) Cell Type]
    [
      ['Title', :title],
      ['Content', content.strip],
      ['Author', (author.name if author)],
      ['Published?', (published ? 'Yes' : 'No')],
      :published_at, # uses the method name as header title Ex. 'Published At'
      ['# of Views', :number_of_views, :float],
      ['Rating', :rating],
      ['Category/Tags', "#{category.name} - #{tags.collect(&:name).join(', ')}"]
    ]
  end

end

Then use it on the class or ActiveRecord relations of the class

posts = Post.order(name: :asc).where(published: true)
posts.to_xlsx
posts.to_ods
posts.to_csv

# Plain Ruby Objects
posts_array = 10.times.map{|i| Post.new(number: i)}
Post.to_xlsx(instances: posts_array)
Post.to_ods(instances: posts_array)
Post.to_csv(instances: posts_array)

If you want to use a different method name then spreadsheet_columns you can pass a method name to the :spreadsheet_columns option.

Post.to_xlsx(instances: posts, spreadsheet_columns: :my_special_method)

Alternatively, you can pass a proc to the spreadsheet_columns option. For those purists that really dont want to define any extra spreadsheet_columns instance method on your model, this option can help you work with that methodology.

Post.to_xlsx(instances: posts, spreadsheet_columns: ->(instance){
  [
    ['Title', :title],
    ['Content', instance.content.strip],
    ['Author', (instance.author.name if instance.author)],
    ['Published?', (instance.published ? 'Yes' : 'No')],
    :published_at, # uses the method name as header title Ex. 'Published At'
    ['# of Views', :number_of_views, :float],
    ['Rating', :rating],
    ['Category/Tags', "#{instance.category.name} - #{instance.tags.collect(&:name).join(', ')}"],
    ['URL', :url, (val.start_with?("http") ? :hyperlink : :string)],
  ]
})

Sending & Saving Spreadsheets

Method 1: Save to a file manually

file_data = SpreadsheetArchitect.to_xlsx(headers: headers, data: data)

File.open('path/to/file.xlsx', 'w+b') do |f|
  f.write file_data
end

Method 2: Send Data via Rails Controller

class PostsController < ActionController::Base
  respond_to :html, :xlsx, :ods, :csv

  def index
    @posts = Post.order(published_at: :asc)

    render xlsx: @posts
  end

  # Using respond_with
  def index
    @posts = Post.order(published_at: :asc)

    respond_with @posts
  end

  # OR Using respond_with with custom options
  def index
    @posts = Post.order(published_at: :asc)

    if ['xlsx','ods','csv'].include?(request.format)
      respond_with @posts.to_xlsx(row_style: {bold: true}), filename: 'Posts'
    else
      respond_with @posts
    end
  end

  # OR Using responders
  def index
    @posts = Post.order(published_at: :asc)

    respond_to do |format|
      format.html
      format.xlsx { render xlsx: @posts }
      format.ods { render ods: @posts }
      format.csv{ render csv: @posts }
    end
  end

  # OR Using responders with custom options
  def index
    @posts = Post.order(published_at: :asc)

    respond_to do |format|
      format.html
      format.xlsx { render xlsx: @posts.to_xlsx(headers: false) }
      format.ods { render ods: Post.to_ods(instances: @posts) }
      format.csv{ render csv: @posts.to_csv(headers: false), filename: 'articles' }
    end
  end
end

Multi Sheet Spreadsheets

XLSX

axlsx_package = SpreadsheetArchitect.to_axlsx_package({headers: headers, data: data})
axlsx_package = SpreadsheetArchitect.to_axlsx_package({headers: headers, data: data}, axlsx_package)

File.open('path/to/multi_sheet_file.xlsx', 'w+b') do |f|
  f.write axlsx_package.to_stream.read
end

ODS

ods_spreadsheet = SpreadsheetArchitect.to_rodf_spreadsheet({headers: headers, data: data})
ods_spreadsheet = SpreadsheetArchitect.to_rodf_spreadsheet({headers: headers, data: data}, ods_spreadsheet)

File.open('path/to/multi_sheet_file.ods', 'w+b') do |f|
  f.write ods_spreadsheet.bytes
end

Methods

to_xlsx(options={})

to_axlsx_spreadsheet(options={}, axlsx_package_to_join=nil)

Same options as to_xlsx

to_ods(options={})

to_rodf_spreadsheet(options={}, spreadsheet_to_join=nil)

Same options as to_ods

to_csv(options={})

Change class-wide default method options

class Post < ApplicationRecord
  include SpreadsheetArchitect

  def spreadsheet_columns
    [:name, :content]
  end

  SPREADSHEET_OPTIONS = {
    headers: [
      ['My Post Report'],
      self.column_names.map{|x| x.titleize}
    ],
     spreadsheet_columns: :spreadsheet_columns,
    header_style: {background_color: 'AAAAAA', color: 'FFFFFF', align: :center, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false},
    row_style: {background_color: nil, color: '000000', align: :left, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false},
    sheet_name: self.name,
    column_styles: [],
    range_styles: [],
    conditional_row_styles: [],
    merges: [],
    borders: [],
    column_types: [],
  }
end

Change project-wide default method options

# config/initializers/spreadsheet_architect.rb

SpreadsheetArchitect.default_options = {
  headers: true,
  spreadsheet_columns: :spreadsheet_columns,
  header_style: {background_color: 'AAAAAA', color: 'FFFFFF', align: :center, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false},
  row_style: {background_color: nil, color: '000000', align: :left, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false},
  sheet_name: 'My Project Export',
  column_styles: [],
  range_styles: [],
  conditional_row_styles: [],
  merges: [],
  borders: [],
  column_types: [],
  use_zero_based_row_index: false,
}

Kitchen Sink Examples with Styling for XLSX and ODS

See test "kitchen sink" for XLSX and ODS

Axlsx Style Reference

I have compiled a list of all available style options for axlsx here: docs/axlsx_style_reference.md

Tips for Reducing Memory Usage

• Use the :data option instead of active record relations
• Utilize the light_record gem

Testing / Validating your Spreadsheets

A wise word of advice, when testing your spreadsheets I recommend to use Excel instead of LibreOffice. This is because I have seen through testing, that where LibreOffice seems to just let most incorrect things just slide on through, Excel will not even open the spreadsheet as apparently it is much more strict about the spreadsheet validations. This will help you better identify any incorrect styling or customization issues.

Contributing

We use the appraisal gem for testing multiple versions of axlsx. Please use the following steps to test using appraisal.

1. bundle exec appraisal install
2. bundle exec appraisal rake test

At this time the spreadsheets generated by the test suite are manually inspected. After running the tests, the test output can be viewed in tmp/

Credits

Created & Maintained by Weston Ganger - @westonganger