Class: RobustExcelOle::RangeOwners

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

Direct Known Subclasses

Excel, Workbook, Worksheet

Instance Method Summary collapse

Methods inherited from REOCommon

#excel, #own_methods, puts_hash, tr1, trace

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



346
347
348
349
350
351
352
353
354
355
356
357
358
 
# File 'lib/robust_excel_ole/reo_common.rb', line 346

def add_name(name, addr, addr_deprecated = :__not_provided)
  addr = [addr,addr_deprecated] unless addr_deprecated == :__not_provided
  address = Address.new(addr)
  address_string = 'Z' + address.rows.min.to_s + 'S' + address.columns.min.to_s +
                   ':Z' + address.rows.max.to_s + 'S' + address.columns.max.to_s
  begin
    self.Names.Add('Name' => name, 'RefersToR1C1' => '=' + address_string)
  rescue WIN32OLERuntimeError => msg
    # trace "WIN32OLERuntimeError: #{msg.message}"
    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



383
384
385
386
387
388
389
390
391
392
393
394
 
# File 'lib/robust_excel_ole/reo_common.rb', line 383

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

#name2range(name) ⇒ Object

:deprecated: #



339
340
341
 
# File 'lib/robust_excel_ole/reo_common.rb', line 339

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

#nameval(name, opts = { :default => :__not_provided }) ⇒ Object

:deprecated: #



299
300
301
 
# File 'lib/robust_excel_ole/reo_common.rb', line 299

def nameval(name, opts = { :default => :__not_provided })   # :deprecated: #
  namevalue_glob(name, opts)
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



257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
 
# File 'lib/robust_excel_ole/reo_common.rb', line 257

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



206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
 
# File 'lib/robust_excel_ole/reo_common.rb', line 206

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
  value = begin
    name_obj.RefersToRange.Value
  rescue WIN32OLERuntimeError
    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
    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) || value.nil?
  value
end

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

creates a range from a given defined name or address

Returns:



318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
 
# File 'lib/robust_excel_ole/reo_common.rb', line 318

def range(name_or_address, address2 = :__not_provided)
  begin
    if address2 == :__not_provided
      range = RobustExcelOle::Range.new(name_object(name_or_address).RefersToRange) rescue nil
    end
    if self.is_a?(Worksheet) && (range.nil? || (address2 != :__not_provided))
      address = name_or_address
      address = [name_or_address,address2] unless address2 == :__not_provided
      address = Address.new(address)
      range = RobustExcelOle::Range.new(@ole_worksheet.Range(
                                          @ole_worksheet.Cells(address.rows.min, address.columns.min),
                                          @ole_worksheet.Cells(address.rows.max, address.columns.max)
                                        ))
    end
  rescue WIN32OLERuntimeError
    address2_string = address2.nil? ? "" : ", #{address2.inspect}"
    raise RangeNotCreated, "cannot create range (#{name_or_address.inspect}#{address2_string})"
  end      
  range
end

#rangeval(name, opts = { :default => :__not_provided }) ⇒ Object

:deprecated: #



307
308
309
 
# File 'lib/robust_excel_ole/reo_common.rb', line 307

def rangeval(name, opts = { :default => :__not_provided })  # :deprecated: #
  namevalue(name, opts)
end

#rename_range(name, new_name) ⇒ Object

renames a range

Parameters:

  • name (String) —

    the previous range name

  • new_name (String) —

    the new range name



367
368
369
370
371
372
373
374
375
376
377
378
 
# File 'lib/robust_excel_ole/reo_common.rb', line 367

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

#set_name(name, row, column) ⇒ Object

:deprecated :#



360
361
362
 
# File 'lib/robust_excel_ole/reo_common.rb', line 360

def set_name(name,row,column)     # :deprecated :#
  add_name(name,row,column)
end

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

:deprecated: #



303
304
305
 
# File 'lib/robust_excel_ole/reo_common.rb', line 303

def set_nameval(name, value, opts = { :color => 0 })        # :deprecated: #
  set_namevalue_glob(name, value, opts)
end

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



283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
 
# File 'lib/robust_excel_ole/reo_common.rb', line 283

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

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

Raises:



240
241
242
243
244
245
246
247
 
# File 'lib/robust_excel_ole/reo_common.rb', line 240

def set_namevalue_glob(name, value, opts = { :color => 0 })
  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

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

:deprecated: #



311
312
313
 
# File 'lib/robust_excel_ole/reo_common.rb', line 311

def set_rangeval(name, value, opts = { :color => 0 })       # :deprecated: #
  set_namevalue(name, value, opts)
end