How to generate Excel file with passing params from AJAX search?

Question

I'm performing AJAX search in my Rails application. Here is code from controller:

def show
@website = Website.find(params[:id])
if (current_user.id != @website.user_id)
  redirect_to root_path
  flash[:notice] = 'You are not owner!'
end
if params[:report] && params[:report][:start_date] && params[:report][:end_date]
  @performance_reports = @website.performance_reports.where("created_at between ? and ?", params[:report][:start_date].to_date, params[:report][:end_date].to_date)    
else
  @performance_reports = @website.performance_reports
end

but when I'm trying to generate Excel document it alway goes to branch without params, because there are no params in URL.

One man reccomend me to use this post. I tried to implement it, but couldn't.

I don't understand this post enough, I just can't get where data is passing(spreadsheet gem)

Here is code:

 def export

  @website = Website.last

  @data = @website.performance_reports

  report = Spreadsheet::Workbook.new

  spreadsheet = StringIO.new

  contruct_body(spreadsheet, @data)

  report.write spreadsheet 

  send_data spreadsheet.string, :filename => "yourfile.xls", :type =>  "application/vnd.ms-excel"


 end

and it gives me error:

 undefined method `contruct_body'

Code from view:

    <%= form_tag( url_for, :method => :get, :id => "report") do%>
      ...show action posted above...
     <% end %>

    <%= link_to export_path do%>
     <b>Export</b>
    <% end %>
   ...working code without AJAX...
       <%= link_to url_for(request.parameters.merge({:format => :xls}))  do%>
    <b>Export</b>
    <% end %>

Please tell me where is my mistake or suggest ano

score 2 · Accepted Answer · answered Oct 16 '12 at 14:31

For the first problem, you need to show the view code and the path ajax is taking. Give us more information how the excel is being called.

For the second issue, you need to define that method. Specify how you will populate the spreadsheet with the data. Here is the guide. https://github.com/zdavatz/spreadsheet/blob/master/GUIDE.txt

== Writing is easy As before, make sure you have Spreadsheet required and the client_encoding set. Then make a new Workbook:

  book = Spreadsheet::Workbook.new

Add a Worksheet and you're good to go:

  sheet1 = book.create_worksheet

This will create a Worksheet with the Name "Worksheet1". If you prefer another name, you may do either of the following:

  sheet2 = book.create_worksheet :name => 'My Second Worksheet'
  sheet1.name = 'My First Worksheet'

Now, add data to the Worksheet, using either Worksheet#[]=, Worksheet#update_row, or work directly on Row using any of the Array-Methods that modify an Array in place:

  sheet1.row(0).concat %w{Name Country Acknowlegement}
  sheet1[1,0] = 'Japan'
  row = sheet1.row(1)
  row.push 'Creator of Ruby'
  row.unshift 'Yukihiro Matsumoto'
  sheet1.row(2).replace [ 'Daniel J. Berger', 'U.S.A.',
                          'Author of original code for Spreadsheet::Excel' ]
  sheet1.row(3).push 'Charles Lowe', 'Author of the ruby-ole Library'
  sheet1.row(3).insert 1, 'Unknown'
  sheet1.update_row 4, 'Hannes Wyss', 'Switzerland', 'Author'

Add some Formatting for flavour:

  sheet1.row(0).height = 18

  format = Spreadsheet::Format.new :color => :blue,
                                   :weight => :bold,
                                   :size => 18
  sheet1.row(0).default_format = format

  bold = Spreadsheet::Format.new :weight => :bold
  4.times do |x| sheet1.row(x + 1).set_format(0, bold) end

And finally, write the Excel File:

  book.write '/path/to/output/excel-file.xls'

Check the log it is probably looking for a template you need to have. For your first problem, if you redirect you need to redirect with the params. if not then you need to debug and find where you to lose them. — Sully, Oct 16 '12 at 14:59
It's generating excel file. I can see it in my folder. Can you tell me how to use variable in export action from show action ? — MID, Oct 16 '12 at 15:03
How are you redirecting to show from export? Either you will have to preserve the params, but that depends on the scenario. Or you can keep track of them in an instance variable. — Sully, Oct 16 '12 at 15:06
It will be another problem for me - because it is on not default tab. For now I will use `redirect_to :back` — MID, Oct 16 '12 at 15:09
Ok to solve that don't redirect, because that sends a new request. Try `render :show` http://en.wikibooks.org/wiki/Ruby_on_Rails/ActionView/Rendering_and_Redirecting — Sully, Oct 16 '12 at 15:12
update your params with the desired tab you want to load then call your logic then update your view like this http://stackoverflow.com/a/4565209/643500. Dont forget to accept answers to your questions http://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work — Sully, Oct 16 '12 at 15:19
I mean it is not fully working. Now, I'm trying to work with spreadsheet gem. — MID, Oct 16 '12 at 15:20

How to generate Excel file with passing params from AJAX search?

1 Answers1