Workbook-Rails — Workbook templates for Rails views

In your Gemfile:

gem 'workbook_rails'

• Rails 6.0 to 7.1 (tested)
• Workbook 0.4.16 requires Axlsx 2.0.1, which requires rubyzip 1.0.0
• You must use render_to_string to render a mail attachment.

• This gem depends on Workbook. See README or rdoc for usage.

Workbook-Rails provides a renderer and a template handler. It adds the :xlsx and :xls formats and parses .wb templates. This lets you take all the Workbook code out of your controller or model and place it inside the template, where view code belongs!

To use Workbook-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

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

table = workbook.sheet.table
@buttons.each do |button|
  table << [button.name, button.category, button.price]
end

This is where you place all your Workbook specific markup. Add worksheets, fill content, merge cells, add styles.

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

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

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'

You can create a template with .wb extension, without format, and use for generating different spreadsheets formats, such as xls and xlsx, using respond_to and format param.

Set :format param in the route (/route_path.xlsx, or /route_path.xls) and use respond_to in the controller

respond_to do |format|
  format.xlsx
  format.xls
end

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.

If Rails calls Workbook 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'

Partials work as expected:

render :partial => 'cover_sheet', :locals => {:sheet => workbook.sheet}
workbook << [['Content']]
workbook.last.name = 'Content'

With the partial simply using the passed variables:

sheet.name = "Cover Sheet"
sheet.table.push ['Cover', 'Sheet']

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 handlers: [:wb], formats: [:xlsx], template: "users/export", locals: {users: users}
    attachments["Users.xlsx"] = {mime_type: Mime::XLSX, content: xlsx}
    ...
  end
end

• If the route specifies or suggests the :xlsx format you do not need to specify formats or handlers.
• If the template (users/export) can refer to only one file (the xlsx.axlsx template), you do not need to specify handlers, provided the formats includes :xlsx.

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

• Using rails runner
• Without rails runner

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:

• If it says your template is missing, check that its extension is .xlsx.wb.
• If you get the error uninitialized constant Mime::XSLX you have used format.xslx instead of format.xlsx, or something similar.

Before Rails 4.2 you could call:

  render xlsx: "users/index"

And workbook_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!

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 Workbook-Rails to see if you are having Workbook problems. If you can manually create the spreadsheet, create an issue and we will work it out.

• Rails
• Workbook

• Sergio Cambra

Many thanks to straydogstudio for axlsx_rails, which this gem is based in.