Class: RobustExcelOle::ListObject

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

Overview

This class essentially wraps a Win32Ole ListObject. You can apply all VBA methods (starting with a capital letter) that you would apply for a ListObject. See docs.microsoft.com/en-us/office/vba/api/excel.listobject#methods

Instance Attribute Summary collapse

Instance Method Summary collapse

Methods inherited from VbaObjects

#to_reo

Constructor Details

#initialize(worksheet_or_listobject, table_name_or_number = "_table_name", position = [1,1], rows_count = 1, columns_count_or_names = 1) ⇒ ListObject

constructs a list object (or table).

Parameters:

  • worksheet_or_listobject (Variable) —

    a worksheet or a list object

  • table_name_or_number (Variable) (defaults to: "_table_name") —

    a table name or table number

  • position (Array) (defaults to: [1,1]) —

    a position of the upper left corner

  • rows_count (Integer) (defaults to: 1) —

    number of rows

  • columns_count_or_names (Variable) (defaults to: 1) —

    number of columns or array of column names



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
64
65
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
91
 
# File 'lib/robust_excel_ole/list_object.rb', line 27

def initialize(worksheet_or_listobject,
               table_name_or_number = "_table_name",
               position = [1,1],
               rows_count = 1, 
               columns_count_or_names = 1)

  # ole_table is being assigned to the first parameter, if this parameter is a ListObject
  # otherwise the first parameter could be a worksheet, and get the ole_table via the ListObject name or number
  @ole_table = if worksheet_or_listobject.respond_to?(:ListRows)
    worksheet_or_listobject.ole_table
  else
    begin
      worksheet_or_listobject.send(:ListRows)
      worksheet_or_listobject
    rescue
      @worksheet = worksheet_or_listobject.to_reo
      @worksheet.ListObjects.Item(table_name_or_number) rescue nil
    end
  end
  unless @ole_table
    columns_count = 
      columns_count_or_names.is_a?(Integer) ? columns_count_or_names : columns_count_or_names.length 
    column_names = columns_count_or_names.respond_to?(:first) ? columns_count_or_names : []
    begin
      listobjects = @worksheet.ListObjects
      @ole_table = listobjects.Add(XlSrcRange, 
                                   @worksheet.range([position[0]..position[0]+rows_count-1,
                                              position[1]..position[1]+columns_count-1]).ole_range,
                                   XlYes)
      @ole_table.Name = table_name_or_number
      @ole_table.HeaderRowRange.Value = [column_names] unless column_names.empty?
    rescue WIN32OLERuntimeError => msg # , Java::OrgRacobCom::ComFailException => msg
      raise TableError, "#{$!.message}"
    end
  end


ole_table = @ole_table

  @row_class = Class.new(ListRow) do

    @ole_table = ole_table

    #def ole_table
    #  self.class.instance_variable_get(:ole_table)
    #end

    def ole_table
      self.class.ole_table

    end

    def self.ole_table
      @ole_table
    end

    def self.ole_table= tab
      @ole_table = tab
    end
  
  end      

  @row_class.ole_table = @ole_table

end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object 



408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
 
# File 'lib/robust_excel_ole/list_object.rb', line 408

def method_missing(name, *args) 
  super unless name.to_s[0,1] =~ /[A-Z]/
  if ::ERRORMESSAGE_JRUBY_BUG
    begin
      @ole_table.send(name, *args)
    rescue Java::OrgRacobCom::ComFailException 
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  else
    begin
      @ole_table.send(name, *args)
    rescue NoMethodError 
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  end
end

Instance Attribute Details

#ole_table ⇒ Object (readonly)

Returns the value of attribute ole_table



16
17
18
 
# File 'lib/robust_excel_ole/list_object.rb', line 16

def ole_table
  @ole_table
end

Instance Method Details

#==(other_table) ⇒ Object 



371
372
373
374
375
 
# File 'lib/robust_excel_ole/list_object.rb', line 371

def == other_table
  other_table.is_a?(ListObject) && 
    self.Range.Address == other_table.Range.Address &&
    self.Parent.to_reo == other_table.Parent.to_reo
end

#[](key_hash_or_number, options = { }) ⇒ Variant

accesses a table row object

Parameters:

  • a (Variant) —

    hash of key (key column: value) or a row number (>= 1)

  • opts (Hash) —

    a customizable set of options

Returns:

  • (Variant) —

    a listrow, if limit == :first an array of listrows, with maximal number=limit, if list rows were found and limit is not :first nil, if no list object was found



110
111
112
113
114
115
116
117
118
119
120
121
122
123
 
# File 'lib/robust_excel_ole/list_object.rb', line 110

def [] (key_hash_or_number, options = { })
  return @row_class.new(key_hash_or_number) if key_hash_or_number.respond_to?(:succ)
  options = {limit: :first}.merge(options)   
  opts = options.dup
  opts[:limit] = 1 if options[:limit] == :first
  key_hash = key_hash_or_number.transform_keys{|k| k.downcase.to_sym}
  matching = if @ole_table.ListRows.Count < 150
    matching_via_traversing(key_hash, opts)
  else
    matching_via_filter(key_hash, opts)
  end
  matching_listrows = matching.map{ |r| @row_class.new(r) }
  options[:limit] == :first ? matching_listrows.first : matching_listrows
end

#delete_empty_columns ⇒ Object

deletes columns that have an empty contents



326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
 
# File 'lib/robust_excel_ole/list_object.rb', line 326

def delete_empty_columns
    listcolumns = @ole_table.ListColumns
    nil_array = [].fill([nil],0..(@ole_table.ListRows.Count-1))
    i = 1
    while i <= listcolumns.Count do 
      column = listcolumns.Item(i)
      if column.Range.Value[1..-1] == nil_array
        column.Delete
      else
        i += 1
      end
    end
  end

  # finds all cells containing a given value
  # @param[Variant] value to find
  # @return [Array] win32ole cells containing the given value
  def find_cells(value)
    encode_utf8 = ->(val) {val.respond_to?(:gsub) ? val.encode('utf-8') : val}   
    listrows = @ole_table.ListRows   
    listrows.map { |listrow|
      listrow_range = listrow.Range
      listrow_range.Value.first.map{ |v| encode_utf8.(v) }.find_all_indices(value).map do |col_number| 
        listrow_range.Cells(1,col_number+1).to_reo 
      end
    }.flatten
  end

  # sorts the rows of the list object according to the given column
  # @param [Variant] column number or name
  # @option opts [Symbol]   sort order
  def sort(column_number_or_name, sort_order = :ascending)
    key_range = @ole_table.ListColumns.Item(column_number_or_name).Range
    @ole_table.Sort.SortFields.Clear
    sort_order_option = sort_order == :ascending ? XlAscending : XlDescending
    @ole_table.Sort.SortFields.Add(key_range, XlSortOnValues,sort_order_option,XlSortNormal)
    @ole_table.Sort.Apply
  end

  # @return [Array] position of the first cell of the table
  def position
    first_cell = self.Range.Cells(1,1)
    @position = [first_cell.Row, first_cell.Column]
  end

  def == other_table
    other_table.is_a?(ListObject) && 
      self.Range.Address == other_table.Range.Address &&
      self.Parent.to_reo == other_table.Parent.to_reo
  end

  # @private
  def workbook
    @workbook ||= ole_table.Parent.Parent.to_reo
  end

  # @private
  # returns true, if the list object responds to VBA methods, false otherwise
  def alive?
    @ole_table.ListRows
    true
  rescue
    # trace $!.message
    false
  end

  # @private
  def to_s    
    @ole_table.Name.to_s
  end

  # @private
  def inspect    
    "#<ListObject:#{@ole_table.Name}" + 
    " #{@ole_table.ListRows.Count}x#{@ole_table.ListColumns.Count}" +
    " #{@ole_table.Parent.Name} #{@ole_table.Parent.Parent.Name}>"
  end

  include MethodHelpers

private

  def method_missing(name, *args) 
    super unless name.to_s[0,1] =~ /[A-Z]/
    if ::ERRORMESSAGE_JRUBY_BUG
      begin
        @ole_table.send(name, *args)
      rescue Java::OrgRacobCom::ComFailException 
        raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
      end
    else
      begin
        @ole_table.send(name, *args)
      rescue NoMethodError 
        raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
      end
    end
  end
end

#each ⇒ Enumerator

Returns traversing all list row objects.

Returns:

  • (Enumerator) —

    traversing all list row objects



94
95
96
97
98
99
100
101
102
 
# File 'lib/robust_excel_ole/list_object.rb', line 94

def each
  if block_given?
    @ole_table.ListRows.lazy.each do |ole_listrow|
      yield @row_class.new(ole_listrow)
    end
  else
    to_enum(:each).lazy
  end
end

#find_cells(value) ⇒ Array

finds all cells containing a given value @param value to find

Returns:

  • (Array) —

    win32ole cells containing the given value



343
344
345
346
347
348
349
350
351
352
 
# File 'lib/robust_excel_ole/list_object.rb', line 343

def find_cells(value)
  encode_utf8 = ->(val) {val.respond_to?(:gsub) ? val.encode('utf-8') : val}   
  listrows = @ole_table.ListRows   
  listrows.map { |listrow|
    listrow_range = listrow.Range
    listrow_range.Value.first.map{ |v| encode_utf8.(v) }.find_all_indices(value).map do |col_number| 
      listrow_range.Cells(1,col_number+1).to_reo 
    end
  }.flatten
end

#position ⇒ Array

Returns position of the first cell of the table.

Returns:

  • (Array) —

    position of the first cell of the table



366
367
368
369
 
# File 'lib/robust_excel_ole/list_object.rb', line 366

def position
  first_cell = self.Range.Cells(1,1)
  @position = [first_cell.Row, first_cell.Column]
end

#sort(column_number_or_name, sort_order = :ascending) ⇒ Object

sorts the rows of the list object according to the given column

Parameters:

  • column (Variant) —

    number or name

  • opts (Hash) —

    a customizable set of options



357
358
359
360
361
362
363
 
# File 'lib/robust_excel_ole/list_object.rb', line 357

def sort(column_number_or_name, sort_order = :ascending)
  key_range = @ole_table.ListColumns.Item(column_number_or_name).Range
  @ole_table.Sort.SortFields.Clear
  sort_order_option = sort_order == :ascending ? XlAscending : XlDescending
  @ole_table.Sort.SortFields.Add(key_range, XlSortOnValues,sort_order_option,XlSortNormal)
  @ole_table.Sort.Apply
end