=begin
To add:
Hasheesh - Done
Input multidimensional array - Done
Allow reordering of columns and rows
Allow row / col append
Allow row / col insertion
Allow row / col deletion
Allow export to TSV - Done
Allow export to MultiD Array - Done
Allow mass strip / upcase
Allow "removal" of empty cols / rows (compact) - Done
Count cols n rows - Done
Select column(s) based on header(s) 
Skip headers when parsing the contents -> .rows(2) do - Done
Get row by header (title) and lookup key
Get value by val_header, lookup_header, and lookup_key
Find a value and return its address - Done
Filter the data to a header and regex (true/false) - Done
Unique a column when given a header
=end

class Regexp
  def to_proc
    proc { |s| self =~ s }
  end
end

class String
  def row
    self[/\d+/]
  end
  
  def column
    self[/[A-Z]+/]
  end
end

class RubyExcel
  
  ColumnRefs = ('A'..'ZZZ').to_a
  
  attr_reader :maxcolumn, :maxrow
  
  def initialize
    @maxrow = 0
    @maxcolumn = 0
    self
  end
  
  def []( addr )
    data[ addr.upcase ]
  end

  def []=( addr, val )
    fail ArgumentError 'Invalid address' if addr !~ /^[A-Z]+\d+$/ || addr[ /[A-Z]+/ ] > 'ZZZ'
    val.nil? ? data.delete( addr ) : data[ addr ] = val
    calc_dimensions
  end

  def row( row )
    Row.new( self, row )
  end

  def column( col )
    Column.new( self, col )
  end

  def load ( multi_array )
    @data = {}
    import_data multi_array, '1'
  end
  
  def append ( multi_array )
    import_data multi_array, ( maxrow + 1 ).to_s
  end
  
  def calc_dimensions
    @maxcolumn = calc_columns
    @maxrow = calc_rows
  end
  
  def calc_columns
    col_val( data.keys.map { |k| k[ /[A-Z]+/ ] }.max ) rescue 0
  end
  
  def calc_rows
    val = data.keys.map { |k| k[ /\d+/ ].to_i }.max
    val ||= 0
  end
  
  def col_val ref
    ref.class == Fixnum ? ColumnRefs[ ref - 1 ] : ColumnRefs.index( ref ) + 1
  end
  
  def find val
    data.key val
  end
  
  def find_all &block
    ( data.select &block ).keys
  end
  
  def filter( header )
    fail ArgumentError 'No block given' unless block_given?
    header_address = find( header )
    search_col = column( header_address.column )
    remove_rows = []
    
    search_col.each_with_index { |cell, idx| remove_rows << ( idx + 1 ).to_i unless ( yield cell ) || ( idx < header_address.row.to_i ) }
    data.keys.each { |k| data[k] = nil if remove_rows.include? k.row.to_i }
    compact_rows
    self
  end
  
  def compact_rows
    until rows.all? { |r| !r.empty? }
      rows.each do |r|
        if r.empty?
          r.each_with_address do |val, key|
            self[ key ], self[ key.column + key.row.next ] = self[ key.column + key.row.next ], nil
          end
        end
        calc_dimensions
      end
    end
    self
  end
  
  def compact_columns
    until columns.all? { |c| !c.empty? }
      columns.each do |c|
        if c.empty?
          c.each_with_address do |val, key|
            self[ key ], self[ key.column.next + key.row ] = self[ key.column.next + key.row ], nil
          end
        end
        calc_dimensions
      end
    end
    self
  end

  def compact
    compact_rows
    compact_columns
  end
  
  def to_a
    fail NoMethodError, 'No data found' if maxrow == 0
    rows.map do |row|
      row.to_a
    end
  end
  
  def to_s
    to_a.map { |ar| ar.map { |el| "#{el}".strip.gsub( /\s/, ' ' ) }.join "\t" }.join $/
  end
  
  def rows( start_row = 1, end_row = maxrow )
  
    fail TypeError, 'Data is empty' if maxrow == 0
    fail ArgumentError, 'The starting row must be less than the maximum row' if maxrow < start_row
    
    return to_enum(:rows, start_row, end_row) unless block_given?
    ( start_row..end_row ).each do |idx|
      yield row( idx )
    end
    
    self
  end

  def columns( start_column = 'A', end_column = col_val( maxcolumn ) )
  
    start_column = col_val start_column if start_column.class == Fixnum
    end_column = col_val end_column if end_column.class == Fixnum
    fail ArgumentError, 'The columns must be sequential and within the data range' if maxcolumn < col_val( start_column ) || maxcolumn < col_val( end_column ) || start_column > end_column
    fail ArgumentError, 'The columns must be between A and ZZZ' unless ColumnRefs.include?( start_column ) && ColumnRefs.include?( end_column )
    
    return to_enum(:columns, start_column, end_column) unless block_given?
    ( start_column..end_column ).each do |idx|
      yield column( idx )
    end
    
    self
  end
  
  def inspect
    if maxrow == 0
      return 'empty'
    else
      return "columns: #{maxcolumn}\nrows: #{maxrow}\nvalues: #{data.values.count}"
    end
  end

  private

  def data
    @data ||= {}
  end

  def import_data( multi_array, r )
    fail ArgumentError 'Must be multidimensional Array' unless multi_array.all? { |el| el.class == Array }
    multi_array.each { |ar| c = 'A';  ar.each { |el| data[ c + r ] = el; c.next! }; r.next! }
    calc_dimensions
    self
  end
  
end

class Section

  attr_reader :matrix

  def initialize( matrix )
    @matrix = matrix
  end

  def [](item)
    matrix[ indexes( item ) ]
  end

  def []=(item, val)
    matrix[ indexes( item ) ] = val
  end

  def inspect
    "Class: #{ self.class }, Index: #{ index }"
  end
  
  def empty?
    each { |val| return false unless val.nil? }
    true
  end
  
  def to_s
    delim = ( self.class == Row ? "\t" : "\n" )
    each.map{ |x| x }.join( delim )
  end
  
  include Enumerable

  def each
    return to_enum(:each) unless block_given?
    each_address do |col, row|
      yield matrix[ "#{col}#{row}" ]
    end
    self
  end
  
  def each_with_address
    each_address do |col, row|
      addr = "#{col}#{row}"
      yield matrix[ addr ], addr
    end
    self
  end

end

class Row < Section

  def initialize( matrix, row_id )
    @row_id = row_id.to_i
    super matrix
  end

  def index
    @row_id
  end
    
  private

  def indexes( col_id )
    "#{col_id}#{@row_id}"
  end

  def each_address
    ( 'A'..matrix.col_val( matrix.maxcolumn ) ).each { |col_id| yield "#{col_id}#{@row_id}" }
    self
  end

end

class Column < Section

  def initialize( matrix, col )
    @col_id = col
    super matrix
  end

  def index
    @col_id
  end
  
  private

  def indexes( row_id )
    "#{@col_id}#{row_id}"
  end

  def each_address
    matrix.maxrow.times { |row_id| yield "#{@col_id}#{row_id + 1}" }
    self
  end

end

if $0 == __FILE__
m = RubyExcel.new.load [ ['a','b'],['c','d'],['e','f'],['g','h'] ]
puts m
m.filter 'a', &/c|g/
puts m
m['E1'] = 1
p m
puts m
m.compact
puts m
end