Class: Excel

Inherits:
Openoffice show all
Defined in:
lib/roo/excel.rb

Instance Method Summary collapse

Methods inherited from Openoffice

#as_letter, #first_column_as_letter, #first_row_as_letter, #last_column_as_letter, #last_row_as_letter, letter_to_number, #number_to_letter, #officeversion, #reload, #save, #set, #to_s

Constructor Details

#initialize(filename) ⇒ Excel

Returns a new instance of Excel.



6
7
8
9
 
# File 'lib/roo/excel.rb', line 6

def initialize(filename)
  @workbook = Spreadsheet::ParseExcel.parse(filename)
  @default_sheet = nil
end

Instance Method Details

#cell(row, col) ⇒ Object 



37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
# File 'lib/roo/excel.rb', line 37

def cell(row,col)
  row,col = normalize(row,col)
  worksheet = @workbook.worksheet(@default_sheet)
  skip = 0
  line = 1
  worksheet.each(skip) { |row_par|
    if line == row
      if row_par == nil
        return nil
      end
      cell = row_par.at(col-1)
      # p "celltype: "
      # p cell.type
      return nil unless cell
      case cell.type
        when :numeric then return cell.to_i
        when :text then return cell.to_s('latin1')
        when :date then return cell.date
      else  
        return cell.to_s
      end
    end
    line += 1
  }
end

#celltype(row, col) ⇒ Object 



63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/roo/excel.rb', line 63

def celltype(row,col)
  row,col = normalize(row,col)
  worksheet = @workbook.worksheet(@default_sheet)
  skip = 0
  line = 1
  worksheet.each(skip) { |row_par|
    if line == row
      cell = row_par.at(col-1)
      return nil unless cell
      case cell.type
      when :numeric then return "float"
      when :text then return "string"
      when :date then return "date"
      else  return cell.type
      end
    end
    line += 1
  }
end

#default_sheet=(n) ⇒ Object

sets the working sheet (1,2,3,..) – im Excel-Bereich muesste man wahrscheinlich intern mit Nummern arbeiten von aussen arbeite ich mit (1,2,3… intern wird Index 0,1,2,… verwendet.



30
31
32
33
34
35
 
# File 'lib/roo/excel.rb', line 30

def default_sheet=(n)
  unless n.kind_of?(Fixnum)
    fail ArgumentError.new("Number expected")
  end
  @default_sheet = n-1
end

#empty?(row, col) ⇒ Boolean

Returns:

  • (Boolean) 


121
122
123
124
125
126
127
128
129
130
131
132
133
 
# File 'lib/roo/excel.rb', line 121

def empty?(row, col)
  row,col = normalize(row,col)
  return true if row < first_row || row > last_row || col < first_column || col > last_column
  # read_cells unless @cells_read
  return true unless cell(row, col)
  # p celltype(row,col)
  #p cell(row,col)
  return true if celltype(row, col) == "string" && cell(row, col) == ""
        #when :text then return cell.to_s('latin1')
  # p celltype(row,col)
  # return true if cell(row, col) == ""
  false
end

#first_column ⇒ Object 



101
102
103
104
 
# File 'lib/roo/excel.rb', line 101

def first_column
  fr, lr, fc, lc = get_firsts_lasts
  fc
end

#first_row ⇒ Object 



111
112
113
114
 
# File 'lib/roo/excel.rb', line 111

def first_row
  fr, lr, fc, lc = get_firsts_lasts
  fr
end

#last_column ⇒ Object 



106
107
108
109
 
# File 'lib/roo/excel.rb', line 106

def last_column
  fr, lr, fc, lc = get_firsts_lasts
  lc
end

#last_row ⇒ Object 



116
117
118
119
 
# File 'lib/roo/excel.rb', line 116

def last_row
  fr, lr, fc, lc = get_firsts_lasts
  lr
end

#row(rownumber) ⇒ Object 



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
# File 'lib/roo/excel.rb', line 83

def row(rownumber)
  worksheet = @workbook.worksheet(@default_sheet)
  therow = worksheet.row(rownumber-1) 
  result = []
  therow.each {|cell|
    case cell.type
       when :numeric then result << cell.to_i
       when :text then result << cell.to_s('latin1')
       when :date then result << cell.date
    else
      return result << cell.to_s
    end

    #result << cell.value
  }
  return result
end

#sheets ⇒ Object

TODO: waiting for ich glaube, parseexcel kann keine namen der sheets ???



13
14
15
16
17
18
19
20
21
22
 
# File 'lib/roo/excel.rb', line 13

def sheets
  if DateTime.now < Date.new(2007,6,10)
    return  ["Tabelle1", "Name of Sheet 2", "Sheet3"]
  else
    #worksheet = @workbook.worksheet(0)
    #  p @workbook
    # p @workbook.worksheet(0)
    ["aaa","bbb","ccc"]
  end
end