Class: RobustExcelOle::RangeOwners

Inherits:
REOCommon
  • Object
show all
Defined in:
lib/robust_excel_ole/reo_common.rb

Direct Known Subclasses

Book, Excel, Sheet

Instance Method Summary collapse

Methods inherited from REOCommon

#excel, #own_methods, puts_hash, tr1, trace

Instance Method Details

#name_object(name) ⇒ Object 



243
244
245
246
247
248
249
250
251
252
253
 
# File 'lib/robust_excel_ole/reo_common.rb', line 243

def name_object(name)
  begin
    self.Parent.Names.Item(name)
  rescue WIN32OLERuntimeError
    begin
      self.Names.Item(name)
    rescue WIN32OLERuntimeError
      raise RobustExcelOle::NameNotFound, "name #{name.inspect} not in #{self.inspect}"  
    end
  end
end

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



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

def nameval(name, opts = {:default => :__not_provided})
  name_obj = begin
    name_object(name)
  rescue NameNotFound => msg
    return opts[:default] unless opts[:default] == :__not_provided
    raise
  end
  value = begin
    name_obj.RefersToRange.Value
  rescue WIN32OLERuntimeError
    sheet = if self.is_a?(Sheet) then self
    elsif self.is_a?(Book) then self.sheet(1)
    elsif self.is_a?(Excel) then self.workbook.sheet(1)
    end
    begin
      sheet.Evaluate(name_obj.Name).Value
    rescue # WIN32OLERuntimeError
      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 or value.nil?
  value      
end

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



197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
 
# File 'lib/robust_excel_ole/reo_common.rb', line 197

def rangeval(name, opts = {:default => :__not_provided})
  return nameval(name, opts) if self.is_a?(Book)
  begin
    range = self.Range(name)
  rescue WIN32OLERuntimeError
    return opts[:default] unless opts[:default] == :__not_provided
    raise NameNotFound, "name #{name.inspect} not in #{self.inspect}"
  end
  begin
    value = range.Value
  rescue  WIN32OLERuntimeError
    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 or value.nil?
  value      
  #return opts[:default] unless opts[:default] == :__not_provided
  #raise RangeNotEvaluatable, "cannot evaluate range named #{name.inspect}" if value == -2146828288 + RobustExcelOle::XlErrName
end

#set_nameval(name, value, opts = {:color => 0}) ⇒ Object

sets the contents of a range

Parameters:

  • name (String) —

    the name of a range

  • value (Variant) —

    the contents of the range

  • color (FixNum) —

    the color when setting a value

  • opts (Hash) (defaults to: {:color => 0}) —

    :color [FixNum] the color when setting the contents



178
179
180
181
182
183
184
185
186
187
 
# File 'lib/robust_excel_ole/reo_common.rb', line 178

def set_nameval(name, value, opts = {:color => 0})
  begin
    cell = name_object(name).RefersToRange
    cell.Interior.ColorIndex = opts[:color] 
    workbook.modified_cells << cell if workbook #unless cell_modified?(cell)
    cell.Value = value
  rescue WIN32OLERuntimeError
    raise RangeNotEvaluatable, "cannot assign value to range named #{name.inspect} in #{self.inspect}" 
  end
end

#set_rangeval(name, value, opts = {:color => 0}) ⇒ 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: {:color => 0}) —

    :color [FixNum] the color when setting the contents



225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
 
# File 'lib/robust_excel_ole/reo_common.rb', line 225

def set_rangeval(name,value, opts = {:color => 0})
  begin
    return set_nameval(name, value, opts) if self.is_a?(Book)
    range = self.Range(name)
  rescue WIN32OLERuntimeError
    raise NameNotFound, "name #{name.inspect} not in #{self.inspect}"
  end
  begin
    range.Interior.ColorIndex = opts[:color]
    workbook.modified_cells << range if workbook #unless cell_modified?(range)
    range.Value = value
  rescue  WIN32OLERuntimeError
    raise RangeNotEvaluatable, "cannot assign value to range named #{name.inspect} in #{self.inspect}"
  end
end