Difficulty with send_data in Ruby on Rails in conjunction with Spreadsheet plug-in

Question

I have a function in a controller that takes in some specifications and generates a report on them. This function user_report is called in a view:

< %= submit_to_remote 'submit-button', "Export Report to Excel", :url => { :controller => :reports, :action => :user_report, :print_state => 'print'} % >

In reports_controller I use the Spreadsheet plugin to generate an Excel file within the user_report function. I want to use send_data to stream the file to the user without creating it on the server first. The research I've done shows that using StringIO is the way to go, as shown below. Frustratingly, nothing happens when I call send_data. The plugin seems to work well creating a file and saving it on the server, but does nothing when I use send_file, suggesting that the problem doesn't lie in the plugin. But then what am I doing wrong with send_file/send_data?

The function itself looks like this:

def user_report

if request.post?
  unless params[:reports][:userid].blank?
    @userid=params[:reports][:userid]
  end
  if params[:print_state]=='print'  

    report = Spreadsheet::Workbook.new
    info = report.create_worksheet :name => 'User Information'
    info.row(1).push 'User ID', @userid

    @outfile = "Report_for_#{@userid}.xls"

    require 'stringio'
    data = StringIO.new ''
    report.write data
    send_data data.string, :type=>"application/excel", :disposition=>'attachment', :filename => @outfile
  end

  respond_to do |format|
    format.js { }
  end
end

end

The log file reads 2010-10-18 14:13:59 INFO -- Sending data Report_for_jjohnson.xls but no download begins in-browser. I've succeed in using send_data on this app before, which is confusing.

I'm using Rails v2.3, Ruby v1.8.7, and Spreadsheet v6.4.1 at spreadsheet.rubyforge.org.

score 7 · Answer 1 · answered Oct 23 '10 at 15:53

7

Just change the line:

send_data data.string, :type=>"application/excel", :disposition=>'attachment', :filename => @outfile

to:

send_data data.string.bytes.to_a.pack("C*"), :type=>"application/excel", :disposition=>'attachment', :filename => @outfile

answered Oct 23 '10 at 15:53

Asang Dani

71
1

4

This is an old question... But I have an urge to notice that `data.string.force_encoding('binary')` works for me and looks a bit better. – scaryzet Mar 13 '14 at 13:28

score 1 · Answer 2 · answered May 06 '22 at 08:49

For someone looking at this in (or after) 2022, a possible solution to this would be to use Axlsx Gem. The interface provides a method for converting it to a StringIO object. (From Axlsx Documentation)

#serialize to a file
p = Axlsx::Package.new
# ......add cool stuff to your workbook......
# Serialize to a stream
s = p.to_stream()
send_data(
      s.read,
      :type => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      :disposition => 'attachment',
      :filename => @filename
)

score 0 · Answer 3 · answered Sep 06 '13 at 08:41

Even though I dont like to write and delete , but with spreadsheet seems like the only solution.

  # write the file

 book.write "Employee_History_#{ params[:id]}.xls"

 # send the file

 send_file "Employee_History_#{ params[:id]}.xls", :type => "application/vnd.ms-excel", :filename => "data.xls", :stream => false

 # and then delete the file

 File.delete("Employee_History_#{ params[:id]}.xls")

Difficulty with send_data in Ruby on Rails in conjunction with Spreadsheet plug-in

3 Answers3

Linked