Class: RobustExcelOle::Worksheet

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

Overview

worksheet: see github.com/Thomas008/robust_excel_ole/blob/master/lib/robust_excel_ole/worksheet.rb

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from RangeOwners

#add_name, #delete_name, #name2range, #namevalue, #namevalue_glob, #range, #rename_range, #set_name, #set_namevalue, #set_namevalue_glob

Methods inherited from VbaObjects

#to_reo

Constructor Details

#initialize(win32_worksheet) ⇒ Worksheet

Returns a new instance of Worksheet.



17
18
19
20
21
22
23
24
25
26
27
28
 
# File 'lib/robust_excel_ole/worksheet.rb', line 17

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
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

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



254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
 
# File 'lib/robust_excel_ole/worksheet.rb', line 254

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

Instance Attribute Details

#ole_worksheet ⇒ Object (readonly)

Returns the value of attribute ole_worksheet



14
15
16
 
# File 'lib/robust_excel_ole/worksheet.rb', line 14

def ole_worksheet
  @ole_worksheet
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook



15
16
17
 
# File 'lib/robust_excel_ole/worksheet.rb', line 15

def workbook
  @workbook
end

Instance Method Details

#==(other_worksheet) ⇒ Object 



221
222
223
224
225
 
# File 'lib/robust_excel_ole/worksheet.rb', line 221

def == other_worksheet
  other_worksheet.is_a?(Worksheet) && 
    self.workbook == other_worksheet.workbook &&
    self.Name == other_worksheet.Name
end

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

a cell given the defined name or row and column



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

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), @worksheet)
    rescue
      raise RangeNotEvaluatable, "cannot read cell (#{x.inspect},#{y.inspect})"
    end
  else
    name = p1
    begin
      namevalue_glob(name)
    rescue REOError
      namevalue(name)
    end
  end
end

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

sets the value of a cell



90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
# File 'lib/robust_excel_ole/worksheet.rb', line 90

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_namevalue_glob(name, value, :color => 42)
    rescue REOError
      begin
        workbook.set_namevalue_glob(name, value)
      rescue REOError
        set_namevalue(name, value)
      end
    end
  end
end

#cellval(x, y) ⇒ Object

value of a cell, if row and column are given



111
112
113
114
115
116
117
 
# File 'lib/robust_excel_ole/worksheet.rb', line 111

def cellval(x,y)
  begin
    @ole_worksheet.Cells.Item(x, y).Value
  rescue
    raise RangeNotEvaluatable, "cannot read cell (#{x.inspect},#{y.inspect})"
  end
end

#col_range(col, integer_range = nil) ⇒ Object 



216
217
218
219
 
# File 'lib/robust_excel_ole/worksheet.rb', line 216

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

#each ⇒ Object 



147
148
149
150
151
152
153
 
# File 'lib/robust_excel_ole/worksheet.rb', line 147

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

#each_column(offset = 0) ⇒ Object 



197
198
199
200
201
202
203
 
# File 'lib/robust_excel_ole/worksheet.rb', line 197

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)), self)
  end
end

#each_column_with_index(offset = 0) ⇒ Object 



205
206
207
208
209
 
# File 'lib/robust_excel_ole/worksheet.rb', line 205

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

#each_row(offset = 0) ⇒ Object 



183
184
185
186
187
188
189
 
# File 'lib/robust_excel_ole/worksheet.rb', line 183

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)), self)
  end
end

#each_row_with_index(offset = 0) ⇒ Object 



191
192
193
194
195
 
# File 'lib/robust_excel_ole/worksheet.rb', line 191

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

#each_rowvalue ⇒ Object 



165
166
167
168
169
 
# File 'lib/robust_excel_ole/worksheet.rb', line 165

def each_rowvalue
  @ole_worksheet.UsedRange.Value.each do |row_values|
    yield row_values
  end
end

#each_rowvalue_with_index(offset = 0) ⇒ Object 



175
176
177
178
179
180
181
 
# File 'lib/robust_excel_ole/worksheet.rb', line 175

def each_rowvalue_with_index(offset = 0)
  i = offset
  @ole_worksheet.UsedRange.Value.each do |row_values|
    yield row_values, i
    i += 1
  end
end

#each_value ⇒ Object

:deprecated: #



171
172
173
 
# File 'lib/robust_excel_ole/worksheet.rb', line 171

def each_value   
  each_rowvalue
end

#each_with_index(offset = 0) ⇒ Object 



155
156
157
158
159
160
161
162
163
 
# File 'lib/robust_excel_ole/worksheet.rb', line 155

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

#excel ⇒ Object 



41
42
43
 
# File 'lib/robust_excel_ole/worksheet.rb', line 41

def excel
  workbook.excel
end

#name ⇒ Object

sheet name



47
48
49
50
51
 
# File 'lib/robust_excel_ole/worksheet.rb', line 47

def name
  @ole_worksheet.Name
rescue
  raise WorksheetREOError, "name could not be determined"
end

#name=(new_name) ⇒ Object

sets sheet name

Parameters:

  • new_name (String) —

    the new name of the sheet



55
56
57
58
59
60
61
62
63
 
# File 'lib/robust_excel_ole/worksheet.rb', line 55

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

#row_range(row, integer_range = nil) ⇒ Object 



211
212
213
214
 
# File 'lib/robust_excel_ole/worksheet.rb', line 211

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

#set_cellval(x, y, value, opts = { }) ⇒ Object

def cellval(x,y)

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

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

Parameters:

  • opts (Hash) (defaults to: { }) —

    a customizable set of options

Options Hash (opts):

  • :color (Symbol) —

    the color of the cell when set



135
136
137
138
139
140
141
 
# File 'lib/robust_excel_ole/worksheet.rb', line 135

def set_cellval(x,y,value, opts = { }) # option opts is deprecated
  cell = @ole_worksheet.Cells.Item(x, y)
  cell.Interior.ColorIndex = opts[:color] unless opts[:color].nil?
  cell.Value = value
rescue # WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
  raise RangeNotEvaluatable, "cannot assign value #{value.inspect} to cell (#{y.inspect},#{x.inspect})"
end

#values ⇒ Object 



143
144
145
 
# File 'lib/robust_excel_ole/worksheet.rb', line 143

def values
  @ole_worksheet.UsedRange.Value
end