Module: ExcelHelper

Defined in:
lib/tabbyx/helpers/excel_helper.rb

Class Method Summary collapse

Class Method Details

.get_value_by_location(filename, worksheet, row, column) ⇒ Object

example:

ExcelHelper.get_value_by_location("api_testcases.xlsx",0,1,1)



29
30
31
32
33
34
35
36
 
# File 'lib/tabbyx/helpers/excel_helper.rb', line 29

def self.get_value_by_location(filename,worksheet,row,column)
  file = Base.file_exists?(filename)
  excel = RubyXL::Parser.parse(file)
  sheet = excel.worksheets[worksheet]
  value = ''
  value = sheet[row][column].value unless sheet[row][column].nil? unless sheet[row].nil?
  value
end

.read_from_excel(filename, worksheet) ⇒ Object

read content of specified sheet by row and store in an array example:

ExcelHelper.read_from_excel("api_testcases.xlsx",0) # by sheet number
ExcelHelper.read_from_excel("api_testcases.xlsx",'Sheet 1') # by sheet name



10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
# File 'lib/tabbyx/helpers/excel_helper.rb', line 10

def self.read_from_excel(filename,worksheet)
  content = []
  file = Base.file_exists?(filename)
  excel = RubyXL::Parser.parse(file)
  sheet = excel.worksheets[worksheet]

  sheet.each do |row|
    row_array = []
    row && row.cells.each { |cell|
      val = cell && cell.value
      row_array.push val
    }
    content.push row_array
  end
  content
end

.write_dictionary_to_excel(dic, filename, worksheet) ⇒ Object

write an array, of which contains array items into excel file. example:

dic = [["test", "test2", "test3"],["test", "test2", "test3"],["test", "test2", "test3"]]
 ExcelHelper.write_dictionary_to_excel(dic,"test1.xlsx",0)



70
71
72
73
74
75
76
 
# File 'lib/tabbyx/helpers/excel_helper.rb', line 70

def self.write_dictionary_to_excel(dic,filename,worksheet)
  dic.each_with_index do |array, row|
    array.each_with_index do |item,column|
      write_into_worksheet(filename,worksheet,row,column,item)
    end
  end
end

.write_into_worksheet(filename, worksheet, row, column, value) ⇒ Object

if file exists, it writes value to specified cell. if file does not exist,create an excel and put in outputs folder. example:

ExcelHelper.write_into_worksheet("test.xlsx",0,5,5,"Hi Excel")



41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
# File 'lib/tabbyx/helpers/excel_helper.rb', line 41

def self.write_into_worksheet(filename,worksheet,row,column,value)
  if Base.file_exists?(filename)
    file = Base.file(filename)
    raise("TABBYX: 指定的文件不是excel,请检查") unless File.extname(file) == ".xlsx"
    excel = RubyXL::Parser.parse(file)
  elsif Base.file_exists?(filename,"../outputs/")
    file = Base.file(filename,"../outputs/")
    excel = RubyXL::Parser.parse(file)
  else
    file = Base.file(filename,"../outputs/")
    puts "TABBYX: 创建excel文件"+filename
    excel = RubyXL::Workbook.new
  end

  sheet = excel.worksheets[worksheet]
  if sheet[row].nil?
    sheet.add_cell(row,column,value)
  elsif sheet[row][column].nil?
    sheet.add_cell(row,column,value)
  else
    sheet[row][column].change_contents(value)
  end
  excel.save(file)
end