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

Raises:

  • (RangeNotEvaluatable) 


127
128
129
130
131
132
133
 
# File 'lib/robust_excel_ole/range_owners.rb', line 127

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

#delete_name(name) ⇒ Object

deletes a name of an Excel object

Raises:

  • (UnexpectedREOError) 


153
154
155
156
157
158
 
# File 'lib/robust_excel_ole/range_owners.rb', line 153

def delete_name(name)
  item = get_name_object(name)
  item.Delete
rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
  raise UnexpectedREOError, "name error with name #{name.inspect} in #{File.basename(self.stored_filename).inspect}\n#{$!.message}"
end

#name2range(name) ⇒ Object

:deprecated: #



120
121
122
 
# File 'lib/robust_excel_ole/range_owners.rb', line 120

def name2range(name)   # :deprecated: #
  range(name)
end

#names ⇒ Array 



161
162
163
 
# File 'lib/robust_excel_ole/range_owners.rb', line 161

def names
  self.Names.to_a.map(&:name)
end

#namevalue_global(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

Options Hash (opts):

  • :default (Symbol) —

    the default value that is provided if no contents could be returned



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
61
62
63
 
# File 'lib/robust_excel_ole/range_owners.rb', line 17

def namevalue_global(name, opts = { default: :__not_provided })
  begin
    name_obj = begin
      get_name_object(name)
    rescue NameNotFound
      raise
    end
    ole_range = name_obj.RefersToRange
    worksheet = self if self.is_a?(Worksheet)
    value = begin
      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
    end
  rescue # WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException 
    sheet = if self.is_a?(Worksheet) then self
    # chooses simply the 1st worksheet?
    elsif self.is_a?(Workbook) then self.sheet(1)
    end
    begin
      # 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
      if name_obj.nil?
        raise NameNotFound, "cannot find name #{name.inspect}"
      else
        raise RangeNotEvaluatable, "cannot evaluate range named #{name.inspect}"
      end
    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] if opts[:default] != :__not_provided && !value.nil?
  value
end

#range(*args) ⇒ Range

creates a range from a given defined name or address

Raises:

  • (RangeNotCreated) 


116
117
118
 
# File 'lib/robust_excel_ole/range_owners.rb', line 116

def range(*args)
  raise RangeNotCreated, "not yet implemented"
end

#rename_name(old_name, new_name) ⇒ Object

renames an Excel object

Raises:

  • (UnexpectedREOError) 


140
141
142
143
144
145
146
147
 
# File 'lib/robust_excel_ole/range_owners.rb', line 140

def rename_name(old_name, new_name)
  item = get_name_object(old_name)
  item.Name = new_name
rescue RobustExcelOle::NameNotFound
  raise
rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
  raise UnexpectedREOError, "name error with name #{old_name.inspect} in #{File.basename(self.stored_filename).inspect}\n#{$!.message}"
end

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

sets the contents of a range

Options Hash (opts):

  • :color (Symbol) —

    the color of the range when set



69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
 
# File 'lib/robust_excel_ole/range_owners.rb', line 69

def set_namevalue_global(name, value, opts = { }) 
  name_obj = begin
    get_name_object(name)
  rescue NameNotFound => msg
    raise
  end        
  ole_range = name_obj.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?(:pop) ? 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}\n#{$!.message}"
end