Download XLSX file created by XlsxWriter in rails

Question

I am trying to save an xlsx file to the browser downloads, the following functions gets activated when clicking a browser button

def trip_bit
@car = Car.find(params[:id])
@t = @car.trips.all
@trips = @t.order(:started_at)

if @trips then
  doc = XlsxWriter.new
  doc.quiet_booleans!
  sheet = doc.add_sheet("Vitácora de Viajes "+@car.model+' '+@car.year.to_s)
  sheet.freeze_top_left = 'A2'
  sheet.add_row(['Salida', 'Llegada', 'Origen', 'Destino', 'Distancia', 'Duración', 'Score'])
  @trips.each do |trip|
    sheet.add_row([trip.started_at.to_s, trip.finished_at.to_s, if trip.origin then trip.origin.address end, if trip.destination then trip.destination.address end, trip.distance.to_s+'km', trip.duration.to_s+'min', trip.grade])
  end
  sheet.add_row(["","","","Total", @trips.map { |trip| trip.distance }.sum.to_s+"km", @trips.map { |trip| trip.duration}.sum.to_s+"min", ""])
else
  redirect_to my_car_details_path
  flash.now[:alert] = 'Este coche no tiene viajes registrados'
end

send_file doc, :filename => "bitacora.xlsx"

end

The xlsx file doc gets created and saved to the local filesystem, but I can't find a way to send it to the browser downloads.

Answer 1

After looking at the docs for send_file and XlsxWriter, the error tells you that it is expecting a String but you are passing it an object of type XlsxWriter. You can try the path method to tell sendfile where the file is at...

send_file(doc.path, :disposition => 'attachment', :filename => "bitacora.xlsx", type: "application/xml")

You don't indicate any structure of your rails app so I'm assuming this is a controller method. You could probably do something along the lines of:

def trip_bit
@car = Car.find(params[:id])
@t = @car.trips.all
@trips = @t.order(:started_at)

if @trips then
  doc = XlsxWriter.new
  doc.quiet_booleans!
  sheet = doc.add_sheet("Vitácora de Viajes "+@car.model+' '+@car.year.to_s)
  sheet.freeze_top_left = 'A2'
  sheet.add_row(['Salida', 'Llegada', 'Origen', 'Destino', 'Distancia', 'Duración', 'Score'])
  @trips.each do |trip|
    sheet.add_row([trip.started_at.to_s, trip.finished_at.to_s, if trip.origin then trip.origin.address end, if trip.destination then trip.destination.address end, trip.distance.to_s+'km', trip.duration.to_s+'min', trip.grade])
  end
  sheet.add_row(["","","","Total", @trips.map { |trip| trip.distance }.sum.to_s+"km", @trips.map { |trip| trip.duration}.sum.to_s+"min", ""])
else
  redirect_to my_car_details_path
  flash.now[:alert] = 'Este coche no tiene viajes registrados'
end

respond_to do |format|
    format.xlsx
  end

end

The code above is a straight up guess as XlsxWriter is a binding of a C library and the documentation from a Rails perspective is really thin and pretty much useless to me. I have been using the axlsx_rails gem. It allows you to define a template and just hand that template a Rails data structure. I highly encourage you to check it out at: https://github.com/straydogstudio/axlsx_rails

It allows you to keep working in a Rails-ish way. When I want to export a spreadsheet of users to xlsx I just do this in my controller:

  def index
    @users = User.active
    respond_to do |format|
      format.html
      format.xlsx
    end
  end

Then in my views alongside my index.html.erb I have a file called index.xlsx.axlsx that looks like:

wb = xlsx_package.workbook
wb.add_worksheet(name: "Users") do |sheet|
sheet.add_row ['User', 'User Access Role']
  @users.each do |user|
    sheet.add_row [user.email, user.role.name]
  end
end

As you can see you have your logic for spreadsheet creation in the template. The way you are doing it is very hack-is and messy.