README.md in spreadsheet_architect-2.1.2 vs README.md in spreadsheet_architect-3.0.0

- old
+ new

@@ -1,94 +1,131 @@ # Spreadsheet Architect -<a href='https://travis-ci.org/westonganger/spreadsheet_architect' target='_blank'><img height='24' style='border:0px;height:24px;' src='https://api.travis-ci.org/westonganger/spreadsheet_architect.svg?branch=master' border='0' alt='Build Status' /></a> -<a href='https://ko-fi.com/A5071NK' target='_blank'><img height='24' style='border:0px;height:24px;' src='https://az743702.vo.msecnd.net/cdn/kofi1.png?v=a' border='0' alt='Buy Me a Coffee' /></a> +<a href="https://badge.fury.io/rb/spreadsheet_architect" target="_blank"><img height="21" style='border:0px;height:21px;' border='0' src="https://badge.fury.io/rb/spreadsheet_architect.svg" alt="Gem Version"></a> +<a href='https://travis-ci.org/westonganger/spreadsheet_architect' target='_blank'><img height='21' style='border:0px;height:21px;' src='https://api.travis-ci.org/westonganger/spreadsheet_architect.svg?branch=master' border='0' alt='Build Status' /></a> +<a href='https://rubygems.org/gems/spreadsheet_architect' target='_blank'><img height='21' style='border:0px;height:21px;' src='https://ruby-gem-downloads-badge.herokuapp.com/spreadsheet_architect?label=rubygems&type=total&total_label=downloads&color=brightgreen' border='0' alt='RubyGems Downloads' /></a> +<a href='https://ko-fi.com/A5071NK' target='_blank'><img height='22' style='border:0px;height:22px;' src='https://az743702.vo.msecnd.net/cdn/kofi1.png?v=a' border='0' alt='Buy Me a Coffee' /></a> -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. +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: -- 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 +- Data Sources: ActiveRecord relations, array of plain Ruby object instances, or tabular 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 +- Plain Ruby (without Rails) completely supported -Spreadsheet Architect adds the following methods: +# Install ```ruby -# 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 +gem 'spreadsheet_architect' +``` -# Plain Ruby Class -Post.to_xlsx(instances: posts_array) -Post.to_ods(instances: posts_array) -Post.to_csv(instances: posts_array) +# General Usage -# One Time Usage +### Tabular (Array) Data + +```ruby 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) +SpreadsheetArchitect.to_xlsx(headers: headers, data: data) +SpreadsheetArchitect.to_ods(headers: headers, data: data) +SpreadsheetArchitect.to_csv(headers: headers, data: data) ``` -# Install +### Rails relation or an array of plain Ruby object instances + ```ruby -# Gemfile -gem 'spreadsheet_architect' +posts = Post.order(name: :asc).where(published: true) +# OR +posts = 10.times.map{|i| Post.new(number: i)} + +SpreadsheetArchitect.to_xlsx(instances: posts) +SpreadsheetArchitect.to_ods(instances: posts) +SpreadsheetArchitect.to_csv(instances: posts) ``` -# Basic Class/Model Setup +**(Optional)** 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. A good default strategy is to simply add it to the ApplicationRecord or another parent class to have it available on all appropriate classes. For example: -### Model ```ruby -class Post < ActiveRecord::Base #activerecord not required +class ApplicationRecord < ActiveRecord::Base include SpreadsheetArchitect +end +``` - belongs_to :author - belongs_to :category - has_many :tags +Then use it on the class or ActiveRecord relations of the class - #optional for activerecord classes, defaults to the models column_names +```ruby +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) +``` + +# Usage with Instances / ActiveRecord Relations + +When NOT using the `:data` option, ie. on an AR Relation or using the `:instances` option, Spreadsheet Architect requires an instance method defined on the class to generate the data. 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 (not recommended). If using the `:data` option this is ignored. + +```ruby +class Post + def spreadsheet_columns - #[[Label, Method/Statement, Type(optional) to Call on each Instance, Cell Type(optional)]....] + ### Column format is: [Header, Cell Data / Method (if symbol) to Call on each Instance, (optional) Cell Type] [ ['Title', :title], - ['Content', content], + ['Content', content.strip], ['Author', (author.name if author)], ['Published?', (published ? 'Yes' : 'No')], - ['Published At', :published_at], + :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 +``` - # 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] +Alternatively, if `spreadsheet_columns` is passed as an option, this instance method does not need to be defined on the class. If defined on the class then naturally this will override it. - # 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 +```ruby +Post.to_xlsx(instances: posts, spreadsheet_columns: Proc.new{|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 Einstance. 'Published At' + ['# of Views', :number_of_views, :float], + ['Rating', :rating], + ['Category/Tags', "#{instance.category.name} - #{instance.tags.collect(&:name).join(', ')}"] + ] +}) ``` -Note: Do not define your labels inside this method if you are going to be using custom headers in the model or project defaults. +# Sending & Saving Spreadsheets -# Usage +### Method 1: Send Data via Rails Controller -### Method 1: Controller (for Rails) ```ruby class 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 @@ -130,120 +167,104 @@ end end ``` ### Method 2: Save to a file manually + ```ruby -# Ex. with ActiveRecord relation +### Ex. with ActiveRecord relation +file_data = Post.order(published_at: :asc).to_xlsx File.open('path/to/file.xlsx', 'w+b') do |f| - f.write Post.order(published_at: :asc).to_xlsx + f.write file_data end + +file_data = Post.order(published_at: :asc).to_ods File.open('path/to/file.ods', 'w+b') do |f| - f.write Post.order(published_at: :asc).to_ods + f.write file_data end + +file_data = Post.order(published_at: :asc).to_csv File.open('path/to/file.csv', 'w+b') do |f| - f.write Post.order(published_at: :asc).to_csv + f.write file_data 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 +# Multi Sheet XLSX Spreadsheets +```ruby +axlsx_package = SpreadsheetArchitect.to_axlsx_package({headers: headers, data: data}) +axlsx_package = SpreadsheetArchitect.to_axlsx_package({headers: headers, data: data}, package) -# 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) + f.write axlsx_package.to_stream.read end ``` -<br> -# Methods & Options +See this file for more details: https://github.com/westonganger/spreadsheet_architect/blob/master/test/spreadsheet_architect/multi_sheet_test.rb +### Multi Sheet ODS Spreadsheets +```ruby +ods_spreadsheet = SpreadsheetArchitect.to_rodf_spreadsheet({headers: headers, data: data}) +ods_spreadsheet = SpreadsheetArchitect.to_rodf_spreadsheet({headers: headers, data: data}, spreadsheet) -## SomeClass.to_xlsx +File.open('path/to/file.ods', 'w+b') do |f| + f.write ods_spreadsheet +end +``` -|Option|Default|Notes| -|---|---|---| -|**spreadsheet_columns**<br>*Array*| This defaults to your models custom `spreadsheet_columns` method or any custom defaults defined.<br>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**<br>*Array*| |**Required only for Non-ActiveRecord classes** Array of class/model instances.| -|**headers**<br>*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**<br>*String*|The class name|| -|**header_style**<br>*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](https://github.com/westonganger/spreadsheet_architect/blob/master/docs/axlsx_styles_reference.md)| -|**row_style**<br>*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](https://github.com/westonganger/spreadsheet_architect/blob/master/docs/axlsx_styles_reference.md)| -|**column_styles**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| -|**range_styles**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| -|**merges**<br>*Array*||Merge cells. [See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| -|**borders**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| -|**column_widths**<br>*Array*||Sometimes you may want explicit column widths. Use nil if you want a column to autofit again.| - -<br> +See this file for more details: https://github.com/westonganger/spreadsheet_architect/blob/master/test/spreadsheet_architect/multi_sheet_test.rb -## SomeClass.to_ods +# Methods -|Option|Default|Notes| -|---|---|---| -|**spreadsheet_columns**<br>*Array*| This defaults to your models custom `spreadsheet_columns` method or any custom defaults defined.<br>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**<br>*Array*| |**Required only for Non-ActiveRecord models** Array of class/model instances.| -|**headers**<br>*2D Array*|`self.column_names.collect(&:titleize)`|Pass `false` to skip the header row.| -|**sheet_name**<br>*String*|The class name|| -|**header_style**<br>*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**<br>*Hash*|`{background_color: nil, color: "000000", align: :left, font_size: 10, bold: false}`|Styles for non-header rows. Currently ODS only supports these options| - -<br> +## `to_xlsx(options={})` -## SomeClass.to_csv - |Option|Default|Notes| |---|---|---| -|**spreadsheet_columns**<br>*Array*| This defaults to your models custom `spreadsheet_columns` method or any custom defaults defined.<br>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**<br>*Array*| |**Required only for Non-ActiveRecord classes** Array of class/model instances.| -|**headers**<br>*2D Array*|`self.column_names.collect(&:titleize)`| Data for the header rows cells. Pass `false` to skip the header row.| - -<br> - -## SpreadsheetArchitect.to_xlsx - -|Option|Default|Notes| -|---|---|---| -|**data**<br>*Array*| |Data for the non-header row cells. | -|**headers**<br>*2D Array*|`false`|Data for the header row cells. Pass `false` to skip the header row.| +|**data**<br>*2D Array*| |Cannot be used with the `:instances` option.<br><br>Tabular data for the non-header row cells. | +|**instances**<br>*Array*| |Cannot be used with the `:data` option.<br><br>Array of class/model instances to be used as row data. Cannot be used with :data option| +|**spreadsheet_columns**<br>*Array*| If using the instances option or on a ActiveRecord relation, this defaults to the classes custom `spreadsheet_columns` method or any custom defaults defined.<br>If none of those then falls back to `self.column_names` for ActiveRecord models. | Cannot be used with the `:data` option.<br><br>Use this option to override or define the spreadsheet columns. | +|**headers**<br>*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**<br>*String*|`Sheet1`|| |**header_style**<br>*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](https://github.com/westonganger/spreadsheet_architect/blob/master/docs/axlsx_styles_reference.md)| |**row_style**<br>*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](https://github.com/westonganger/spreadsheet_architect/blob/master/docs/axlsx_styles_reference.md)| -|**column_styles**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| -|**range_styles**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| -|**merges**<br>*Array*||Merge cells. [See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| -|**borders**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb)| +|**column_styles**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/test/spreadsheet_architect/kitchen_sink_test.rb)| +|**range_styles**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/test/spreadsheet_architect/kitchen_sink_test.rb)| +|**merges**<br>*Array*||Merge cells. [See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/test/spreadsheet_architect/kitchen_sink_test.rb). 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**<br>*Array*||[See this example for usage](https://github.com/westonganger/spreadsheet_architect/blob/master/test/spreadsheet_architect/kitchen_sink_test.rb)| |**column_types**<br>*Array*||Valid types for XLSX are :string, :integer, :float, :boolean, nil = auto determine.| |**column_widths**<br>*Array*||Sometimes you may want explicit column widths. Use nil if you want a column to autofit again.| - -<br> -## SpreadsheetArchitect.to_ods +## `to_axlsx_spreadsheet(options={}, axlsx_package_to_join=nil)` +Same options as `to_xlsx`. For more details +## `to_ods(options={})` + |Option|Default|Notes| |---|---|---| -|**data**<br>*2D Array*| |Data for the non-header row cells.| -|**headers**<br>*2D Array*|`false`|Data for the header rows cells. Pass `false` to skip the header row.| +|**data**<br>*2D Array*| |Cannot be used with the `:instances` option.<br><br>Tabular data for the non-header row cells. | +|**instances**<br>*Array*| |Cannot be used with the `:data` option.<br><br>Array of class/model instances to be used as row data. Cannot be used with :data option| +|**spreadsheet_columns**<br>*Array*| If using the instances option or on a ActiveRecord relation, this defaults to the classes custom `spreadsheet_columns` method or any custom defaults defined.<br>If none of those then falls back to `self.column_names` for ActiveRecord models. | Cannot be used with the `:data` option.<br><br>Use this option to override or define the spreadsheet columns. | +|**headers**<br>*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**<br>*String*|`Sheet1`|| |**header_style**<br>*Hash*|`{background_color: "AAAAAA", color: "FFFFFF", align: :center, font_size: 10, bold: true}`|Note: Currently ODS only supports these options| |**row_style**<br>*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**<br>*Array*||Valid types for ODS are :string, :float, :date, :time, :percent, :currency, nil = auto determine. Due to [RODF issue #19](https://github.com/thiagoarrais/rodf/issues/19), :date/:time will be converted to :string | - -<br> +|**column_types**<br>*Array*||Valid types for ODS are :string, :float :percent, :currency, :date, :time,, nil = auto determine. Due to [RODF Issue #19](https://github.com/thiagoarrais/rodf/issues/19), :date/:time will be converted to :string | -## SpreadsheetArchitect.to_csv +## `to_rodf_spreadsheet(options={}, spreadsheet_to_join=nil)` +Same options as `to_ods` +## `to_csv(options={})` + |Option|Default|Notes| |---|---|---| -|**data**<br>*2D Array*| |Data for the non-header row cells.| -|**headers**<br>*2D Array*|`false`|Data for the header rows cells. Pass `false` to skip the header row.| +|**data**<br>*2D Array*| |Cannot be used with the `:instances` option.<br><br>Tabular data for the non-header row cells. | +|**instances**<br>*Array*| |Cannot be used with the `:data` option.<br><br>Array of class/model instances to be used as row data. Cannot be used with :data option| +|**spreadsheet_columns**<br>*Array*| If using the instances option or on a ActiveRecord relation, this defaults to the classes custom `spreadsheet_columns` method or any custom defaults defined.<br>If none of those then falls back to `self.column_names` for ActiveRecord models. | Cannot be used with the `:data` option.<br><br>Use this option to override or define the spreadsheet columns. | +|**headers**<br>*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. | -# Change model default method options +# Change class-wide default method options + ```ruby class Post include SpreadsheetArchitect def spreadsheet_columns @@ -260,16 +281,17 @@ sheet_name: self.name, column_styles: [], range_styles: [], merges: [], borders: [], - column_types: [] + column_types: [], } end ``` -# Change project wide default method options +# Change project-wide default method options + ```ruby # config/initializers/spreadsheet_architect.rb SpreadsheetArchitect.default_options = { headers: true, @@ -278,36 +300,34 @@ sheet_name: 'My Project Export', column_styles: [], range_styles: [], merges: [], borders: [], - column_types: [] + column_types: [], } ``` -# Complex XLSX Example with Styling -See this example: https://github.com/westonganger/spreadsheet_architect/blob/master/examples/complex_xlsx_styling.rb +# Kitchen Sink Examples with Styling for XLSX and ODS +See this example: https://github.com/westonganger/spreadsheet_architect/blob/master/test/spreadsheet_architect/kitchen_sink_test.rb +# Axlsx Style Reference -# Multi Sheet XLSX or ODS spreadsheets -```ruby -# 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 +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 -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 -``` +# Testing / Validating your Spreadsheets -See this example: https://github.com/westonganger/spreadsheet_architect/blob/master/examples/multi_sheet_spreadsheets.rb +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. +# Contributing -# 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 +We use the `appraisal` gem for testing multiple versions of `axlsx`. Please use the following steps to test using `appraisal`. +1. `bundle exec appraisal install` +2. `bundle exec appraisal rake test` # Credits -Created by [@westonganger](https://github.com/westonganger) + +Created & Maintained by [Weston Ganger](https://westonganger.com) - [@westonganger](https://github.com/westonganger) For any consulting or contract work please contact me via my company website: [Solid Foundation Web Development](https://solidfoundationwebdev.com) [![Solid Foundation Web Development Logo](https://solidfoundationwebdev.com/logo-sm.png)](https://solidfoundationwebdev.com)