XLS Export in ActiveAdmin

Today I want to talk about my recent experience with ActiveAdmin and export to XLS.

To obtain an XLS from ActiveAdmin we currently have two simple solutions that allow us to do it in a short time: activeadmin-axlsx and activeadmin-xls. The second takes much from the first.

Unfortunately for me, neither of the two was giving me the result that I wanted, therefore I decided to approach the problem from a lower level and use spreadsheet.

Thanks to spreadsheet we can have complete control over the file that is generated, though at the price of having to write a little more code.

As the first thing we add spreadsheet to the Gemfile

gem ‘spreadsheet’

Next, in order to maintain the folder structure as ordered as possible we create a folder (`app/spreadsheets’) where we wil place all the classes that will take care of the generation of XLS files. Then we add to config/application.rb:

module TestApp
  class Application < Rails::Application
    …
    config.autoload_paths += %W(#{config.root}/app/spreadsheets)
    …
  end
end

In this way we can have classes for creating XLS without using require.

MimeType

In order to allow our application to export xls file, we must define the format. To do this we modify the file config/initializers/mime_types.rb and add

Mime::Type.register “application/vnd.ms-excel”, :xls

ActiveAdmin download links

The download links for ActiveAdmin are set in config/initializers/active_admin.rb, in my case I did not need the pdf so I set:

ActiveAdmin.setup do |config|
  …
  config.download_links = [:csv, :xml, :xls]
  …
end

At this point we can set up our controller ActiveAdmin to respond properly to the XLS format. We will take the model User as example. We edit the file app/admin/user.rb adding the code needed to handle the format xls

ActiveAdmin.register User do
  …
  controller do
    def index
      …
      index! do |format|
        format.xls {
          spreadsheet = UsersSpreadsheet.new @users
          send_data spreadsheet.generate_xls, filename: “users.xls”
        }
      end
    end
  end
  …
end

XLS File generation

At this point we are finally able to generate XLS files without constraints.

Create the file app/spreadsheets/users_spreadsheet.rb

class UsersSpreadsheet
  attr_accessor :users
  
  def initialize users
    @users = users
  end

  def generate_xls
    book = Spreadsheet::Workbook.new
    sheet = book.create_worksheet name: “Users”

    create_body sheet

    data_to_send = StringIO.new
    book.write data_to_send
    data_to_send.string 
  end

  def create_body sheet
    # Header row with a specific format
    sheet.row(0).concat %w{Email Username Newsletter}
    sheet.row(0).default_format = Spreadsheet::Format.new weight: :bold

    row_index = 1
    users.each do |user|
      sheet.row(row_index).concat [user.email, user.username, user.newsletter_subscription]
      # sheet.row(row_index).default_format = Spreadsheet.Format.new # define a custom format
      row_index += 1
    end
  end
end

Note: I’ve used the row_index variable to set more easily the cell format, if needed.

We just have to click on the “XLS” link in the Users index.

At this point we have our xls, simple, but that we can manage as we want through the application’s code. We can set the format by entire row or by single cell but we can do more complex things such as printing more rows for record, maybe for handling a relationship. Such customizations were impossible with the two gems mentioned at the beginning.

I leave you with the link of an initial guide on what you can do with Spreadsheet, it is small but on Stack Overflow can find many examples about how to vary many cell formats and settings.

I hope this post has been helpful.

Bye and see you soon!