module SEquipsHelper #设备台账,从excel文件读取信息 require 'roo' #require 'roo-xls' #读取excel文件 # SEquipsHelper.read_excel def self.read_excel(path) #path = 'http://172.168.10.7:6060/file/102.xlsx' #xlsx = Roo::Spreadsheet.open('/Users/h/Documents/rubyProject/新版本文案.xls',extension: :xls) # if type == 'xlsx' # end xlsx = Roo::Spreadsheet.open(path, extension: :xlsx) # if type == 'xls' #exce中日期类型 不能自动转化 # xlsx = Roo::Spreadsheet.open(path, extension: :xls) # end #sheets = xlsx.sheets #返回所有的工作表名 # cell = xlsx.sheet(0).cell('C',1) #取第一个工作表的C1 # cell2 = xlsx.sheet(0).cell('D',2) cols = ['B','C','D','E','F','G','H','I','J','K','L','M','N'] ods = xlsx.sheet(0) #定义默认第一个表 #n1 = ods.first_column #第一列数 #n1 = ods.last_column #最后一列数 #n1 = ods.first_row #第一行数 n2 = ods.last_row #最后一行数 rows = n2 -2 Rails.logger.info "-----------总行数---#{n2}-----" Rails.logger.info "-----------有效数据行数---#{rows}-----" @excel_datas =[] #存所有行的数据 rows.times do |r| #行数 row_data=[] #存每行的数据 cols.each do |c| c1 = xlsx.sheet(0).cell(c, r+3) Rails.logger.info "---------第#{r+3}行,第#{c}列---#{c1}-----" row_data.push(c1) end @excel_datas.push(row_data) end @excel_datas.each do |e| #将区域转为 id area_name = e[12] area = SArea.find_by(:name => area_name) s_area_id = area.present? ? area.id : '' e[12] = s_area_id e.push(Time.now) #添加 时间 e.push(Time.now) e.push(1) # 设备状态 equip_status 1 启用 end return @excel_datas end #保存excel到数据库 # SEquipsHelper.save_excel def self.save_excel(excel_datas) Rails.logger.info "---save_data----#{excel_datas}---" # SEquip.bulk_insert(:bit_code, # :equip_code, # :equip_name, # :equip_location, # :equip_norm, # :equip_nature, # :equip_material, # :equip_num, # :apper_code, # :apper_time, # :factory, # :equip_note, # :s_area_id, # :created_at, # :updated_at, # :equip_status, # :s_region_code_id, # :region_name, # update_duplicates: true # ) do |worker| # excel_datas.each do |attrs| # Rails.logger.info "导入台账信息----#{attrs}" # worker.add(attrs) # end # end destination_columns = [:bit_code, :equip_code, :equip_name, :equip_location, :equip_norm, :equip_nature, :equip_material, :equip_num, :apper_code, :apper_time, :factory, :equip_note, :s_area_id, :created_at, :updated_at, :equip_status, :s_region_code_id, :region_name] SEquip.bulk_insert(*destination_columns, update_duplicates: true) do |worker| excel_datas.each do |attrs| Rails.logger.info "导入台账信息----#{attrs}" worker.add(attrs) end end end # SEquipsHelper.save # def self.save # hash = [ # {:bit_code => 'L-FJ0101', :equip_code => 'GJSW12-08-005',:equip_name => '龙门吊(东)' }, # {:bit_code => 'L-FJ0102', :equip_code => 'GJSW12-08-005',:equip_name => '附防爆电机' } # ] # SEquip.bulk_insert(update_duplicates: true) do |worker| # hash.each do |attrs| # Rails.logger.info "导入台账信息----#{attrs}" # worker.add(attrs) # end # end # end end
调用:
#从excel导入台账信息 # post s_equips/excel_import def excel_import s_region_code_id = params[:s_region_code_id] excel = params[:excel] filename = excel.original_filename Rails.logger.info "----------filename------#{filename}-------" names = filename.split(".") file_type = names[1] if file_type == 'xlsx' region_code = SRegionCode.find_by(:id => s_region_code_id) region_name = region_code.present? ? region_code.region_name : '' @excel_datas = SEquipsHelper.read_excel(excel) #从excel读取信息 @excel_datas.each do |e| e.push(s_region_code_id.to_i) e.push(region_name) end SEquipsHelper.save_excel(@excel_datas) #保存读取到的信息 render json: {status: 200} # else # render json: {status: '请选择.xlsx文件'} end end