Dinesh Saini Dinesh Saini - 7 months ago 148
Ruby Question

Rails - Export records to downloadable excel file using axlsx gem (Keep MVC)

I have installed the axlsx gem successfully from https://github.com/randym/axlsx
Here is my controller code that I used to create an excel file through this gem.

But nothing happen with this code instead it shows me an error uninitialized mime

class Coaches::PaymentsController < ApplicationController

before_filter :authenticate_coach!

# List all the payments
def index
if !params[:sort].blank?
@payments = Payment.includes(:member).paginate(:page => params[:page], :order => sort_column + " " + sort_direction)
else
@payments = Payment.includes(:member).paginate(:page => params[:page], :order=>'id desc')
end
respond_to do |format|
format.html
# Change format to xlsx
format.xlsx
format.json { render json: @payments }
end
end
end


Secondly,I try with this code:

wb = xlsx_package.workbook
wb.add_worksheet(name: "Buttons") do |sheet|
@buttons.each do |button|
sheet.add_row [button.name, button.category, button.price]
end
end


But unfortunately, it does not work. Can anyone tell me only hint not a solution to do my task?

I have tried third times as per suggestion:

def index
if !params[:sort].blank?
@payments = Payment.includes(:member).paginate(:page => params[:page], :order => sort_column + " " + sort_direction)
else
@payments = Payment.includes(:member).paginate(:page => params[:page], :order=>'id desc')
end
respond_to do |format|
format.xlsx do
p = Axlsx::Package.new
wb = p.workbook
wb.add_worksheet(name: "Your worksheet name") do |sheet|
sheet.add_row ["First Column", "Second", "Third"]
sheet.add_row [1, 2, 3]
sheet.add_row [' preserving whitespace']
end
send_data p.to_stream.read, type: "application/xlsx", filename: "filename.xlsx"
end
end
end


It thrown me http 406 error

Answer

Try using axlsx_rails Gem with template. In my case i used below configuration to make it work. and also a link with extension .xlsx to render it in xlsx format.

GEM FILE

gem 'axlsx', '~> 2.0'
gem "axlsx_rails"

controller file- payments_controller.rb

def download
    @payments = Payment.all
    respond_to do |format| 
       format.xlsx {render xlsx: 'download',filename: "payments.xlsx"}
    end
end

View file- download.xlsx.axlsx

wb = xlsx_package.workbook
wb.add_worksheet(name: "Payments") do |sheet|
    sheet.add_row ["ID", "Notes","Amount($)","Deposit Date"]
    @payments.each do |payment|
        sheet.add_row [payment.id, payment.notes,payment.amount,payment.date_deposite]
    end
end
Comments