require 'rubygems'
require 'parseexcel'

class Excel < Openoffice

  def initialize(filename)
    @workbook = Spreadsheet::ParseExcel.parse(filename)
    @default_sheet = nil
  end

  # TODO: waiting for
  # ich glaube, parseexcel kann keine namen der sheets ???
  def sheets
    if DateTime.now < Date.new(2007,6,10)
      return  ["Tabelle1", "Name of Sheet 2", "Sheet3"]
    else
      #worksheet = @workbook.worksheet(0)
      #  p @workbook
      # p @workbook.worksheet(0)
      ["aaa","bbb","ccc"]
    end
  end

  # sets the working sheet (1,2,3,..)
  #--
  # im Excel-Bereich muesste man wahrscheinlich intern mit Nummern arbeiten
  # von aussen arbeite ich mit (1,2,3... intern wird Index 0,1,2,...
  # verwendet.

  def default_sheet=(n)
    unless n.kind_of?(Fixnum)
      fail ArgumentError.new("Number expected")
    end
    @default_sheet = n-1
  end

  def cell(row,col)
    row,col = normalize(row,col)
    worksheet = @workbook.worksheet(@default_sheet)
    skip = 0
    line = 1
    worksheet.each(skip) { |row_par|
      if line == row
        if row_par == nil
          return nil
        end
        cell = row_par.at(col-1)
        # p "celltype: "
        # p cell.type
        return nil unless cell
        case cell.type
          when :numeric then return cell.to_i
          when :text then return cell.to_s('latin1')
          when :date then return cell.date
        else  
          return cell.to_s
        end
      end
      line += 1
    }
  end

  def celltype(row,col)
    row,col = normalize(row,col)
    worksheet = @workbook.worksheet(@default_sheet)
    skip = 0
    line = 1
    worksheet.each(skip) { |row_par|
      if line == row
        cell = row_par.at(col-1)
        return nil unless cell
        case cell.type
        when :numeric then return "float"
        when :text then return "string"
        when :date then return "date"
        else  return cell.type
        end
      end
      line += 1
    }
  end

  def row(rownumber)
    worksheet = @workbook.worksheet(@default_sheet)
    therow = worksheet.row(rownumber-1) 
    result = []
    therow.each {|cell|
      case cell.type
         when :numeric then result << cell.to_i
         when :text then result << cell.to_s('latin1')
         when :date then result << cell.date
      else
        return result << cell.to_s
      end

      #result << cell.value
    }
    return result
  end

  def first_column
    fr, lr, fc, lc = get_firsts_lasts
    fc
  end

  def last_column
    fr, lr, fc, lc = get_firsts_lasts
    lc
  end

  def first_row
    fr, lr, fc, lc = get_firsts_lasts
    fr
  end

  def last_row
    fr, lr, fc, lc = get_firsts_lasts
    lr
  end

  def empty?(row, col)
    row,col = normalize(row,col)
    return true if row < first_row || row > last_row || col < first_column || col > last_column
    # read_cells unless @cells_read
    return true unless cell(row, col)
    # p celltype(row,col)
    #p cell(row,col)
    return true if celltype(row, col) == "string" && cell(row, col) == ""
          #when :text then return cell.to_s('latin1')
    # p celltype(row,col)
    # return true if cell(row, col) == ""
    false
  end

private

  def get_firsts_lasts
    fr = fc = 999_999
    lr = lc = -999_999  
    worksheet = @workbook.worksheet(@default_sheet)
    skip = 0
    line = 1
    worksheet.each(skip) { |row_par|
      if row_par
        row_par.each_with_index {|cell,i|
          # nicht beruecksichtigen, wenn nil und vorher noch nichts war
          # p cell
          if !cell 
            # nix
          else
            fc = [fc, i+1].min
            lc = [lc, i+1].max
            fr = [fr, line].min
            lr = [lr, line].max
          end
        }
      else
        #???
      end
      line += 1
    }
    return fr, lr, fc, lc
  end

end