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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
 
# 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

    # returns the value of the cell with given column name or number
    # @param [Variant]  column number or column name
    # @return [Variant] value of the cell 
    def [] column_number_or_name
      begin
        ole_cell = @@ole_table.Application.Intersect(
          @ole_listrow.Range, @@ole_table.ListColumns.Item(column_number_or_name).Range)
        ole_cell.Value
      rescue WIN32OLERuntimeError
        raise TableRowError, "could not determine the value at column #{column_number_or_name}"
      end
    end


    def []=(column_number_or_name, value)
      begin
        ole_cell = @@ole_table.Application.Intersect(
          @ole_listrow.Range, @@ole_table.ListColumns.Item(column_number_or_name).Range)
        ole_cell.Value = value
      rescue WIN32OLERuntimeError
        raise TableRowError, "could not assign value #{value.inspect} to cell at column #{column_number_or_name}"
      end
    end

    # values of the row
    # @return [Array] values of the row
    def values
      begin
        @ole_listrow.Range.Value.first
      rescue WIN32OLERuntimeError
        raise TableError, "could not read values"
      end
    end

    # sets the values of the row
    # @param [Array] values of the row
    def set_values values
      begin
        updated_values = self.values
        updated_values[0,values.length] = values
        @ole_listrow.Range.Value = [updated_values]
        values
      rescue WIN32OLERuntimeError
        raise TableError, "could not set values #{values.inspect}"
      end
    end

    # deletes the values of the row
    def delete_values
      begin
        @ole_listrow.Range.Value = [[].fill(nil,0..(@@ole_table.ListColumns.Count)-1)]
        nil
      rescue WIN32OLERuntimeError
        raise TableError, "could not delete values"
      end
    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.Item(column_name).Range)
        define_getting_setting_method(ole_cell,name.to_s)            
        self.send(name, *args)
      else
        super
      end
    end

  private

    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 



366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
 
# File 'lib/robust_excel_ole/list_object.rb', line 366

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



152
153
154
 
# File 'lib/robust_excel_ole/list_object.rb', line 152

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

#add_column(column_name = nil, position = nil, contents = nil) ⇒ Object

adds a column

Parameters:

  • name (String) —

    of the column

  • position (Integer) (defaults to: nil) —

    of the new column

  • values (Array) —

    of the column



183
184
185
186
187
188
189
190
191
 
# File 'lib/robust_excel_ole/list_object.rb', line 183

def add_column(column_name = nil, position = nil, contents = nil)
  begin
    new_column = @ole_table.ListColumns.Add(position)
    new_column.Name = column_name if column_name
    set_column_values(column_name, contents) if contents
  rescue WIN32OLERuntimeError, TableError
    raise TableError, ("could not add column"+ ("at position #{position.inspect} with name #{column_name.inspect}" if position))
  end
end

#add_row(position = nil, contents = nil) ⇒ Object

adds a row

Parameters:

  • position (Integer) (defaults to: nil) —

    of the new row

  • values (Array) —

    of the column



170
171
172
173
174
175
176
177
 
# File 'lib/robust_excel_ole/list_object.rb', line 170

def add_row(position = nil, contents = nil)
  begin
    @ole_table.ListRows.Add(position)
    set_row_values(position, contents) if contents
  rescue WIN32OLERuntimeError
    raise TableError, ("could not add row" + (" at position #{position.inspect}" if position))
  end
end

#column_names ⇒ Array

Returns a list of column names.

Returns:

  • (Array) —

    a list of column names



159
160
161
162
163
164
165
 
# File 'lib/robust_excel_ole/list_object.rb', line 159

def column_names
  begin
    @ole_table.HeaderRowRange.Value.first
  rescue WIN32OLERuntimeError
    raise TableError, "could not determine column names"
  end
end

#column_values(column_number_or_name) ⇒ Array

Returns contents of a column.

Returns:

  • (Array) —

    contents of a column



273
274
275
276
277
278
279
 
# File 'lib/robust_excel_ole/list_object.rb', line 273

def column_values(column_number_or_name)
  begin
    @ole_table.ListColumns.Item(column_number_or_name).Range.Value[1,@ole_table.ListRows.Count].flatten
  rescue WIN32OLERuntimeError
    raise TableError, "could not read the values of column #{column_number_or_name.inspect}"
  end
end

#delete_column(column_number_or_name) ⇒ Object

deletes a column

Parameters:

  • column (Variant) —

    number or column name



205
206
207
208
209
210
211
 
# File 'lib/robust_excel_ole/list_object.rb', line 205

def delete_column(column_number_or_name)
  begin
    @ole_table.ListColumns.Item(column_number_or_name).Delete
  rescue WIN32OLERuntimeError
    raise TableError, "could not delete column #{column_number_or_name.inspect}"
  end
end

#delete_column_values(column_number_or_name) ⇒ Object

deletes the contents of a column

Parameters:

  • column (Variant) —

    number or column name



226
227
228
229
230
231
232
233
234
 
# File 'lib/robust_excel_ole/list_object.rb', line 226

def delete_column_values(column_number_or_name)
  begin
    column_name = @ole_table.ListColumns.Item(column_number_or_name).Range.Value.first
    @ole_table.ListColumns.Item(column_number_or_name).Range.Value = [column_name] + [].fill([nil],0..(@ole_table.ListRows.Count-1))
    nil
  rescue WIN32OLERuntimeError
    raise TableError, "could not delete contents of column #{column_number_or_name.inspect}"
  end
end

#delete_empty_columns ⇒ Object

deletes columns that have an empty contents



312
313
314
315
316
317
318
319
320
321
322
323
324
325
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
 
# File 'lib/robust_excel_ole/list_object.rb', line 312

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 = 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)
    listrows = @ole_table.ListRows      
    result = []
    (1..listrows.Count).each do |row_number|
      row_values(row_number).find_each_index(value).each do |col_number|
        result << @ole_table.Application.Intersect(listrows.Item(row_number).Range, 
                                                   @ole_table.ListColumns.Item(col_number+1).Range).to_reo
      end
    end
    result
  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

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

private

  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
end

#delete_empty_rows ⇒ Object

deletes rows that have an empty contents



297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
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
 
# File 'lib/robust_excel_ole/list_object.rb', line 297

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

    # deletes columns that have an empty contents
    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 = 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)
      listrows = @ole_table.ListRows      
      result = []
      (1..listrows.Count).each do |row_number|
        row_values(row_number).find_each_index(value).each do |col_number|
          result << @ole_table.Application.Intersect(listrows.Item(row_number).Range, 
                                                     @ole_table.ListColumns.Item(col_number+1).Range).to_reo
        end
      end
      result
    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

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

  private

    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
  end

  # @private
  class TableError < WorksheetREOError
  end

  # @private
  class TableRowError < WorksheetREOError
  end

  Table = ListObject
  TableRow = ListRow

end

#delete_row(row_number) ⇒ Object

deletes a row

Parameters:

  • position (Integer) —

    of the old row



195
196
197
198
199
200
201
 
# File 'lib/robust_excel_ole/list_object.rb', line 195

def delete_row(row_number)
  begin
    @ole_table.ListRows.Item(row_number).Delete
  rescue WIN32OLERuntimeError
    raise TableError, "could not delete row #{row_number.inspect}"
  end
end

#delete_row_values(row_number) ⇒ Object

deletes the contents of a row

Parameters:

  • row (Integer) —

    number



215
216
217
218
219
220
221
222
 
# File 'lib/robust_excel_ole/list_object.rb', line 215

def delete_row_values(row_number)
  begin
    @ole_table.ListRows.Item(row_number).Range.Value = [[].fill(nil,0..(@ole_table.ListColumns.Count-1))]
    nil
  rescue WIN32OLERuntimeError
    raise TableError, "could not delete contents of row #{row_number.inspect}"
  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



329
330
331
332
333
334
335
336
337
338
339
 
# File 'lib/robust_excel_ole/list_object.rb', line 329

def find_cells(value)
  listrows = @ole_table.ListRows      
  result = []
  (1..listrows.Count).each do |row_number|
    row_values(row_number).find_each_index(value).each do |col_number|
      result << @ole_table.Application.Intersect(listrows.Item(row_number).Range, 
                                                 @ole_table.ListColumns.Item(col_number+1).Range).to_reo
    end
  end
  result
end

#rename_column(name_or_number, new_name) ⇒ Object

renames a row

Parameters:

  • previous (String) —

    name or number of the column

  • new (String) —

    name of the column



239
240
241
242
243
244
245
 
# File 'lib/robust_excel_ole/list_object.rb', line 239

def rename_column(name_or_number, new_name)
  begin
    @ole_table.ListColumns.Item(name_or_number).Name = new_name
  rescue
    raise TableError, "could not rename column #{name_or_number.inspect} to #{new_name.inspect}"
  end
end

#row_values(row_number) ⇒ Array

contents of a row

Parameters:

  • row (Integer) —

    number

Returns:

  • (Array) —

    contents of a row



250
251
252
253
254
255
256
 
# File 'lib/robust_excel_ole/list_object.rb', line 250

def row_values(row_number)
  begin
    @ole_table.ListRows.Item(row_number).Range.Value.first
  rescue WIN32OLERuntimeError
    raise TableError, "could not read the values of row #{row_number.inspect}"
  end
end

#set_column_values(column_number_or_name, values) ⇒ Object

sets the contents of a column

Parameters:

  • column (Integer) —

    name or column number

  • contents (Array) —

    of the column



284
285
286
287
288
289
290
291
292
293
294
 
# File 'lib/robust_excel_ole/list_object.rb', line 284

def set_column_values(column_number_or_name, values)
  begin
    updated_values = column_values(column_number_or_name)
    updated_values[0,values.length] = values
    column_name = @ole_table.ListColumns.Item(column_number_or_name).Range.Value.first
    @ole_table.ListColumns.Item(column_number_or_name).Range.Value = column_name + updated_values.map{|v| [v]}
    values
  rescue WIN32OLERuntimeError
    raise TableError, "could not read the values of column #{column_number_or_name.inspect}"
  end
end

#set_row_values(row_number, values) ⇒ Object

sets the contents of a row

Parameters:

  • row (Integer) —

    number

  • values (Array) —

    of the row



261
262
263
264
265
266
267
268
269
270
 
# File 'lib/robust_excel_ole/list_object.rb', line 261

def set_row_values(row_number, values)
  begin
    updated_values = row_values(row_number)
    updated_values[0,values.length] = values
    @ole_table.ListRows.Item(row_number).Range.Value = [updated_values]
    values
  rescue WIN32OLERuntimeError
    raise TableError, "could not set the values of row #{row_number.inspect}"
  end
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



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

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