Spreadsheet Architect is a library that allows you to create XLSX, ODS, or CSV spreadsheets super easily from ActiveRecord relations, plain Ruby objects, or tabular data.
Key Features:
- Dead simple custom spreadsheets with custom data
- Data Sources: Tabular Data from an Array, ActiveRecord relations, or array of plain Ruby object instances
- 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) completely supported
gem 'spreadsheet_architect'The simplest and preferred usage is to simply create the data array yourself.
headers = ['Col 1','Col 2','Col 3']
data = [[1,2,3], [4,5,6], [7,8,9]]
SpreadsheetArchitect.to_xlsx(headers: headers, data: data)
SpreadsheetArchitect.to_ods(headers: headers, data: data)
SpreadsheetArchitect.to_csv(headers: headers, data: data)Using this style will allow you to utilize any custom performance optimizations during your data generation process. This will come in handy when the spreadsheets get large and any loss in performance starts to matter.
If you would like to add the methods to_xlsx, to_ods, to_csv, to_axlsx_package, to_rodf_spreadsheet to some class, you can simply include the SpreadsheetArchitect module to whichever classes you choose. For example:
class Post < ApplicationRecord
include SpreadsheetArchitect
endWhen using on an AR Relation or using the :instances option, SpreadsheetArchitect requires an instance method to be defined on the class to generate the data. By default it looks for the spreadsheet_columns method on the class. If you are using on an ActiveRecord model and that method is not defined, it would fallback to the models column_names method. If using the :data option this is completely ignored.
class Post
include SpreadsheetArchitect
def spreadsheet_columns
### Column format is: [Header, Cell Data / Method (if symbol) to Call on each Instance, (optional) Cell Type]
[
['Title', :title],
['Content', content.strip],
['Author', (author.name if author)],
['Published?', (published ? 'Yes' : 'No')],
:published_at, # uses the method name as header title Ex. 'Published At'
['# of Views', :number_of_views, :float],
['Rating', :rating],
['Category/Tags', "#{category.name} - #{tags.collect(&:name).join(', ')}"]
]
end
endThen use it on the class or ActiveRecord relations of the class
posts = Post.order(name: :asc).where(published: true)
posts.to_xlsx
posts.to_ods
posts.to_csv
# Plain Ruby Objects
posts_array = 10.times.map{|i| Post.new(number: i)}
Post.to_xlsx(instances: posts_array)
Post.to_ods(instances: posts_array)
Post.to_csv(instances: posts_array)If you want to use a different method name then spreadsheet_columns you can pass a method name to the :spreadsheet_columns option.
Post.to_xlsx(instances: posts, spreadsheet_columns: :my_special_method)Alternatively, you can pass a proc to the spreadsheet_columns option. For those purists that really dont want to define any extra spreadsheet_columns instance method on your model, this option can help you work with that methodology.
Post.to_xlsx(instances: posts, spreadsheet_columns: ->(instance){
[
['Title', :title],
['Content', instance.content.strip],
['Author', (instance.author.name if instance.author)],
['Published?', (instance.published ? 'Yes' : 'No')],
:published_at, # uses the method name as header title Ex. 'Published At'
['# of Views', :number_of_views, :float],
['Rating', :rating],
['Category/Tags', "#{instance.category.name} - #{instance.tags.collect(&:name).join(', ')}"],
['URL', :url, (val.start_with?("http") ? :hyperlink : :string)],
]
})file_data = SpreadsheetArchitect.to_xlsx(headers: headers, data: data)
File.open('path/to/file.xlsx', 'w+b') do |f|
f.write file_data
endclass PostsController < ActionController::Base
respond_to :html, :xlsx, :ods, :csv
def index
@posts = Post.order(published_at: :asc)
render xlsx: @posts
end
# 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), filename: 'articles' }
end
end
endaxlsx_package = SpreadsheetArchitect.to_axlsx_package({headers: headers, data: data})
axlsx_package = SpreadsheetArchitect.to_axlsx_package({headers: headers, data: data}, axlsx_package)
File.open('path/to/multi_sheet_file.xlsx', 'w+b') do |f|
f.write axlsx_package.to_stream.read
endods_spreadsheet = SpreadsheetArchitect.to_rodf_spreadsheet({headers: headers, data: data})
ods_spreadsheet = SpreadsheetArchitect.to_rodf_spreadsheet({headers: headers, data: data}, ods_spreadsheet)
File.open('path/to/multi_sheet_file.ods', 'w+b') do |f|
f.write ods_spreadsheet.bytes
end| Option | Default | Notes |
|---|---|---|
| data 2D Array |
Cannot be used with the :instances option.Tabular data for the non-header row cells. |
|
| instances Array |
Cannot be used with the :data option.Array of class/model instances to be used as row data. Cannot be used with :data option |
|
| spreadsheet_columns Proc/Symbol/String |
Use this option to override or define the spreadsheet columns. Normally, if this option is not specified and are using the instances option/ActiveRecord relation, it uses the classes custom spreadsheet_columns method or any custom defaults defined.If neither of those and is an ActiveRecord model, then it will falls back to the models self.column_names |
Cannot be used with the :data option.If a Proc value is passed it will be evaluated on the instance object. If a Symbol or String value is passed then it will search the instance for a method name that matches and call it. |
| headers Array / 2D Array |
Data for the header row cells. If using on a class/relation, this defaults to the ones provided via spreadsheet_columns. 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 the kitchen sink example for usage | |
| range_styles Array |
See the kitchen sink example for usage | |
| conditional_row_styles Array |
See the kitchen sink example for usage. The if/unless proc will called with the following args: row_index, row_data |
|
| merges Array |
Merge cells. See the kitchen sink example for usage. Warning merges cannot overlap eachother, if you attempt to do so Excel will claim your spreadsheet is corrupt and refuse to open your spreadsheet. | |
| borders Array |
See the kitchen sink example for usage | |
| column_types Array |
Valid types for XLSX are :string, :integer, :float, :date, :time, :boolean, :hyperlink, nil = auto determine. You may also pass a Proc which evaluates to any of the valid types, for example ->(cell_val){ cell_val.start_with?('http') ? :hyperlink : :string } |
|
| column_widths Array |
Sometimes you may want explicit column widths. Use nil if you want a column to autofit again. | |
| freeze_headers Boolean |
Make all header rows frozen/fixed so they do not scroll. | |
| freeze Hash |
Make all specified row and/or column frozen/fixed so they do not scroll. See example usage | |
| skip_defaults Boolean |
false |
Removes defaults and default styles. Particularily useful for heavily customized spreadsheets where the default styles get in the way. |
| escape_formulas Boolean or Array |
true |
Pass a single boolean to apply to all cells, or an array of booleans to control column-by-column. Advisable to be set true when involved with untrusted user input. See an example of the underlying functionality. NOTE: Header row cells are not escaped. |
| use_zero_based_row_index Boolean |
false |
Allows you to use zero-based row indexes when defining range_styles, merges, etc. Recommended to set this option for the whole project rather than per call. The original reason it was designed to be 1-based is because spreadsheet row numbers actually start with 1. |
Same options as to_xlsx
| Option | Default | Notes |
|---|---|---|
| data 2D Array |
Cannot be used with the :instances option.Tabular data for the non-header row cells. |
|
| instances Array |
Cannot be used with the :data option.Array of class/model instances to be used as row data. Cannot be used with :data option |
|
| spreadsheet_columns Proc/Symbol/String |
Use this option to override or define the spreadsheet columns. Normally, if this option is not specified and are using the instances option/ActiveRecord relation, it uses the classes custom spreadsheet_columns method or any custom defaults defined.If neither of those and is an ActiveRecord model, then it will falls back to the models self.column_names |
Cannot be used with the :data option.If a Proc value is passed it will be evaluated on the instance object. If a Symbol or String value is passed then it will search the instance for a method name that matches and call it. |
| headers Array / 2D Array |
Data for the header row cells. If using on a class/relation, this defaults to the ones provided via spreadsheet_columns. 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, :time, :boolean, :hyperlink, nil = auto determine. Due to RODF Issue #19, :date/:time will be converted to :string. You may also pass a Proc which evaluates to any of the valid types, for example ->(cell_val){ cell_val.start_with?('http') ? :hyperlink : :string } |
|
| skip_defaults Boolean |
false |
Skip defaults and default styles. Particularly useful for heavily customized spreadsheets where the default styles get in the way. |
Same options as to_ods
| Option | Default | Notes |
|---|---|---|
| data 2D Array |
Cannot be used with the :instances option.Tabular data for the non-header row cells. |
|
| instances Array |
Cannot be used with the :data option.Array of class/model instances to be used as row data. Cannot be used with :data option |
|
| spreadsheet_columns Proc/Symbol/String |
Use this option to override or define the spreadsheet columns. Normally, if this option is not specified and are using the instances option/ActiveRecord relation, it uses the classes custom spreadsheet_columns method or any custom defaults defined.If neither of those and is an ActiveRecord model, then it will falls back to the models self.column_names |
Cannot be used with the :data option.If a Proc value is passed it will be evaluated on the instance object. If a Symbol or String value is passed then it will search the instance for a method name that matches and call it. |
| headers Array / 2D Array |
Data for the header row cells. If using on a class/relation, this defaults to the ones provided via spreadsheet_columns. Pass false to skip the header row. |
class Post < ApplicationRecord
include SpreadsheetArchitect
def spreadsheet_columns
[:name, :content]
end
SPREADSHEET_OPTIONS = {
headers: [
['My Post Report'],
self.column_names.map{|x| x.titleize}
],
spreadsheet_columns: :spreadsheet_columns,
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: [],
conditional_row_styles: [],
merges: [],
borders: [],
column_types: [],
}
end# config/initializers/spreadsheet_architect.rb
SpreadsheetArchitect.default_options = {
headers: true,
spreadsheet_columns: :spreadsheet_columns,
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: 'My Project Export',
column_styles: [],
range_styles: [],
conditional_row_styles: [],
merges: [],
borders: [],
column_types: [],
use_zero_based_row_index: false,
}See test "kitchen sink" for XLSX and ODS
I have compiled a list of all available style options for axlsx here: docs/axlsx_style_reference.md
- Use the
:dataoption instead of active record relations - Utilize the
light_recordgem
A wise word of advice, when testing your spreadsheets I recommend to use Excel instead of LibreOffice. This is because I have seen through testing, that where LibreOffice seems to just let most incorrect things just slide on through, Excel will not even open the spreadsheet as apparently it is much more strict about the spreadsheet validations. This will help you better identify any incorrect styling or customization issues.
We use the appraisal gem for testing multiple versions of axlsx. Please use the following steps to test using appraisal.
bundle exec appraisal installbundle exec appraisal rake test
At this time the spreadsheets generated by the test suite are manually inspected. After running the tests, the test output can be viewed in tmp/
Created & Maintained by Weston Ganger - @westonganger
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent