require File.dirname(__FILE__) + '/test_helper.rb'

class TestRoo < Test::Unit::TestCase
  
  OPENOFFICE = true  # do Openoffice-Spreadsheet Tests?
  EXCEL      = true  # do Excel Tests?
  GOOGLE     = false # do Google-Spreadsheet Tests?

  OPENOFFICEWRITE = false # experimental: write access with OO-Dokuments
  
  def setup
    if GOOGLE
      @goo = Google.new(ENV['GOOGLE_MAIL'],
                        ENV['GOOGLE_PASSWORD'],
                        ENV['GOOGLE_KEY'])
    end
  end 
  
  def test_letters
    assert_equal 1, Openoffice.letter_to_number('A')
    assert_equal 1, Openoffice.letter_to_number('a')
    assert_equal 2, Openoffice.letter_to_number('B')
    assert_equal 26, Openoffice.letter_to_number('Z')
    assert_equal 27, Openoffice.letter_to_number('AA')
    assert_equal 27, Openoffice.letter_to_number('aA')
    assert_equal 27, Openoffice.letter_to_number('Aa')
    assert_equal 27, Openoffice.letter_to_number('aa')
  end
  
  def test_sheets
    oo = Openoffice.new(File.join("test","numbers1.ods"))
    assert_equal ["Tabelle1","Name of Sheet 2","Sheet3","Sheet4","Sheet5"], oo.sheets
    #--
    if EXCEL
if DateTime.now > Date.new(2007,6,30)
      oo = Excel.new(File.join("test","numbers1.xls"))
      assert_equal ["Tabelle1","Name of Sheet 2","Sheet3"], oo.sheets
end
    end
    #-- Google
    if GOOGLE
      if DateTime.now > Date.new(2007,6,10)
        assert_equal "Testspreadsheet Roo", @goo.title
      end
      assert_equal ["Sheet eins","Sheet zwei","Sheet drei"], @goo.sheets
    end
  end
  
  def test_cell
    oo = Openoffice.new(File.join("test","numbers1.ods"))
    oo.default_sheet = oo.sheets.first
    assert_equal 1, oo.cell(1,1)
    assert_equal 2, oo.cell(1,2)
    assert_equal 3, oo.cell(1,3)
    assert_equal 4, oo.cell(1,4)
    assert_equal 5, oo.cell(2,1)
    assert_equal 6, oo.cell(2,2)
    assert_equal 7, oo.cell(2,3)
    assert_equal 8, oo.cell(2,4)
    assert_equal 9, oo.cell(2,5)
    assert_equal "test", oo.cell(2,6)
    assert_equal "string", oo.celltype(2,6)
    assert_equal 11, oo.cell(2,7)
    assert_equal "float", oo.celltype(2,7)
    
    assert_equal 10, oo.cell(4,1)
    assert_equal 11, oo.cell(4,2)
    assert_equal 12, oo.cell(4,3)
    assert_equal 13, oo.cell(4,4)
    assert_equal 14, oo.cell(4,5)
    
    assert_equal 10, oo.cell(4,'A')
    assert_equal 11, oo.cell(4,'B')
    assert_equal 12, oo.cell(4,'C')
    assert_equal 13, oo.cell(4,'D')
    assert_equal 14, oo.cell(4,'E')
    
    assert_equal "date", oo.celltype(5,1)
    assert_equal Date.new(1961,11,21), oo.cell(5,1)
    assert_equal "1961-11-21", oo.cell(5,1).to_s
    
    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      oo.default_sheet = 1 # oo.sheets.first
      assert_equal 1, oo.cell(1,1)
      assert_equal 2, oo.cell(1,2)
      assert_equal 3, oo.cell(1,3)
      assert_equal 4, oo.cell(1,4)
      assert_equal 5, oo.cell(2,1)
      assert_equal 6, oo.cell(2,2)
      assert_equal 7, oo.cell(2,3)
      assert_equal 8, oo.cell(2,4)
      assert_equal 9, oo.cell(2,5)
      assert_equal "test", oo.cell(2,6)
      assert_equal "string", oo.celltype(2,6)
      assert_equal 11, oo.cell(2,7)
      assert_equal "float", oo.celltype(2,7)
      
      assert_equal 10, oo.cell(4,1)
      assert_equal 11, oo.cell(4,2)
      assert_equal 12, oo.cell(4,3)
      assert_equal 13, oo.cell(4,4)
      assert_equal 14, oo.cell(4,5)
      
      assert_equal 10, oo.cell(4,'A')
      assert_equal 11, oo.cell(4,'B')
      assert_equal 12, oo.cell(4,'C')
      assert_equal 13, oo.cell(4,'D')
      assert_equal 14, oo.cell(4,'E')
      
      assert_equal "date", oo.celltype(5,1)
      assert_equal Date.new(1961,11,21), oo.cell(5,1)
      assert_equal "1961-11-21", oo.cell(5,1).to_s
    end
    
    if GOOGLE
      @goo.default_sheet = 1 # @goo.sheets.first
      assert_equal 1, @goo.cell(1,1).to_i
      assert_equal 2, @goo.cell(1,2).to_i
      assert_equal 3, @goo.cell(1,3).to_i
      assert_equal 4, @goo.cell(1,4).to_i
      assert_equal 5, @goo.cell(2,1).to_i
      assert_equal 6, @goo.cell(2,2).to_i
      assert_equal 7, @goo.cell(2,3).to_i
      assert_equal 8, @goo.cell(2,4).to_i
      assert_equal 9, @goo.cell(2,5).to_i
      assert_equal "test", @goo.cell(2,6)
      assert_equal "string", @goo.celltype(2,6)
      assert_equal 11, @goo.cell(2,7).to_i
      if DateTime.now > Date.new(2007,6,15)
        assert_equal "float", @goo.celltype(2,7)
      end
      
      assert_equal 10, @goo.cell(4,1).to_i
      assert_equal 11, @goo.cell(4,2).to_i
      assert_equal 12, @goo.cell(4,3).to_i
      assert_equal 13, @goo.cell(4,4).to_i
      assert_equal 14, @goo.cell(4,5).to_i
      
      assert_equal 10, @goo.cell(4,'A').to_i
      assert_equal 11, @goo.cell(4,'B').to_i
      assert_equal 12, @goo.cell(4,'C').to_i
      assert_equal 13, @goo.cell(4,'D').to_i
      assert_equal 14, @goo.cell(4,'E').to_i
      
      if DateTime.now > Date.new(2007,6,15)
        assert_equal "date", @goo.celltype(5,1)
      end
      if DateTime.now > Date.new(2007,6,15)
        assert_equal Date.new(1961,11,21), @goo.cell(5,1)
        assert_equal "1961-11-21", @goo.cell(5,1).to_s
      else
        assert_equal "21/11/1961", @goo.cell(5,1)
      end
      end # GOOGLE
    end
    
    def test_cell_address
      oo = Openoffice.new(File.join("test/numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal "tata", oo.cell(6,1)
      assert_equal "tata", oo.cell(6,'A')
      assert_equal "tata", oo.cell('A',6)
      assert_equal "tata", oo.cell(6,'a')
      assert_equal "tata", oo.cell('a',6)
      
      assert_equal "thisisc8", oo.cell(8,3)
      assert_equal "thisisc8", oo.cell(8,'C')
      assert_equal "thisisc8", oo.cell('C',8)
      assert_equal "thisisc8", oo.cell(8,'c')
      assert_equal "thisisc8", oo.cell('c',8)
      
      assert_equal "thisisd9", oo.cell('d',9)
      assert_equal "thisisa11", oo.cell('a',11)
      
      if EXCEL
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal "tata", oo.cell(6,1)
        assert_equal "tata", oo.cell(6,'A')
        assert_equal "tata", oo.cell('A',6)
        assert_equal "tata", oo.cell(6,'a')
        assert_equal "tata", oo.cell('a',6)
        
        assert_equal "thisisc8", oo.cell(8,3)
        assert_equal "thisisc8", oo.cell(8,'C')
        assert_equal "thisisc8", oo.cell('C',8)
        assert_equal "thisisc8", oo.cell(8,'c')
        assert_equal "thisisc8", oo.cell('c',8)
        
        assert_equal "thisisd9", oo.cell('d',9)
        assert_equal "thisisa11", oo.cell('a',11)
        #assert_equal "lulua", oo.cell('b',10)
      end
      
      if GOOGLE
        @goo.default_sheet = 1 # @goo.sheets.first
        assert_equal "tata", @goo.cell(6,1)
        assert_equal "tata", @goo.cell(6,'A')
        assert_equal "tata", @goo.cell('A',6)
        assert_equal "tata", @goo.cell(6,'a')
        assert_equal "tata", @goo.cell('a',6)
        
        assert_equal "thisisc8", @goo.cell(8,3)
        assert_equal "thisisc8", @goo.cell(8,'C')
        assert_equal "thisisc8", @goo.cell('C',8)
        assert_equal "thisisc8", @goo.cell(8,'c')
        assert_equal "thisisc8", @goo.cell('c',8)
        
        assert_equal "thisisd9", @goo.cell('d',9)
        assert_equal "thisisa11", @goo.cell('a',11)
      end
    end
    
    # Version of the (XML) office document
    # please note that "1.0" is returned even if it was created with OpenOffice V. 2.0
    def test_officeversion
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      assert_equal "1.0", oo.officeversion
      if EXCEL
        # excel does not have a officeversion
        # is there a similar version number which ist considerable
        #-- Excel
        #if DateTime.now > Date.new(2007,6,15)
        #  oo = Excel.new(File.join("test","numbers1.xls"))
        #  assert_equal "1.0", oo.officeversion
        #end
      end
      #-- Google
      if GOOGLE
        if DateTime.now > Date.new(2007,6,15)
          assert_equal "1.0", @goo.officeversion
        end
      end
    end
    
    def test_rows
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 41, oo.cell('a',12)
      assert_equal 42, oo.cell('b',12)
      assert_equal 43, oo.cell('c',12)
      assert_equal 44, oo.cell('d',12)
      assert_equal 45, oo.cell('e',12)
      assert_equal [41.0,42.0,43.0,44.0,45.0], oo.row(12)
      assert_equal "einundvierzig", oo.cell('a',16)
      assert_equal "zweiundvierzig", oo.cell('b',16)
      assert_equal "dreiundvierzig", oo.cell('c',16)
      assert_equal "vierundvierzig", oo.cell('d',16)
      assert_equal "fuenfundvierzig", oo.cell('e',16)
      assert_equal ["einundvierzig", "zweiundvierzig", "dreiundvierzig", "vierundvierzig", "fuenfundvierzig"], oo.row(16)
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 41, oo.cell('a',12)
        assert_equal 42, oo.cell('b',12)
        assert_equal 43, oo.cell('c',12)
        assert_equal 44, oo.cell('d',12)
        assert_equal 45, oo.cell('e',12)
        assert_equal [41,42,43,44,45], oo.row(12)
        assert_equal "einundvierzig", oo.cell('a',16)
        assert_equal "zweiundvierzig", oo.cell('b',16)
        assert_equal "dreiundvierzig", oo.cell('c',16)
        assert_equal "vierundvierzig", oo.cell('d',16)
        assert_equal "fuenfundvierzig", oo.cell('e',16)
        assert_equal ["einundvierzig",
                      "zweiundvierzig",
                      "dreiundvierzig",
                      "vierundvierzig",
                      "fuenfundvierzig"], oo.row(16)
      end
      if DateTime.now > Date.new(2007,6,17)
        #-- GOOGLE
        if GOOGLE
          oo = Google.new(File.join("test","numbers1.xls"))
          oo.default_sheet = 1 # oo.sheets.first
          assert_equal 41, oo.cell('a',12)
          assert_equal 42, oo.cell('b',12)
          assert_equal 43, oo.cell('c',12)
          assert_equal 44, oo.cell('d',12)
          assert_equal 45, oo.cell('e',12)
          assert_equal [41.0,42.0,43.0,44.0,45.0], oo.row(12)
      assert_equal "einundvierzig", oo.cell('a',16)
      assert_equal "zweiundvierzig", oo.cell('b',16)
      assert_equal "dreiundvierzig", oo.cell('c',16)
      assert_equal "vierundvierzig", oo.cell('d',16)
      assert_equal "fuenfundvierzig", oo.cell('e',16)
      assert_equal "xxxfuenfundvierzig", oo.cell('e',16)
      assert_equal ["einundvierzig",
                    "zweiundvierzig",
                    "dreiundvierzig",
                    "vierundvierzig",
                    "fuenfundvierzig"], oo.row(16)
        end
      end
    end
    
    def test_last_row
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 18, oo.last_row
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 18, oo.last_row
      end
      if GOOGLE
        #-- Google
        @goo.default_sheet = @goo.sheets.first
        assert_equal 18, @goo.last_row
        assert_equal "xxx", @goo.to_s, @goo.to_s
      end
    end
    
    def test_last_column
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 7, oo.last_column
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 7, oo.last_column
      end
      if GOOGLE
        #-- Google
        @goo.default_sheet = @goo.sheets.first
        assert_equal 7, @goo.last_column
      end
    end
    
    def test_last_column_as_letter
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 'G', oo.last_column_as_letter
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 'G', oo.last_column_as_letter
      end
      if GOOGLE
        #-- Google
        @goo.default_sheet = @goo.sheets.first
        assert_equal 'G', @goo.last_column_as_letter
      end
    end
    
    def test_first_row
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 1, oo.first_row
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 1, oo.first_row
      end
      if GOOGLE
        #-- Google
        @goo.default_sheet = @goo.sheets.first
        assert_equal 1, @goo.first_row
      end
    end
    
    def test_first_column
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 1, oo.first_column
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 1, oo.first_column
      end
      if GOOGLE
        #-- Google
        @goo.default_sheet = 1 # @goo.sheets.first
        assert_equal 1, @goo.first_column
      end
    end
    
    def test_first_column_as_letter
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 'A', oo.first_column_as_letter
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 'A', oo.first_column_as_letter
      end
      if GOOGLE
        #-- Google
        @goo.default_sheet = @goo.sheets.first
        assert_equal 'A', @goo.first_column_as_letter
      end
    end
    
    def test_sheetname
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = "Name of Sheet 2"
      assert_equal 'I am sheet 2', oo.cell('C',5)
      if DateTime.now > Date.new(2007,6,16)
        #-- Excel
        if DateTime.now > Date.new(2007,6,30)
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = "Name of Sheet 2"
        assert_equal 'I am sheet 2', oo.cell('C',5)
        end
      end
    end
    
    def test_boundaries
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = "Name of Sheet 2"
      assert_equal 2, oo.first_column
      assert_equal 'B', oo.first_column_as_letter
      assert_equal 5, oo.first_row
      assert_equal 'E', oo.last_column_as_letter
      assert_equal 14, oo.last_row
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 2 # "Name of Sheet 2"
        assert_equal 2, oo.first_column
        assert_equal 'B', oo.first_column_as_letter
        assert_equal 5, oo.first_row
        assert_equal 'E', oo.last_column_as_letter
        assert_equal 14, oo.last_row
      end
    end
    
    def test_multiple_letters
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = "Sheet3"
      assert_equal "i am AA", oo.cell('AA',1)
      assert_equal "i am AB", oo.cell('AB',1)
      assert_equal "i am BA", oo.cell('BA',1)
      assert_equal 'BA', oo.last_column_as_letter
      assert_equal "i am BA", oo.cell(1,'BA')
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 3 # "Sheet3"
        assert_equal "i am AA", oo.cell('AA',1)
        assert_equal "i am AB", oo.cell('AB',1)
        assert_equal "i am BA", oo.cell('BA',1)
        assert_equal 'BA', oo.last_column_as_letter
        assert_equal "i am BA", oo.cell(1,'BA')
      end
    end
    
    def test_setting_cell
      assert true
    end
    
    def test_argument_error
      if EXCEL
        oo = Excel.new(File.join("test","numbers1.xls"))
        assert_raise(ArgumentError) {
          oo.default_sheet = "first sheet"
        }
        assert_nothing_raised(ArgumentError) {
          oo.default_sheet = 1
        }
      end
    end
    
    def test_empty_eh
      #-- OpenOffice
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert oo.empty?('a',14)
      assert ! oo.empty?('a',15)
      assert oo.empty?('a',20)
      if EXCEL
        #-- Excel
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1
        assert oo.empty?('a',14)
        assert ! oo.empty?('a',15)
        assert oo.empty?('a',20)
      end
    end

    def test_writeopenoffice
      if OPENOFFICEWRITE
      File.cp(File.join("test","numbers1.ods"),
              File.join("test","numbers2.ods"))
      File.cp(File.join("test","numbers2.ods"),
              File.join("test","bak_numbers2.ods"))
      oo = Openoffice.new(File.join("test","numbers2.ods"))
      oo.default_sheet = oo.sheets.first
      oo.first_row.upto(oo.last_row) {|y|
        oo.first_column.upto(oo.last_column) {|x|
          unless oo.empty?(y,x) 
            oo.set(y, x, oo.cell(y,x) + 7) if oo.celltype(y,x) == "float"
          end
        }
      }
      oo.save
 
      oo1 = Openoffice.new(File.join("test","numbers2.ods"))
      oo2 = Openoffice.new(File.join("test","bak_numbers2.ods"))
      p oo2.to_s
      assert_equal 999, oo2.cell('a',1), oo2.cell('a',1)
      assert_equal oo2.cell('a',1) + 7, oo1.cell('a',1)
      assert_equal oo2.cell('b',1)+7, oo1.cell('b',1)
      assert_equal oo2.cell('c',1)+7, oo1.cell('c',1)
      assert_equal oo2.cell('d',1)+7, oo1.cell('d',1)
      assert_equal oo2.cell('a',2)+7, oo1.cell('a',2)
      assert_equal oo2.cell('b',2)+7, oo1.cell('b',2)
      assert_equal oo2.cell('c',2)+7, oo1.cell('c',2)
      assert_equal oo2.cell('d',2)+7, oo1.cell('d',2)
      assert_equal oo2.cell('e',2)+7, oo1.cell('e',2)
      
      File.cp(File.join("test","bak_numbers2.ods"),
              File.join("test","numbers2.ods"))
      end
    end

    def test_reload
      if OPENOFFICE
        oo = Openoffice.new(File.join("test","numbers1.ods"))
        oo.default_sheet = oo.sheets.first
        assert_equal 1, oo.cell(1,1)

        oo.reload
        assert_equal 1, oo.cell(1,1)
      end
      if EXCEL
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 1 # oo.sheets.first
        assert_equal 1, oo.cell(1,1)

        oo.reload
        assert_equal 1, oo.cell(1,1)
      end
    end

    def test_bug_contiguous_cells
      if OPENOFFICE
        oo = Openoffice.new(File.join("test","numbers1.ods"))
        oo.default_sheet = "Sheet4"
        assert_equal Date.new(2007,06,16), oo.cell('a',1) 
        assert_equal 10, oo.cell('b',1)
        assert_equal 10, oo.cell('c',1)
        assert_equal 10, oo.cell('d',1)
        assert_equal 10, oo.cell('e',1)
      end
      if EXCEL
        oo = Excel.new(File.join("test","numbers1.xls"))
        oo.default_sheet = 4
        assert_equal Date.new(2007,06,16), oo.cell('a',1) 
        assert_equal 10, oo.cell('b',1)
        assert_equal 10, oo.cell('c',1)
        assert_equal 10, oo.cell('d',1)
        assert_equal 10, oo.cell('e',1)
      end
      if GOOGLE
        @goo.default_sheet = "Sheet4"
        assert_equal Date.new(2007,06,16), @goo.cell('a',1) 
        assert_equal 10, @goo.cell('b',1)
        assert_equal 10, @goo.cell('c',1)
        assert_equal 10, @goo.cell('d',1)
        assert_equal 10, @goo.cell('e',1)
     end
   end

  def test_bug_italo_ve
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = "Sheet5"
      assert_equal 1, oo.cell('A',1) 
      assert_equal 5, oo.cell('b',1) 
      assert_equal 5, oo.cell('c',1) 
      assert_equal 2, oo.cell('a',2) 
      assert_equal 3, oo.cell('a',3) 
    end
    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      oo.default_sheet = 5
      assert_equal 1, oo.cell('A',1) 
      assert_equal 5, oo.cell('b',1) 
      assert_equal 5, oo.cell('c',1) 
      assert_equal 2, oo.cell('a',2) 
      assert_equal 3, oo.cell('a',3) 
    end
  end

  def DONT_test_large_file
    if OPENOFFICE
      count = 0
      oo = Openoffice.new(File.join("test","Bibelbund.ods"))
      oo.default_sheet = oo.sheets.first
      oo.first_row.upto(oo.last_row) do |row|
        oo.first_column.upto(oo.last_column) do |col|
          unless oo.empty?(row,col)
            count += 1
            a = oo.cell(row,col)
#            puts a
#            b = gets
          end
        end
      end
      puts count.to_s+" cells with content"
    end

  end  
  
  def test_italo_table
    oo = Openoffice.new(File.join("test","simple_spreadsheet_from_italo.ods"))
    oo.default_sheet = oo.sheets.first
    
    assert_equal  '1', oo.cell('A',1)
    assert_equal  '1', oo.cell('B',1)
    assert_equal  '1', oo.cell('C',1)

    # assert_equal  1, oo.cell('A',2)
    # assert_equal  2, oo.cell('B',2)
    # assert_equal  1, oo.cell('C',2)
    # are stored as strings, not numbers

    assert_equal  1, oo.cell('A',2).to_i
    assert_equal  2, oo.cell('B',2).to_i
    assert_equal  1, oo.cell('C',2).to_i

    assert_equal  1, oo.cell('A',3)
    assert_equal  3, oo.cell('B',3)
    assert_equal  1, oo.cell('C',3)

    assert_equal  'A', oo.cell('A',4)
    assert_equal  'A', oo.cell('B',4)
    assert_equal  'A', oo.cell('C',4)

    assert_equal  '0.01', oo.cell('A',5)
    assert_equal  '0.01', oo.cell('B',5)
    assert_equal  '0.01', oo.cell('C',5)


 #   1.0

# Cells values in row 1:
    assert_equal "1:string", oo.cell(1, 1)+":"+oo.celltype(1, 1)
    assert_equal "1:string",oo.cell(1, 2)+":"+oo.celltype(1, 2)
    assert_equal "1:string",oo.cell(1, 3)+":"+oo.celltype(1, 3)

# Cells values in row 2:
    assert_equal "1:string",oo.cell(2, 1)+":"+oo.celltype(2, 1)
    assert_equal "2:string",oo.cell(2, 2)+":"+oo.celltype(2, 2)
    assert_equal "1:string",oo.cell(2, 3)+":"+oo.celltype(2, 3)

# Cells values in row 3:
    assert_equal "1.0:float",oo.cell(3, 1).to_s+":"+oo.celltype(3, 1)
    assert_equal "3.0:float",oo.cell(3, 2).to_s+":"+oo.celltype(3, 2)
    assert_equal "1.0:float",oo.cell(3, 3).to_s+":"+oo.celltype(3, 3)

# Cells values in row 4:
    assert_equal "A:string",oo.cell(4, 1)+":"+oo.celltype(4, 1)
    assert_equal "A:string",oo.cell(4, 2)+":"+oo.celltype(4, 2)
    assert_equal "A:string",oo.cell(4, 3)+":"+oo.celltype(4, 3)

# Cells values in row 5:
    assert_equal "0.01:percentage",oo.cell(5, 1)+":"+oo.celltype(5, 1)
    assert_equal "0.01:percentage",oo.cell(5, 2)+":"+oo.celltype(5, 2)
    assert_equal "0.01:percentage",oo.cell(5, 3)+":"+oo.celltype(5, 3)


  end

  def test_external1
    if File.exist?(File.join("test","external1.xls"))
      oo = Excel.new(File.join("test","external1.xls"))
      oo.default_sheet = 2
      assert_equal(-105.675, oo.cell('Z',5))
      assert_equal(41.31205555, oo.cell('AA',5))
      assert_equal(2218.3344, oo.cell('AB',5))
    end
  end

  def myfunc(n)
    puts "#{n} Euro"
  end

  def test_formula
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","formula.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 1, oo.cell('A',1)
      assert_equal 2, oo.cell('A',2)
      assert_equal 3, oo.cell('A',3)
      assert_equal 4, oo.cell('A',4)
      assert_equal 5, oo.cell('A',5)
      assert_equal 6, oo.cell('A',6)
      assert_equal 21, oo.cell('A',7)
      assert_equal :formula, oo.celltype('A',7)
      assert_equal "=[Sheet2.A1]", oo.formula('C',7)
      assert_nil oo.formula('A',6)
      assert_equal [[7, 1, "=SUM([.A1:.A6])"],
         [7, 2, "=SUM([.$A$1:.B6])"],
         [7, 3, "=[Sheet2.A1]"],
         [8, 2, "=SUM([.$A$1:.B7])"],
         ], oo.formulas

      if DateTime.now > Date.new(2007,6,25)
        # setting a cell
        oo.set('A',15, 41)
        assert_equal 41, oo.cell('A',15)
        oo.set('A',16, "41")
        assert_equal "41", oo.cell('A',16)
        oo.set('A',17, 42.5)
        assert_equal 42.5, oo.cell('A',17)
      end
      if DateTime.now > Date.new(2007,6,30)
        assert_equal 21, oo.solve('a',7)
      end

      oo = Openoffice.new(File.join("test","external1.ods"))
      # each spreadsheet, each row, each column
      oo.sheets.each {|sheet|
        oo.default_sheet = sheet
        if oo.first_row
          oo.first_row.upto(oo.last_row) do |row|
            oo.first_column.upto(oo.last_column) do |col|
              value = oo.cell(row,col)
              # is it a formula?
              if oo.formula?(row,col)
                # formula
                puts oo.formula(row,col)
                # value
                puts value if value
              else
                puts value if value
              end
            end
          end
        end
      }
    end
  end


  def test_borders_sheets
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","borders.ods"))
      oo.default_sheet = oo.sheets[1]
      assert_equal 6, oo.first_row
      assert_equal 11, oo.last_row
      assert_equal 4, oo.first_column
      assert_equal 8, oo.last_column

      oo.default_sheet = oo.sheets.first
      #assert_nil  oo.first_row
      assert_equal 5, oo.first_row
      #assert_nil oo.last_row 
      assert_equal 10, oo.last_row 
      assert_equal 3, oo.first_column
      #assert_nil oo.first_column
      assert_equal 7, oo.last_column
      #assert_nil oo.last_column

      oo.default_sheet = oo.sheets[2]
      assert_equal 7, oo.first_row
      assert_equal 12, oo.last_row
      assert_equal 5, oo.first_column
      assert_equal 9, oo.last_column
    end
    if EXCEL
      oo = Excel.new(File.join("test","borders.xls"))
      oo.default_sheet = 2 # oo.sheets[1]
      assert_equal 6, oo.first_row
      assert_equal 11, oo.last_row
      assert_equal 4, oo.first_column
      assert_equal 8, oo.last_column

      oo.default_sheet = 1 # oo.sheets.first
      #assert_nil  oo.first_row
      assert_equal 5, oo.first_row
      #assert_nil oo.last_row 
      assert_equal 10, oo.last_row 
      assert_equal 3, oo.first_column
      #assert_nil oo.first_column
      assert_equal 7, oo.last_column
      #assert_nil oo.last_column

      oo.default_sheet = 3 # oo.sheets[2]
      assert_equal 7, oo.first_row
      assert_equal 12, oo.last_row
      assert_equal 5, oo.first_column
      assert_equal 9, oo.last_column
    end
    
  end

  def yaml_entry(row,col,type,value)
    "cell_#{row}_#{col}: \n  row: #{row} \n  col: #{col} \n  celltype: #{type} \n  value: #{value} \n"
  end

  def test_to_yaml
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal "--- \n"+yaml_entry(5,1,"date","1961-11-21"), oo.to_yaml({}, 5,1,5,1)
      assert_equal "--- \n"+yaml_entry(8,3,"string","thisisc8"), oo.to_yaml({}, 8,3,8,3)
      assert_equal "--- \n"+yaml_entry(12,3,"float",43.0), oo.to_yaml({}, 12,3,12,3)
      assert_equal \
        "--- \n"+yaml_entry(12,3,"float",43.0) +
        yaml_entry(12,4,"float",44.0) +
        yaml_entry(12,5,"float",45.0), oo.to_yaml({}, 12,3,12)
      assert_equal \
        "--- \n"+yaml_entry(12,3,"float",43.0)+
        yaml_entry(12,4,"float",44.0)+
        yaml_entry(12,5,"float",45.0)+
        yaml_entry(15,3,"float",43.0)+
        yaml_entry(15,4,"float",44.0)+
        yaml_entry(15,5,"float",45.0)+
        yaml_entry(16,3,"string","dreiundvierzig")+
        yaml_entry(16,4,"string","vierundvierzig")+
        yaml_entry(16,5,"string","fuenfundvierzig"), oo.to_yaml({}, 12,3)
    end
    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      oo.default_sheet = 1 
      assert_equal "--- \n"+yaml_entry(5,1,"date","1961-11-21"), oo.to_yaml({}, 5,1,5,1)
      assert_equal "--- \n"+yaml_entry(8,3,"string","thisisc8"), oo.to_yaml({}, 8,3,8,3)
      assert_equal "--- \n"+yaml_entry(12,3,"float",43.0), oo.to_yaml({}, 12,3,12,3)
      assert_equal \
        "--- \n"+yaml_entry(12,3,"float",43.0) +
        yaml_entry(12,4,"float",44.0) +
        yaml_entry(12,5,"float",45.0), oo.to_yaml({}, 12,3,12)
      assert_equal \
        "--- \n"+yaml_entry(12,3,"float",43.0)+
        yaml_entry(12,4,"float",44.0)+
        yaml_entry(12,5,"float",45.0)+
        yaml_entry(15,3,"float",43.0)+
        yaml_entry(15,4,"float",44.0)+
        yaml_entry(15,5,"float",45.0)+
        yaml_entry(16,3,"string","dreiundvierzig")+
        yaml_entry(16,4,"string","vierundvierzig")+
        yaml_entry(16,5,"string","fuenfundvierzig"), oo.to_yaml({}, 12,3)
    end
  end

end # class