Export from database table to Excel Workbook. Level 1

Use the gem caxlsx to download and format data into XLSX.

• XLS - proprietary Microsoft Excel format
• XLSX - free format

Gemfile

gem 'caxlsx'
gem 'caxlsx_rails'

/app/controllers/posts_controller.rb

  def index
    respond_to do |format|
      format.html do
        @posts = Post.order(created_at: :desc)
      end
      format.xlsx do
        @posts = Post.all
        render xlsx: 'posts', template: 'posts/whatever'
      end
    end
  end

/app/views/posts/whatever.xlsx.axlsx

wb = xlsx_package.workbook

wb.add_worksheet(name: "Post") do |sheet|
  sheet.add_row ['name', 'creation date']
  @posts.each do |post|
    sheet.add_row [post.name, post.created_at]
  end
end

any view:

= link_to 'xlsx', posts_path(format: :xlsx), target: :_blank

That's it! Now you can export you posts to Excel!

Here is a more detailed article (that initially inspired me): https://www.sitepoint.com/generate-excel-spreadsheets-rails-axlsx-gem/

Comments

[Pavel Kalashnikov]

Looks like, we can just add gem 'caxlsx_rails'.

gem 'caxlsx' is a dependency and will be installed :)

[Merdan Durdyyev]

Unfortunately it does not install automatically.
I had an hour of misunderstanding because of this.
We have to explicitly include

`gem 'caxlsx_rails'`

in Gemfile.