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

原创
2017/02/09 15:04
阅读数 1.7K
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
展开阅读全文
打赏
0
0 收藏
分享
加载中
Mediv博主

引用来自“min1124”的评论

itdeMac-mini:~ it$ git clone https://github.com/cxn03651/writeexcel.git
Cloning into 'writeexcel'...
remote: Counting objects: 5405, done.
remote: Total 5405 (delta 0), reused 0 (delta 0), pack-reused 5405
Receiving objects: 100% (5405/5405), 2.80 MiB | 343.00 KiB/s, done.
Resolving deltas: 100% (3418/3418), done.
报错如下:
LoadError (cannot load such file -- writeexcel):
app/controllers/deliveryorder_controller.rb:6:in `index'
想问下这是什么原因啊,感谢!
在rails项目的Gemfile里加 gem 'write_xlsx' gem 'writeexcel' ,然后bundle install。别clone源码。
2017/08/10 12:21
回复
举报
该评论暂时无法显示,详情咨询 QQ 群:912889742
更多评论
打赏
2 评论
0 收藏
0
分享
返回顶部
顶部