class RobustExcelOle::Table

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

Constants

Name
Value

Attributes

ole_object[R]
ole_table[R]

Public Class Methods

new(worksheet_or_listobject, table_name_or_number = "_table_name", position = [1,1], rows_count = 1, columns_count_or_names = 1) click to toggle source

constructs a list object (or table). @param [Variable] worksheet_or_listobject a worksheet or a list object @param [Variable] table_name_or_number a table name or table number @param [Array] position a position of the upper left corner @param [Integer] rows_count number of rows @param [Variable] columns_count_or_names number of columns or array of column names @return [ListObject] a ListObject object

# File lib/robust_excel_ole/list_object.rb, line 26
    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

=begin

      ole_table = @ole_table

      @row_class = Class.new(ListRow) do

        @@ole_table = ole_table

        def ole_table
          @@ole_table
        end
      
      end      

    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
ole_table() click to toggle source
# File lib/robust_excel_ole/list_object.rb, line 95
def self.ole_table
  @ole_table
end
ole_table=(tab) click to toggle source
# File lib/robust_excel_ole/list_object.rb, line 99
def self.ole_table= tab
  @ole_table = tab
end

Public Instance Methods

==(other_table) click to toggle source
# File lib/robust_excel_ole/list_object.rb, line 382
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 = { }) click to toggle source

accesses a table row object @param [Variant] a hash of key (key column: value) or a row number (>= 1) @option opts [Variant] limit: maximal number of matching table rows to return, or return the first matching table row (default :first) @return [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
# File lib/robust_excel_ole/list_object.rb, line 126
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
add_column(column_name = nil, position = nil, contents = nil) click to toggle source

adds a column

@param [String] name of the column @param [Integer] position of the new column @param [Array] values of the column

# File lib/robust_excel_ole/list_object.rb, line 228
def add_column(column_name = nil, position = nil, contents = nil)
  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) + "\n#{$!.message}")
end
add_row(position = nil, contents = nil) click to toggle source

adds a row @param [Integer] position of the new row @param [Array] values of the column

# File lib/robust_excel_ole/list_object.rb, line 217
def add_row(position = nil, contents = nil)
  @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) + "\n#{$!.message}")
end
alive?() click to toggle source

@private returns true, if the list object responds to VBA methods, false otherwise

# File lib/robust_excel_ole/list_object.rb, line 391
def alive?
  @ole_table.ListRows
  true
rescue
  # trace $!.message
  false
end
column_names() click to toggle source

@return [Array] a list of column names

# File lib/robust_excel_ole/list_object.rb, line 199
def column_names
  @ole_table.HeaderRowRange.Value.first.map{|v| v.encode('utf-8')}
rescue WIN32OLERuntimeError
  raise TableError, "could not determine column names\n#{$!.message}"
end
column_names_to_index() click to toggle source

@return [Hash] pairs of column names and index

# File lib/robust_excel_ole/list_object.rb, line 206
def column_names_to_index
  header_row_values = @ole_table.HeaderRowRange.Value.first
  header_row_values.map{|v| v.encode('utf-8').downcase.to_sym}.zip(0..header_row_values.size-1).to_h
rescue WIN32OLERuntimeError
  raise TableError, "could not determine column names\n#{$!.message}"
end
column_values(column_number_or_name) click to toggle source

@return [Array] contents of a column

# File lib/robust_excel_ole/list_object.rb, line 302
def column_values(column_number_or_name)
  @ole_table.ListColumns.Item(column_number_or_name).Range.Value[1,@ole_table.ListRows.Count].flatten.map{|v| v.respond_to?(:gsub) ? v.encode('utf-8') : v}
rescue WIN32OLERuntimeError
  raise TableError, "could not read the values of column #{column_number_or_name.inspect}\n#{$!.message}"
end
delete_column_values(column_number_or_name) click to toggle source

deletes the contents of a column @param [Variant] column number or column name

# File lib/robust_excel_ole/list_object.rb, line 263
def delete_column_values(column_number_or_name)
  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}\n#{$!.message}"
end
delete_empty_columns() click to toggle source

deletes columns that have an empty contents

# File lib/robust_excel_ole/list_object.rb, line 337
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
delete_empty_rows() click to toggle source

deletes rows that have an empty contents

# File lib/robust_excel_ole/list_object.rb, line 322
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 += 1
    end
  end
end
delete_row_values(row_number) click to toggle source

deletes the contents of a row @param [Integer] row number

# File lib/robust_excel_ole/list_object.rb, line 254
def delete_row_values(row_number)
  @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}\n#{$!.message}"
end
each() { |row_class| ... } click to toggle source

@return [Enumerator] traversing all list row objects

# File lib/robust_excel_ole/list_object.rb, line 110
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) click to toggle source

finds all cells containing a given value @param value to find @return [Array] win32ole cells containing the given value

# File lib/robust_excel_ole/list_object.rb, line 354
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
inspect() click to toggle source

@private

# File lib/robust_excel_ole/list_object.rb, line 405
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
position() click to toggle source

@return [Array] position of the first cell of the table

# File lib/robust_excel_ole/list_object.rb, line 377
def position
  first_cell = self.Range.Cells(1,1)
  @position = [first_cell.Row, first_cell.Column]
end
row_values(row_number) click to toggle source

contents of a row @param [Integer] row number @return [Array] contents of a row

# File lib/robust_excel_ole/list_object.rb, line 283
def row_values(row_number)
  @ole_table.ListRows.Item(row_number).Range.Value.first.map{|v| v.respond_to?(:gsub) ? v.encode('utf-8') : v}
rescue WIN32OLERuntimeError
  raise TableError, "could not read the values of row #{row_number.inspect}\n#{$!.message}"
end
rows_number() click to toggle source

@return [Integer] number of rows

# File lib/robust_excel_ole/list_object.rb, line 189
def rows_number
  @ole_table.ListRows.Count
end
set_column_values(column_number_or_name, values) click to toggle source

sets the contents of a column @param [Integer] column name or column number @param [Array] contents of the column

# File lib/robust_excel_ole/list_object.rb, line 311
def set_column_values(column_number_or_name, values)
  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}\n#{$!.message}"
end
set_row_values(row_number, values) click to toggle source

sets the contents of a row @param [Integer] row number @param [Array] values of the row

# File lib/robust_excel_ole/list_object.rb, line 292
def set_row_values(row_number, values)
  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}\n#{$!.message}"
end
sort(column_number_or_name, sort_order = :ascending) click to toggle source

sorts the rows of the list object according to the given column @param [Variant] column number or name @option opts [Symbol] sort order

# File lib/robust_excel_ole/list_object.rb, line 368
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
to_s() click to toggle source

@private

# File lib/robust_excel_ole/list_object.rb, line 400
def to_s    
  @ole_table.Name.to_s
end
value() click to toggle source

@return [Array] values of the table

# File lib/robust_excel_ole/list_object.rb, line 194
def value
  [column_names] + self.DataBodyRange.Value
end

Private Instance Methods

matching_via_filter(key_hash, opts) click to toggle source
# File lib/robust_excel_ole/list_object.rb, line 155
def matching_via_filter(key_hash, opts)
  ole_worksheet = self.Parent
  ole_workbook =  ole_worksheet.Parent
  row_numbers = []
  ole_workbook.retain_saved do
    added_ole_worksheet = ole_workbook.Worksheets.Add
    criteria = Table.new(added_ole_worksheet, "criteria", [2,1], 2, key_hash.keys.map{|s| s.to_s})
    criteria[1].values = key_hash.values
    self.Range.AdvancedFilter({
      Action: XlFilterInPlace, 
      CriteriaRange: added_ole_worksheet.range([2..3,1..key_hash.length]).ole_range, Unique: false})
    filtered_ole_range = self.DataBodyRange.SpecialCells(XlCellTypeVisible) rescue nil         
    ole_worksheet.ShowAllData        
    self.Range.AdvancedFilter({Action: XlFilterInPlace, 
                               CriteriaRange: added_ole_worksheet.range([1,1]).ole_range, Unique: false})          
    ole_workbook.Parent.with_displayalerts(false){added_ole_worksheet.Delete}
    if filtered_ole_range
      filtered_ole_range.Areas.each do |area|
        break if area.Rows.each do |row|
          row_numbers << row.Row-position.first
          break true if row_numbers.count == opts[:limit]
        end
      end
    end          
    @ole_table = ole_worksheet.table(self.Name)
  end   
  row_numbers
rescue
  raise(TableError, "cannot find row with key #{key_hash}")
end
matching_via_traversing(key_hash, opts) click to toggle source
# File lib/robust_excel_ole/list_object.rb, line 143
def matching_via_traversing(key_hash, opts)      
   encode_utf8 = ->(val) {val.respond_to?(:gsub) ? val.encode('utf-8') : val}
   cn2i = column_names_to_index
   max_matching_num = opts[:limit] || 65536     
   matching_rows = @ole_table.ListRows.lazy.select { |listrow|
     rowvalues = listrow.Range.Value.first
     key_hash.all?{ |key,val| encode_utf8.(rowvalues[cn2i[key]])==val}
   }.take(max_matching_num).to_a
 rescue
   raise(TableError, "cannot find row with key #{key_hash}")
 end
method_missing(name, *args) click to toggle source
Calls superclass method
# File lib/robust_excel_ole/list_object.rb, line 415
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