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.worksheet#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.



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

def initialize(win32_range, worksheet = nil)
  @ole_range = win32_range
  @worksheet = worksheet ? worksheet : worksheet_class.new(self.Parent)
  #@worksheet = worksheet_class.new(self.Parent)
end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

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



259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/robust_excel_ole/range.rb', line 259

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

Instance Attribute Details

#ole_range ⇒ Object (readonly)

Returns the value of attribute ole_range



12
13
14
 
# File 'lib/robust_excel_ole/range.rb', line 12

def ole_range
  @ole_range
end

#worksheet ⇒ Object (readonly)

Returns the value of attribute worksheet



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

def worksheet
  @worksheet
end

Instance Method Details

#==(other_range) ⇒ Object 



231
232
233
234
235
 
# File 'lib/robust_excel_ole/range.rb', line 231

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

#[](index) ⇒ Object 



27
28
29
30
31
 
# File 'lib/robust_excel_ole/range.rb', line 27

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

#copy(dest_address1, sheet_or_dest_address2 = :__not_provided, options_or_sheet = :__not_provided, not_provided_or_options = :__not_provided) ⇒ Object

copies a range



109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
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
 
# File 'lib/robust_excel_ole/range.rb', line 109

def copy(dest_address1, sheet_or_dest_address2 = :__not_provided, options_or_sheet = :__not_provided, not_provided_or_options = :__not_provided)
  dest_address = if sheet_or_dest_address2.is_a?(Object::Range) or sheet_or_dest_address2.is_a?(Integer)
    [dest_address1,sheet_or_dest_address2] 
  else
    dest_address1
  end
  dest_sheet = if sheet_or_dest_address2.is_a?(Worksheet)
    sheet_or_dest_address2
  else
    if options_or_sheet.is_a?(Worksheet)
      options_or_sheet
    else
      @worksheet
    end
  end
  options = if options_or_sheet.is_a?(Hash)
    options_or_sheet 
  else
    if not_provided_or_options.is_a?(Hash)
      not_provided_or_options
    else
      { }
    end
  end
  rows, columns = address_tool.as_integer_ranges(dest_address)
  dest_address_is_position = (rows.min == rows.max && columns.min == columns.max)
  dest_range_address = if (not dest_address_is_position) 
      [rows.min..rows.max,columns.min..columns.max]
    else
      if (not options[:transpose])
        [rows.min..rows.min+self.Rows.Count-1,
         columns.min..columns.min+self.Columns.Count-1]
      else
        [rows.min..rows.min+self.Columns.Count-1,
         columns.min..columns.min+self.Rows.Count-1]
      end
    end
  dest_range = dest_sheet.range(dest_range_address)
  begin
    if options[:values_only]
      # dest_range.Value = options[:transpose] ? self.Value.transpose : self.Value
      dest_range.v = options[:transpose] ? self.v.transpose : self.v
    else
      if dest_range.worksheet.workbook.excel == @worksheet.workbook.excel 
        if options[:transpose]
          self.Copy              
          #dest_range.PasteSpecial('transpose' => true) 
          dest_range.PasteSpecial(XlPasteAll,XlPasteSpecialOperationNone,false,true)
        else
          #self.Copy('destination' => dest_range.ole_range)
          self.Copy(dest_range.ole_range)
        end            
      else
        if options[:transpose]
          added_sheet = @worksheet.workbook.add_sheet
          self.copy_special(dest_address, added_sheet, :transpose => true)
          added_sheet.range(dest_range_address).copy_special(dest_address,dest_sheet)
          @worksheet.workbook.excel.with_displayalerts(false) {added_sheet.Delete}
        else
          self.Copy
          #dest_sheet.Paste('destination' => dest_range.ole_range)
          dest_sheet.Paste(dest_range.ole_range)
        end
      end
    end
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    raise RangeNotCopied, 'cannot copy range'
  end
end

#copy_special(dest_address, dest_sheet = :__not_provided, options = { }) ⇒ Object

becomes copy copies a range



184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
 
# File 'lib/robust_excel_ole/range.rb', line 184

def copy_special(dest_address, dest_sheet = :__not_provided, options = { })
  rows, columns = address_tool.as_integer_ranges(dest_address)
  dest_sheet = @worksheet if dest_sheet == :__not_provided
  dest_address_is_position = (rows.min == rows.max && columns.min == columns.max)
  dest_range_address = if (not dest_address_is_position) 
      [rows.min..rows.max,columns.min..columns.max]
    else
      if (not options[:transpose])
        [rows.min..rows.min+self.Rows.Count-1,
         columns.min..columns.min+self.Columns.Count-1]
      else
        [rows.min..rows.min+self.Columns.Count-1,
         columns.min..columns.min+self.Rows.Count-1]
      end
    end
  dest_range = dest_sheet.range(dest_range_address)
  begin
    if options[:values_only]
      dest_range.Value = options[:transpose] ? self.Value.transpose : self.Value
    else
      if dest_range.worksheet.workbook.excel == @worksheet.workbook.excel     
        if options[:transpose]
          self.Copy
          #dest_range.PasteSpecial('transpose' => true) 
          dest_range.PasteSpecial(XlPasteAll,XlPasteSpecialOperationNone,false,true)
        else
          #self.Copy('destination' => dest_range.ole_range)
          self.Copy(dest_range.ole_range)
        end            
      else
        if options[:transpose]
          added_sheet = @worksheet.workbook.add_sheet
          self.copy_special(dest_address, added_sheet, :transpose => true)
          added_sheet.range(dest_range_address).copy_special(dest_address,dest_sheet)
          @worksheet.workbook.excel.with_displayalerts(false) {added_sheet.Delete}
        else
          self.Copy
          #dest_sheet.Paste('destination' => dest_range.ole_range)
          dest_sheet.Paste(dest_range.ole_range)
        end
      end
    end
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg 
    raise RangeNotCopied, 'cannot copy range'
  end
end

#each ⇒ Object 



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

def each
  @ole_range.each_with_index do |ole_cell, index|
    yield cell(index){ole_cell}
  end
end

#v ⇒ Object Also known as: value 



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

def v
  begin
    if !::RANGES_JRUBY_BUG
      self.Value
    else
      address_r1c1 = self.AddressLocal(true,true,XlR1C1)
      row, col = address_tool.as_integer_ranges(address_r1c1)
      values = []
      row.each do |r|
        values_col = []
        col.each{ |c| values_col << worksheet.Cells(r,c).Value}
        values << values_col
      end
      values
    end
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    raise RangeNotEvaluatable, 'cannot read value'
  end 

end

#v=(value) ⇒ Object Also known as: value= 



83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
 
# File 'lib/robust_excel_ole/range.rb', line 83

def v=(value)
  begin
    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?(:first) ? value[i][j] : value)
        end
      end
    end
    value
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg  
    raise RangeNotEvaluatable, "cannot assign value to range #{address_r1c1.inspect}"
  end
end

#values(range = nil) ⇒ Object

returns flat array of the values of a given range



45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
# File 'lib/robust_excel_ole/range.rb', line 45

def values(range = nil)
  #result = map { |x| x.Value }.flatten
  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