Class: RobustExcelOle::Range

Inherits:
VbaObjects show all
Defined in:
lib/robust_excel_ole/range.rb

Overview

This class essentially wraps a Win32Ole Range object. You can apply all VBA methods (starting with a capital letter) that you would apply for a Range object. See docs.microsoft.com/en-us/office/vba/api/excel.range#methods

Direct Known Subclasses

Cell

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from VbaObjects

#to_reo

Constructor Details

#initialize(win32_range, worksheet = nil) ⇒ Range

Returns a new instance of Range.



21
22
23
24
25
26
27
28
29
 
# File 'lib/robust_excel_ole/range.rb', line 21

def initialize(win32_range, worksheet = nil)
  @ole_range = begin
    win32_range.send(:Areas)
    win32_range
  rescue
    raise TypeREOError, "given win32ole object is not a range"
  end
  @worksheet = (worksheet ? worksheet : self.Parent).to_reo
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

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



268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
 
# File 'lib/robust_excel_ole/range.rb', line 268

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

Instance Attribute Details

#ole_range ⇒ Object (readonly)

Returns the value of attribute ole_range



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

def ole_range
  @ole_range
end

#worksheet ⇒ Object (readonly)

Returns the value of attribute worksheet



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

def worksheet
  @worksheet
end

Instance Method Details

#==(other_range) ⇒ Object 



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

def == other_range
  other_range.is_a?(Range) &&
    self.worksheet == other_range.worksheet
    self.Address == other_range.Address 
end

#[](index) ⇒ Object 



49
50
51
52
53
 
# File 'lib/robust_excel_ole/range.rb', line 49

def [] index
  cell(index) {
    @ole_range.Cells.Item(index + 1)
  }
end

#columns ⇒ Object 



35
36
37
 
# File 'lib/robust_excel_ole/range.rb', line 35

def columns
  @columns ||= (1..@ole_range.Columns.Count)
end

#copy(dest_address, *remaining_args) ⇒ Object

copies a range

Raises:

  • (RangeNotCopied) 


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

def copy(dest_address, *remaining_args)
  dest_sheet = @worksheet
  options = { }
  remaining_args.each do |arg|
    case arg
    when ::Range, Integer then dest_address = [dest_address,arg]
    when Worksheet, WIN32OLE    then dest_sheet = arg.to_reo
    when Hash                   then options = arg
    else raise RangeNotCopied, "cannot copy range: argument error: #{remaining_args.inspect}"
    end
  end
  dest_range_address = destination_range(dest_address, dest_sheet, options)
  dest_range = dest_sheet.range(dest_range_address)
  if options[:values_only]
    dest_range.v = !options[:transpose] ? self.v : self.v.transpose
  else
    copy_ranges(dest_address, dest_range, dest_range_address, dest_sheet, options)
  end
  dest_range
rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
  raise RangeNotCopied, "cannot copy range\n#{$!.message}"
end

#each ⇒ Object 



39
40
41
42
43
44
45
46
47
 
# File 'lib/robust_excel_ole/range.rb', line 39

def each
  if block_given?
    @ole_range.lazy.each_with_index do |ole_cell, index|
      yield cell(index){ole_cell}
    end
  else
    to_enum(:each).lazy
  end
end

#rows ⇒ Object 



31
32
33
 
# File 'lib/robust_excel_ole/range.rb', line 31

def rows
  @rows ||= (1..@ole_range.Rows.Count)
end

#value ⇒ Object

returns value of a given range restricted to used range



85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
# File 'lib/robust_excel_ole/range.rb', line 85

def value
  value = begin
    if !::RANGES_JRUBY_BUG    
      intersection_range = ole_range.Application.Intersect(ole_range, worksheet.Range(
        worksheet.Cells(1,1),worksheet.Cells(worksheet.last_row,worksheet.last_column)))
      begin
        intersection_range.Value 
      rescue 
        nil
      end
    else
      # optimization is possible here
      rows_used_range = [rows, last_row].min
      columns_used_rage = [columns, last_column].min
      values = rows_used_range.map{|r| columns_used_range.map {|c| worksheet.Cells(r,c).Value} }
      (values.size==1 && values.first.size==1) ? values.first.first : values
    end
  rescue
    raise RangeNotEvaluatable, "cannot evaluate range #{self.inspect}\n#{$!.message}"
  end
  if value == -2146828288 + RobustExcelOle::XlErrName
    raise RangeNotEvaluatable, "cannot evaluate range #{self.inspect}"
  end
  value
end

#value=(value) ⇒ Object

sets the values if the range

Parameters:

  • value (Variant)

Raises:

  • (RangeNotEvaluatable) 


113
114
115
116
117
118
119
120
121
122
123
124
125
126
 
# File 'lib/robust_excel_ole/range.rb', line 113

def value=(value)
  if !::RANGES_JRUBY_BUG
    ole_range.Value = value
  else
    rows.each_with_index do |r,i|
      columns.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 => msg  
  raise RangeNotEvaluatable, "cannot assign value to range #{self.inspect}\n#{$!.message}"
end

#values(range = nil) ⇒ Object

returns flat array of the values of a given range



67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
 
# File 'lib/robust_excel_ole/range.rb', line 67

def values(range = nil)
  result_unflatten = if !::RANGES_JRUBY_BUG
    map { |x| x.v }
  else
    self.v
  end
  result = result_unflatten.flatten
  if range
    relevant_result = []
    result.each_with_index { |row_or_column, i| relevant_result << row_or_column if range.include?(i) }
    relevant_result
  else
    result
  end
end