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, or one creation with 2D array of data
• Plain Ruby support
• Plain from ActiveRecord relations or Ruby Objects from models ActiveRecord, or 2d Array Data
• Easily style headers and rows
• Model/Class or Project specific defaults
• Simple to use ActionController renderers

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)

# These return a ruby object that can be further manipulated
# These methods may also be used on models/classes
SpreadsheetArchitect.to_axlsx('package', {data: data, headers: headers})
SpreadsheetArchitect.to_axlsx('sheet', {data: data, headers: headers})
SpreadsheetArchitect.to_rodf_spreadsheet(data: data, headers: headers)

Install

gem install spreadsheet_architect

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 to Call on each Instance, Cell Type(optional)]....]
    [
      ['Title', :title],
      ['Content', content],
      ['Author', (author.name rescue nil)],
      ['Published?', (published ? 'Yes' : 'No')],
      ['Published At', :published_at],
      ['# of Views', :number_of_views],
      ['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)], :published]
  end
end

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_odf(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 realtion
File.open('path/to/file.xlsx') do |f|
  f.write{ Post.order(published_at: :asc).to_xlsx }
end
File.open('path/to/file.ods') do |f|
  f.write{ Post.order(published_at: :asc).to_ods }
end
File.open('path/to/file.csv') do |f|
  f.write{ Post.order(published_at: :asc).to_csv }
end

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

# Ex. One time Usage
File.open('path/to/file.xlsx') 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

Method & Options

#### `.to_xlsx` - (on custom class/model) |Option|Type|Default|Notes| |---|---|---|---| |**spreadsheet_columns**|Array| AR Model column_names | Required if `spreadsheet_columns` not defined on class except with ActiveRecord models which default to the `column_names` method. Will override models `spreadsheet_columns` method | |**instances**|Array| |**Required for Non-ActiveRecord classes** Array of class/model instances.| |**headers**|Boolean|`true`|Pass false to skip the header row.| |**sheet_name**|String|Class name|| |**header_style**|Hash|`{background_color: "AAAAAA", color: "FFFFFF", align: :center, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false}`|| |**row_style**|Hash|`{background_color: nil, color: "FFFFFF", align: :left, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false, number_format_code: nil}`|Styles for non-header rows.|
#### `.to_ods` - (on custom class/model) |Option|Type|Default|Notes| |---|---|---|---| |**spreadsheet_columns**|Array| AR Model column_names | Required if `spreadsheet_columns` not defined on class except with ActiveRecord models which default to the `column_names` method. Will override models `spreadsheet_columns` method | |**instances**|Array| |**Required for Non-ActiveRecord classes** Array of class/model instances.| |**headers**|Boolean|`true`|Pass false to skip the header row.| |**sheet_name**|String|Class name|| |**header_style**|Hash|`{color: "000000", align: :center, font_size: 10, bold: true}`|Note: Currently only supports these options (values can be changed though)| |**row_style**|Hash|`{color: "000000", align: :left, font_size: 10, bold: false}`|Styles for non-header rows. Currently only supports these options (values can be changed though)|
#### `.to_csv` - (on custom class/model) |Option|Type|Default|Notes| |---|---|---|---| |**spreadsheet_columns**|Array| AR Model column_names | Required if `spreadsheet_columns` not defined on class except with ActiveRecord models which default to the `column_names` method. Will override models `spreadsheet_columns` method | |**instances**|Array| |**Required for Non-ActiveRecord classes** Array of class/model instances.| |**headers**|Boolean|`true`|Pass false to skip the header row.|
#### `SpreadsheetArchitect.to_xlsx` |Option|Type|Default|Notes| |---|---|---|---| |**data**|Array| |**Required** 2D Array of data for the non-header row cells. | |**headers**|Array|`false`|2D Array of data for the header rows cells. Pass false to skip the header row.| |**sheet_name**|String|`SpreadsheetArchitect`|| |**header_style**|Hash|`{background_color: "AAAAAA", color: "FFFFFF", align: :center, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false}`|| |**row_style**|Hash|`{background_color: nil, color: "FFFFFF", align: :left, font_name: 'Arial', font_size: 10, bold: false, italic: false, underline: false, number_format_code: nil}`|Styles for non-header rows.|
#### `SpreadsheetArchitect.to_ods` |Option|Type|Default|Notes| |---|---|---|---| |**data**|Array| |**Required** 2D Array of data for the non-header row cells.| |**headers**|Array|`false`|2D Array of data for the header rows cells. Pass false to skip the header row.| |**sheet_name**|String|`SpreadsheetArchitect`|| |**header_style**|Hash|`{color: "000000", align: :center, font_size: 10, bold: true}`|Note: Currently only supports these options (values can be changed though)| |**row_style**|Hash|`{color: "000000", align: :left, font_size: 10, bold: false}`|Styles for non-header rows. Currently only supports these options (values can be changed though)|
#### `SpreadsheetArchitect.to_csv` |Option|Type|Default|Notes| |---|---|---|---| |**data**|Array| |**Required** 2D Array of data for the non-header row cells.| |**headers**|Array|`false`|2D Array of 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: 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: self.name
  }
end

Change project wide default method options

# config/initializers/spreadsheet_architect.rb

SpreadsheetArchitect.module_eval do
  const_set('SPREADSHEET_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'
  })
end

Format all numbers, money, or currency in xlsx only

Unfortunately so far I have only been successful in applying the format_code to all numbers in the entire spreadsheet.

# Ex. dollar sign, comma's, and minumum two decimal places
Product.to_xlsx(headers: headers, data: data, number_format_code: "$#,##0.00")

TODO

Would love for any help with new features for this projects. Some desired features are:

• More ODS style options
• Apply format codes to only certain columns (xlsx)
• Add Columns styles (xlsx & ods)
• Create multiple sheets (xlsx & ods)

Credits

Created by Weston Ganger - @westonganger

Heavily influenced by the dead gem acts_as_xlsx by @randym but adapted to work for more spreadsheet types and plain ruby models.