# simple wrapper to read Excel Data

path = File.expand_path $0
path = File.dirname(path)


require 'Microsoft.Office.Interop.Excel, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'
include Microsoft::Office::Interop::Excel



class ExcelUtils
  def initialize( filename, visible=true, log=nil )
    @xls_filename = filename  
    @excel = nil
    @workbook = nil
    @worksheet = nil
    @bVisibe = visible
    @log = log
  end
  
  def log(message)
    if @log then
      @log.message(message)
    else
      puts message
    end
  end
  
  def close()
    @workbook.close(false)
    @excel.quit
    @excel = nil
    GC.start
    log("Closing Excel.....")
  end
  
  def open()
    if File::exists?( @xls_filename ) then 
      begin
        @excel = ApplicationClass.new
        @excel.Visible = @bVisibe
        @excel.DisplayAlerts = false
        @workbook = @excel.Workbooks.Open(@xls_filename)
        
        log("Opening Excel.....")
        
        #data = @worksheet.UsedRange.Value
				#num_rows = data.size
				#num_columns	= data[0].size
          
        #close()
      rescue Exception => ex
        puts ex
        close()
      end
    end
  end
  
  # will go through each column to find a value, returns the index
  def getNameInHeader( strvalue )
    puts strvalue
    validCol = nil
    data = @worksheet.UsedRange.Value
    puts data
    puts data[0].size
		num_columns	= data[0].size  

    num_columns.times do | cnt |
      #if @worksheet.rows(1).cells(cnt+1).value == strvalue then
      #  validCol =  cnt+1
      #  break
      #end
    end
    validCol
    
  end
  
  #return the indexs of sheets that contain a value in the header (1st cell)
  def getValidSheetsByHeader( strvalue )
    validSheets = Array.new

    @workbook.Worksheets.count.times do |cnt|
      @worksheet	= @workbook.Worksheets(cnt+1)								
      @worksheet.Activate
      
      #end
      if @worksheet.rows(1).cells(1).value == strvalue then
        validSheets.push(cnt+1)
      end      
    end
    return validSheets
  end
  
  
  # Return data from headers
  def getDatafromRowNeaders(colHeadersArray, sheetIndexs)
    # Validate
    
    WorkBookData = Hash.new
    if colHeadersArray.class == Array and sheetIndexs.class == Array then
      #sheetIndexs
      
      
      sheetIndexs.each do | sheetIndex |    
        # Get Header Index from active Sheet
        @worksheet	= @workbook.Worksheets(sheetIndex)								
        @worksheet.Activate
        
        

        
        xlRange = @worksheet.UsedRange
        cols = xlRange.Columns.Count;
        rows =  xlRange.Rows.Count;
        
        # if we for some reason have a million rows then limit to 1000
        if rows > 1000 then
          rows = 1000
        end
        
        puts rows
        
        # GET OUR VALID INDEX COLS WE NEED
        colIndexArray = Array.new
        xlRange.Columns.Count.times do |col|
          colHeadersArray.each do | name |
            if @worksheet.rows(1).cells(col+1).value == name then
              colIndexArray.push (col+1)          
            end
          end
        end
        
        # Read our Data
        log("Reading Sheet #{@worksheet.Name} Data")
        
        sheetData = Array.new        
        # Loop each Row
        # ignore 1st header row
        rows.times do | row |
          #if @worksheet.rows[row+2].cells(colIndexArray[0]).value != nil # Check if 1st col has valid data, if so colelct the rest
            #puts @worksheet.rows[row+2]
            rowData = Array.new
            colIndexArray.each do | col |       
              #if @worksheet.rows[row+2].cells(col).value != nil
                #puts @worksheet.rows[row+2].cells(col).value
                #puts @worksheet.rows[row+2].cells(col).value
                rowData << @worksheet.rows[row+2].cells(col).value
                
              #end
            end
            if not rowData.all? {|x| x.nil?}
              sheetData << rowData
            end
          #end
          
          
          
        end
        
        WorkBookData[@worksheet.Name.to_s] = sheetData
        
        
      end
      
      
    else
      log('One or more arguments are not Array Inputs')
    end
    WorkBookData
  end

end