Class: RobustExcelOle::Sheet

Inherits:
RangeOwners show all
Defined in:
lib/robust_excel_ole/sheet.rb

Instance Attribute Summary collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods inherited from RangeOwners

#name_object, #nameval, #rangeval, #set_nameval, #set_rangeval

Methods inherited from REOCommon

#excel, #own_methods, puts_hash, tr1, trace

Constructor Details

#initialize(win32_worksheet) ⇒ Sheet

Returns a new instance of Sheet.



10
11
12
13
14
15
16
17
18
19
20
21
22
 
# File 'lib/robust_excel_ole/sheet.rb', line 10

def initialize(win32_worksheet)
  @ole_worksheet = win32_worksheet
  if @ole_worksheet.ProtectContents
    @ole_worksheet.Unprotect
    @end_row = last_row
    @end_column = last_column
    @ole_worksheet.Protect
  else
    @end_row = last_row
    @end_column = last_column
  end
  @workbook = book_class.new(self.Parent)
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object (private)

:nodoc: #



212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
# File 'lib/robust_excel_ole/sheet.rb', line 212

def method_missing(name, *args)    # :nodoc: #
  if name.to_s[0,1] =~ /[A-Z]/ 
    begin
      @ole_worksheet.send(name, *args)
    rescue WIN32OLERuntimeError => msg
      if msg.message =~ /unknown property or method/
        raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
      else 
        raise msg
      end
    end
  else  
    super 
  end
end

Instance Attribute Details

#ole_worksheet ⇒ Object (readonly)

Returns the value of attribute ole_worksheet



7
8
9
 
# File 'lib/robust_excel_ole/sheet.rb', line 7

def ole_worksheet
  @ole_worksheet
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook



8
9
10
 
# File 'lib/robust_excel_ole/sheet.rb', line 8

def workbook
  @workbook
end

Class Method Details

.book_class ⇒ Object

:nodoc: #



189
190
191
192
193
194
195
196
 
# File 'lib/robust_excel_ole/sheet.rb', line 189

def self.book_class   
  @book_class ||= begin
    module_name = self.parent_name
    "#{module_name}::Book".constantize
  rescue NameError => e
    book
  end
end

Instance Method Details

#[](p1, p2 = :__not_provided) ⇒ Object

returns cell, if row and column are given



48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
 
# File 'lib/robust_excel_ole/sheet.rb', line 48

def [] p1, p2 = :__not_provided
  if p2 != :__not_provided  
    x, y = p1, p2
    xy = "#{x}_#{y}"
    @cells = { }
    begin
      @cells[xy] = RobustExcelOle::Cell.new(@ole_worksheet.Cells.Item(x, y))
    rescue
      raise RangeNotEvaluatable, "cannot read cell (#{x.inspect},#{y.inspect})"
    end
  else
    name = p1
    begin
      nameval(name) 
    rescue REOError
      rangeval(name)
    end
  end
end

#[]=(p1, p2, p3 = :__not_provided) ⇒ Object

sets the value of a cell, if row and column are given sets the value of a range if its name is given



70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 
# File 'lib/robust_excel_ole/sheet.rb', line 70

def []= (p1, p2, p3 = :__not_provided)
  if p3 != :__not_provided
    x, y, value = p1, p2, p3
    set_cellval(x,y,value)       
  else
    name, value = p1, p2
    begin
      set_nameval(name, value, :color => 42) # aqua-marin, 4-green
    rescue REOError
      begin
        workbook.set_nameval(name, value)
      rescue REOError
        set_rangeval(name, value)
      end
    end
  end
end

#book_class ⇒ Object

:nodoc: #



198
199
200
 
# File 'lib/robust_excel_ole/sheet.rb', line 198

def book_class        
  self.class.book_class
end

#cellval(x, y) ⇒ Object

returns the value of a cell, if row and column are given



89
90
91
92
93
94
95
96
97
98
 
# File 'lib/robust_excel_ole/sheet.rb', line 89

def cellval(x,y)
  xy = "#{x}_#{y}"
  @cells = { }
  begin
    @cells[xy] = RobustExcelOle::Cell.new(@ole_worksheet.Cells.Item(x, y))
    @cells[xy].Value
  rescue
    raise RangeNotEvaluatable, "cannot read cell (#{p1.inspect},#{p2.inspect})"
  end
end

#col_range(col, range = nil) ⇒ Object 



183
184
185
186
 
# File 'lib/robust_excel_ole/sheet.rb', line 183

def col_range(col, range = nil)
  range ||= 1..@end_row
  RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(range.min , col ), @ole_worksheet.Cells(range.max , col )))
end

#each ⇒ Object 



132
133
134
135
136
137
138
 
# File 'lib/robust_excel_ole/sheet.rb', line 132

def each
  each_row do |row_range|
    row_range.each do |cell|
      yield cell
    end
  end
end

#each_column(offset = 0) ⇒ Object 



164
165
166
167
168
169
170
 
# File 'lib/robust_excel_ole/sheet.rb', line 164

def each_column(offset = 0)
  offset += 1
  1.upto(@end_column) do |column|
    next if column < offset
    yield RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(1, column), @ole_worksheet.Cells(@end_row, column)))
  end
end

#each_column_with_index(offset = 0) ⇒ Object 



172
173
174
175
176
 
# File 'lib/robust_excel_ole/sheet.rb', line 172

def each_column_with_index(offset = 0)
  each_column(offset) do |column_range|
    yield RobustExcelOle::Range.new(column_range), (column_range.column - 1 - offset)
  end
end

#each_row(offset = 0) ⇒ Object 



150
151
152
153
154
155
156
 
# File 'lib/robust_excel_ole/sheet.rb', line 150

def each_row(offset = 0)
  offset += 1
  1.upto(@end_row) do |row|
    next if row < offset
    yield RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(row, 1), @ole_worksheet.Cells(row, @end_column)))
  end
end

#each_row_with_index(offset = 0) ⇒ Object 



158
159
160
161
162
 
# File 'lib/robust_excel_ole/sheet.rb', line 158

def each_row_with_index(offset = 0)
  each_row(offset) do |row_range|
    yield RobustExcelOle::Range.new(row_range), (row_range.row - 1 - offset)
  end
end

#each_with_index(offset = 0) ⇒ Object 



140
141
142
143
144
145
146
147
148
 
# File 'lib/robust_excel_ole/sheet.rb', line 140

def each_with_index(offset = 0)
  i = offset
  each_row do |row_range|
    row_range.each do |cell|          
      yield cell, i
      i+=1
    end
  end
end

#inspect ⇒ Object

:nodoc: #



206
207
208
 
# File 'lib/robust_excel_ole/sheet.rb', line 206

def inspect    
  self.to_s
end

#name ⇒ Object

returns name of the sheet



25
26
27
28
29
30
31
 
# File 'lib/robust_excel_ole/sheet.rb', line 25

def name
  begin
    @ole_worksheet.Name
  rescue
    raise SheetREOError, "name #{name.inspect} could not be determined"
  end
end

#name=(new_name) ⇒ Object

name the sheet

Parameters:

  • new_name (String) —

    the new name of the sheet



35
36
37
38
39
40
41
42
43
44
45
 
# File 'lib/robust_excel_ole/sheet.rb', line 35

def name= (new_name)
  begin
    @ole_worksheet.Name = new_name
  rescue WIN32OLERuntimeError => msg
    if msg.message =~ /800A03EC/ 
      raise NameAlreadyExists, "sheet name #{new_name.inspect} already exists"
    else
      raise UnexpectedREOError, "unexpected WIN32OLERuntimeError: #{msg.message}"
    end
  end
end

#row_range(row, range = nil) ⇒ Object 



178
179
180
181
 
# File 'lib/robust_excel_ole/sheet.rb', line 178

def row_range(row, range = nil)
  range ||= 1..@end_column
  RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(row , range.min ), @ole_worksheet.Cells(row , range.max )))
end

#set_cellval(x, y, value, opts = {:color => 0}) ⇒ Object

sets the value of a cell, if row, column and color of the cell are given



101
102
103
104
105
106
107
108
109
110
 
# File 'lib/robust_excel_ole/sheet.rb', line 101

def set_cellval(x,y,value, opts = {:color => 0})
  begin
    cell = @ole_worksheet.Cells.Item(x, y)        
    cell.Interior.ColorIndex = opts[:color] # 42 - aqua-marin, 4-green
    @workbook.modified_cells << cell if @workbook
    cell.Value = value
  rescue WIN32OLERuntimeError
    raise RangeNotEvaluatable, "cannot assign value #{value.inspect} to cell (#{y.inspect},#{x.inspect})"
  end
end

#set_name(name, row, column) ⇒ Object

assigns a name to a range (a cell) given by an address

Parameters:

  • name (String) —

    the range name

  • row (Fixnum) —

    the row

  • column (Fixnum) —

    the column



116
117
118
119
120
121
122
123
124
125
126
127
128
129
 
# File 'lib/robust_excel_ole/sheet.rb', line 116

def set_name(name,row,column)
  begin
    old_name = self[row,column].Name.Name rescue nil
    if old_name
      self[row,column].Name.Name = name
    else
      address = "Z" + row.to_s + "S" + column.to_s 
      self.Names.Add("Name" => name, "RefersToR1C1" => "=" + address)
    end
  rescue WIN32OLERuntimeError => msg
    #trace "WIN32OLERuntimeError: #{msg.message}"
    raise RangeNotEvaluatable, "cannot add name #{name.inspect} to cell with row #{row.inspect} and column #{column.inspect}"
  end
end

#to_s ⇒ Object

:nodoc: #



202
203
204
 
# File 'lib/robust_excel_ole/sheet.rb', line 202

def to_s    
  "#<Sheet: " + "#{"not alive " unless @workbook.alive?}" + "#{name}" + " #{File.basename(@workbook.stored_filename)} >"
end