Rails 5: Exporting Ransack result set with axlsx

Question

I'm trying generate Excel file using axlsx gem based on Ransack gem result set.

controller:

@q = Candy.ransack(params[:q])
@candies = @q.result.all

When I call @candies with parameters like "chocolate" in the view using Ransack gem, I get 30 or so results out of 600. It was successfully filtered!

But when I download @candies using axlsx using:

//index.xlsx.axlsx

    require 'axlsx'
    xlsx_package = Axlsx::Package.new

    workbook = xlsx_package.workbook

    workbook.add_worksheet(name: "Candies") do |sheet|
    sheet.add_row ["id", "name", "type", "date"]

        @candies.each do |candy|
             sheet.add_row [candy.id, candy.name, candy.type, candy.date]
        end
    end

It generates file with all 600 records!

This question very similar to Ransack Search Results - to_xls? However I've encountered same problem using axlsx gem instead of to_xls gem!

I have found a solution in the comments section of railscast video "Erick Alvarez about 5 years ago You can use <%= link_to "Excel", user_contacts_path(params.merge(format: 'xls')) %> and this will maintain the current parameters you have plus the ones you add or edit inside merge." — , Aug 17 '17 at 07:04

widjajayd · Answer 1 · 2017-08-15T02:12:52.567

0

your search result should be without "all"

@q = Candy.ransack(params[:q])
@candies = @q.result
put "total record = ", @candies.count

I add trace method, so you can trace the result, you can check the result from rails server console / development log file

edited Aug 15 '17 at 02:12

answered Aug 15 '17 at 01:27

widjajayd

6,090
4
30
41

he llo widjajayd! Thank you for response. But I am getting same 600 records inside file, instead of 30 results from ransack filter. – Aug 15 '17 at 01:41

score 0 · Answer 2 · answered Nov 30 '18 at 12:10

Merge the params to include the relevant format type you are downloading. The relevant path for my application is: search_shifts_path - yours will be different so please change that accordingly.

Then make sure that you have permitted the relevant parameter in your controller. Given we are not changing any fields, we can safely do so:

def search
  params.permit![:format] # must permit this

  @q = Shift.ransack(params[:q])
  @shifts = @q.result

  respond_to do |format|
    format.xlsx
    format.html
  end

end

score 0 · Answer 3 · edited Apr 15 '20 at 02:32

0

@q = Candy.ransack(params[:q])
@candies = @q.result.page
puts "total record = #{@candies.count}"

I think you have set a page limit to display on UI. Use .page to filter and send the same var to axslx file.

edited Apr 15 '20 at 02:32

RajeshM

872
11
21

answered Apr 14 '20 at 19:24

Santosh Kumar

1
2

Rails 5: Exporting Ransack result set with axlsx

3 Answers3