class RobustExcelOle::Worksheet

worksheet: see github.com/Thomas008/robust_excel_ole/blob/master/lib/robust_excel_ole/worksheet.rb

Constants

ColorIndex
Name
Saved
Value

Attributes

ole_object[R]
ole_worksheet[R]
workbook[R]

Public Class Methods

listobject_class() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 399
def self.listobject_class  
  @listobject_class ||= begin
    module_name = self.parent_name
    "#{module_name}::ListObject".constantize        
  rescue NameError => e
    ListObject
  end
end
new(win32_worksheet) click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 22
def initialize(win32_worksheet)
  @ole_worksheet = win32_worksheet
  if @ole_worksheet.ProtectContents
    @ole_worksheet.Unprotect
    @end_row = last_row
    @end_column = last_column
    @ole_worksheet.Protect
  else
    @end_row = last_row
    @end_column = last_column
  end
end
workbook_class() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 384
def self.workbook_class  
  @workbook_class ||= begin
    module_name = self.parent_name
    "#{module_name}::Workbook".constantize        
  rescue NameError => e
    Workbook
  end
end

Public Instance Methods

==(other_worksheet) click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 332
def == other_worksheet
  other_worksheet.is_a?(Worksheet) && 
  self.workbook == other_worksheet.workbook &&
  self.Name == other_worksheet.Name
end
[](name_or_address, address2 = :__not_provided) click to toggle source

value of a range given its defined name or address @params [Variant] defined name or address @returns [Variant] value (contents) of the range

# File lib/robust_excel_ole/worksheet.rb, line 73
def [](name_or_address, address2 = :__not_provided)
  range(name_or_address, address2).value
end
[]=(name_or_address, value_or_address2, remaining_arg = :__not_provided) click to toggle source

sets the value of a range given its defined name or address, and the value @params [Variant] defined name or address of the range @params [Variant] value (contents) of the range @returns [Variant] value (contents) of the range

# File lib/robust_excel_ole/worksheet.rb, line 81
def []=(name_or_address, value_or_address2, remaining_arg = :__not_provided) 
  if remaining_arg != :__not_provided
    name_or_address, value = [name_or_address, value_or_address2], remaining_arg
  else
    value = value_or_address2
  end
  begin
    range(name_or_address).value = value
  rescue #WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    raise RangeNotEvaluatable, "cannot assign value to range with name or address #{name_or_address.inspect}\n#{$!.message}"
  end
end
alive?() click to toggle source

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

# File lib/robust_excel_ole/worksheet.rb, line 349
def alive?
  @ole_worksheet.UsedRange
  true
rescue
  # trace $!.message
  false
end
cellval(x,y) click to toggle source

value of a cell, if row and column are given @params row and column @returns value of the cell

# File lib/robust_excel_ole/worksheet.rb, line 211
def cellval(x,y)                         # :deprecated :#
  @ole_worksheet.Cells.Item(x, y).Value
rescue
  raise RangeNotEvaluatable, "cannot read cell (#{x.inspect},#{y.inspect})\n#{$!.message}"
end
col_range(col, integer_range = nil) click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 327
def col_range(col, integer_range = nil)
  integer_range ||= 1..@end_row
  RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(integer_range.min, col), @ole_worksheet.Cells(integer_range.max, col)), self)
end
each() { |nil? ? [] : first| ... } click to toggle source

@return [Enumerator] traversing the rows values

# File lib/robust_excel_ole/worksheet.rb, line 234
def each
  if block_given?
    @ole_worksheet.UsedRange.Rows.lazy.each do |ole_row|
      row_value = ole_row.Value
      yield (row_value.nil? ? [] : row_value.first)  
    end
  else
    to_enum(:each).lazy
  end
end
each_cell() { |cell| ... } click to toggle source

@return [Enumerator] traversing the cells

# File lib/robust_excel_ole/worksheet.rb, line 284
def each_cell
  if block_given?
    each_row do |row_range|
      row_range.lazy.each do |cell|
        yield cell
      end
    end
  else
    to_enum(:each_cell).lazy
  end
end
each_column(offset = 0) { |range(ole_worksheet.Range(ole_worksheet.Cells(1, column), ole_worksheet.Cells(end_row, column)), self)| ... } click to toggle source

@return [Enumerator] traversing the columns

# File lib/robust_excel_ole/worksheet.rb, line 265
def each_column(offset = 0)
  if block_given?
    offset += 1
    1.upto(@end_column) do |column|
      next if column < offset
      yield RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(1, column), @ole_worksheet.Cells(@end_row, column)), self)
    end
  else
    to_enum(:each_column).lazy
  end
end
each_row(offset = 0) { |range(ole_worksheet.Range(ole_worksheet.Cells(row, 1), ole_worksheet.Cells(row, end_column)), self)| ... } click to toggle source

@return [Enumerator] traversing the rows

# File lib/robust_excel_ole/worksheet.rb, line 246
def each_row(offset = 0)
  if block_given?
    offset += 1
    1.upto(@end_row) do |row|
      next if row < offset
      yield RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(row, 1), @ole_worksheet.Cells(row, @end_column)), self)
    end
  else
    to_enum(:each_row).lazy
  end
end
each_rowvalue() { |row_values| ... } click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 306
def each_rowvalue  # :deprecated: #
  values.each do |row_values|
    yield row_values
  end
end
Also aliased as: each_value
each_rowvalue_with_index(offset = 0) { |row_values, i| ... } click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 312
def each_rowvalue_with_index(offset = 0)    # :deprecated: #
  i = offset
  values.each do |row_values|
    yield row_values, i
    i += 1
  end
end
each_value()
Alias for: each_rowvalue
excel() click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 46
def excel
  workbook.excel
end
inspect() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 419
def inspect  
  to_s
end
last_column() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 374
def last_column
  special_last_column = @ole_worksheet.UsedRange.SpecialCells(RobustExcelOle::XlLastCell).Column
  used_last_column = @ole_worksheet.UsedRange.Columns.Count
  [special_last_column, used_last_column].max
end
last_row() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 367
def last_row
  special_last_row = @ole_worksheet.UsedRange.SpecialCells(RobustExcelOle::XlLastCell).Row
  used_last_row = @ole_worksheet.UsedRange.Rows.Count
  [special_last_row, used_last_row].max
end
listobject_class() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 409
def listobject_class        
  self.class.listobject_class
end
name() click to toggle source

sheet name @returns name of the sheet

# File lib/robust_excel_ole/worksheet.rb, line 52
def name
  @ole_worksheet.Name.encode('utf-8')
rescue
  raise WorksheetREOError, "name could not be determined\n#{$!.message}"
end
name=(new_name) click to toggle source

sets sheet name @param [String] new_name the new name of the sheet

# File lib/robust_excel_ole/worksheet.rb, line 60
def name= (new_name)
  @ole_worksheet.Name = new_name
rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException => msg
  if msg.message =~ /800A03EC/ || msg.message =~ /Visual Basic/
    raise NameAlreadyExists, "sheet name #{new_name.inspect} already exists"
  else
    raise UnexpectedREOError, "unexpected WIN32OLERuntimeError: #{msg.message}"
  end
end
namevalue(name, opts = { default: :__not_provided }) click to toggle source

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 @param [String] name the name of a range @param [Hash] opts the options @option opts [Symbol] :default the default value that is provided if no contents could be returned @return [Variant] the contents of a range with given name

# File lib/robust_excel_ole/worksheet.rb, line 151
def namevalue(name, opts = { default: :__not_provided })
  begin
    ole_range = self.Range(name)
  rescue # WIN32OLERuntimeError, VBAMethodMissingError, Java::OrgRacobCom::ComFailException
    return opts[:default] unless opts[:default] == :__not_provided
    raise NameNotFound, "name #{name.inspect} not in #{self.inspect}"
  end
  begin
    worksheet = self if self.is_a?(Worksheet)
    #value = ole_range.Value
    value = if !::RANGES_JRUBY_BUG
      ole_range.Value
    else
      values = RobustExcelOle::Range.new(ole_range, worksheet).v
      (values.size==1 && values.first.size==1) ? values.first.first : values
    end
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException 
    return opts[:default] unless opts[:default] == :__not_provided
    raise RangeNotEvaluatable, "cannot determine value of range named #{name.inspect} in #{self.inspect}\n#{$!.message}"
  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}\n#{$!.message}"
  end
  return opts[:default] unless (opts[:default] == :__not_provided) || value.nil?
  value
end
range(name_or_address, address2 = :__not_provided) click to toggle source

a range given a defined name or address @params [Variant] defined name or address @return [Range] a range

# File lib/robust_excel_ole/worksheet.rb, line 97
def range(name_or_address, address2 = :__not_provided)
  if name_or_address.respond_to?(:gsub) && address2 == :__not_provided
    name = name_or_address
    range = get_name_object(name).RefersToRange rescue nil
  end
  unless range
    address = normalize_address(name_or_address, address2)
    workbook.retain_saved do
      begin
        a1_address = address_tool.as_a1(address) rescue nil
        if a1_address
          range = self.Range(a1_address)              
        else
          saved = self.Parent.Saved
          begin
            self.Names.Add('__dummy_name_object_001__',nil,true,nil,nil,nil,nil,nil,nil,'=' + address_tool.as_r1c1(address))
            range = get_name_object('__dummy_name_object_001__').RefersToRange
          ensure
            self.Names.Item('__dummy_name_object_001__').Delete
            self.Parent.Saved = saved
          end
        end
      rescue
        address2_string = (address2.nil? || address2 == :__not_provided) ? "" : ", #{address2.inspect}"
        raise RangeNotCreated, "cannot find name or address #{name_or_address.inspect}#{address2_string})"
      end
    end
  end
  range.to_reo
end
row_range(row, integer_range = nil) click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 322
def row_range(row, integer_range = nil)
  integer_range ||= 1..@end_column
  RobustExcelOle::Range.new(@ole_worksheet.Range(@ole_worksheet.Cells(row, integer_range.min), @ole_worksheet.Cells(row, integer_range.max)), self)
end
set_cellval(x,y,value, opts = { }) click to toggle source

sets the value of a cell, if row, column and color of the cell are given @params [Integer] x,y row and column @option opts [Symbol] :color the color of the cell when set

# File lib/robust_excel_ole/worksheet.rb, line 220
def set_cellval(x,y,value, opts = { }) # option opts is deprecated
  cell = @ole_worksheet.Cells.Item(x, y)
  cell.Interior.ColorIndex = opts[:color] unless opts[:color].nil?
  cell.Value = value
rescue # WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
  raise RangeNotEvaluatable, "cannot assign value #{value.inspect} to cell (#{y.inspect},#{x.inspect})\n#{$!.message}"
end
set_namevalue(name, value, opts = { }) click to toggle source

assigns a value to a range given a locally defined name @param [String] name the name of a range @param [Variant] value the assigned value

@option opts [Symbol] :color the color of the cell when set

# File lib/robust_excel_ole/worksheet.rb, line 183
def set_namevalue(name, value, opts = { })  
  begin
    ole_range = self.Range(name)
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException, VBAMethodMissingError
    raise NameNotFound, "name #{name.inspect} not in #{self.inspect}"
  end
  begin
    ole_range.Interior.ColorIndex = opts[:color] unless opts[:color].nil?
    if !::RANGES_JRUBY_BUG
      ole_range.Value = value
    else
      address_r1c1 = ole_range.AddressLocal(true,true,XlR1C1)
      row, col = address_tool.as_integer_ranges(address_r1c1)
      row.each_with_index do |r,i|
        col.each_with_index do |c,j|
          ole_range.Cells(i+1,j+1).Value = (value.respond_to?(:pop) ? value[i][j] : value)
        end
      end
    end
    value
  rescue WIN32OLERuntimeError, Java::OrgRacobCom::ComFailException
    raise RangeNotEvaluatable, "cannot assign value to range named #{name.inspect} in #{self.inspect}\n#{$!.message}"
  end
end
table(number_or_name) click to toggle source

@params [Variant] table (listobject) name or number @return [ListObject] a table (listobject)

# File lib/robust_excel_ole/worksheet.rb, line 341
def table(number_or_name)
  listobject_class.new(@ole_worksheet.ListObjects.Item(number_or_name))
rescue
  raise WorksheetREOError, "table #{number_or_name} not found"
end
to_s() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 414
def to_s    
  "#<Worksheet: #{(workbook.nil? ? "not alive " : (name + ' ' + File.basename(workbook.stored_filename)))}>"
end
values() click to toggle source

@return [Array] a 2-dimensional array that contains the values in each row of the used range

# File lib/robust_excel_ole/worksheet.rb, line 229
def values
  @ole_worksheet.UsedRange.Value
end
workbook_class() click to toggle source

@private

# File lib/robust_excel_ole/worksheet.rb, line 394
def workbook_class        
  self.class.workbook_class
end

Private Instance Methods

method_missing(name, *args) click to toggle source
Calls superclass method
# File lib/robust_excel_ole/worksheet.rb, line 427
def method_missing(name, *args)
  super unless name.to_s[0,1] =~ /[A-Z]/
  if ::ERRORMESSAGE_JRUBY_BUG 
    begin
      @ole_worksheet.send(name, *args)
    rescue Java::OrgRacobCom::ComFailException 
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  else
    begin
      @ole_worksheet.send(name, *args)
    rescue NoMethodError 
      raise VBAMethodMissingError, "unknown VBA property or method #{name.inspect}"
    end
  end
end
normalize_address(address, address2) click to toggle source
# File lib/robust_excel_ole/worksheet.rb, line 130
def normalize_address(address, address2)
  address = [address,address2] unless address2 == :__not_provided     
  address = if address.is_a?(Integer) || address.is_a?(::Range)
    [address, nil]
  elsif address.is_a?(Array) && address.size == 1 && (address.first.is_a?(Integer) || address.first.is_a?(::Range))
    [address.first, nil]
  else 
    address
  end
end