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

  # 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

  # 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

  # 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