Exporting data with multiple XLSX spreadsheets on Rails.
If you want to generate multiple xlsx sheets then you can not go with “roo” gem then you have to go with the following gems.
you will be able to click on a link at our app and get a spreadsheet with the list of entries of some table from your database.
Add the following gems in gem file :
# Gemfile
gem ‘rubyzip’, ‘= 1.0.0’
gem ‘axlsx’, ‘= 2.0.1’
gem ‘axlsx_rails’
Run the command to install gem :
>> bundle install
Add a link to your view:
app/views/some_view.html.erb
<%= link_to “Export users as spreadsheet”, users_path(format: “xlsx”) %>
Add xlsx format in the controller:
# app/controllers/users/post_controller.rb
def index
@posts = Post.all
respond_to do |format|
format.html
format.xlsx
end
end
Mention the xlsx file with name:
#There is the mention of the file with the name XLSX File and with the current date.
def index
@posts = Post.all
respond_to do |format|
format.html
format.xlsx {
response.headers[‘Content-Disposition’] = “attachment; filename=”XLSX File #{Time.now.strftime(“%m-%d-%Y”)}.xlsx””
}
end
end
Finally you can define your spreadsheet template:
If you want to design the xlsx style sheets so you can also do this in the XLSX files
First of all you have to create a file for generating a xlsx sheet with the same name of the method name but with xlsx.axlsx extensions
Suppose your method name is index then your file would be index.xlsx.axlsx in the view folder.
# app/views/posts/index.xlsx.axlsx
wb = xlsx_package.workbook
wb.add_worksheet(name: “POST DATA”) do |sheet|
sheet.add_row %w(id name)
#you can also put the Array here
# each post is a row on your spreadsheet
@posts.each do |post|
sheet.add_row [post.id, post.name]
end
end
How to create designing in the XLSX Sheets with Ruby :
You can easily create the design using xlsx sheets by following some steps.
# app/views/posts/index.xlsx.axlsx
wb = xlsx_package.workbook
item_style_heading = wb.styles.add_style :b => true, :sz => 11, :font_name => ‘Liberation Sans Narrow’, :alignment => { :horizontal => :center, :vertical => :center, :wrap_text => false, :font_color => “#C52F24”}
item_style = wb.styles.add_style :b => false, :sz => 10, :font_name => ‘Liberation Sans Narrow’, :alignment => { :horizontal => :left, :vertical => :center, :wrap_text => true }
wb.add_worksheet(name: “POST DATA”) do |sheet|
sheet.add_row %w(id name), :style => item_style_heading
#you can also put the Array here
# each post is a row on your spreadsheet
@posts.each do |post|
sheet.add_row [post.id, post.name], :style => item_style
end
end
Here is created the designing for the spreadsheet and the design is assigned to the local variable, we can easily use the variables by the using in the loops. You can see the code how to use it.
