Rails: Exporting data to spreadsheets

Allowing admins of data systems to export data to spreadsheets allows them to manipulate it, make it pretty, generate reports, and pass it on in their own way without the developer having to bake in all that functionality to the app. Out of the box Rails can dump to XML and JSON, but that's not so user friendly. Dumping to CSV is closer, but it's too easy to have issues with field termination and value quoting, plus you have no chance to style the output. A little can go a long way.

Enter the axlsx gem. It allows you to quickly and easily create spreadsheets in Rails, exporting to the .xlsx format which is readable by Excel, OpenOffice, and Google Docs. Solid coverage. When you combine this with the axlsx-rails gem you can generate spreadsheets as easily as you generate HTML with an ERB template.

As of this writing, you want version 2.1.0.pre of axlsx, so add this to your Gemfile:

# Export data to spreadsheets
gem 'axlsx', '2.1.0.pre'
gem 'axlsx_rails'

Then, create a view file just as you would for HTML. Here's a generic index.xlsx.axlsx view template for dumping a record's attributes:

# app/views/base_controller/index.xlsx.axlsx

# Hook into the xlsx package provided by the axlsx-rails gem
wb = xlsx_package.workbook

# Create a style we can use for record headers
styles = xlsx_package.workbook.styles
header_style = styles.add_style bg_color: "00",
                                fg_color: "FF",
                                bold: true,
                                alignment: { horizontal: :center }

# Get a list of associations. We want to export the association value rather than ID
# e.x. Get Post#user rather than Post#user_id
assocs = resource_class.reflect_on_all_associations(:belongs_to).map{|a| [a.foreign_key.to_sym, a.name]}.to_h

# Create a worksheet. Name it after the resource we're exporting
wb.add_worksheet(name: @resource.model_name.plural.titleize) do |sheet|
  # Get list of attributes to export
  attrs = policy(@resources.first).permitted_export_attributes

  # Add a header row using the header style we defined
  sheet.add_row attrs.map { |n| @resources.first.class.human_attribute_name(n) }, style: header_style

  # Add each row to our sheet
  @resources.each do |row|
    sheet.add_row attrs.map { |a| row.send(assocs[a] || a) }
  end
end

One thing of note is the policy(@resource_value.first).permitted_export_attributes line. This allows us to define a method in Pundit policies that returns a list of fields allowed for export. In application_policy.rb one might add something like:

# app/policies/application_policy.rb

def permitted_export_attributes
  # don't export friendly_id slug, or legacy data mapping fields.
  record.class.attribute_names.map(&:to_sym) - %i(slug legacy_id data_source)
end

One last thing I found useful was to set the Content-Disposition to attachment and set my own export file name. This triggers file download (rather than the user having to right click, save as) and allows us to set a sensible file name.

# app/controllers/people_controller.rb

def index
  respond_to do |format|
    format.html
    format.xlsx { set_attachment_name "sites #{Time.now.utc.strftime('%Y%M%d%H%M%S')}.xlsx" }
  end
end

# app/controllers/base_controller.rb

protected

def set_attachment_name(name)
  escaped = URI.encode(name)
  response.headers['Content-Disposition'] = "attachment; filename*=UTF-8''#{escaped}"
end

And here we go! sites 20172419062423.xlsx