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', (.name rescue nil)],
['Published?', (published ? 'Yes' : 'No')],
['Published At', :published_at],
['# of Views', :number_of_views],
['Rating', :rating],
['Category/Tags', "#{category.name} - #{.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',(.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.