Rails - 使用axlsx gem将记录导出为可下载的excel文件（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.

我已经从https://github.com/randym/axlsx成功安装了axlsx gem这是我用来通过这个gem创建excel文件的控制器代码。

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

但是这个代码没有任何反应，而是向我展示了一个未初始化的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

它抛出了我的http 406错误

5 个解决方案

#1

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.

尝试使用axlsx_rails Gem与模板。在我的情况下，我使用下面的配置使其工作。以及扩展名.xlsx的链接，以xlsx格式呈现它。

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

查看文件 - 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

#2

To prevent the uninitialized mime type error add the following file:

要防止未初始化的mime类型错误，请添加以下文件：

# config/initializers/mime_types.rb

Mime::Type.register "application/xlsx", :xlsx

And here is a short example of what to do to download the xlsx file:

以下是如何下载xlsx文件的简短示例：

format.xlsx do
  p = Axlsx::Package.new
  wb = p.workbook
  wb.add_worksheet(name: "Your worksheet name") do |sheet|
    # Add your stuff
  end
  send_data p.to_stream.read, type: "application/xlsx", filename: "filename.xlsx"
end

#3

Please set render false to HTML and avoid JSON instead use XLS and for trace, you can see terminal where you have started the rails.

请将render false设置为HTML并避免使用JSON而不是使用XLS，并且对于trace，您可以看到启动rails的终端。

#4

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

#5

I use gems.

我用宝石。

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

But these gems occured a error. When I remove 'rubyzip' gem from Gemfile.lock , then bundle install problem has been solved. Thanks.

但这些宝石出现了错误。当我从Gemfile.lock中删除'rubyzip'宝石时，捆绑安装问题已经解决。谢谢。

#1