ruby导出含格式和下拉列表（支持超长下拉）的excel文件

2019独角兽企业重金招聘Python工程师标准>>>

require 'write_xlsx'

def test_excel
  path = './test.xlsx'
# 按列来分
  data = [
      {t: '名称'},
      {t: '类型', l: %W{单品 套餐}},
      {t: '编号'},
      {t: '库存', v: {validate: 'integer', criteria: '>', value: -1}},
      {t: '原价', v: {validate: 'decimal', criteria: '>', value: 0}},
      {t: '售价', v: {validate: 'decimal', criteria: '>', value: 0}},
      {t: '上市时间', v: {validate: 'date', criteria: '>', value: '1900-01-01T'}},
      {t: '批文号'},
      {t: '商品简介'},
      {t: '商品详情'},
  ]
# 创建excel及表
  data_to_excel(path, data)
end

# 创建一个选择项的列
# d_sheet:创建列的表，h_sheet:隐藏的表，col_num:列序号
# h_col_num:选项列表在隐藏表中的列序号，title:列标题，select_list:选择项列表
# {d_sheet:,h_sheet:,col_num:,h_col_num:,title:,select_list:}
# return 表的下一个空列的序号，隐藏表的下一列空列的序号
def create_selection_col(*arges)
  arge = arges.first
  d_sheet, h_sheet, col_num, h_col_num, title, select_list = arge[:d_sheet], arge[:h_sheet], arge[:col_num], arge[:h_col_num], arge[:title], arge[:select_list]
  h_col = num_to_col(h_col_num)
  h_sheet.write_col("#{h_col}1", select_list)
  create_col_title(d_sheet, col_num, title)
  add_validation_to_col(d_sheet, col_num, {
      validate: 'list',
      ignore_blank: 0,
      source: "=#{h_sheet.name}!$#{h_col}$1:$#{h_col}$#{select_list.length}"
  })
  [col_num + 1, h_col_num + 1]
end

# 创建列标题
# d_sheet:表对象   col_num:列index   title:列标题
def create_col_title(d_sheet, col_num, title)
  col = num_to_col(col_num)
  d_sheet.write("#{col}1", title)
  d_sheet.data_validation(0, col_num, {validate: 'list', source: [title], dropdown: 0, ignore_blank: 0})
end

# 列字母转列序号
def col_to_num(col)
  col =~ /([A-Z]{1,3})/
  chars = col.split(//)
  expn, num = 0, 0
  chars.reverse.each do |char|
    num += (char.ord - 'A'.ord + 1) * (26 ** expn)
    expn += 1
  end
  # 在上边会多算一格（因为必须从1开始计算，否则AAA算出来是0）
  num - 1
end

# 列序号转列字母
def num_to_col(col)
  ColName.instance.col_str(col)
end

# 将数据转化成excel
def data_to_excel(path, data)
  raise('生成excel的路径不能为空') unless path
  # 创建excel及表
  workbook  = WriteXLSX.new(path)
  wt1 = workbook.add_worksheet
  total_col = num_to_col(data.length)
  wt1.set_column("A:#{total_col}", 15)
  hide_sheet = workbook.add_worksheet
  hide_sheet.hide
  # 表内容col_num列序号，h_col_num隐藏列序号
  col_num, h_col_num = 0, 0
  # 生成模板
  data.each do |col_json|
    if col_json[:l]
      col_num, h_col_num = create_selection_col(d_sheet: wt1, h_sheet: hide_sheet,col_num: col_num,h_col_num: h_col_num,title: col_json[:t],select_list: col_json[:l])
    else
      create_col_title(wt1, col_num, col_json[:t])
      add_validation_to_col(wt1, col_num, col_json[:v]) if col_json[:v]
      col_num += 1
    end
  end
  workbook.close
end

# 给某列加条件限制
def add_validation_to_col(d_sheet, col_num, validation)
  (1..500).each do |j|
    d_sheet.data_validation(j, col_num, validation)
  end
end


test_excel

转载于:https://my.oschina.net/lovelyBoy/blog/834703