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

export-to-excel, ruby, ruby-on-rails

Solution

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

Problem

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

Original source