Multiple xlsx sheets in rails

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.

 

About the author

Leave a Reply

Your email address will not be published.