require 'rubygems'
gem 'parseexcel', '>= 0.5.2'
require 'parseexcel'

class Excel < Openoffice

  def initialize(filename, packed = nil)
    @tmpdir = "oo_"+$$.to_s
    unless File.exists?(@tmpdir)
      FileUtils::mkdir(@tmpdir)
    end
    filename = open_from_uri(filename) if filename[0,7] == "http://"
    filename = unzip(filename) if packed and packed == :zip
    if filename[-4..-1] != ".xls"
      warn "are you sure, this is an excel file?"
    end
    @filename = filename
    @workbook = Spreadsheet::ParseExcel.parse(filename)
    @default_sheet = nil
    # no need to set default_sheet if there is only one sheet in the document
    if self.sheets.size == 1
      @default_sheet = self.sheets.first
    end
    @first_row = @last_row = @first_column = @last_column = nil
  end

  def sheets
    #if DateTime.now < Date.new(2007,6,10)
    #  return  ["Tabelle1", "Name of Sheet 2", "Sheet3"]
    #else
      result = []
      0.upto(@workbook.sheet_count - 1) do |i|
        # TODO: is there a better way to do conversion?
        result << Iconv.new('utf-8','unicode').iconv(
                      @workbook.worksheet(i).name
                           )
      end
      return result
    #end
  end

  # sets the working sheet (1,2,3,..)
  def default_sheet=(n)
    if n.kind_of?(Fixnum)
      @default_sheet = n #-1
    elsif n.kind_of?(String)
      raise RangeError if ! self.sheets.include?(n)
      # parseexcel supports now the name of a sheet
      @default_sheet = n
    else
      raise TypeError, "what are you trying to set as default sheet?"
    end
    @first_row = @last_row = @first_column = @last_column = nil
    @cells_read = false
  end

  # returns the content of a cell. The upper left corner is (1,1) or ('A',1)
  def cell(row,col)
    row,col = normalize(row,col)
    worksheet = @workbook.worksheet(sheet_no(@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)
        return nil unless cell
        case cell.type
          when :numeric then return cell.to_f
          when :text then return cell.to_s('utf-8')
          when :date then return cell.date
        else  
          return cell.to_s('utf-8')
        end
      end
      line += 1
    }
  end

  # returns the type of a cell: "float", "string", "date"
  def celltype(row,col)
    row,col = normalize(row,col)
    default_sheet_check
    worksheet = @workbook.worksheet(sheet_no(@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.to_sym
        end
      end
      line += 1
    }
  end

  # return this row a an array off cells
  def row(rownumber)
    default_sheet_check
    worksheet = @workbook.worksheet(sheet_no(@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('utf-8')
         when :date then result << cell.date
      else
        return result << cell.to_s('utf-8')
      end
    }
    return result
  end

  # returns the first non empty column
  def first_column
    return @first_column if @first_column
    fr, lr, fc, lc = get_firsts_lasts
    fc
  end

  # returns the last non empty column
  def last_column
    return @last_column if @last_column
    fr, lr, fc, lc = get_firsts_lasts
    lc
  end

  # returns the first non empty row
  def first_row
    return @first_row if @first_row
    fr, lr, fc, lc = get_firsts_lasts
    fr
  end

  # returns the last non empty row
  def last_row
    return @last_row if @last_row
    fr, lr, fc, lc = get_firsts_lasts
    lr
  end

  # true if a cell is empty
  def empty?(row, col)
    row,col = normalize(row,col)
    return true if row < first_row || row > last_row || col < first_column || col > last_column
    return true unless cell(row, col)
    return true if celltype(row, col) == "string" && cell(row, col) == ""
    false
  end

private

  # check if default_sheet was set
  def default_sheet_check
    raise ArgumentError, "Error: default_sheet not set" if @default_sheet == nil
  end

  # determine the first and last boundaries
  def get_firsts_lasts
    fr = fc = 999_999
    lr = lc = -999_999  
    worksheet = @workbook.worksheet(sheet_no(@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
          if cell 
            fc = [fc, i+1].min
            lc = [lc, i+1].max
            fr = [fr, line].min
            lr = [lr, line].max
          end
        }
      end
      line += 1
    }
    @first_row = fr
    @last_row = lr
    @first_column = fc
    @last_column = lc
    return fr, lr, fc, lc
  end


  # converts name of a sheet to index (0,1,2,..)
  def sheet_no(name)
    return name-1 if name.kind_of?(Fixnum)
    0.upto(@workbook.sheet_count - 1) do |i|
      # TODO: is there a better way to do conversion?
      return i if name == Iconv.new('utf-8','unicode').iconv(
                    @workbook.worksheet(i).name
                         )
    end
    raise StandardError, "sheet '#{name}' not found"
  end

end