POSTS

Rails集成axlsx实现导出excel功能

March 1, 2019

首先在Gemfile文件中添加依赖:

gem 'rubyzip', '>= 1.2.1'
gem 'axlsx', git: 'https://github.com/randym/axlsx.git', ref: 'c8ac844'
gem 'axlsx_rails'

然后下载依赖，执行命令：

bundle install

在controller层添加导出接口，下面是实际业务中的一段代码，从数据库中获取数据：

  #导出PCI导航栏各区PCI指数表
  # pciIndex/pciIndexRanking/:barTypeId/export.xlsx?year=2018
  def export
    barTypeId = params[:barTypeId]
    year = params[:year] || 2018
    @lists = RegionIndexSummary.where(navigation_bar_type_id:barTypeId,year: year).order('pci_index desc')
    respond_to do |format|
      format.xlsx {
          response.headers['Content-Disposition'] = 'attachment; filename="PCI指数排名表.xlsx"'
      }
    end
  end

在view层新建export.xlsx.axlsx模板，并实现具体导出功能：

wb = xlsx_package.workbook
wb.styles do |s|
    style_cell = s.add_style :fg_color => "0F0F0F", :sz => 14, :alignment => { :horizontal=> :center,:vertical => :center  }
    wb.add_worksheet(name: "Sheet1") do |sheet|
      sheet.column_widths 5, 20, 20, 20
      sheet.add_row ['排行', '城区名称', 'PCI指数','公共卫生','公共秩序','公共交往','公共观赏','公共参与','年份'], :style => style_cell, :height => 30
      @lists.each do |obj|
        sheet.add_row [obj.ranking, obj.region_name, obj.pci_index,obj.public_health,obj.public_order,obj.public_social,obj.public_viewing,obj.public_participation,obj.year], :style => style_cell, :height => 30
      end
  end
end

上面代码中加入了一些excel的一些样式。如果想看更多的样式传送门

在conf/initializers/mime_types.rb文件中添加如下：

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

最后启动服务，请求接口地址以xlsx结尾，这里是http://localhost:3000/pciIndex/pciIndexRanking/1/export.xlsx

ruby