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, #names, #namevalue_global, #rename_name, #set_namevalue_global

Methods inherited from VbaObjects

#to_reo

Constructor Details

#initialize(win32_worksheet) ⇒ Worksheet

Returns a new instance of Worksheet.



23
24
25
26
27
28
29
30
31
32
33
34
 
# File 'lib/robust_excel_ole/worksheet.rb', line 23

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) 



428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
 
# File 'lib/robust_excel_ole/worksheet.rb', line 428

def method_missing(name, *args)
  super unless 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
end

Instance Attribute Details

#ole_worksheet ⇒ Object (readonly)

Returns the value of attribute ole_worksheet



18
19
20
 
# File 'lib/robust_excel_ole/worksheet.rb', line 18

def ole_worksheet
  @ole_worksheet
end

#workbook ⇒ Object (readonly)

Returns the value of attribute workbook



19
20
21
 
# File 'lib/robust_excel_ole/worksheet.rb', line 19

def workbook
  @workbook
end

Instance Method Details

#==(other_worksheet) ⇒ Object 



333
334
335
336
337
 
# File 'lib/robust_excel_ole/worksheet.rb', line 333

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

#[](name_or_address, address2 = :__not_provided) ⇒ Object

value of a range given its defined name or address



74
75
76
 
# File 'lib/robust_excel_ole/worksheet.rb', line 74

def [](name_or_address, address2 = :__not_provided)
  range(name_or_address, address2).value
end

#[]=(name_or_address, value_or_address2, remaining_arg = :__not_provided) ⇒ Object

sets the value of a range given its defined name or address, and the value



82
83
84
85
86
87
88
89
90
91
92
93
 
# File 'lib/robust_excel_ole/worksheet.rb', line 82

def []=(name_or_address, value_or_address2, remaining_arg = :__not_provided) 
  if remaining_arg != :__not_provided
    name_or_address, value = [name_or_address, value_or_address2], remaining_arg
  else
    value = value_or_address2
  end
  begin
    range(name_or_address).value = value
  rescue #WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    raise RangeNotEvaluatable, "cannot assign value to range with name or address #{name_or_address.inspect}\n#{$!.message}"
  end
end

#cellval(x, y) ⇒ Object

value of a cell, if row and column are given



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

def cellval(x,y)                         # :deprecated :#
  @ole_worksheet.Cells.Item(x, y).Value
rescue
  raise RangeNotEvaluatable, "cannot read cell (#{x.inspect},#{y.inspect})\n#{$!.message}"
end

#col_range(col, integer_range = nil) ⇒ Object 



328
329
330
331
 
# File 'lib/robust_excel_ole/worksheet.rb', line 328

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 ⇒ Enumerator

Returns traversing the rows values.

Returns:

  • (Enumerator) —

    traversing the rows values



235
236
237
238
239
240
241
242
243
244
 
# File 'lib/robust_excel_ole/worksheet.rb', line 235

def each
  if block_given?
    @ole_worksheet.UsedRange.Rows.lazy.each do |ole_row|
      row_value = ole_row.Value
      yield (row_value.nil? ? [] : row_value.first)  
    end
  else
    to_enum(:each).lazy
  end
end

#each_cell ⇒ Enumerator

Returns traversing the cells.

Returns:

  • (Enumerator) —

    traversing the cells



285
286
287
288
289
290
291
292
293
294
295
 
# File 'lib/robust_excel_ole/worksheet.rb', line 285

def each_cell
  if block_given?
    each_row do |row_range|
      row_range.lazy.each do |cell|
        yield cell
      end
    end
  else
    to_enum(:each_cell).lazy
  end
end

#each_cell_with_index(offset = 0) ⇒ Object

:nodoc: # # :deprecated :#



297
298
299
300
301
302
303
304
305
 
# File 'lib/robust_excel_ole/worksheet.rb', line 297

def each_cell_with_index(offset = 0)   # :nodoc: #  # :deprecated :#
  i = offset
  each_row do |row_range|
    row_range.each do |cell|
      yield cell, i
      i += 1
    end
  end
end

#each_column(offset = 0) ⇒ Enumerator

Returns traversing the columns.

Returns:

  • (Enumerator) —

    traversing the columns



266
267
268
269
270
271
272
273
274
275
276
 
# File 'lib/robust_excel_ole/worksheet.rb', line 266

def each_column(offset = 0)
  if block_given?
    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
  else
    to_enum(:each_column).lazy
  end
end

#each_column_with_index(offset = 0) ⇒ Object

:nodoc: # # :deprecated :#



278
279
280
281
282
 
# File 'lib/robust_excel_ole/worksheet.rb', line 278

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

#each_row(offset = 0) ⇒ Enumerator

Returns traversing the rows.

Returns:

  • (Enumerator) —

    traversing the rows



247
248
249
250
251
252
253
254
255
256
257
 
# File 'lib/robust_excel_ole/worksheet.rb', line 247

def each_row(offset = 0)
  if block_given?
    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
  else
    to_enum(:each_row).lazy
  end
end

#each_row_with_index(offset = 0) ⇒ Object

:nodoc: # # :deprecated :#



259
260
261
262
263
 
# File 'lib/robust_excel_ole/worksheet.rb', line 259

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

#each_rowvalue ⇒ Object

:deprecated: #



307
308
309
310
311
 
# File 'lib/robust_excel_ole/worksheet.rb', line 307

def each_rowvalue  
  values.each do |row_values|
    yield row_values
  end
end

#each_rowvalue_with_index(offset = 0) ⇒ Object

:deprecated: #



313
314
315
316
317
318
319
 
# File 'lib/robust_excel_ole/worksheet.rb', line 313

def each_rowvalue_with_index(offset = 0)    # :deprecated: #
  i = offset
  values.each do |row_values|
    yield row_values, i
    i += 1
  end
end

#excel ⇒ Object 



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

def excel
  workbook.excel
end

#name ⇒ Object

sheet name



53
54
55
56
57
 
# File 'lib/robust_excel_ole/worksheet.rb', line 53

def name
  @ole_worksheet.Name.encode('utf-8')
rescue
  raise WorksheetREOError, "name could not be determined\n#{$!.message}"
end

#name=(new_name) ⇒ Object

sets sheet name

Parameters:

  • new_name (String) —

    the new name of the sheet



61
62
63
64
65
66
67
68
69
 
# File 'lib/robust_excel_ole/worksheet.rb', line 61

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

#namevalue(name, opts = { default: :__not_provided }) ⇒ Variant

returns the contents of a range with a locally defined name evaluates the formula if the contents is a formula if the name could not be found or the range or value could not be determined, then return default value, if provided, raise error otherwise

Parameters:

  • name (String) —

    the name of a range

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

    the options

Options Hash (opts):

  • :default (Symbol) —

    the default value that is provided if no contents could be returned

Returns:

  • (Variant) —

    the contents of a range with given name



152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
 
# File 'lib/robust_excel_ole/worksheet.rb', line 152

def namevalue(name, opts = { default: :__not_provided })
  begin
    ole_range = self.Range(name)
  rescue # WIN32OLERuntimeError, VBAMethodMissingError, Java::OrgRacobCom::ComFailException 
    return opts[:default] unless opts[:default] == :__not_provided
    raise NameNotFound, "name #{name.inspect} not in #{self.inspect}"
  end
  begin
    worksheet = self if self.is_a?(Worksheet)
    #value = ole_range.Value
    value = if !::RANGES_JRUBY_BUG
      ole_range.Value
    else
      values = RobustExcelOle::Range.new(ole_range, worksheet).v
      (values.size==1 && values.first.size==1) ? values.first.first : values
    end
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException 
    return opts[:default] unless opts[:default] == :__not_provided
    raise RangeNotEvaluatable, "cannot determine value of range named #{name.inspect} in #{self.inspect}\n#{$!.message}"
  end
  if value == -2146828288 + RobustExcelOle::XlErrName
    return opts[:default] unless opts[:default] == __not_provided
    raise RangeNotEvaluatable, "cannot evaluate range named #{name.inspect} in #{File.basename(workbook.stored_filename).inspect rescue nil}\n#{$!.message}"
  end
  return opts[:default] unless (opts[:default] == :__not_provided) || value.nil?
  value
end

#range(name_or_address, address2 = :__not_provided) ⇒ Range

a range given a defined name or address

Returns:



98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
 
# File 'lib/robust_excel_ole/worksheet.rb', line 98

def range(name_or_address, address2 = :__not_provided)
  if name_or_address.respond_to?(:gsub) && address2 == :__not_provided
    name = name_or_address
    range = get_name_object(name).RefersToRange rescue nil
  end
  unless range
    address = normalize_address(name_or_address, address2)
    workbook.retain_saved do
      begin
        a1_address = address_tool.as_a1(address) rescue nil
        if a1_address
          range = self.Range(a1_address)              
        else
          saved = self.Parent.Saved
          begin
            self.Names.Add('__dummy_name_object_001__',nil,true,nil,nil,nil,nil,nil,nil,'=' + address_tool.as_r1c1(address))
            range = get_name_object('__dummy_name_object_001__').RefersToRange
          ensure
            self.Names.Item('__dummy_name_object_001__').Delete
            self.Parent.Saved = saved
          end
        end
      rescue
        address2_string = (address2.nil? || address2 == :__not_provided) ? "" : ", #{address2.inspect}"
        raise RangeNotCreated, "cannot find name or address #{name_or_address.inspect}#{address2_string})"
      end
    end
  end
  range.to_reo
end

#row_range(row, integer_range = nil) ⇒ Object 



323
324
325
326
 
# File 'lib/robust_excel_ole/worksheet.rb', line 323

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

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



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

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})\n#{$!.message}"
end

#set_namevalue(name, value, opts = { }) ⇒ Object

assigns a value to a range given a locally defined name

Parameters:

  • name (String) —

    the name of a range

  • value (Variant) —

    the assigned value

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

    a customizable set of options

Options Hash (opts):

  • :color (Symbol) —

    the color of the cell when set



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
 
# File 'lib/robust_excel_ole/worksheet.rb', line 184

def set_namevalue(name, value, opts = { })  
  begin
    ole_range = self.Range(name)
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException, VBAMethodMissingError
    raise NameNotFound, "name #{name.inspect} not in #{self.inspect}"
  end
  begin
    ole_range.Interior.ColorIndex = opts[:color] unless opts[:color].nil?
    if !::RANGES_JRUBY_BUG
      ole_range.Value = value
    else
      address_r1c1 = ole_range.AddressLocal(true,true,XlR1C1)
      row, col = address_tool.as_integer_ranges(address_r1c1)
      row.each_with_index do |r,i|
        col.each_with_index do |c,j|
          ole_range.Cells(i+1,j+1).Value = (value.respond_to?(:pop) ? value[i][j] : value)
        end
      end
    end
    value
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    raise RangeNotEvaluatable, "cannot assign value to range named #{name.inspect} in #{self.inspect}\n#{$!.message}"
  end
end

#table(number_or_name) ⇒ ListObject

Returns a table (listobject).

Returns:



342
343
344
345
346
 
# File 'lib/robust_excel_ole/worksheet.rb', line 342

def table(number_or_name)
  listobject_class.new(@ole_worksheet.ListObjects.Item(number_or_name))
rescue
  raise WorksheetREOError, "table #{number_or_name} not found"
end

#values ⇒ Array

Returns a 2-dimensional array that contains the values in each row of the used range.

Returns:

  • (Array) —

    a 2-dimensional array that contains the values in each row of the used range



230
231
232
 
# File 'lib/robust_excel_ole/worksheet.rb', line 230

def values
  @ole_worksheet.UsedRange.Value
end