class RobustExcelOle::AddressTool

Public Class Methods

new(address_string) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 6
def initialize(address_string)
  r1c1_letters = address_string.gsub(/[0-9]/,'')
  @row_letter = r1c1_letters[0..0]
  @col_letter = r1c1_letters[1..1]
end

Public Instance Methods

as_a1(address) click to toggle source

transformation from integer-range or r1c1.format to a1-format not implemented

# File lib/robust_excel_ole/address_tool.rb, line 25
def as_a1(address)
  transform_address(address, :a1)
end
as_integer_ranges(address) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 29
def as_integer_ranges(address)
  transform_address(address, :int_range)
end
as_r1c1(address) click to toggle source

valid address formats are:

r1c1-format: e.g. "Z3S1", "Z3S1:Z5S2", "Z[3]S1", "Z3S[-1]:Z[5]S1", "Z[3]", "S[-2]"
                   infinite ranges are not possible, e.g. "Z3:Z5", "S2:S5", "Z2", "S3", "Z[2]" 
integer_ranges-fromat: e.g. [3,1], [3,"A"], [3..5,1..2], [1..[2],[3]..8], [3..5, "A".."B"], 
                            [3..4, nil], [nil, 2..4], [2,nil], [nil,4]
a1-format: e.g. "A3", "A3:B5", "A:B", "3:5", "A", "3"
# File lib/robust_excel_ole/address_tool.rb, line 20
def as_r1c1(address)
  transform_address(address, :r1c1)
end

Private Instance Methods

analyze(comp, format) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 100
def analyze(comp, format)
  row_comp, col_comp = if format==:a1 
    [comp.gsub(/[A-Z]/,''), comp.gsub(/[0-9]/,'')]
  else
    a,b = comp.split(@row_letter)
    c,d = b.split(@col_letter)
    b.nil? ? ["",b] : (d.nil? ? [c,""] : [c,d])  
  end
  def s2n(s)
    s!="" ? (s[0] == "[" ? [s.gsub(/\[|\]/,'').to_i] : (s.to_i!=0 ? s.to_i : s)) : nil
  end
  [s2n(row_comp), s2n(col_comp)]
end
r1c1_string(letter, int_range,type) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 92
def r1c1_string(letter, int_range,type)
  return "" if int_range.nil? || int_range.begin.nil?
  parameter = type == :min ? int_range.begin : int_range.end
  is_relative = parameter.is_a?(Array)
  parameter = parameter.first if is_relative
  letter + (is_relative ? "(" : "") + parameter.to_s + (is_relative ? ")" : "")       
end
rows_and_columns(address, format) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 55
def rows_and_columns(address, format)
  if address.size != 1
    address_comp1, address_comp2 = address      
  else
    comp1, comp2 = address[0].split(':')
    a1_expr = /^(([A-Z]+[0-9]+)|([A-Z]+$)|([0-9]+))$/
    is_a1 = comp1 =~ a1_expr && (comp2.nil? || comp2 =~ a1_expr)
    r1c1_expr = /^(([A-Z]\[?-?[0-9]+\]?[A-Z]\[?-?[0-9]+\]?)|([A-Z]\[?-?[0-9]+\]?)|([A-Z]\[?-?[0-9]+\]?))$/
    is_r1c1 = comp1 =~ r1c1_expr && (comp2.nil? || comp2 =~ r1c1_expr) && (not is_a1) 
    raise AddressInvalid, "address #{address.inspect} not in A1- or r1c1-format" unless (is_a1 || is_r1c1)
    raise AddressAlreadyInRightFormat if (is_a1 && format==:a1) || (is_r1c1 && format==:r1c1)   
    given_format = (is_a1) ? :a1 : :r1c1
    row_comp1, col_comp1 = analyze(comp1,given_format)
    row_comp2, col_comp2 = analyze(comp2,given_format) unless comp2.nil?
    address_comp1 = comp2 && (not row_comp1.nil?) ? (row_comp1 .. row_comp2) : row_comp1
    address_comp2 = comp2 && (not col_comp1.nil?) ? (col_comp1 .. col_comp2) : col_comp1          
  end
  address_comp1 = address_comp1..address_comp1 if (address_comp1.is_a?(Integer) || address_comp1.is_a?(String) || address_comp1.is_a?(Array))
  address_comp2 = address_comp2..address_comp2 if (address_comp2.is_a?(Integer) || address_comp2.is_a?(String) || address_comp2.is_a?(Array))
  raise AddressInvalid, "address contains row 0" if !address_comp1.nil? && address_comp1.begin == 0
  rows = address_comp1.begin..address_comp1.end unless address_comp1.nil? || address_comp1.begin == 0
  columns = unless address_comp2.nil?
    if address_comp2.begin.is_a?(String) #address_comp2.begin.to_i == 0
      col_range = str2num(address_comp2.begin)..str2num(address_comp2.end)
      col_range==(0..0) ? nil : col_range
    else
      address_comp2.begin..address_comp2.end
    end          
  end
  [rows, columns]
rescue
  raise AddressInvalid, "address (#{address.inspect}) format not correct"
end
s2n(s) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 108
def s2n(s)
  s!="" ? (s[0] == "[" ? [s.gsub(/\[|\]/,'').to_i] : (s.to_i!=0 ? s.to_i : s)) : nil
end
str2num(str) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 114
def str2num(str)
  str.tr("A-Z","0-9A-P").to_i(26) + (26**str.size-1)/25
end
transform_address(address, format) click to toggle source
# File lib/robust_excel_ole/address_tool.rb, line 35
def transform_address(address, format)
  address = address.is_a?(Array) ? address : [address]
  raise AddressInvalid, "address #{address.inspect} has more than two components" if address.size > 2
  rows, columns = begin
    rows_and_columns(address, format)
  rescue AddressInvalid
    raise
  rescue AddressAlreadyInRightFormat
    return address[0].gsub('[','(').gsub(']',')') 
  end
  if format == :int_range
    [rows,columns]
  elsif format == :r1c1
    r1c1_string(@row_letter,rows,:min) + r1c1_string(@col_letter,columns,:min) + ":" + 
    r1c1_string(@row_letter,rows,:max) + r1c1_string(@col_letter,columns,:max)
  else
    raise NotImplementedREOError, "not implemented"
  end
end