# damit keine falschen Vermutungen aufkommen: Ich habe religoes rein gar nichts 
# mit diesem Bibelbund zu tun, aber die hatten eine ziemlich grosse 
# Spreadsheet-Datei oeffentlich im Netz, die sich ganz gut zum Testen eignete.
#
require File.dirname(__FILE__) + '/test_helper.rb'
#require 'soap/rpc/driver'
require 'fileutils'
require 'timeout'
include FileUtils

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-Documents
  ONLINE = false

  # helper method
  def local_only
    if ENV["roo_local"] == "thomas-p"
      yield
    end
  end

  # helper method
  def after(d)
    if DateTime.now > d
      yield
    end
  end

  # helper method
  def before(d)
    if DateTime.now <= d
      yield
    end
  end

  def setup
    if GOOGLE
      after Date.new(2007,8,15) do
        @goo = Google.new(ENV['GOOGLE_MAIL'],
        ENV['GOOGLE_PASSWORD'],
        ENV['GOOGLE_KEY'],"Roo Testspreadheet")
      end
    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
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      assert_equal ["Tabelle1","Name of Sheet 2","Sheet3","Sheet4","Sheet5"], oo.sheets
      assert_raise(RangeError) {
        oo.default_sheet = "no_sheet"
      }
      assert_raise(TypeError) {
        oo.default_sheet = [1,2,3]
      }
    end
    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      assert_equal ["Tabelle1","Name of Sheet 2","Sheet3","Sheet4","Sheet5"], oo.sheets
      assert_raise(RangeError) {
        oo.default_sheet = "no_sheet"
      }
      assert_raise(TypeError) {
        oo.default_sheet = [1,2,3]
      }
    end
    if GOOGLE
      after Date.new(2007,6,10) do
        assert_equal "Testspreadsheet Roo", @goo.title
      end
      assert_equal ["Sheet eins","Sheet zwei","Sheet drei"], @goo.sheets
      assert_raise(RangeError) {
        oo.default_sheet = "no_sheet"
      }
      assert_raise(TypeError) {
        oo.default_sheet = [1,2,3]
      }
    end
  end

  def test_cell
    if OPENOFFICE
      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 :string, oo.celltype(2,6)
      assert_equal 11, oo.cell(2,7)
      # assert_equal "float", oo.celltype(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, 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 EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      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 :string, oo.celltype(2,6)
      assert_equal 11, oo.cell(2,7)
      # assert_equal "float", oo.celltype(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, 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 :string, @goo.celltype(2,6)
      assert_equal 11, @goo.cell(2,7).to_i
      after Date.new(2007,6,15) do
        # assert_equal "float", @goo.celltype(2,7)
        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

      after Date.new(2007,6,15) do
        # assert_equal "date", @goo.celltype(5,1)
        assert_equal :date, @goo.celltype(5,1)
      end
      after Date.new(2007,6,15) do
        assert_equal Date.new(1961,11,21), @goo.cell(5,1)
        assert_equal "1961-11-21", @goo.cell(5,1).to_s
      end
      before Date.new(2007,6,15) do
        assert_equal "21/11/1961", @goo.cell(5,1)
      end
    end # GOOGLE
  end

  def test_celltype
    ###
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal :string, oo.celltype(2,6)
    end
    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      oo.default_sheet = oo.sheets.first
      assert_equal :string, oo.celltype(2,6)
    end
    if GOOGLE
      oo = Google.new(File.join("test","nxxxxxxxxxxxxxumbers1.xls"))
      oo.default_sheet = oo.sheets.first
      assert_equal :string, oo.celltype(2,6)
    end
  end

  def test_cell_address
    if OPENOFFICE
      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_raise(ArgumentError) {
        assert_equal "tata", oo.cell('a','f')
      }
      assert_raise(ArgumentError) {
        assert_equal "tata", oo.cell('f','a')
      }
      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)
    end

    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      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_raise(ArgumentError) {
        assert_equal "tata", oo.cell('a','f')
      }
      assert_raise(ArgumentError) {
        assert_equal "tata", oo.cell('f','a')
      }

      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
    end
    #-- Google
    if GOOGLE
      after Date.new(2007,6,15) do
        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 = 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
    after Date.new(2007,6,17) do
      #-- 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
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 18, oo.last_row
    end
    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      oo.default_sheet = oo.sheets.first
      assert_equal 18, oo.last_row
    end
    if 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
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal 7, oo.last_column
    end
    if EXCEL
      #-- Excel
      oo = Excel.new(File.join("test","numbers1.xls"))
      oo.default_sheet = 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
    if OPENOFFICE
      #-- 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)
    end
    if EXCEL
      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

  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"))
      before Date.new(2007,7,20) do
        assert_raise(ArgumentError) {
          oo.default_sheet = "first sheet"
        }
      end
      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"
            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
    local_only do
      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)
      #
      assert_equal  0.01, oo.cell('A',5)
      assert_equal  0.01, oo.cell('B',5)
      assert_equal  0.01, oo.cell('C',5)

      assert_equal 0.03, oo.cell('a',5)+oo.cell('b',5)+oo.cell('c',5)
  
  
        #   1.0
  
        # Cells values in row 1:
        assert_equal "1:string", oo.cell(1, 1)+":"+oo.celltype(1, 1).to_s
        assert_equal "1:string",oo.cell(1, 2)+":"+oo.celltype(1, 2).to_s
        assert_equal "1:string",oo.cell(1, 3)+":"+oo.celltype(1, 3).to_s
  
        # Cells values in row 2:
        assert_equal "1:string",oo.cell(2, 1)+":"+oo.celltype(2, 1).to_s
        assert_equal "2:string",oo.cell(2, 2)+":"+oo.celltype(2, 2).to_s
        assert_equal "1:string",oo.cell(2, 3)+":"+oo.celltype(2, 3).to_s
  
        # Cells values in row 3:
        assert_equal "1.0:float",oo.cell(3, 1).to_s+":"+oo.celltype(3, 1).to_s
        assert_equal "3.0:float",oo.cell(3, 2).to_s+":"+oo.celltype(3, 2).to_s
        assert_equal "1.0:float",oo.cell(3, 3).to_s+":"+oo.celltype(3, 3).to_s
  
        # Cells values in row 4:
        assert_equal "A:string",oo.cell(4, 1)+":"+oo.celltype(4, 1).to_s
        assert_equal "A:string",oo.cell(4, 2)+":"+oo.celltype(4, 2).to_s
        assert_equal "A:string",oo.cell(4, 3)+":"+oo.celltype(4, 3).to_s
  
        # Cells values in row 5:
        assert_equal "0.01:percentage",oo.cell(5, 1).to_s+":"+oo.celltype(5, 1).to_s
        assert_equal "0.01:percentage",oo.cell(5, 2).to_s+":"+oo.celltype(5, 2).to_s
        assert_equal "0.01:percentage",oo.cell(5, 3).to_s+":"+oo.celltype(5, 3).to_s

      oo = Excel.new(File.join("test","simple_spreadsheet_from_italo.xls"))
      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)
      #
      assert_equal  0.01, oo.cell('A',5)
      assert_equal  0.01, oo.cell('B',5)
      assert_equal  0.01, oo.cell('C',5)

      assert_equal 0.03, oo.cell('a',5)+oo.cell('b',5)+oo.cell('c',5)
  
  
        #   1.0
  
        # Cells values in row 1:
        assert_equal "1:string", oo.cell(1, 1)+":"+oo.celltype(1, 1).to_s
        assert_equal "1:string",oo.cell(1, 2)+":"+oo.celltype(1, 2).to_s
        assert_equal "1:string",oo.cell(1, 3)+":"+oo.celltype(1, 3).to_s
  
        # Cells values in row 2:
        assert_equal "1:string",oo.cell(2, 1)+":"+oo.celltype(2, 1).to_s
        assert_equal "2:string",oo.cell(2, 2)+":"+oo.celltype(2, 2).to_s
        assert_equal "1:string",oo.cell(2, 3)+":"+oo.celltype(2, 3).to_s
  
        # Cells values in row 3:
        assert_equal "1.0:float",oo.cell(3, 1).to_s+":"+oo.celltype(3, 1).to_s
        assert_equal "3.0:float",oo.cell(3, 2).to_s+":"+oo.celltype(3, 2).to_s
        assert_equal "1.0:float",oo.cell(3, 3).to_s+":"+oo.celltype(3, 3).to_s
  
        # Cells values in row 4:
        assert_equal "A:string",oo.cell(4, 1)+":"+oo.celltype(4, 1).to_s
        assert_equal "A:string",oo.cell(4, 2)+":"+oo.celltype(4, 2).to_s
        assert_equal "A:string",oo.cell(4, 3)+":"+oo.celltype(4, 3).to_s
  
        # Cells values in row 5:
        #assert_equal "0.01:percentage",oo.cell(5, 1).to_s+":"+oo.celltype(5, 1).to_s
        #assert_equal "0.01:percentage",oo.cell(5, 2).to_s+":"+oo.celltype(5, 2).to_s
        #assert_equal "0.01:percentage",oo.cell(5, 3).to_s+":"+oo.celltype(5, 3).to_s
        # why do we get floats here? in the spreadsheet the cells were defined
        # to be percentage
        # TODO: should be fixed
        # the excel gem does not support the cell type 'percentage' these
        # cells are returned to be of the type float.
        assert_equal "0.01:float",oo.cell(5, 1).to_s+":"+oo.celltype(5, 1).to_s
        assert_equal "0.01:float",oo.cell(5, 2).to_s+":"+oo.celltype(5, 2).to_s
        assert_equal "0.01:float",oo.cell(5, 3).to_s+":"+oo.celltype(5, 3).to_s
    end

  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

      after Date.new(2007,6,25) do
        # 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
      #after Date.new(2007,7,30) do
      #  assert_equal 21, oo.solve('a',7)
      #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"))
      #p oo.sheets
      #p oo.sheets[1]
      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 = 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)
      #example: puts oo.to_yaml({}, 12,3)
      #example: puts oo.to_yaml({"probe" => "bodenproben_2007-06-30"}, 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

  if false
    def test_soap_server
      #threads = []
      #threads << Thread.new("serverthread") do
      fork do
        p "serverthread started"
        puts "in child, pid = #$$"
        puts `/usr/bin/ruby rooserver.rb`
        p "serverthread finished"
      end
      #threads << Thread.new("clientthread") do
      p "clientthread started"
      sleep 10
      proxy = SOAP::RPC::Driver.new("http://localhost:12321","spreadsheetserver")
      proxy.add_method('cell','row','col')
      proxy.add_method('officeversion')
      proxy.add_method('last_row')
      proxy.add_method('last_column')
      proxy.add_method('first_row')
      proxy.add_method('first_column')
      proxy.add_method('sheets')
      proxy.add_method('set_default_sheet','s')
      proxy.add_method('ferien_fuer_region', 'region')

      sheets = proxy.sheets
      p sheets
      proxy.set_default_sheet(sheets.first)

      assert_equal 1, proxy.first_row
      assert_equal 1, proxy.first_column
      assert_equal 187, proxy.last_row
      assert_equal 7, proxy.last_column
      assert_equal 42, proxy.cell('C',8)
      assert_equal 43, proxy.cell('F',12)
      assert_equal "1.0", proxy.officeversion
      p "clientthread finished"
      #end
      #threads.each {|t| t.join }
      puts "fertig"
      Process.kill("INT",pid)
      pid=Process.wait
      puts "child terminated, pid= #{pid}, status= #{$?.exitstatus}"
    end
  end # false

  def split_coord(s)
    letter = ""
    number = 0
    i = 0
    while i<s.length and "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz".include?(s[i,1])
      letter += s[i,1]
      i+=1
    end
    while i<s.length and "01234567890".include?(s[i,1])
      number = number*10 + s[i,1].to_i
      i+=1
    end
    if letter=="" or number==0
      raise ArgumentError
    end
    return letter,number
  end

  #def sum(s,expression)
  #  arg = expression.split(':')
  #  b,z = split_coord(arg[0])
  #  first_row = z
  #  first_col = Openoffice.letter_to_number(b)
  #  b,z = split_coord(arg[1])
  #  last_row = z
  #  last_col = Openoffice.letter_to_number(b)
  #  result = 0
  #  first_row.upto(last_row) {|row|
  #    first_col.upto(last_col) {|col|
  #      result = result + s.cell(row,col)
  #    }
  #  }
  #  result
  #end

  #def test_dsl
  #  s = Openoffice.new(File.join("test","numbers1.ods"))
  #  s.default_sheet = s.sheets.first
  #
  #    s.set 'a',1, 5
  #    s.set 'b',1, 3
  #    s.set 'c',1, 7
  #    s.set('a',2, s.cell('a',1)+s.cell('b',1))
  #    assert_equal 8, s.cell('a',2)
  #
  #    assert_equal 15, sum(s,'A1:C1')
  #  end

  #def test_create_spreadsheet1
  #  name=File.join('test','createdspreadsheet.ods')
  #  rm(name) if File.exists?(File.join('test','createdspreadsheet.ods'))
  #  # anlegen, falls noch nicht existierend
  #  s = Openoffice.new(name,true)
  #  assert File.exists?(name)
  #end

  #def test_create_spreadsheet2
  #  # anlegen, falls noch nicht existierend
  #  s = Openoffice.new(File.join("test","createdspreadsheet.ods"),true)
  #  s.set 'a',1,42
  #  s.set 'b',1,43
  #  s.set 'c',1,44
  #  s.save
  #
  #  #after Date.new(2007,7,3) do
  #  #  t = Openoffice.new(File.join("test","createdspreadsheet.ods"))
  #  #  assert_equal 42, t.cell(1,'a')
  #  #  assert_equal 43, t.cell('b',1)
  #  #  assert_equal 44, t.cell('c',3)
  #  #end
  #end

  def test_only_one_sheet
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","only_one_sheet.ods"))
      # oo.default_sheet = oo.sheets.first
      assert_equal 42, oo.cell('B',4)
      assert_equal 43, oo.cell('C',4)
      assert_equal 44, oo.cell('D',4)
      oo.default_sheet = oo.sheets.first
      assert_equal 42, oo.cell('B',4)
      assert_equal 43, oo.cell('C',4)
      assert_equal 44, oo.cell('D',4)
    end
    if EXCEL
      oo = Excel.new(File.join("test","only_one_sheet.xls"))
      # oo.default_sheet = oo.sheets.first
      assert_equal 42, oo.cell('B',4)
      assert_equal 43, oo.cell('C',4)
      assert_equal 44, oo.cell('D',4)
      oo.default_sheet = oo.sheets.first
      assert_equal 42, oo.cell('B',4)
      assert_equal 43, oo.cell('C',4)
      assert_equal 44, oo.cell('D',4)
    end
    if GOOGLE
      after Date.new(2007,8,30) do
        raise "need to write test for GOOGLE"
      end
    end

  end

  def test_excel_open_from_uri_and_zipped
    if ONLINE
      url = 'http://stiny-leonhard.de/bode-v1.xls.zip'
      excel = Excel.new(url, :zip)
      assert_equal 'ist "e" im Nenner von H(s)', excel.cell('b', 5)
      excel.remove_tmp # don't forget to remove the temporary files
    end
  end

  def test_openoffice_open_from_uri_and_zipped
    if ONLINE
      url = 'http://spazioinwind.libero.it/s2/rata.ods.zip'
      sheet = Openoffice.new(url, :zip)
      assert_equal 'ist "e" im Nenner von H(s)', sheet.cell('b', 5)
      sheet.remove_tmp # don't forget to remove the temporary files
    end
  end

  def test_excel_zipped
    excel = Excel.new(File.join("test","bode-v1.xls.zip"), :zip)
    assert excel
    assert_equal 'ist "e" im Nenner von H(s)', excel.cell('b', 5)
    excel.remove_tmp # don't forget to remove the temporary files
  end

  def test_openoffice_zipped
    oo = Openoffice.new(File.join("test","bode-v1.ods.zip"), :zip)
    assert oo
    oo.default_sheet = oo.sheets.first
    assert_equal 'ist "e" im Nenner von H(s)', oo.cell('b', 5)
    oo.remove_tmp # don't forget to remove the temporary files
  end

  def test_bug_ric
      oo = Openoffice.new(File.join("test","ric.ods"))
      oo.default_sheet = oo.sheets.first
      assert oo.empty?('A',1)
      assert oo.empty?('B',1)
      assert oo.empty?('C',1)
      assert oo.empty?('D',1)
      expected = 1
      letter = 'e'
      while letter <= 'u'
        assert_equal expected, oo.cell(letter,1)
        letter.succ!
        expected += 1
      end
      #assert_equal 2, oo.cell('f',1)
      #assert_equal 3, oo.cell('g',1)
      #assert_equal 4, oo.cell('h',1)
      #assert_equal 5, oo.cell('i',1)
      #assert_equal 6, oo.cell('j',1)
      #assert_equal 7, oo.cell('k',1)
      #assert_equal 8, oo.cell('l',1)
      #assert_equal 9, oo.cell('m',1)
      #assert_equal 10, oo.cell('n',1)
      #assert_equal 11, oo.cell('o',1)
      #assert_equal 12, oo.cell('p',1)
      #assert_equal 13, oo.cell('q',1)
      #assert_equal 14, oo.cell('r',1)
      #assert_equal 15, oo.cell('s',1)
      #assert_equal 16, oo.cell('t',1)
      #assert_equal 17, oo.cell('u',1)
      assert_equal 'J', oo.cell('v',1)
      assert_equal 'P', oo.cell('w',1)
      assert_equal 'B', oo.cell('x',1)
      assert_equal 'All', oo.cell('y',1)
      assert_equal 0, oo.cell('a',2)
      assert oo.empty?('b',2)
      assert oo.empty?('c',2)
      assert oo.empty?('d',2)

      #'e'.upto('s') {|letter|
      #  assert_equal 'B', oo.cell(letter,2)
      #}
      after Date.new(2007,10,1) do
        assert_equal 'B', oo.cell('e',2)
        assert_equal 'B', oo.cell('f',2)
        assert_equal 'B', oo.cell('g',2)
        assert_equal 'B', oo.cell('h',2)
        assert_equal 'B', oo.cell('i',2)
        assert_equal 'B', oo.cell('j',2)
        assert_equal 'B', oo.cell('k',2)
        assert_equal 'B', oo.cell('l',2)
        assert_equal 'B', oo.cell('m',2)
        assert_equal 'B', oo.cell('n',2)
        assert_equal 'B', oo.cell('o',2)
        assert_equal 'B', oo.cell('p',2)
        assert_equal 'B', oo.cell('q',2)
        assert_equal 'B', oo.cell('r',2)
        assert_equal 'B', oo.cell('s',2)
      end

      assert oo.empty?('t',2)
      assert oo.empty?('u',2)
      assert_equal 0  , oo.cell('v',2)
      assert_equal 0  , oo.cell('w',2)
      assert_equal 15 , oo.cell('x',2)
      assert_equal 15 , oo.cell('y',2)
  end

  def test_mehrteilig
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","Bibelbund1.ods"))
      oo.default_sheet = oo.sheets.first
      assert_equal "Tagebuch des Sekret\303\244rs.    Letzte Tagung 15./16.11.75 Schweiz", oo.cell(45,'A')
    end
  end

  def test_to_csv
    after Date.new(2007,10,20) do
      if OPENOFFICE
        assert_nothing_raised(Timeout::Error) {
          Timeout::timeout(40*60*2) do |timeout_length|
            #puts Time.now.to_s + "test_to_csv Openoffice gestartet"
            oo = Openoffice.new(File.join("test","Bibelbund.ods"))
            oo.default_sheet = oo.sheets.first
            assert_equal "Tagebuch des Sekret\303\244rs.    Letzte Tagung 15./16.11.75 Schweiz", oo.cell(45,'A')
            assert_equal "Tagebuch des Sekret\303\244rs.  Nachrichten aus Chile", oo.cell(46,'A')
            assert_equal "Tagebuch aus Chile  Juli 1977", oo.cell(55,'A')
            #puts Time.now.to_s + "vor to_csv"
            #assert oo.to_csv
            #puts Time.now.to_s + "nach to_csv"
     
            #puts Time.now.to_s + "vor to_csv(datei)"
            assert oo.to_csv("/tmp/Bibelbund.csv")
            #puts Time.now.to_s + "nach to_csv(datei)"
            #puts Time.now.to_s + "vor File.exist"
            assert File.exists?("/tmp/Bibelbund.csv")
            #puts Time.now.to_s + "nach File.exist"
            assert_equal "", `diff test/Bibelbund.csv /tmp/Bibelbund.csv`
            #puts Time.now.to_s + "test_to_csv Openoffice beendet"
          end # Timeout
        } # nothing_raised
      end # OPENOFFICE
    end # after

    after Date.new(2007,10,20) do
      if EXCEL
        assert_nothing_raised(Timeout::Error) {
          Timeout::timeout(40*60*2) do |timeout_length|
            #puts Time.now.to_s + "test_to_csv Excel gestartet"
            oo = Excel.new(File.join("test","Bibelbund.xls"))
            oo.default_sheet = oo.sheets.first
            #puts Time.now.to_s + "vor to_csv"
            #assert oo.to_csv
            #puts Time.now.to_s + "nach to_csv"
            #Timeout:timeout(3*60)) do |timeout_length|
            #puts Time.now.to_s + "vor to_csv(datei)"
            assert oo.to_csv("/tmp/Bibelbund.csv")
            #puts Time.now.to_s + "nach to_csv(datei)"
            #puts Time.now.to_s + "vor File.exist"
            assert File.exists?("/tmp/Bibelbund.csv")
            #puts Time.now.to_s + "nach File.exist"
            assert_equal "", `diff test/Bibelbund.csv /tmp/Bibelbund.csv`
            #puts Time.now.to_s + "test_to_csv Excel beendet"
          end
        }
      end
    end
  end # def to_csv

  def test_bug_mehrere_datum
    if OPENOFFICE
      oo = Openoffice.new(File.join("test","numbers1.ods"))
      oo.default_sheet = 'Sheet5'
      assert_equal :date, oo.celltype('A',4)
      assert_equal :date, oo.celltype('B',4)
      assert_equal :date, oo.celltype('C',4)
      assert_equal :date, oo.celltype('D',4)
      assert_equal :date, oo.celltype('E',4)
      assert_equal Date.new(2007,11,21), oo.cell('A',4)
      assert_equal Date.new(2007,11,21), oo.cell('B',4)
      assert_equal Date.new(2007,11,21), oo.cell('C',4)
      assert_equal Date.new(2007,11,21), oo.cell('D',4)
      assert_equal Date.new(2007,11,21), oo.cell('E',4)
      assert_equal :float, oo.celltype('A',5)
      assert_equal :float, oo.celltype('B',5)
      assert_equal :float, oo.celltype('C',5)
      assert_equal :float, oo.celltype('D',5)
      assert_equal :float, oo.celltype('E',5)
      assert_equal 42, oo.cell('A',5)
      assert_equal 42, oo.cell('B',5)
      assert_equal 42, oo.cell('C',5)
      assert_equal 42, oo.cell('D',5)
      assert_equal 42, oo.cell('E',5)
      assert_equal :string, oo.celltype('A',6)
      assert_equal :string, oo.celltype('B',6)
      assert_equal :string, oo.celltype('C',6)
      assert_equal :string, oo.celltype('D',6)
      assert_equal :string, oo.celltype('E',6)
      assert_equal "ABC", oo.cell('A',6)
      assert_equal "ABC", oo.cell('B',6)
      assert_equal "ABC", oo.cell('C',6)
      assert_equal "ABC", oo.cell('D',6)
      assert_equal "ABC", oo.cell('E',6)
    end # Openoffice

    if EXCEL
      oo = Excel.new(File.join("test","numbers1.xls"))
      oo.default_sheet = 'Sheet5'
      assert_equal :date, oo.celltype('A',4)
      assert_equal :date, oo.celltype('B',4)
      assert_equal :date, oo.celltype('C',4)
      assert_equal :date, oo.celltype('D',4)
      assert_equal :date, oo.celltype('E',4)
      assert_equal Date.new(2007,11,21), oo.cell('A',4)
      assert_equal Date.new(2007,11,21), oo.cell('B',4)
      assert_equal Date.new(2007,11,21), oo.cell('C',4)
      assert_equal Date.new(2007,11,21), oo.cell('D',4)
      assert_equal Date.new(2007,11,21), oo.cell('E',4)
      assert_equal :float, oo.celltype('A',5)
      assert_equal :float, oo.celltype('B',5)
      assert_equal :float, oo.celltype('C',5)
      assert_equal :float, oo.celltype('D',5)
      assert_equal :float, oo.celltype('E',5)
      assert_equal 42, oo.cell('A',5)
      assert_equal 42, oo.cell('B',5)
      assert_equal 42, oo.cell('C',5)
      assert_equal 42, oo.cell('D',5)
      assert_equal 42, oo.cell('E',5)
      assert_equal :string, oo.celltype('A',6)
      assert_equal :string, oo.celltype('B',6)
      assert_equal :string, oo.celltype('C',6)
      assert_equal :string, oo.celltype('D',6)
      assert_equal :string, oo.celltype('E',6)
      assert_equal "ABC", oo.cell('A',6)
      assert_equal "ABC", oo.cell('B',6)
      assert_equal "ABC", oo.cell('C',6)
      assert_equal "ABC", oo.cell('D',6)
      assert_equal "ABC", oo.cell('E',6)
    end # Excel
  end

end # class