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

Raises:

  • (RangeNotEvaluatable) 


120
121
122
123
124
125
126
 
# File 'lib/robust_excel_ole/range_owners.rb', line 120

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

Parameters:

  • name (String) —

    the name of the Excel object

Raises:

  • (UnexpectedREOError) 


146
147
148
149
150
151
 
# File 'lib/robust_excel_ole/range_owners.rb', line 146

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: #



113
114
115
 
# File 'lib/robust_excel_ole/range_owners.rb', line 113

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

#names ⇒ Array

Returns defined names.

Returns:

  • (Array) —

    defined names



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

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

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

def namevalue_global(name, opts = { default: :__not_provided })
  name_obj = begin
    get_name_object(name)
  rescue NameNotFound => msg
    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
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException 
    sheet = if self.is_a?(Worksheet) then self
    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
      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(*args) ⇒ Range

creates a range from a given defined name or address

Returns:

Raises:

  • (RangeNotCreated) 


109
110
111
 
# File 'lib/robust_excel_ole/range_owners.rb', line 109

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

#rename_name(old_name, new_name) ⇒ Object

renames an Excel object

Parameters:

  • old_name (String) —

    the previous name of the Excel object

  • new_name (String) —

    the new name of the Excel object

Raises:

  • (UnexpectedREOError) 


133
134
135
136
137
138
139
140
 
# File 'lib/robust_excel_ole/range_owners.rb', line 133

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

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 range when set



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

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