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)

1. Install

# Gemfile
gem 'spreadsheet_architect'

1.1 Basic Class/Model Setup

1.1.1 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.

2. Usage

2.1 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

2.2 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

2.3 Complex XLSX Example with Styling

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

2.4 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

2.5 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

References

westonganger/spreadsheet_architect: SpreadsheetArchitect lets you turn any activerecord relation or ruby object collection into a XLSX, ODS, or CSV spreadsheet
https://github.com/westonganger/spreadsheet_architect

打赏

Leave a Reply

Your email address will not be published. Required fields are marked *