Ruby-on-rails Rails - 使用 axlsx gem (Keep MVC) 将记录导出到可下载的 excel 文件

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

提问by Dinesh Saini

I have installed the axlsx gem successfully from https://github.com/randym/axlsxHere 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 错误

回答by Ammy T

Try using axlsx_railsGem 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_railsGem 与模板一起使用。就我而言，我使用以下配置使其工作。还有一个扩展名为 .xlsx 的链接，以 xlsx 格式呈现它。

GEM FILE

宝石档案

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

controller file- payments_controller.rb

控制器文件-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

回答by Daniel

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

回答by user3735287

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，对于跟踪，您可以看到启动 rails 的终端。

回答by Avtar Singh

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

回答by murat

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 installproblem has been solved. Thanks.

但是这些宝石出现了错误。当我从 Gemfile.lock 中删除 'rubyzip' gem 时，bundle install问题就解决了。谢谢。