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_ole_listobject, table_name_or_number = "", position = [1,1], rows_count = 1, columns_count_or_names = 1) ⇒ ListObject

constructs a list object (or table).

Parameters:

  • worksheet_or_ole_listobject (Variable) —

    a worksheet or a Win32Ole list object

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

    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



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
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
92
93
94
95
96
97
 
# File 'lib/robust_excel_ole/list_object.rb', line 24

def initialize(worksheet_or_ole_listobject,
               table_name_or_number = "",
               position = [1,1],
               rows_count = 1, 
               columns_count_or_names = 1)
               
  if (worksheet_or_ole_listobject.ListRows rescue nil)
    @ole_table = worksheet_or_ole_listobject
  else
    @worksheet = worksheet_or_ole_listobject.to_reo
    @ole_table = @worksheet.ListObjects.Item(table_name_or_number) rescue nil
  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, "error #{$!.message}"
    end
  end

  ole_table = @ole_table
  @row_class = Class.new(ListRow) do

    @@ole_table = ole_table

    def initialize(row_number)
      @ole_listrow = @@ole_table.ListRows.Item(row_number)
    end
   
    def method_missing(name, *args)
      name_before_last_equal = name.to_s.split('=').first
      column_names = @@ole_table.HeaderRowRange.Value.first
      method_names = column_names.map{|c| c.underscore.gsub(/[^[\w\d]]/, '_')}
      column_name = column_names[method_names.index(name_before_last_equal)]
      if column_name
        ole_cell = @@ole_table.Application.Intersect(
          @ole_listrow.Range, @@ole_table.ListColumns(column_name).Range)
        define_getting_setting_method(ole_cell,name.to_s)            
        self.send(name, *args)
      else
        super
      end
    end

    def define_getting_setting_method(ole_cell,name_str)
      if name_str[-1] != '='
        self.class.define_method(name_str) do
          ole_cell.Value
        end
      else
        self.class.define_method(name_str) do |value|
          ole_cell.Value = value
        end
      end
    end
  end     

  # accesses a table row object
  # @param [Integer]  a row number (>= 1)
  # @return [ListRow] a object of dynamically constructed class with superclass ListRow 
  def [] row_number
    @row_class.new(row_number)
  end

end

Dynamic Method Handling

This class handles dynamic methods through the method_missing method

#method_missing(name, *args) ⇒ Object (private) 



101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
 
# File 'lib/robust_excel_ole/list_object.rb', line 101

def method_missing(name, *args) 
  if 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
  else
    super
  end
end

Instance Attribute Details

#ole_table ⇒ Object (readonly)

Returns the value of attribute ole_table



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

def ole_table
  @ole_table
end

Instance Method Details

#[](row_number) ⇒ ListRow

accesses a table row object

Parameters:

  • a (Integer) —

    row number (>= 1)

Returns:

  • (ListRow) —

    a object of dynamically constructed class with superclass ListRow



93
94
95
 
# File 'lib/robust_excel_ole/list_object.rb', line 93

def [] row_number
  @row_class.new(row_number)
end