Class: RobustExcelOle::RangeOwners

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

Direct Known Subclasses

Workbook, Worksheet

Instance Method Summary collapse

Methods inherited from VbaObjects

#to_reo

Instance Method Details

#add_name(name, addr, addr_deprecated = :__not_provided) ⇒ Object

adds a name referring to a range given by the row and column

Parameters:

  • name (String) —

    the range name



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

def add_name(name, addr, addr_deprecated = :__not_provided)
  addr = [addr,addr_deprecated] unless addr_deprecated == :__not_provided
  begin       
    self.Names.Add(name, nil, true, nil, nil, nil, nil, nil, nil, '=' + address_tool.as_r1c1(addr))
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    raise RangeNotEvaluatable, "cannot add name #{name.inspect} to range #{addr.inspect}"
  end
  name
end

#delete_name(name) ⇒ Object

deletes a name of a range

Parameters:

  • name (String) —

    the previous range name

  • new_name (String) —

    the new range name



252
253
254
255
256
257
258
259
260
261
262
263
 
# File 'lib/robust_excel_ole/range_owners.rb', line 252

def delete_name(name)
  begin
    item = self.Names.Item(name)
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    raise NameNotFound, "name #{name.inspect} not in #{File.basename(self.stored_filename).inspect}"
  end
  begin
    item.Delete
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    raise UnexpectedREOError, "name error in #{File.basename(self.stored_filename).inspect}"
  end
end

#name2range(name) ⇒ Object

:deprecated: #



212
213
214
 
# File 'lib/robust_excel_ole/range_owners.rb', line 212

def name2range(name)   # :deprecated: #
  range(name)
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



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/range_owners.rb', line 100

def namevalue(name, opts = { :default => :__not_provided })
  return namevalue_glob(name, opts) if self.is_a?(Workbook)
  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}"
  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}"
  end
  return opts[:default] unless (opts[:default] == :__not_provided) || value.nil?
  value
end

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

returns the contents of a range with given name if the name could not be found or the value could not be determined,

then return default value, if provided, raise error otherwise

Excel Bug: if a local name without a qualifier is given,

then by default Excel takes the first worksheet,
even if a different worksheet is active

Parameters:

  • name (String) —

    the name of the 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



17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
 
# File 'lib/robust_excel_ole/range_owners.rb', line 17

def namevalue_glob(name, opts = { :default => :__not_provided })
  name_obj = begin
    name_object(name)
  rescue NameNotFound => msg
    return opts[:default] unless opts[:default] == :__not_provided
    raise
  end
  ole_range = name_obj.RefersToRange
  worksheet = self if self.is_a?(Worksheet)
  value = begin
    #name_obj.RefersToRange.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 
    sheet = if self.is_a?(Worksheet) then self
            elsif self.is_a?(Workbook) then self.sheet(1)
            elsif self.is_a?(Excel) then self.workbook.sheet(1)
    end
    begin
      #sheet.Evaluate(name_obj.Name).Value
      # does it result in a range?
      ole_range = sheet.Evaluate(name_obj.Name)
      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 evaluate range named #{name.inspect} in #{self}"
    end
  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}"
  end
  return opts[:default] unless (opts[:default] == :__not_provided) || value.nil?
  value
end

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

creates a range from a given defined name or address range(address) does work for Worksheet objects only

Returns:



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

def range(name_or_address, address2 = :__not_provided)
  begin
    worksheet = self if self.is_a?(Worksheet)
    if address2 == :__not_provided
      range = if name_or_address.is_a?(String)
        begin
          RobustExcelOle::Range.new(name_object(name_or_address).RefersToRange, worksheet) 
        rescue NameNotFound
          nil
        end
      end
    end
    if self.is_a?(Worksheet) && (range.nil? || (address2 != :__not_provided))
      address = name_or_address
      address = [name_or_address,address2] unless address2 == :__not_provided         
      self.Names.Add('__dummy001',nil,true,nil,nil,nil,nil,nil,nil,'=' + address_tool.as_r1c1(address))          
      range = RobustExcelOle::Range.new(name_object('__dummy001').RefersToRange, worksheet)
      self.Names.Item('__dummy001').Delete
      workbook = self.is_a?(Workbook) ? self : self.workbook
      workbook.save
      range                    
    end
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    address2_string = address2.nil? ? "" : ", #{address2.inspect}"
    raise RangeNotCreated, "cannot create range (#{name_or_address.inspect}#{address2_string})"
  end      
  range
end

#rename_range(name, new_name) ⇒ Object

renames a range

Parameters:

  • name (String) —

    the previous range name

  • new_name (String) —

    the new range name



236
237
238
239
240
241
242
243
244
245
246
247
 
# File 'lib/robust_excel_ole/range_owners.rb', line 236

def rename_range(name, new_name)
  begin
    item = self.Names.Item(name)
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    raise NameNotFound, "name #{name.inspect} not in #{File.basename(self.stored_filename).inspect}"
  end
  begin
    item.Name = new_name
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
    raise UnexpectedREOError, "name error in #{File.basename(self.stored_filename).inspect}"
  end
end

#set_name(name, row, column) ⇒ Object

:deprecated :#



229
230
231
 
# File 'lib/robust_excel_ole/range_owners.rb', line 229

def set_name(name,row,column)     # :deprecated :#
  add_name(name,row,column)
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



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

def set_namevalue(name, value, opts = { })  
  begin
    return set_namevalue_glob(name, value, opts) if self.is_a?(Workbook) 
    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?(:first) ? 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}"
  end
end

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

sets the contents of a range

Parameters:

  • name (String) —

    the name of a range

  • value (Variant) —

    the contents of the range

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

    a customizable set of options

Options Hash (opts):

  • :color (Symbol) —

    the color of the cell when set



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

def set_namevalue_glob(name, value, opts = { }) 
  begin
    name_obj = begin
      name_object(name)
    rescue NameNotFound => msg
      raise
    end        
    ole_range = name_object(name).RefersToRange
    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?(:first) ? 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}"
  end
end