Spreadsheet Architect

Spreadsheet Architect is a library that allows you to create XLSX, ODS, or CSV spreadsheets easily from ActiveRecord relations, Plain Ruby classes, or predefined data.

Key Features:

Can generate headers & columns from ActiveRecord column_names or a Class/Model's spreadsheet_columns method
Dead simple custom spreadsheets with custom data
Data Sources: ActiveRecord relations, array of Ruby Objects, or 2D Array Data
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) supported

Spreadsheet Architect adds the following methods:

# Rails ActiveRecord Model
Post.order(name: :asc).where(published: true).to_xlsx
Post.order(name: :asc).where(published: true).to_ods
Post.order(name: :asc).where(published: true).to_csv

# Plain Ruby Class
Post.to_xlsx(instances: posts_array)
Post.to_ods(instances: posts_array)
Post.to_csv(instances: posts_array)

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

Install

# Gemfile
gem 'spreadsheet_architect'

Basic Class/Model Setup

Model

class Post < ActiveRecord::Base #activerecord not required
  include SpreadsheetArchitect

  belongs_to :author
  belongs_to :category
  has_many :tags

  #optional for activerecord classes, defaults to the models column_names
  def spreadsheet_columns

    #[[Label, Method/Statement, Type(optional) to Call on each Instance, Cell Type(optional)]....]
    [
      ['Title', :title],
      ['Content', content],
      ['Author', (author.name if author)],
      ['Published?', (published ? 'Yes' : 'No')],
      ['Published At', :published_at],
      ['# of Views', :number_of_views, :float],
      ['Rating', :rating],
      ['Category/Tags', "#{category.name} - #{tags.collect(&:name).join(', ')}"]
    ]

    # OR if you want to use the method or attribute name as a label it must be a symbol ex. "Title", "Content", "Published"
    [:title, :content, :published]

    # OR a Combination of Both ex. "Title", "Content", "Author Name", "Published"
    [:title, :content, ['Author Name',(author.name rescue nil)], ['# of Views', :number_of_views, :float], :published]
  end
end

Note: Do not define your labels inside this method if you are going to be using custom headers in the model or project defaults.

Usage

Method 1: Controller (for Rails)

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

  # 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), file_name: 'articles' }
    end
  end
end

Method 2: Save to a file manually

# Ex. with ActiveRecord relation
File.open('path/to/file.xlsx', 'w+b') do |f|
  f.write Post.order(published_at: :asc).to_xlsx
end
File.open('path/to/file.ods', 'w+b') do |f|
  f.write Post.order(published_at: :asc).to_ods
end
File.open('path/to/file.csv', 'w+b') do |f|
  f.write Post.order(published_at: :asc).to_csv
end

# Ex. with plain ruby class
File.open('path/to/file.xlsx', 'w+b') do |f|
  f.write Post.to_xlsx(instances: posts_array)
end

# Ex. One time Usage
File.open('path/to/file.xlsx', 'w+b') do |f|
  headers = ['Col 1','Col 2','Col 3']
  data = [[1,2,3], [4,5,6], [7,8,9]]
  f.write SpreadsheetArchitect::to_xlsx(data: data, headers: headers)
end

Methods & Options

SomeClass.to_xlsx

Option	Default	Notes
spreadsheet_columns Array	This defaults to your models custom `spreadsheet_columns` method or any custom defaults defined. If none of those then falls back to `self.column_names` for ActiveRecord models.	Use this option to override the model instances `spreadsheet_columns` method
instances Array		Required only for Non-ActiveRecord classes Array of class/model instances.
headers 2D Array	This defaults to your models custom spreadsheet_columns method or `self.column_names.collect(&:titleize)`	Pass `false` to skip the header row.
sheet_name String	The class name
header_style Hash	`{background_color: "AAAAAA", color: "FFFFFF", align: :center, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false}`	See all available style options here
row_style Hash	`{background_color: nil, color: "000000", align: :left, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false, format_code: nil}`	Styles for non-header rows. See all available style options here
column_styles Array		See this example for usage
range_styles Array		See this example for usage
merges Array		Merge cells. See this example for usage
borders Array		See this example for usage
column_widths Array		Sometimes you may want explicit column widths. Use nil if you want a column to autofit again.

SomeClass.to_ods

Option	Default	Notes
spreadsheet_columns Array	This defaults to your models custom `spreadsheet_columns` method or any custom defaults defined. If none of those then falls back to `self.column_names` for ActiveRecord models.	Use this option to override the model instances `spreadsheet_columns` method
instances Array		Required only for Non-ActiveRecord models Array of class/model instances.
headers 2D Array	`self.column_names.collect(&:titleize)`	Pass `false` to skip the header row.
sheet_name String	The class name
header_style Hash	`{background_color: "AAAAAA", color: "FFFFFF", align: :center, font_size: 10, bold: true}`	Note: Currently ODS only supports these options (values can be changed though)
row_style Hash	`{background_color: nil, color: "000000", align: :left, font_size: 10, bold: false}`	Styles for non-header rows. Currently ODS only supports these options

SomeClass.to_csv

Option	Default	Notes
spreadsheet_columns Array	This defaults to your models custom `spreadsheet_columns` method or any custom defaults defined. If none of those then falls back to `self.column_names` for ActiveRecord models.	Use this to option override the model instances `spreadsheet_columns` method
instances Array		Required only for Non-ActiveRecord classes Array of class/model instances.
headers 2D Array	`self.column_names.collect(&:titleize)`	Data for the header rows cells. Pass `false` to skip the header row.

SpreadsheetArchitect.to_xlsx

Option	Default	Notes
data Array		Data for the non-header row cells.
headers 2D Array	`false`	Data for the header row cells. Pass `false` to skip the header row.
sheet_name String	`Sheet1`
header_style Hash	`{background_color: "AAAAAA", color: "FFFFFF", align: :center, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false}`	See all available style options here
row_style Hash	`{background_color: nil, color: "000000", align: :left, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false, format_code: nil}`	Styles for non-header rows. See all available style options here
column_styles Array		See this example for usage
range_styles Array		See this example for usage
merges Array		Merge cells. See this example for usage
borders Array		See this example for usage
column_types Array		Valid types for XLSX are :string, :integer, :float, :date, :time, :boolean, nil = auto determine
column_widths Array		Sometimes you may want explicit column widths. Use nil if you want a column to autofit again.

SpreadsheetArchitect.to_ods

Option	Default	Notes
data 2D Array		Data for the non-header row cells.
headers 2D Array	`false`	Data for the header rows cells. Pass `false` to skip the header row.
sheet_name String	`Sheet1`
header_style Hash	`{background_color: "AAAAAA", color: "FFFFFF", align: :center, font_size: 10, bold: true}`	Note: Currently ODS only supports these options
row_style Hash	`{background_color: nil, color: "000000", align: :left, font_size: 10, bold: false}`	Styles for non-header rows. Currently ODS only supports these options
column_types Array		Valid types for ODS are :string, :float, :date, :percent, :currency, nil = auto determine

SpreadsheetArchitect.to_csv

Option	Default	Notes
data 2D Array		Data for the non-header row cells.
headers 2D Array	`false`	Data for the header rows cells. Pass `false` to skip the header row.

Change model default method options

class Post
  include SpreadsheetArchitect

  def spreadsheet_columns
    [:name, :content]
  end

  SPREADSHEET_OPTIONS = {
    headers: [
      ['My Post Report'],
      self.column_names.map{|x| x.titleize}
    ],
    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: [],
    merges: [],
    borders: [],
    column_types: []
  }
end

Change project wide default method options

# config/initializers/spreadsheet_architect.rb

SpreadsheetArchitect.default_options = {
  headers: true,
  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: 'FFFFFF', align: :left, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false},
  sheet_name: 'My Project Export',
  column_styles: [],
  range_styles: [],
  merges: [],
  borders: [],
  column_types: []
}

Complex XLSX Example with Styling

See this example: https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb

Multi Sheet XLSX or ODS spreadsheets

# Returns corresponding spreadsheet libraries object
package = SpreadsheetArchitect.to_axlsx_package({data: data, headers: headers})
SpreadsheetArchitect.to_axlsx_package({data: data, headers: headers}, package) # to combine two sheets to one file

spreadsheet = SpreadsheetArchitect.to_rodf_spreadsheet({data: data, headers: headers})
SpreadsheetArchitect.to_rodf_spreadsheet({data: data, headers: headers}, spreadsheet) # to combine two sheets to one file

See this example: https://github.com/westonganger/spreadsheet_architect/blob/master/examples/multi_sheet_spreadsheets.rb

Axlsx Style Reference

I have compiled a list of all available style options for axlsx here: https://github.com/westonganger/spreadsheet_architect/blob/master/docs/axlsx_style_reference.md

Credits

Created by @westonganger

For any consulting or contract work please contact me via my company website: Solid Foundation Web Development

jonathanpa/spreadsheet_architect