Class: Excel

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

Instance Method Summary collapse

Methods inherited from Openoffice

#create_openoffice, #first_column_as_letter, #formula, #formula?, #formulas, #last_column_as_letter, #officeversion, #reload, #remove_tmp, #save, #set, #solve, #to_csv, #to_s, #to_yaml

Constructor Details

#initialize(filename, packed = nil) ⇒ Excel

Returns a new instance of Excel.



7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
# File 'lib/roo/excel.rb', line 7

def initialize(filename, packed = nil)
  @tmpdir = "oo_"+$$.to_s
  unless File.exists?(@tmpdir)
    FileUtils::mkdir(@tmpdir)
  end
  filename = open_from_uri(filename) if filename[0,7] == "http://"
  filename = unzip(filename) if packed and packed == :zip
  if filename[-4..-1] != ".xls"
    warn "are you sure, this is an excel file?"
  end
  @filename = filename
  @workbook = Spreadsheet::ParseExcel.parse(filename)
  @default_sheet = nil
  # no need to set default_sheet if there is only one sheet in the document
  if self.sheets.size == 1
    @default_sheet = self.sheets.first
  end
  @first_row = @last_row = @first_column = @last_column = nil
end

Instance Method Details

#cell(row, col) ⇒ Object

returns the content of a cell. The upper left corner is (1,1) or (‘A’,1)



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

def cell(row,col)
  row,col = normalize(row,col)
  worksheet = @workbook.worksheet(sheet_no(@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)
      return nil unless cell
      case cell.type
        when :numeric then return cell.to_f
        when :text then return cell.to_s('utf-8')
        when :date then return cell.date
      else  
        return cell.to_s('utf-8')
      end
    end
    line += 1
  }
end

#celltype(row, col) ⇒ Object

returns the type of a cell: “float”, “string”, “date”



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

def celltype(row,col)
  row,col = normalize(row,col)
  default_sheet_check
  worksheet = @workbook.worksheet(sheet_no(@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.to_sym
      end
    end
    line += 1
  }
end

#default_sheet=(n) ⇒ Object

sets the working sheet (1,2,3,..)



43
44
45
46
47
48
49
50
51
52
53
54
55
 
# File 'lib/roo/excel.rb', line 43

def default_sheet=(n)
  if n.kind_of?(Fixnum)
    @default_sheet = n #-1
  elsif n.kind_of?(String)
    raise RangeError if ! self.sheets.include?(n)
    # parseexcel supports now the name of a sheet
    @default_sheet = n
  else
    raise TypeError, "what are you trying to set as default sheet?"
  end
  @first_row = @last_row = @first_column = @last_column = nil
  @cells_read = false
end

#empty?(row, col) ⇒ Boolean

true if a cell is empty

Returns:

  • (Boolean) 


151
152
153
154
155
156
157
 
# File 'lib/roo/excel.rb', line 151

def empty?(row, col)
  row,col = normalize(row,col)
  return true if row < first_row || row > last_row || col < first_column || col > last_column
  return true unless cell(row, col)
  return true if celltype(row, col) == "string" && cell(row, col) == ""
  false
end

#first_column ⇒ Object

returns the first non empty column



123
124
125
126
127
 
# File 'lib/roo/excel.rb', line 123

def first_column
  return @first_column if @first_column
  fr, lr, fc, lc = get_firsts_lasts
  fc
end

#first_row ⇒ Object

returns the first non empty row



137
138
139
140
141
 
# File 'lib/roo/excel.rb', line 137

def first_row
  return @first_row if @first_row
  fr, lr, fc, lc = get_firsts_lasts
  fr
end

#last_column ⇒ Object

returns the last non empty column



130
131
132
133
134
 
# File 'lib/roo/excel.rb', line 130

def last_column
  return @last_column if @last_column
  fr, lr, fc, lc = get_firsts_lasts
  lc
end

#last_row ⇒ Object

returns the last non empty row



144
145
146
147
148
 
# File 'lib/roo/excel.rb', line 144

def last_row
  return @last_row if @last_row
  fr, lr, fc, lc = get_firsts_lasts
  lr
end

#row(rownumber) ⇒ Object

return this row a an array off cells



105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
 
# File 'lib/roo/excel.rb', line 105

def row(rownumber)
  default_sheet_check
  worksheet = @workbook.worksheet(sheet_no(@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('utf-8')
       when :date then result << cell.date
    else
      return result << cell.to_s('utf-8')
    end
  }
  return result
end

#sheets ⇒ Object 



27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
# File 'lib/roo/excel.rb', line 27

def sheets
  #if DateTime.now < Date.new(2007,6,10)
  #  return  ["Tabelle1", "Name of Sheet 2", "Sheet3"]
  #else
    result = []
    0.upto(@workbook.sheet_count - 1) do |i|
      # TODO: is there a better way to do conversion?
      result << Iconv.new('utf-8','unicode').iconv(
                    @workbook.worksheet(i).name
                         )
    end
    return result
  #end
end