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
 
# File 'lib/robust_excel_ole/range.rb', line 21

def initialize(win32_range, worksheet = nil)
  @ole_range = win32_range
  @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) 



282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
 
# File 'lib/robust_excel_ole/range.rb', line 282

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 



229
230
231
232
233
 
# File 'lib/robust_excel_ole/range.rb', line 229

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

#[](index) ⇒ Object 



44
45
46
47
48
 
# File 'lib/robust_excel_ole/range.rb', line 44

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

#columns ⇒ Object 



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

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

#copy(dest_address, *remaining_args) ⇒ Object

copies a range

Raises:

  • (RangeNotCopied) 


168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
 
# File 'lib/robust_excel_ole/range.rb', line 168

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 



34
35
36
37
38
39
40
41
42
 
# File 'lib/robust_excel_ole/range.rb', line 34

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 



26
27
28
 
# File 'lib/robust_excel_ole/range.rb', line 26

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

#value ⇒ Object

returns values of a given range



104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/robust_excel_ole/range.rb', line 104

def value
  value = begin
    if !::RANGES_JRUBY_BUG    
      ole_range.Application.Intersect(ole_range, worksheet.Range(
        worksheet.Cells(1,1),worksheet.Cells(worksheet.last_row,worksheet.last_column))).Value
    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) 


127
128
129
130
131
132
133
134
135
136
137
138
139
140
 
# File 'lib/robust_excel_ole/range.rb', line 127

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



62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
 
# File 'lib/robust_excel_ole/range.rb', line 62

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