=begin
                  CODE KITTY JUDGES YOU!
              .__....._             _.....__,
                 .": o :':         ;': o :".
                 `. `-' .'.       .'. `-' .'  
                   `---'             `---' 

         _...----...      ...   ...      ...----..._
      .-'__..-""'----    `.  `"`  .'    ----'""-..__`-.
     '.-'   _.--"""'       `-._.-'       '"""--._   `-.`
     '  .-"'                  :                  `"-.  `
       '   `.              _.'"'._              .'   `
             `.       ,.-'"       "'-.,       .'
               `.                           .'
                 `-._                   _.-'
                     `"'--...___...--'"`
=end

=begin
To add:
Hasheesh - Done
Input multidimensional array - Done
Allow reordering of columns and rows -> delete and insert - Done
Allow row / col insertion - Done
Allow row / col deletion - Done
Allow export to TSV - Done
Allow export to MultiD Array - Done
Allow mass strip / upcase - Done
Allow "removal" of empty cols / rows (compact) - Done
Count cols n rows - Done
Select column(s) based on header(s) -> columns.reverse_each { |c| m.delete_column( c.index ) unless c[1] =~ /criteria/ } - Done
Skip headers when parsing the contents -> .rows(2) do - Done
Get row by header (title) and lookup key - This can now be (sort of) done: m.find 'Value' => address; RubyExcel.row( address ) => row_number; m.row( row_number )
Get value by val_header, lookup_header, and lookup_key -> m.find_all { |k,v| k =~ /^B\d/ && v =~ /a/ } => array of matching addresses - Done
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 - Done
Add an option to get a list of all populated cells - Done
=end

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

#.each &method( :puts )

class RubyExcel
  
  Me = RubyExcel
  ColumnRefs = ('A'..'ZZZ').to_a
  
  def self.row( string )
    string[/\d+/]
  end
  
  def self.column( string )
    "#{string}"[/[A-Z]+/]
  end
  
  def self.multi_array? ar
    ar.all? { |el| el.class == Array } 
  end
  
  def self.col_val ref
    ref.class == Fixnum ? ColumnRefs[ ref - 1 ] : ColumnRefs.index( ref ) + 1
  end
  
  attr_reader :maxcolumn, :maxrow
  
  def initialize
    @maxrow = 0
    @maxcolumn = 0
  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
  end
  
  def append ( multi_array )
    import_data multi_array, ( maxrow + 1 ).to_s
  end
  
  def insert_columns( multi_array, before )
    fail ArgumentError 'Must be multidimensional Array' unless Me.multi_array? multi_array
    steps = calc_columns multi_array
    columns( before ).reverse_each do |c|
      c.each do |v,k|
        new_col = Me.column k
        steps.times { new_col.next! }
        self[ new_col + Me.row( k ) ] = v
        self[ k ] = nil
      end
    end
    import_data multi_array, 1.to_s, before
  end
  
  def insert_rows( multi_array, before )
    fail ArgumentError 'Must be multidimensional Array' unless Me.multi_array? multi_array
    steps = calc_rows multi_array
    rows( before ).reverse_each do |r|
      r.each do |v,k|
        new_row = Me.row( k ).to_s
        steps.times { new_row.next! }
        self[ Me.column( k ) + new_row ] = v
        self[ k ] = nil
      end
    end
    import_data multi_array, before.to_s
  end
  
  def delete_column string
    column( string ).addresses.each { |addr| self[ addr ] = nil }
    compact_columns columns( string )
  end
  
  def delete_row number
    row( number ).addresses.each { |addr| self[ addr ] = nil }
    compact_rows rows( number ) unless number > maxrow
  end
    
  def find val
    data.key val
  end
  
  def find_all &block #This provides |key, value| to the block and returns the keys
    ( data.select &block ).keys
  end
  
  def cells
    data.keys.sort
  end
  
  def filter( header, &block )
    header_address = find( header )
    fail ArgumentError, "#{header} not found" if header_address.nil?
    search_col = column( Me.column header_address )
    remove_rows = []
    search_col.each do |cell, addr| 
      remove_rows << Me.row( addr ).to_i unless ( yield cell ) || ( Me.row( addr ).to_i <= Me.row( header_address ).to_i ) 
      end
    data.keys.each { |k| data[ k ] = nil if remove_rows.include? Me.row( k ).to_i }
    compact_rows
    self
  end
  
  def compact_rows( target = rows )
    idx = []
    target.each { |r| idx << r.index }
    until rows( idx.first, idx.last ).all? { |r| !r.empty? }
      rows( idx.first, idx.last ).each do |r|
        if r.empty?
          r.each do |val, key|
            self[ key ] = self[ Me.column( key ) + Me.row( key ).next ]
            self[ Me.column( key ) + Me.row( key ).next ] = nil
          end
        end
        calc_dimensions
        idx.pop if idx.last == r.index
      end
    end
    self
  end
  
  def compact_columns( target = columns )
    idx = []
    target.each { |c| idx << c.index }
    until columns( idx.first, idx.last ).all? { |c| !c.empty? }
      columns( idx.first, idx.last ).each do |c|
        if c.empty?
          c.each do |val, key|
            self[ key ] = self[ Me.column( key ).next + Me.row( key ) ]
            self[ Me.column( key ).next + Me.row( key ) ] = nil
          end
        end
        calc_dimensions
        idx.pop if idx.last == c.index
      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\nStart: #{start_row}\nEnd: #{end_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 = Me.col_val( maxcolumn ) )
  
    start_column = Me.col_val start_column if start_column.class == Fixnum
    end_column = Me.col_val end_column if end_column.class == Fixnum
    fail ArgumentError, 'The columns must be sequential and within the data range' if maxcolumn < Me.col_val( start_column ) || maxcolumn < Me.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 unique( header )
    fail ArgumentError, "Invalid Header: #{header}" unless ( location = find( header ) )
    target = column( Me.column( location ) )
    target.reverse_each do |value, address|
      if ( t = target.values ) != t.uniq
        if t.count { |val| val == value } > 1
          delete_row( Me.row( address ).to_i ) if target.values.count { |val| "#{val}" == "#{val}" }
          unique( header ) #Recurse!
        end
      else
        compact_rows
        return self
      end
    end
    self
  end
  
  def inspect
    if maxrow == 0
      'empty'
    else
      "#{self.class}\ncolumns: #{maxcolumn}\nrows: #{maxrow}\nvalues: #{data.values.count}\n"
    end
  end
  
  def strip!
    data.each_value { |v| v.strip! if v.class == String }
    self
  end
  
  def upcase!
    data.each_value { |v| v.upcase! if v.class == String }
    self
  end

  private

  def data
    @data ||= {}
  end

  def import_data( multi_array, start_row = '1', start_col = 'A' )
    fail ArgumentError 'Must be multidimensional Array' unless Me.multi_array? multi_array
    r = start_row.dup
    p multi_array
    multi_array.each { |ar| c = start_col.dup;  ar.each { |el| data[ c + r ] = el; c.next! }; r.next! }
    calc_dimensions
    self
  end
  
  def calc_dimensions
    @maxcolumn = calc_columns
    @maxrow = calc_rows
  end
  
  def calc_columns( multi_array = nil )
    if multi_array
      steps = multi_array.max_by( &:length ).length
    else
      Me.col_val( data.keys.map { |k| k[ /[A-Z]+/ ] }.max ) rescue 0
    end
  end
  
  def calc_rows( multi_array = nil )
    if multi_array
      multi_array.length
    else
      val = data.keys.map { |k| k[ /\d+/ ].to_i }.max
      val ||= 0
    end
  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 }\n"
  end
  
  def empty?
    each { |val| return false unless val.nil? }
    true
  end
  
  def to_a
    a = []
    each.map { |v,_| a << v }
    a
  end
  
  def to_s
    delim = ( self.class == Row ? "\t" : "\n" )
    to_a.join( delim )
  end

  include Enumerable

  def each
    return to_enum(:each) unless block_given?
    each_address do |addr|
      yield matrix[ addr ], addr
    end
    self
  end
  
  def reverse_each
    reverse_address do |addr|
      yield matrix[ addr ], addr
    end
    self
  end
  
  def addresses
    ary = []
    each_address { |addr| ary << addr }
    ary
  end
  
  def values
    ary = []
    each { |val, _| ary << val }
    ary
  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'..RubyExcel.col_val( matrix.maxcolumn ) ).each { |col_id| yield "#{col_id}#{@row_id}" }
    self
  end

  def reverse_address
    ( RubyExcel.col_val( matrix.maxcolumn )..'A' ).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
    1.upto( matrix.maxrow ) { |row_id| yield "#{@col_id}#{row_id}" }
    self
  end
  
  def reverse_address
    matrix.maxrow.downto(1) { |row_id| yield "#{@col_id}#{row_id}" }
    self
  end

end

if $0 == __FILE__
require 'pp'
def show_results( name, ob=nil )
  puts '-------------------'
  puts name
  ob = yield ob
  puts '* * * * * * * * * *'
  p ob
  puts '- - - - - - - - - -'
  puts ob
  ob
end
m = show_results( 'New:' ) { |a| RubyExcel.new.load [ ['a','b'],['c','d'],['e','f'],['g','h'] ] }
show_results( 'Filter header "a" to "c" and "g":', m ) { |a| a.filter 'a', &/c|g/ }
show_results( 'E1 = 1', m ) { |a| a['E1'] = 1; a }
show_results( 'Compact:', m ) { |a| a.compact }
show_results( 'Append data:', m ) { |a| a.append [ ['a','b'],['c','d'],['e','f'],['g','h'] ] }
show_results( 'Insert Columns:', m ) { |a| a.insert_columns [['z'],['y'],['x'],['w','v']], 'B' }
show_results( 'Insert Rows:', m ) { |a| a.insert_rows [['q'],['r'],['s'],['t','u']], 2 }
end