Home

Awesome

Notice: axlsx_rails renamed to caxlsx_rails

This gem has been renamed to match other gems in the Axlsx community organization: https://github.com/caxlsx

Axlsx-Rails — Spreadsheet templates for Rails

Gem Version Coverage Status tests downloads

Notice: Community Axlsx Organization

To better maintain the Axlsx ecosystem, all related gems have been forked or moved to the following community organization:

https://github.com/caxlsx

Join the Caxlsx Slack channel

Installation

In your Gemfile:

gem 'caxlsx'
gem 'caxlsx_rails'

See previous installations if needed.

Requirements

FYI

Usage

Axlsx-Rails provides a renderer and a template handler. It adds the :xlsx format and parses .xlsx.axlsx templates. This lets you take all the caxlsx code out of your controller or model and place it inside the template, where view code belongs! See this blog post for a more complete walkthrough.

Controller

To use Axlsx-Rails set your instance variables in your controller and configure the response if needed:

class ButtonController < ApplicationController
  def action_name
    @buttons = Button.all
    respond_to do |format|
      format.xlsx
    end
  end
end

Template

Create the template with the .xlsx.axlsx extension (action_name.xlsx.axlsx for example.) Watch out for typos! In the template, use xlsx_package variable to create your spreadsheet:

wb = xlsx_package.workbook
wb.add_worksheet(name: "Buttons") do |sheet|
  @buttons.each do |button|
    sheet.add_row [button.name, button.category, button.price]
  end
end

This is where you place all your caxlsx specific markup. Add worksheets, fill content, merge cells, add styles. See the caxlsx examples page to see what you can do.

Remember, like in erb templates, view helpers are available to use the .xlsx.axlsx template.

That's it. Call your action and your spreadsheet will be delivered.

Rendering Options

You can call render in any of the following ways:

# rendered, no disposition/filename header
render 'buttons'
# rendered from another controller, no disposition/filename header
render 'featured/latest'
# template and filename of 'buttons'
render xlsx: 'buttons'
# template from another controller, filename of 'latest_buttons'
render xlsx: 'latest_buttons', template: 'featured/latest'

Disposition

To specify a disposition (such as inline so the spreadsheet is opened inside the browser), use the disposition option:

render xlsx: "buttons", disposition: 'inline'

If render xlsx: is called, the disposition defaults to attachment.

File name

If Rails calls Axlsx through default channels (because you use format.xlsx {} for example) you must set the filename using the response header:

format.xlsx {
  response.headers['Content-Disposition'] = 'attachment; filename="my_new_filename.xlsx"'
}

If you use render xlsx: the gem will try to guess the file name:

# filename of 'buttons'
render xlsx: 'buttons'
# filename of 'latest_buttons'
render xlsx: 'latest_buttons', template: 'featured/latest'

If that fails, pass the :filename parameter:

render xlsx: "action_or_template", filename: "my_new_filename.xlsx"

Acts As Xlsx

If you use acts_as_xlsx, configure the active record normally, but specify the package in the template:

User.to_xlsx package: xlsx_package, (other options)

Note: As of 4/1/2014 Acts As Xlsx is not compatible with Rails 4.1, and generates a warning on 4.0. You may use my patched fork until it is remedied.

Axlsx Package Options

Axlsx provides three options for initializing a spreadsheet:

To pass these to the new package, pass them to render :xlsx or pass them as local variables.

For example, to set the author name, pass the :xlsx_author parameter to render :xlsx or as a local variable:

render xlsx: "index", xlsx_author: "Elmer Fudd"
render "index", locals: {xlsx_author: "Elmer Fudd"}

Other examples:

render xlsx: "index", xlsx_created_at: 3.days.ago
render "index", locals: {xlsx_use_shared_strings: true}

Partials

Partials work as expected, but you must pass in relevant spreadsheet variables:

wb = xlsx_package.workbook
render :partial => 'cover_sheet', :locals => {:wb => wb}
wb.add_worksheet(name: "Content") do |sheet|
  sheet.add_row ['Content']
end

With the partial simply using the passed variables:

wb.add_worksheet(name: "Cover Sheet") do |sheet|
  sheet.add_row ['Cover', 'Sheet']
end

Mailers

To use an xlsx template to render a mail attachment, use the following syntax:

class UserMailer < ActionMailer::Base
  def export(users)
    xlsx = render_to_string layout: false, handlers: [:axlsx], formats: [:xlsx], template: "users/export", locals: {users: users}
    attachment = Base64.encode64(xlsx)
    attachments["Users.xlsx"] = {mime_type: Mime[:xlsx], content: attachment, encoding: 'base64'}
    # For rails 4 use Mime::XLSX
    # attachments["Users.xlsx"] = {mime_type: Mime::XLSX, content: attachment, encoding: 'base64'}
    # self.instance_variable_set(:@_lookup_context, nil) # If attachments are rendered as content, try this and open an issue
    ...
  end
end

Scripts

To generate a template within a script, you need to instantiate an ActionView context. Here are two gists showing how to perform this:

Testing

There is no built-in way to test your resulting workbooks / templates. But here is a piece of code that may help you to find a way.

First, create a shared context

RSpec.shared_context 'axlsx' do

  # all xlsx specs describe must be normalized
  # "folder/view_name.xlsx.axlsx"
  # allow to infer the template path
  template_name = description

  let(:template_path) do
    ['app', 'views', template_name]
  end

  # This helper will be used in tests
  def render_template(locals = {})
    axlsx_binding = Kernel.binding
    locals.each do |key, value|
      axlsx_binding.local_variable_set key, value
    end
    # define a default workbook and a default sheet useful when testing partial in isolation
    wb = Axlsx::Package.new.workbook
    axlsx_binding.local_variable_set(:wb, wb)
    axlsx_binding.local_variable_set(:sheet, wb.add_worksheet)

    # mimics an ActionView::Template class, presenting a 'source' method
    # to retrieve the content of the template
    filename = Rails.root.join(*template_path).to_s
    template = Struct.new(:source).new(File.read(filename))
    axlsx_binding.eval(AxlsxRails::TemplateHandler.new.call(template), filename)
    axlsx_binding.local_variable_get(:wb)
  end
end

Include it in your spec files:

require 'spec_helper'
require 'helpers/axlsx_context'

describe 'shared/_total_request.xlsx.axlsx' do
  include_context 'axlsx'

  before :each do
    # all the instance variables here are the one used in 'shared/_total_request.xlsx.axlsx'
    @widget = mock_model(Widget, name: 'My widget')
    @message_counts = Struct.new(:count_all, :positives, :negatives, :neutrals).new(42, 23, 15, 25)
  end

  it 'has a title line mentioning the widget' do
    wb = render_template
    sheet = wb.sheet_by_name('Réf. Requête')
    expect(sheet).to have_header_cells ['My widget : Messages de la requête']
  end

  it 'exports the message counts' do
    wb = render_template
    sheet = wb.sheet_by_name('Réf. Requête')
    expect(sheet).to have_cells(['Toutes tonalités', 'Tonalité positive', 'Tonalité négative', 'Tonalité neutre']).in_row(2)
    expect(sheet).to have_cells([42, 23, 15, 25]).in_row(3)
  end

end

Matchers used


# encoding: UTF-8

require 'rspec/expectations'

module XlsxMatchers

  RSpec::Matchers.define :have_header_cells do |cell_values|
    match do |worksheet|
      worksheet.rows[0].cells.map(&:value) == cell_values
    end

    failure_message do |actual|
      "Expected #{actual.rows[0].cells.map(&:value)} to be #{expected}"
    end
  end

  RSpec::Matchers.define :have_cells do |expected|
    match do |worksheet|
      worksheet.rows[@index].cells.map(&:value) == expected
    end

    chain :in_row do |index|
      @index = index
    end

    failure_message do |actual|
      "Expected #{actual.rows[@index].cells.map(&:value)} to include #{expected} at row #{@index}."
    end
  end
end

Troubleshooting

Mispellings

It is easy to get the spelling wrong in the extension name, the format.xlsx statement, or in a render call. Here are some possibilities:

Using axlsx_rails in API mode

In API mode Rails does not include ActionView, so axlsx_rails will not work. To render axlsx_rails templates you must include ActionView::Rendering in your controller and override render_to_body:

class MyController < ActionController::API
  include ActionView::Rendering

  def show
    respond_to do |format|
      format.xlsx
    end
  end

  private

  def render_to_body(options)
    _render_to_body_with_renderer(options) || super
  end
end

See issue 107

Mailer Attachments: No content, cannot read, Invalid Byte Sequence in UTF-8

If you are having problems with rendering a template and attaching it to a template, try a few options:

class UserMailer < ActionMailer::Base
  def export(users)
    xlsx = render_to_string handlers: [:axlsx], formats: [:xlsx], template: "users/export", locals: {users: users}
    attachments["Users.xlsx"] = {mime_type: Mime[:xlsx], content: xlsx, encoding: 'base64'}
    # For Rails 4 use Mime::XLSX
    # attachments["Users.xlsx"] = {mime_type: Mime::XLSX, content: xlsx, encoding: 'base64'}
    # self.instance_variable_set(:@_lookup_context, nil) # If attachments are rendered as content, try this and open an issue
    ...
  end
end

If you get these errors, please open an issue and share code so the bug can be isolated. Or comment on issue #29 or #25.

The unparsed template is returned, or something similar

Have you followed other tutorials for serving Excel from Rails? Have you declared the MIME type already? These may be incompatible with caxlsx_rails (which declares the MIME type for you.) Remove any vestiges of other tutorials from your code and try again.

Generated Files Can't Be Opened or Invalid Byte Sequence in UTF-8

Both these errors appear to be caused by Rails applying a layout to the template. Passing layout: false to render :xlsx should fix this issue. Version 0.5.0 attempts to fix this issue.

If you get this error, please open an issue and share code so the bug can be isolated.

Rails 4.2 changes

Before Rails 4.2 you could call:

  render xlsx: "users/index"

And caxlsx_rails could adjust the paths and make sure the template was loaded from the right directory. This is no longer possible because the paths are cached between requests for a given controller. As a result, to display a template in another directory you must use the :template parameter (which is normal Rails behavior anyway):

  render xlsx: "index", template: "users/index"

If the request format matches you should be able to call:

  render "users/index"

This is a breaking change if you have the old syntax!

Turbolinks

If you are using turbolinks, you may need to disable turbolinks when you link to your spreadsheet:

# turbolinks 5:
link_to 'Download spreadsheet', path_to_sheet, data: {turbolinks: false}

Rails 7 Unknown Format

In Rails 7, if you get an error of "Unknown Format" you may need to add (format: "xlsx") to the named route.

A clue to the "format" that Rails is responding with is to look at your log file or console after you click the HTML link or submit your form. You should see Processing by Controller#action as XLSX.

Code Examples

Scenario 1 - HTML Link within index.html.erb

link_to "Download as Excel", my_named_route_path(format: "xlsx")

Scenario 2 - Using form_with()

form_with method: :post, url: my_named_route_path(format: :xlsx) do |f|

What to do

If you are having problems, try to isolate the issue. Use the console or a script to make sure your data is good. Then create the spreadsheet line by line without Axlsx-Rails to see if you are having caxlsx problems. If you can manually create the spreadsheet, create an issue and we will work it out.

Previous Installations

In your Gemfile:

gem 'rubyzip', '>= 1.2.1'
gem 'axlsx', git: 'https://github.com/randym/axlsx.git', ref: 'c8ac844'
gem 'axlsx_rails'

If rubyzip 1.0.0 is needed:

gem 'rubyzip', '= 1.0.0'
gem 'axlsx', '= 2.0.1'
gem 'axlsx_rails'

If rubyzip >= 1.1.0 is needed:

gem 'rubyzip', '~> 1.1.0'
gem 'axlsx', '2.1.0.pre'
gem 'axlsx_rails'

Dependencies

Authors

Contributors

Many thanks to contributors:

Change log

June 18, 2024: 0.6.4 release

March 8, 2022: 0.6.3 release

December 18, 2019: 0.6.2 release

December 18, 2019: 0.6.1 release

September 5, 2019: 0.6.0 release

May 1st, 2018: 0.5.2 release

March 29th, 2017: 0.5.1 release

July 26st, 2016: 0.5.0 release

July 13th, 2015: 0.4.0 release

November 20th, 2014: 0.3.0 release

September 4, 2014: 0.2.1 release

April 9, 2014: 0.2.0 release

October 11, 2013

October 4, 2013

July 25, 2013

January 18, 2013: 0.1.4 release

December 6, 2012: 0.1.3 release

July 25, 2012: 0.1.2 release

July 19, 2012: 0.1.1 release

July 17, 2012: 0.1.0 release

July 12, 2012: 0.0.1 release