Class: RobustExcelOle::Range

Inherits:
REOCommon 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

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from REOCommon

#excel, #own_methods, puts_hash, tr1, trace

Constructor Details

#initialize(win32_range) ⇒ Range

Returns a new instance of Range.



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

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



186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
 
# File 'lib/robust_excel_ole/range.rb', line 186

def method_missing(name, *args) 
  #if name.to_s[0,1] =~ /[A-Z]/
    begin
      @ole_range.send(name, *args)
    rescue WIN32OLERuntimeError => msg
      if msg.message =~ /unknown property or method/
        raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
      else
        raise msg
      end
    end
   #   else
   #     super
   #   end
end

Instance Attribute Details

#ole_range ⇒ Object (readonly)

Returns the value of attribute ole_range



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

def ole_range
  @ole_range
end

#worksheet ⇒ Object (readonly)

Returns the value of attribute worksheet



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

def worksheet
  @worksheet
end

Instance Method Details

#[](index) ⇒ Object 



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

def [] index
  @cells = []
  @cells[index + 1] = RobustExcelOle::Cell.new(@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



52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
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
110
111
112
113
114
115
116
117
 
# File 'lib/robust_excel_ole/range.rb', line 52

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
  address = Address.new(dest_address)
  dest_sheet = @worksheet if dest_sheet == :__not_provided
  dest_address_is_position = (address.rows.min == address.rows.max && address.columns.min == address.columns.max)
  dest_range_address = if (not dest_address_is_position) 
      [address.rows.min..address.rows.max,address.columns.min..address.columns.max]
    else
      if (not options[:transpose])
        [address.rows.min..address.rows.min+self.Rows.Count-1,
         address.columns.min..address.columns.min+self.Columns.Count-1]
      else
        [address.rows.min..address.rows.min+self.Columns.Count-1,
         address.columns.min..address.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) 
        else
          self.Copy(:destination => 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)
        end
      end
    end
  rescue WIN32OLERuntimeError
    raise RangeNotCopied, 'cannot copy range'
  end
end

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

becomes copy copies a range



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

def copy_special(dest_address, dest_sheet = :__not_provided, options = { })
  address = Address.new(dest_address)
  dest_sheet = @worksheet if dest_sheet == :__not_provided
  dest_address_is_position = (address.rows.min == address.rows.max && address.columns.min == address.columns.max)
  dest_range_address = if (not dest_address_is_position) 
      [address.rows.min..address.rows.max,address.columns.min..address.columns.max]
    else
      if (not options[:transpose])
        [address.rows.min..address.rows.min+self.Rows.Count-1,
         address.columns.min..address.columns.min+self.Columns.Count-1]
      else
        [address.rows.min..address.rows.min+self.Columns.Count-1,
         address.columns.min..address.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) 
        else
          self.Copy(:destination => 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)
        end
      end
    end
  rescue WIN32OLERuntimeError
    raise RangeNotCopied, 'cannot copy range'
  end
end

#each ⇒ Object 



19
20
21
22
23
 
# File 'lib/robust_excel_ole/range.rb', line 19

def each
  @ole_range.each do |row_or_column|
    yield RobustExcelOle::Cell.new(row_or_column)
  end
end

#v ⇒ Object 



39
40
41
 
# File 'lib/robust_excel_ole/range.rb', line 39

def v
  self.Value
end

#values(range = nil) ⇒ Object

returns flat array of the values of a given range



28
29
30
31
32
33
34
35
36
37
 
# File 'lib/robust_excel_ole/range.rb', line 28

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