gem 'spreadsheet_architect'

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)

class Post < ApplicationRecord
  include SpreadsheetArchitect
end

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

end

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)

Post.to_xlsx(instances: posts, spreadsheet_columns: :my_special_method)

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
end

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
  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
end

axlsx_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
end

ods_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

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,
}