DEV Community

Yaroslav Shmarov
Yaroslav Shmarov

Posted on • Originally published at blog.corsego.com on

Export from database table to Excel Workbook. Level 1

#ruby #rails

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'
Enter fullscreen mode Exit fullscreen mode

/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
Enter fullscreen mode Exit fullscreen mode

/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
Enter fullscreen mode Exit fullscreen mode

any view:

= link_to 'xlsx', posts_path(format: :xlsx), target: :_blank
Enter fullscreen mode Exit fullscreen mode

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/

Top comments (2)

Collapse
 
kalashnikovisme profile image
Pavel Kalashnikov •

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

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

Collapse
 
eminarium profile image
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'`
Enter fullscreen mode Exit fullscreen mode

in Gemfile.