# encoding: utf-8
# damit keine falschen Vermutungen aufkommen: Ich habe religioes rein gar nichts
# mit diesem Bibelbund zu tun, aber die hatten eine ziemlich grosse
# Spreadsheet-Datei mit ca. 3500 Zeilen oeffentlich im Netz, die sich ganz gut
# zum Testen eignete.
#
#--
# these test cases were developed to run under Linux OS, some commands
# (like 'diff') must be changed (or commented out ;-)) if you want to run
# the tests under another OS
#
require 'rubygems'
require 'todonotes'
require 'tmpdir'
require './lib/roo'
#TODO
# Look at formulas in excel - does not work with date/time

class Csv
  def cell_postprocessing(row,col,value)
    if row==1 and col==1
      return value.to_f
    end
    if row==1 and col==2
      return value.to_s
    end
    return value
  end
end

# Dump warnings that come from the test to open files
# with the wrong spreadsheet class
#STDERR.reopen "/dev/null","w"

TESTDIR =  File.dirname(__FILE__) 
TMP_PREFIX = 'oo_*'
# require './' + TESTDIR + '/test_helper.rb'
require TESTDIR + '/test_helper.rb'

require 'fileutils'
require 'timeout'
require 'logger'
$log = Logger.new(File.join(ENV['HOME'],"roo.log"))
#$log.level = Logger::WARN
$log.level = Logger::DEBUG

DISPLAY_LOG = false
DB_LOG = false

if DB_LOG
  require 'activerecord'
end

include FileUtils

def running_windows?
  # to do
  # "besser loesen"
  # end
  File.exists? "C:\\"
end

if DB_LOG
  def activerecord_connect
    ActiveRecord::Base.establish_connection(:adapter => "mysql",
      :database => "test_runs",
      :host => "localhost",
      :username => "root",
      :socket => "/var/run/mysqld/mysqld.sock")
  end

  class Testrun < ActiveRecord::Base
  end
end

class Test::Unit::TestCase
  def key_of(spreadsheetname)
    begin
      
      return {
        #'formula' => 'rt4Pw1WmjxFtyfrqqy94wPw',
        'formula' => 'o10837434939102457526.3022866619437760118',
        #"write.me" => 'r6m7HFlUOwst0RTUTuhQ0Ow',
        "write.me" => '0AkCuGANLc3jFcHR1NmJiYWhOWnBZME4wUnJ4UWJXZHc',
        #'numbers1' => "rYraCzjxTtkxw1NxHJgDU8Q",
        'numbers1' => 'o10837434939102457526.4784396906364855777',
        #'borders' => "r_nLYMft6uWg_PT9Rc2urXw",
        'borders' => "o10837434939102457526.664868920231926255",
        #'simple_spreadsheet' => "r3aMMCBCA153TmU_wyIaxfw",
        'simple_spreadsheet' => "ptu6bbahNZpYe-L1vEBmgGA",
        'testnichtvorhandenBibelbund.ods' => "invalidkeyforanyspreadsheet", # !!! intentionally false key
        #"only_one_sheet" => "rqRtkcPJ97nhQ0m9ksDw2rA",
        "only_one_sheet" => "o10837434939102457526.762705759906130135",
        #'time-test' => 'r2XfDBJMrLPjmuLrPQQrEYw',
        'time-test' => 'ptu6bbahNZpYBMhk01UfXSg',
        #'datetime' => "r2kQpXWr6xOSUpw9MyXavYg",
        'datetime' => "ptu6bbahNZpYQEtZwzL_dZQ",
        'whitespace' => "rZyQaoFebVGeHKzjG6e9gRQ",
        'matrix' => '0AkCuGANLc3jFdHY3cWtYUkM4bVdadjZ5VGpfTzFEUEE',
      }[spreadsheetname]
      # 'numbers1' => "o10837434939102457526.4784396906364855777",
      # 'borders' => "o10837434939102457526.664868920231926255",
      # 'simple_spreadsheet' => "ptu6bbahNZpYe-L1vEBmgGA",
      # 'testnichtvorhandenBibelbund.ods' => "invalidkeyforanyspreadsheet", # !!! intentionally false key
      # "only_one_sheet" => "o10837434939102457526.762705759906130135",
      # "write.me" => 'ptu6bbahNZpY0N0RrxQbWdw&hl',
      # 'formula' => 'o10837434939102457526.3022866619437760118',
      # 'time-test' => 'ptu6bbahNZpYBMhk01UfXSg',
      # 'datetime' => "ptu6bbahNZpYQEtZwzL_dZQ",
    rescue
      raise "unknown spreadsheetname: #{spreadsheetname}"
    end
  end

  if DB_LOG
    if ! (defined?(@connected) and @connected)
      activerecord_connect
    else
      @connected = true
    end
  end
  alias unlogged_run run
  def run(result, &block)
    t1 = Time.now
    #RAILS_DEFAULT_LOGGER.debug "RUNNING #{self.class} #{@method_name} \t#{Time.now.to_s}"
    if DISPLAY_LOG
	    v1,v2,v3 = RUBY_VERSION.split('.')
	    if v1.to_i > 1 or
          (v1.to_i == 1 and v2.to_i > 8)
		    # Ruby 1.9.x
        print "RUNNING #{self.class} #{self.__name__} \t#{Time.now.to_s}"
	    else
		    # Ruby < 1.9.x
        print "RUNNING #{self.class} #{@method_name} \t#{Time.now.to_s}"
	    end
      STDOUT.flush
    end
    unlogged_run result, &block
    t2 = Time.now
    if DISPLAY_LOG
      puts "\t#{t2-t1} seconds"
    end
    if DB_LOG
      domain = Testrun.create(
        :class_name => self.class.to_s,
        :test_name => @method_name,
        :start => t1,
        :duration => t2-t1
      )
    end
    # TODO
    #if  Dir.glob("oo_*") != []
    #	    puts "nicht alle temp. Dateien geloescht"
    #	    puts Dir.glob("oo_*")
    #	    print "? "
    #      STDOUT.flush
    #	    a = gets
    #    end
  end
end

class File
  def File.delete_if_exist(filename)
    if File.exist?(filename)
      File.delete(filename)
    end
  end
end

# :nodoc
class Fixnum
  def minutes
    self * 60
  end
end

class TestRoo < Test::Unit::TestCase

  OPENOFFICE   = true  	# do Openoffice-Spreadsheet Tests? (.ods files)
  EXCEL        = true	# do Excel Tests? (.xls files)
  GOOGLE       = false 	# do Google-Spreadsheet Tests?
  EXCELX       = true  	# do Excelx Tests? (.xlsx files)
  LIBREOFFICE  = true  	# do Libreoffice tests? (.ods files)
  CSV          = true  	# do CSV tests? (.csv files)

  ONLINE = false
  LONG_RUN = true
  GLOBAL_TIMEOUT = 48.minutes 

  def setup
    #if DISPLAY_LOG
    #  puts " GLOBAL_TIMEOUT = #{GLOBAL_TIMEOUT}"
    #end
  end

  def test_internal_minutes
    assert_equal 42*60, 42.minutes
  end
  
  # call a block of code for each spreadsheet type 
  # and yield a reference to the roo object
  def with_each_spreadsheet(options)
    # test if the spreadsheet type is valid :nodoc
    if options[:format]
      if options[:format].is_a? Symbol
        options[:format] = [options[:format]]
      end
      options[:format].each do |formatname|
        unless [:openoffice,:excel,:excelx,:google,:libreoffice].include?(formatname)
          raise "invalid spreadsheet type #{formatname}"
        end
      end
    end
    # end test spreadsheet type :nodoc
    options[:format] ||= [:excel, :excelx, :openoffice, :google, :libreoffice]
    options[:format] = [options[:format]] if options[:format].class == Symbol
    yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.xls')) if EXCEL && options[:format].include?(:excel)
    yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.xlsx')) if EXCELX && options[:format].include?(:excelx)
    yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.ods')) if OPENOFFICE && options[:format].include?(:openoffice)
    yield Roo::Spreadsheet.open(key_of(options[:name]) || options[:name]) if GOOGLE && options[:format].include?(:google)
    yield Roo::Spreadsheet.open(File.join(TESTDIR, options[:name] + '.ods')) if LIBREOFFICE && options[:format].include?(:libreoffice)
  end

  # Using Date.strptime so check that it's using the method
  # with the value set in date_format
  def test_date
    with_each_spreadsheet(:name=>'numbers1', :format=>:google) do |oo|
      # should default to  DDMMYYYY
      assert oo.date?("21/11/1962")
      assert !oo.date?("11/21/1962")
      oo.date_format = '%m/%d/%Y'
      assert !oo.date?("21/11/1962")
      assert oo.date?("11/21/1962")
      oo.date_format = '%Y-%m-%d'
      assert(oo.date?("1962-11-21"))
      assert(!oo.date?("1962-21-11"))
    end
  end

  def test_classes
    if OPENOFFICE
      oo = Openoffice.new(File.join(TESTDIR,"numbers1.ods"))
      assert_kind_of Openoffice, oo
    end
    if EXCEL
      oo = Excel.new(File.join(TESTDIR,"numbers1.xls"))
      assert_kind_of Excel, oo
    end
    if GOOGLE
      oo = Google.new(key_of("numbers1"))
      assert_kind_of Google, oo
    end
    if EXCELX
      oo = Excelx.new(File.join(TESTDIR,"numbers1.xlsx"))
      assert_kind_of Excelx, oo
    end
    if LIBREOFFICE
      oo = Libreoffice.new(File.join(TESTDIR,"numbers1.ods"))
      assert_kind_of Libreoffice, oo
    end
    if CSV
      oo = Csv.new(File.join(TESTDIR,"numbers1.csv"))
      assert_kind_of Csv, oo
    end
  end

  def test_letters
    assert_equal 1, GenericSpreadsheet.letter_to_number('A')
    assert_equal 1, GenericSpreadsheet.letter_to_number('a')
    assert_equal 2, GenericSpreadsheet.letter_to_number('B')
    assert_equal 26, GenericSpreadsheet.letter_to_number('Z')
    assert_equal 27, GenericSpreadsheet.letter_to_number('AA')
    assert_equal 27, GenericSpreadsheet.letter_to_number('aA')
    assert_equal 27, GenericSpreadsheet.letter_to_number('Aa')
    assert_equal 27, GenericSpreadsheet.letter_to_number('aa')
  end

  def test_sheets_csv
    if CSV
      oo = Csv.new(File.join(TESTDIR,'numbers1.csv'))
      assert_equal ["default"], oo.sheets
      assert_raise(RangeError) { oo.default_sheet = "no_sheet" }
      assert_raise(TypeError)  { oo.default_sheet = [1,2,3] }
      oo.sheets.each { |sh|
        oo.default_sheet = sh
        assert_equal sh, oo.default_sheet
      }
    end
  end

  def test_sheets
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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] }
      oo.sheets.each { |sh|
        oo.default_sheet = sh
        assert_equal sh, oo.default_sheet
      }
    end
  end

  def test_cells
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      # warum ist Auswaehlen erstes sheet hier nicht
      # mehr drin?
      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)
      unless oo.kind_of? Csv
        assert_equal :float, oo.celltype(2,7)
      end
      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')
      unless oo.kind_of? Csv
        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
    end
  end
 
  def test_celltype
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      assert_equal :string, oo.celltype(2,6)
    end
  end

  def test_cell_address
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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
  end

  def test_office_version
    with_each_spreadsheet(:name=>'numbers1', :format=>:openoffice) do |oo|
      assert_equal "1.0", oo.officeversion
    end
  end

  def test_libre_office
	  if LIBREOFFICE
      oo = Libreoffice.new("test/numbers1.ods")
      oo.default_sheet = oo.sheets.first
      assert_equal 41, oo.cell('a',12)
	  end
  end

  #TODO: inkonsequente Lieferung Fixnum/Float
  def test_rows
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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, nil, nil], 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", nil, nil], oo.row(16)
    end
  end

  def test_last_row
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal 18, oo.last_row
    end
  end

  def test_last_column
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal 7, oo.last_column
    end
  end

  def test_last_column_as_letter
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal 'G', oo.last_column_as_letter
    end
  end

  def test_first_row
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal 1, oo.first_row
    end
  end

  def test_first_column
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal 1, oo.first_column
    end
  end

  def test_first_column_as_letter
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal 'A', oo.first_column_as_letter
    end
  end

  def test_sheetname
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = "Name of Sheet 2"
      assert_equal 'I am sheet 2', oo.cell('C',5)
      assert_raise(RangeError) { oo.default_sheet = "non existing sheet name" }
      assert_raise(RangeError) { oo.default_sheet = "non existing sheet name" }
      assert_raise(RangeError) { dummy = oo.cell('C',5,"non existing sheet name")}
      assert_raise(RangeError) { dummy = oo.celltype('C',5,"non existing sheet name")}
      assert_raise(RangeError) { dummy = oo.empty?('C',5,"non existing sheet name")}
      if oo.class == Excel
        assert_raise(RuntimeError) { dummy = oo.formula?('C',5,"non existing sheet name")}
        assert_raise(RuntimeError) { dummy = oo.formula('C',5,"non existing sheet name")}
      else
        assert_raise(RangeError) { dummy = oo.formula?('C',5,"non existing sheet name")}
        assert_raise(RangeError) { dummy = oo.formula('C',5,"non existing sheet name")}
        begin
          assert_raise(RangeError) { dummy = oo.set('C',5,42,"non existing sheet name")} unless oo.class == Google
        rescue NameError
          #
        end
        assert_raise(RangeError) { dummy = oo.formulas("non existing sheet name")}
      end
      assert_raise(RangeError) { dummy = oo.to_yaml({},1,1,1,1,"non existing sheet name")}
    end
  end

  def test_boundaries
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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
    end
  end

  def test_multiple_letters
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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')
    end
  end

  def test_argument_error
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      assert_nothing_raised(ArgumentError) {  oo.default_sheet = "Tabelle1" }
    end
  end

  def test_empty_eh
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      assert oo.empty?('a',14)
      assert ! oo.empty?('a',15)
      assert oo.empty?('a',20)
    end
  end

  def test_reload
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.default_sheet = 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
    with_each_spreadsheet(:name=>'numbers1', :format=>:openoffice) do |oo|
      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
  end

  def test_bug_italo_ve
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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
  end

  def test_italo_table
    with_each_spreadsheet(:name=>'simple_spreadsheet_from_italo', :format=>[:openoffice, :excel]) do |oo|
      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).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.03, oo.cell('a',5)+oo.cell('b',5)+oo.cell('c',5)

      # 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:
      if oo.class == Openoffice
        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
      else
        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
  end

  def test_formula_openoffice
    with_each_spreadsheet(:name=>'formula', :format=>:openoffice) do |oo|
      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(oo.sheets.first)

      # 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
  end

  def test_formula_google
    with_each_spreadsheet(:name=>'formula', :format=>:google) do |oo|
      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 21.0, oo.cell('A',7) #TODO: better solution Fixnum/Float
      assert_equal :formula, oo.celltype('A',7)
      # assert_equal "=[Sheet2.A1]", oo.formula('C',7)
      # !!! different from formulas in Openoffice
      #was: assert_equal "=sheet2!R[-6]C[-2]", oo.formula('C',7)
      # has Google changed their format of formulas/references to other sheets?
      assert_equal "=Sheet2!R[-6]C[-2]", 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(oo.sheets.first)
      # different format than in openoffice spreadsheets:
      #was:
      # assert_equal [[7, 1, "=SUM(R[-6]C[0]:R[-1]C[0])"],
      #  [7, 2, "=SUM(R1C1:R[-1]C[0])"],
      #  [7, 3, "=sheet2!R[-6]C[-2]"],
      #  [8, 2, "=SUM(R1C1:R[-1]C[0])"]],
      #  oo.formulas(oo.sheets.first)
      assert_equal [[7, 1, "=SUM(R[-6]C:R[-1]C)"],
        [7, 2, "=SUM(R1C1:R[-1]C)"],
        [7, 3, "=Sheet2!R[-6]C[-2]"],
        [8, 2, "=SUM(R1C1:R[-1]C)"]],
        oo.formulas(oo.sheets.first)
    end
  end

  def test_formula_excelx
    with_each_spreadsheet(:name=>'formula', :format=>:excelx) do |oo|
      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)
      #steht nicht in Datei, oder?
      #nein, diesen Bezug habe ich nur in der Openoffice-Datei
      #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(oo.sheets.first)
      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(oo.sheets.first)

      # 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
  end
  
  # Excel can only read the cell's value
  def test_formula_excel
    with_each_spreadsheet(:name=>'formula', :format=>:excel) do |oo|
      assert_equal 21, oo.cell('A',7)
      assert_equal 21, oo.cell('B',7)
    end
  end

  
  def test_borders_sheets
    with_each_spreadsheet(:name=>'borders') do |oo|
      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_equal 5, oo.first_row
      assert_equal 10, oo.last_row
      assert_equal 3, oo.first_column
      assert_equal 7, 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
  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
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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
  end

  def test_only_one_sheet
    with_each_spreadsheet(:name=>'only_one_sheet') do |oo|
      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
  end

  def test_excel_open_from_uri_and_zipped
    if EXCEL
      if ONLINE
        begin
          url = 'http://stiny-leonhard.de/bode-v1.xls.zip'
          excel = Excel.new(url, :zip)
          excel.default_sheet = excel.sheets.first
          assert_equal 'ist "e" im Nenner von H(s)', excel.cell('b', 5)
        end
      end
    end
  end

  def test_openoffice_open_from_uri_and_zipped
    if OPENOFFICE
      if ONLINE
        begin
          url = 'http://spazioinwind.libero.it/s2/rata.ods.zip'
          sheet = Openoffice.new(url, :zip)
          #has been changed: assert_equal 'ist "e" im Nenner von H(s)', sheet.cell('b', 5)
          assert_in_delta 0.001, 505.14, sheet.cell('c', 33).to_f
        end
      end
    end
  end

  def test_excel_zipped
    after Date.new(2011,12,7) do
      to do
        'hier wieder das Problem, dass ausgepacktes xls File
         unter Windows nicht geloescht werden kann, weil 
         das spreadsheet gem die Datei nicht schliesst.
         Fehler von spreadsheet gem'
      end
      if EXCEL
        begin
          oo = Excel.new(File.join(TESTDIR,"bode-v1.xls.zip"), :zip)
          assert oo
          assert_equal 'ist "e" im Nenner von H(s)', oo.cell('b', 5)
        end
      end
    end
  end

  def test_openoffice_zipped
    if OPENOFFICE
      begin
        oo = Openoffice.new(File.join(TESTDIR,"bode-v1.ods.zip"), :zip)
        assert oo
        assert_equal 'ist "e" im Nenner von H(s)', oo.cell('b', 5)
      end
    end
  end

  def test_bug_ric
    with_each_spreadsheet(:name=>'ric', :format=>:openoffice) do |oo|
      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 '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)
      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)
      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
  end

  def test_mehrteilig
    with_each_spreadsheet(:name=>'Bibelbund1', :format=>:openoffice) do |oo|
      assert_equal "Tagebuch des Sekret\303\244rs.    Letzte Tagung 15./16.11.75 Schweiz", oo.cell(45,'A')
    end
    #if EXCELX
    #    #Datei gibt es noch nicht
    #    oo = Excelx.new(File.join(TESTDIR,"Bibelbund1.xlsx"))
    #    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 tempdir
    Dir::tmpdir
  end

  # "/tmp/xxxx" darf man unter Windows nicht verwenden, weil das nicht erkannt
  # wird.
  # Besser: Methode um temporaeres Dir. portabel zu bestimmen
  def test_huge_document_to_csv
    if LONG_RUN
      with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
          :excel,
          :excelx
          # Google hier nicht, weil Google-Spreadsheets nicht so gross werden
          # duerfen
        ]) do |oo|
        assert_nothing_raised(Timeout::Error) {
          Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
            File.delete_if_exist(File.join(tempdir,"Bibelbund.csv"))
            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')
            assert oo.to_csv(File.join(tempdir,"Bibelbund.csv"))
            assert File.exists?(File.join(tempdir,"Bibelbund.csv"))
            #if running_windows?
            #  to do
            #    "diff is not available under windows"
            #  end
            #else
            # assert_equal "", `diff test/Bibelbund.csv /tmp/Bibelbund.csv`, "error in class #{oo.class}"
            #  assert_equal "", `diff test/Bibelbund.csv #{File.join(tempdir,"Bibelbund.csv")}`, "error in class #{oo.class}"
            assert_equal "",
            diff("test/Bibelbund.csv","#{File.join(tempdir,"Bibelbund.csv")}"),
            "error in class #{oo.class}"
            #end
          end
        }
      end
    end
  end
  
  def test_bug_quotes_excelx
	  if LONG_RUN
      with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
          :excel,
          :excelx]) do |oo|
        oo.default_sheet = oo.sheets.first
        assert_equal 'Einflüsse der neuen Theologie in "de gereformeerde Kerken van Nederland"',
          oo.cell('a',76)
        dummy = oo.to_csv("csv#{$$}")
        assert_equal 'Einflüsse der neuen Theologie in "de gereformeerde Kerken van Nederland"',
          oo.cell('a',78)
        File.delete_if_exist("csv#{$$}")
      end
	  end
  end

  def test_to_csv
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      master = "#{TESTDIR}/numbers1.csv"
      File.delete_if_exist(File.join(tempdir,"numbers1.csv"))
      assert oo.to_csv(File.join(tempdir,"numbers1.csv"),oo.sheets.first)
      assert(File.exists?(File.join(tempdir,"numbers1.csv")), "Datei #{tempdir}/numbers1.csv existiert nicht")
      if running_windows?
        warn "no 'diff' in windows"
      else
        assert_equal "", `diff #{master} #{File.join(tempdir,"numbers1.csv")}`
        assert oo.to_csv(File.join(tempdir,"numbers1.csv"))
        assert File.exists?(File.join(tempdir,"numbers1.csv"))
        assert_equal "", `diff #{master} #{File.join(tempdir,"numbers1.csv")}`
      end
    end
  end

  def test_bug_mehrere_datum
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      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
  end

  def test_multiple_sheets
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      2.times do
        oo.default_sheet = "Tabelle1"
        assert_equal 1, oo.cell(1,1)
        assert_equal 1, oo.cell(1,1,"Tabelle1")
        assert_equal "I am sheet 2", oo.cell('C',5,"Name of Sheet 2")
        sheetname = 'Sheet5'
        assert_equal :date, oo.celltype('A',4,sheetname)
        assert_equal :date, oo.celltype('B',4,sheetname)
        assert_equal :date, oo.celltype('C',4,sheetname)
        assert_equal :date, oo.celltype('D',4,sheetname)
        assert_equal :date, oo.celltype('E',4,sheetname)
        assert_equal Date.new(2007,11,21), oo.cell('A',4,sheetname)
        assert_equal Date.new(2007,11,21), oo.cell('B',4,sheetname)
        assert_equal Date.new(2007,11,21), oo.cell('C',4,sheetname)
        assert_equal Date.new(2007,11,21), oo.cell('D',4,sheetname)
        assert_equal Date.new(2007,11,21), oo.cell('E',4,sheetname)
        assert_equal :float, oo.celltype('A',5,sheetname)
        assert_equal :float, oo.celltype('B',5,sheetname)
        assert_equal :float, oo.celltype('C',5,sheetname)
        assert_equal :float, oo.celltype('D',5,sheetname)
        assert_equal :float, oo.celltype('E',5,sheetname)
        assert_equal 42, oo.cell('A',5,sheetname)
        assert_equal 42, oo.cell('B',5,sheetname)
        assert_equal 42, oo.cell('C',5,sheetname)
        assert_equal 42, oo.cell('D',5,sheetname)
        assert_equal 42, oo.cell('E',5,sheetname)
        assert_equal :string, oo.celltype('A',6,sheetname)
        assert_equal :string, oo.celltype('B',6,sheetname)
        assert_equal :string, oo.celltype('C',6,sheetname)
        assert_equal :string, oo.celltype('D',6,sheetname)
        assert_equal :string, oo.celltype('E',6,sheetname)
        assert_equal "ABC", oo.cell('A',6,sheetname)
        assert_equal "ABC", oo.cell('B',6,sheetname)
        assert_equal "ABC", oo.cell('C',6,sheetname)
        assert_equal "ABC", oo.cell('D',6,sheetname)
        assert_equal "ABC", oo.cell('E',6,sheetname)
        oo.reload
      end
    end
  end


  def test_bug_empty_sheet
    with_each_spreadsheet(:name=>'formula', :format=>[:openoffice, :excelx]) do |oo|
      oo.default_sheet = 'Sheet3' # is an empty sheet
      assert_nothing_raised() {  oo.to_csv(File.join(tempdir,"emptysheet.csv"))  }
      assert_equal "", `cat #{File.join(tempdir,"emptysheet.csv")}`
    end
  end

  def test_find_by_row_huge_document
    if LONG_RUN
      with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
          :excel,
          :excelx]) do |oo|
        Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
          oo.default_sheet = oo.sheets.first
          rec = oo.find 20
          assert rec
          # assert_equal "Brief aus dem Sekretariat", rec[0]
          #p rec
          assert_equal "Brief aus dem Sekretariat", rec[0]['TITEL']
          rec = oo.find 22
          assert rec
          # assert_equal "Brief aus dem Skretariat. Tagung in Amberg/Opf.",rec[0]
          assert_equal "Brief aus dem Skretariat. Tagung in Amberg/Opf.",rec[0]['TITEL']
        end
      end
    end
  end

  def test_find_by_row
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      oo.header_line = nil
      rec = oo.find 16
      assert rec
      assert_nil oo.header_line
      # keine Headerlines in diesem Beispiel definiert
      assert_equal "einundvierzig", rec[0]
      #assert_equal false, rec
      rec = oo.find 15
      assert rec
      assert_equal 41,rec[0]
    end
  end

  def test_find_by_conditions
    if LONG_RUN
      with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
          :excel,
          :excelx]) do |oo|
        assert_nothing_raised(Timeout::Error) {
          Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
            #-----------------------------------------------------------------
            zeilen = oo.find(:all, :conditions => {
                'TITEL' => 'Brief aus dem Sekretariat'
              }
            )
            assert_equal 2, zeilen.size
            assert_equal [{"VERFASSER"=>"Almassy, Annelene von",
                "INTERNET"=>nil,
                "SEITE"=>316.0,
                "KENNUNG"=>"Aus dem Bibelbund",
                "OBJEKT"=>"Bibel+Gem",
                "PC"=>"#C:\\Bibelbund\\reprint\\BuG1982-3.pdf#",
                "NUMMER"=>"1982-3",
                "TITEL"=>"Brief aus dem Sekretariat"},
              {"VERFASSER"=>"Almassy, Annelene von",
                "INTERNET"=>nil,
                "SEITE"=>222.0,
                "KENNUNG"=>"Aus dem Bibelbund",
                "OBJEKT"=>"Bibel+Gem",
                "PC"=>"#C:\\Bibelbund\\reprint\\BuG1983-2.pdf#",
                "NUMMER"=>"1983-2",
                "TITEL"=>"Brief aus dem Sekretariat"}] , zeilen

            #----------------------------------------------------------
            zeilen = oo.find(:all,
              :conditions => { 'VERFASSER' => 'Almassy, Annelene von' }
            )
            assert_equal 13, zeilen.size
            #----------------------------------------------------------
            zeilen = oo.find(:all, :conditions => {
                'TITEL' => 'Brief aus dem Sekretariat',
                'VERFASSER' => 'Almassy, Annelene von',
              }
            )
            assert_equal 2, zeilen.size
            assert_equal [{"VERFASSER"=>"Almassy, Annelene von",
                "INTERNET"=>nil,
                "SEITE"=>316.0,
                "KENNUNG"=>"Aus dem Bibelbund",
                "OBJEKT"=>"Bibel+Gem",
                "PC"=>"#C:\\Bibelbund\\reprint\\BuG1982-3.pdf#",
                "NUMMER"=>"1982-3",
                "TITEL"=>"Brief aus dem Sekretariat"},
              {"VERFASSER"=>"Almassy, Annelene von",
                "INTERNET"=>nil,
                "SEITE"=>222.0,
                "KENNUNG"=>"Aus dem Bibelbund",
                "OBJEKT"=>"Bibel+Gem",
                "PC"=>"#C:\\Bibelbund\\reprint\\BuG1983-2.pdf#",
                "NUMMER"=>"1983-2",
                "TITEL"=>"Brief aus dem Sekretariat"}] , zeilen

            # Result as an array
            zeilen = oo.find(:all,
              :conditions => {
                'TITEL' => 'Brief aus dem Sekretariat',
                'VERFASSER' => 'Almassy, Annelene von',
              }, :array => true)
            assert_equal 2, zeilen.size
            assert_equal [
              [
                "Brief aus dem Sekretariat",
                "Almassy, Annelene von",
                "Bibel+Gem",
                "1982-3",
                316.0,
                nil,
                "#C:\\Bibelbund\\reprint\\BuG1982-3.pdf#",
                "Aus dem Bibelbund",
              ],
              [
                "Brief aus dem Sekretariat",
                "Almassy, Annelene von",
                "Bibel+Gem",
                "1983-2",
                222.0,
                nil,
                "#C:\\Bibelbund\\reprint\\BuG1983-2.pdf#",
                "Aus dem Bibelbund",
              ]] , zeilen
          end # Timeout
        } # nothing_raised
      end
    end
  end


  #TODO: temporaerer Test
  def test_seiten_als_date
	  if LONG_RUN
      with_each_spreadsheet(:name=>'Bibelbund', :format=>:excelx) do |oo|
        assert_equal 'Bericht aus dem Sekretariat', oo.cell(13,1)
        assert_equal '1981-4', oo.cell(13,'D')
        assert_equal String, oo.excelx_type(13,'E')[1].class
        assert_equal [:numeric_or_formula,"General"], oo.excelx_type(13,'E')
        assert_equal '428', oo.excelx_value(13,'E')
        assert_equal 428.0, oo.cell(13,'E')
      end
	  end
  end

  def test_column
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      expected = [1.0,5.0,nil,10.0,Date.new(1961,11,21),'tata',nil,nil,nil,nil,'thisisa11',41.0,nil,nil,41.0,'einundvierzig',nil,Date.new(2007,5,31)]
      assert_equal expected, oo.column(1)
      assert_equal expected, oo.column('a')
    end
  end

  def test_column_huge_document
    if LONG_RUN
      with_each_spreadsheet(:name=>'Bibelbund', :format=>[:openoffice,
          :excel,
          :excelx]) do |oo|
        assert_nothing_raised(Timeout::Error) {
          Timeout::timeout(GLOBAL_TIMEOUT) do |timeout_length|
            oo.default_sheet = oo.sheets.first
            assert_equal 3735, oo.column('a').size
            #assert_equal 499, oo.column('a').size
          end
        }
      end
    end
  end

  def test_simple_spreadsheet_find_by_condition
    with_each_spreadsheet(:name=>'simple_spreadsheet') do |oo|
      oo.header_line = 3
      # oo.date_format = '%m/%d/%Y' if oo.class == Google
      erg = oo.find(:all, :conditions => {'Comment' => 'Task 1'})
      assert_equal Date.new(2007,05,07), erg[1]['Date']
      assert_equal 10.75       , erg[1]['Start time']
      assert_equal 12.50       , erg[1]['End time']
      assert_equal 0           , erg[1]['Pause']
      assert_equal 1.75        , erg[1]['Sum'] unless oo.class == Excel
      assert_equal "Task 1"    , erg[1]['Comment']
    end
  end
  
  # Ruby-spreadsheet now allows us to at least give the current value
  # from a cell with a formula (no possible with parseexcel)
  def test_bug_false_borders_with_formulas
    with_each_spreadsheet(:name=>'false_encoding', :format=>:excel) do |oo|
      assert_equal 1, oo.first_row
      assert_equal 3, oo.last_row
      assert_equal 1, oo.first_column
      assert_equal 4, oo.last_column
    end
  end

  # We'ce added minimal formula support so we can now read these
  # though not sure how the spreadsheet reports older values....
  def test_fe
    with_each_spreadsheet(:name=>'false_encoding', :format=>:excel) do |oo|
      assert_equal Date.new(2007,11,1), oo.cell('a',1)
      #DOES NOT WORK IN EXCEL FILES: assert_equal true, oo.formula?('a',1)
      #DOES NOT WORK IN EXCEL FILES: assert_equal '=TODAY()', oo.formula('a',1)

      assert_equal Date.new(2008,2,9), oo.cell('B',1)
      #DOES NOT WORK IN EXCEL FILES: assert_equal true,               oo.formula?('B',1)
      #DOES NOT WORK IN EXCEL FILES: assert_equal "=A1+100",          oo.formula('B',1)

      assert_kind_of DateTime, oo.cell('C',1)
      #DOES NOT WORK IN EXCEL FILES: assert_equal true,               oo.formula?('C',1)
      #DOES NOT WORK IN EXCEL FILES: assert_equal "=C1",          oo.formula('C',1)

      assert_equal 'H1', oo.cell('A',2)
      assert_equal 'H2', oo.cell('B',2)
      assert_equal 'H3', oo.cell('C',2)
      assert_equal 'H4', oo.cell('D',2)
      assert_equal 'R1', oo.cell('A',3)
      assert_equal 'R2', oo.cell('B',3)
      assert_equal 'R3', oo.cell('C',3)
      assert_equal 'R4', oo.cell('D',3)
    end
  end

  def test_excel_does_not_support_formulas
    with_each_spreadsheet(:name=>'false_encoding', :format=>:excel) do |oo|
      assert_raise(RuntimeError) { void = oo.formula('a',1) }
      assert_raise(RuntimeError) { void = oo.formula?('a',1) }
      assert_raise(RuntimeError) { void = oo.formulas(oo.sheets.first) }
    end
  end

  def get_extension(oo)
    case oo
    when Openoffice
      ".ods"
    when Excel
      ".xls"
    when Excelx
      ".xlsx"
    when Google
      ""
    end
  end
  
  def test_info
    expected_templ = "File: numbers1%s\n"+
      "Number of sheets: 5\n"+
      "Sheets: Tabelle1, Name of Sheet 2, Sheet3, Sheet4, Sheet5\n"+
      "Sheet 1:\n"+
      "  First row: 1\n"+
      "  Last row: 18\n"+
      "  First column: A\n"+
      "  Last column: G\n"+
      "Sheet 2:\n"+
      "  First row: 5\n"+
      "  Last row: 14\n"+
      "  First column: B\n"+
      "  Last column: E\n"+
      "Sheet 3:\n"+
      "  First row: 1\n"+
      "  Last row: 1\n"+
      "  First column: A\n"+
      "  Last column: BA\n"+
      "Sheet 4:\n"+
      "  First row: 1\n"+
      "  Last row: 1\n"+
      "  First column: A\n"+
      "  Last column: E\n"+
      "Sheet 5:\n"+
      "  First row: 1\n"+
      "  Last row: 6\n"+
      "  First column: A\n"+
      "  Last column: E"
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      ext = get_extension(oo)
      expected = sprintf(expected_templ,ext)
      begin
        if oo.class == Google
          assert_equal expected.gsub(/numbers1/,key_of("numbers1")), oo.info
        else
          assert_equal expected, oo.info
        end
      rescue NameError
        #
      end
    end
  end

  def test_bug_excel_numbers1_sheet5_last_row
    with_each_spreadsheet(:name=>'numbers1', :format=>:excel) do |oo|
      oo.default_sheet = "Tabelle1"
      assert_equal 1, oo.first_row
      assert_equal 18, oo.last_row
      assert_equal Openoffice.letter_to_number('A'), oo.first_column
      assert_equal Openoffice.letter_to_number('G'), oo.last_column
      oo.default_sheet = "Name of Sheet 2"
      assert_equal 5, oo.first_row
      assert_equal 14, oo.last_row
      assert_equal Openoffice.letter_to_number('B'), oo.first_column
      assert_equal Openoffice.letter_to_number('E'), oo.last_column
      oo.default_sheet = "Sheet3"
      assert_equal 1, oo.first_row
      assert_equal 1, oo.last_row
      assert_equal Openoffice.letter_to_number('A'), oo.first_column
      assert_equal Openoffice.letter_to_number('BA'), oo.last_column
      oo.default_sheet = "Sheet4"
      assert_equal 1, oo.first_row
      assert_equal 1, oo.last_row
      assert_equal Openoffice.letter_to_number('A'), oo.first_column
      assert_equal Openoffice.letter_to_number('E'), oo.last_column
      oo.default_sheet = "Sheet5"
      assert_equal 1, oo.first_row
      assert_equal 6, oo.last_row
      assert_equal Openoffice.letter_to_number('A'), oo.first_column
      assert_equal Openoffice.letter_to_number('E'), oo.last_column
    end
  end

  def test_should_raise_file_not_found_error
    if OPENOFFICE
      assert_raise(IOError) {
        oo = Openoffice.new(File.join('testnichtvorhanden','Bibelbund.ods'))
      }
    end
    if EXCEL
      assert_raise(IOError) {
        oo = Excel.new(File.join('testnichtvorhanden','Bibelbund.xls'))
      }
    end
    if EXCELX
      assert_raise(IOError) {
        oo = Excelx.new(File.join('testnichtvorhanden','Bibelbund.xlsx'))
      }
    end
    if GOOGLE
      #     assert_raise(Net::HTTPServerException) {
      # oo = Google.new(key_of('testnichtvorhanden'+'Bibelbund.ods'))
      #        oo = Google.new('testnichtvorhanden')
      #      }
    end
  end
  
  def test_write_google
    # write.me: http://spreadsheets.google.com/ccc?key=ptu6bbahNZpY0N0RrxQbWdw&hl=en_GB
    with_each_spreadsheet(:name=>'write.me', :format=>:google) do |oo|
      oo.default_sheet = oo.sheets.first
      oo.set_value(1,1,"hello from the tests")
      assert_equal "hello from the tests", oo.cell(1,1)
      oo.set_value(1,1, 1.0)
      assert_equal 1.0, oo.cell(1,1)
    end
  end

  def test_bug_set_value_with_more_than_one_sheet_google
    # write.me: http://spreadsheets.google.com/ccc?key=ptu6bbahNZpY0N0RrxQbWdw&hl=en_GB
    after Date.new(2011,6,19) do
      with_each_spreadsheet(:name=>'write.me', :format=>:google) do |oo|
        content1 = 'AAA'
        content2 = 'BBB'
        oo.default_sheet = oo.sheets.first
        oo.set_value(1,1,content1)
        oo.default_sheet = oo.sheets[1]
        oo.set_value(1,1,content2) # in the second sheet
        oo.default_sheet = oo.sheets.first
        assert_equal content1, oo.cell(1,1)
        oo.default_sheet = oo.sheets[1]
        assert_equal content2, oo.cell(1,1)
      end
    end
  end

  def test_set_value_with_sheet_argument_google
    with_each_spreadsheet(:name=>'write.me', :format=>:google) do |oo|
      random_row = rand(10)+1
      random_column = rand(10)+1
      content1 = 'ABC'
      content2 = 'DEF'
      oo.set_value(random_row,random_column,content1,oo.sheets.first)
      oo.set_value(random_row,random_column,content2,oo.sheets[1])
      assert_equal content1, oo.cell(random_row,random_column,oo.sheets.first)
      assert_equal content2, oo.cell(random_row,random_column,oo.sheets[1])
    end
  end

  def test_set_value_for_non_existing_sheet_google
    with_each_spreadsheet(:name=>'ptu6bbahNZpY0N0RrxQbWdw', :format=>:google) do |oo|
      assert_raise(RangeError) { oo.set_value(1,1,"dummy","no_sheet")   }
    end
  end

  def test_bug_bbu
    with_each_spreadsheet(:name=>'bbu', :format=>[:openoffice, :excelx, :excel]) do |oo|
      assert_nothing_raised() {
        assert_equal "File: bbu#{get_extension(oo)}
Number of sheets: 3
Sheets: 2007_12, Tabelle2, Tabelle3
Sheet 1:
  First row: 1
  Last row: 4
  First column: A
  Last column: F
Sheet 2:
  - empty -
Sheet 3:
  - empty -", oo.info
      }

      oo.default_sheet = oo.sheets[1] # empty sheet
      assert_nil oo.first_row
      assert_nil oo.last_row
      assert_nil oo.first_column
      assert_nil oo.last_column
    end
  end


  def test_bug_time_nil
    with_each_spreadsheet(:name=>'time-test') do |oo|
      assert_equal 12*3600+13*60+14, oo.cell('B',1) # 12:13:14 (secs since midnight)
      assert_equal :time, oo.celltype('B',1)
      assert_equal 15*3600+16*60, oo.cell('C',1) # 15:16    (secs since midnight)
      assert_equal :time, oo.celltype('C',1)
      assert_equal 23*3600, oo.cell('D',1) # 23:00    (secs since midnight)
      assert_equal :time, oo.celltype('D',1)
    end
  end

  def test_date_time_to_csv
    with_each_spreadsheet(:name=>'time-test') do |oo|
      begin
        csv_output = File.join(tempdir,'time_test.csv')
        assert oo.to_csv(csv_output)
        assert File.exists?(csv_output)
        assert_equal "", `diff --strip-trailing-cr #{TESTDIR}/time-test.csv #{csv_output}`
        # --strip-trailing-cr is needed because the test-file use 0A and
        # the test on an windows box generates 0D 0A as line endings
      ensure
        File.delete_if_exist(csv_output)
      end
    end
  end

  def test_date_time_yaml
    with_each_spreadsheet(:name=>'time-test') do |oo|
      expected =
        "--- \ncell_1_1: \n  row: 1 \n  col: 1 \n  celltype: string \n  value: Mittags: \ncell_1_2: \n  row: 1 \n  col: 2 \n  celltype: time \n  value: 12:13:14 \ncell_1_3: \n  row: 1 \n  col: 3 \n  celltype: time \n  value: 15:16:00 \ncell_1_4: \n  row: 1 \n  col: 4 \n  celltype: time \n  value: 23:00:00 \ncell_2_1: \n  row: 2 \n  col: 1 \n  celltype: date \n  value: 2007-11-21 \n"
      assert_equal expected, oo.to_yaml
    end
  end

  def test_no_remaining_tmp_files_openoffice
    after Date.new(2011,12,7) do
      # alles noch mal ueberarbeiten
      # temp. Directories sollten in diesem Fall ueberhaupt nicht
      # angelegt werden
      if OPENOFFICE
        assert_raise(Zip::ZipError) { #TODO: besseres Fehlerkriterium bei
          # oo = Openoffice.new(File.join(TESTDIR,"no_spreadsheet_file.txt"))
          # es soll absichtlich ein Abbruch provoziert werden, deshalb :ignore
          begin
            oo = Openoffice.new(File.join(TESTDIR,"no_spreadsheet_file.txt"),
              false,
              :ignore)
          rescue Zip::ZipError
            @tmp = Dir.glob(oo.tmpdir)
            raise
          end
        }
        assert @tmp.empty?, "temporay directory was not deleted"
      end
    end
  end

  def test_no_remaining_tmp_files_excel
    after Date.new(2011,12,7) do
      # alles noch mal ueberarbeiten
      # temp. Directories sollten in diesem Fall ueberhaupt nicht
      # angelegt werden
      if EXCEL
        prev = Dir.glob(TMP_PREFIX)
        assert_raise(Ole::Storage::FormatError) {
          oo = Excel.new(File.join(TESTDIR,"no_spreadsheet_file.txt"),
            false,
            :ignore)
        }
        now = Dir.glob(TMP_PREFIX)
        assert (now-prev).empty?, "temporary directory not removed"
      end
    end
  end

  def test_no_remaining_tmp_files_excelx
    after Date.new(2011,12,7) do
      # alles noch mal ueberarbeiten
      # temp. Directories sollten in diesem Fall ueberhaupt nicht
      # angelegt werden
      if EXCELX
        prev = Dir.glob(TMP_PREFIX)
        assert_raise(Zip::ZipError) { #TODO: besseres Fehlerkriterium bei
          # oo = Excelx.new(File.join(TESTDIR,"no_spreadsheet_file.txt"))
          # es soll absichtlich ein Abbruch provoziert werden, deshalb :ignore
          oo = Excelx.new(File.join(TESTDIR,"no_spreadsheet_file.txt"),
            false,
            :ignore)
        }
        now = Dir.glob(TMP_PREFIX)
        assert (now-prev).empty?
      end
    end
  end

  def test_no_remaining_tmp_files_google
    # Exception ist irgendwie anders, nochmal ansehen TODO:
    after Date.new(2011,6,19) do
      if GOOGLE
        prev = Dir.glob(TMP_PREFIX)
        assert_nothing_raised() {
          oo = Google.new(key_of("no_spreadsheet_file.txt"))
        }
        now = Dir.glob(TMP_PREFIX)
        assert (now-prev).empty?
      end
    end
  end

  # Erstellt eine Liste aller Zellen im Spreadsheet. Dies ist nötig, weil ein einfacher
  # Textvergleich des XML-Outputs nicht funktioniert, da xml-builder die Attribute
  # nicht immer in der gleichen Reihenfolge erzeugt.
  def init_all_cells(oo,sheet)
    all = []
    oo.first_row(sheet).upto(oo.last_row(sheet)) do |row|
      oo.first_column(sheet).upto(oo.last_column(sheet)) do |col|
        unless oo.empty?(row,col,sheet)
          all << {:row => row.to_s,
            :column => col.to_s,
            :content => oo.cell(row,col,sheet).to_s,
            :type => oo.celltype(row,col,sheet).to_s,
          }
        end
      end
    end
    all
  end

  def test_to_xml
    with_each_spreadsheet(:name=>'numbers1', :encoding => 'utf8') do |oo|
      assert_nothing_raised {oo.to_xml}
      sheetname = oo.sheets.first
      doc = Nokogiri::XML(oo.to_xml)
      sheet_count = 0
      doc.xpath('//spreadsheet/sheet').each {|tmpelem|
        sheet_count += 1
      }
      assert_equal 5, sheet_count
      doc.xpath('//spreadsheet/sheet').each { |xml_sheet|
        all_cells = init_all_cells(oo, sheetname)
        x = 0
        assert_equal sheetname, xml_sheet.attributes['name'].value
        xml_sheet.children.each {|cell|
          if cell.attributes['name']
            expected = [all_cells[x][:row],
              all_cells[x][:column],
              all_cells[x][:content],
              all_cells[x][:type],
            ]
            result = [
              cell.attributes['row'],
              cell.attributes['column'],
              cell.content,
              cell.attributes['type'],
            ]
            assert_equal expected, result
            x += 1
          end # if
        } # end of sheet
        sheetname = oo.sheets[oo.sheets.index(sheetname)+1]
      }
    end
	end

  def test_bug_row_column_fixnum_float
    with_each_spreadsheet(:name=>'bug-row-column-fixnum-float', :format=>:excel) do |oo|
      assert_equal 42.5, oo.cell('b',2)
      assert_equal 43  , oo.cell('c',2)
      assert_equal ['hij',42.5, 43], oo.row(2)
      assert_equal ['def',42.5, 'nop'], oo.column(2)
    end
  end

  def test_file_warning_default
    if OPENOFFICE
      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError, "test/numbers1.xls is not an openoffice spreadsheet") { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls")) }
      assert_raises(TypeError) { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx")) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?
    end
    if EXCEL
      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.ods")) }
      assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx")) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?, "temporary directory was not removed"
    end
    if EXCELX
      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.ods")) }
      assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.xls")) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?
    end
  end

  def test_file_warning_error
    if OPENOFFICE
      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls"),false,:error) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?

      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx"),false,:error) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?
    end
    if EXCEL
      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.ods"),false,:error) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?, "temporary directory was not deleted"

      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx"),false,:error) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?, "temporary directory was not deleted"
    end
    if EXCELX
      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.ods"),false,:error) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?, "temporary directory was not deleted"

      prev = Dir.glob(TMP_PREFIX)
      assert_raises(TypeError) { oo = Excelx.new(File.join(TESTDIR,"numbers1.xls"),false,:error) }
      now = Dir.glob(TMP_PREFIX)
      assert (now-prev).empty?, "temporary directory was not deleted"
    end
  end

  def test_file_warning_warning
    after Date.new(2011,12,7) do
      if OPENOFFICE
        assert_nothing_raised(TypeError) {
          assert_raises(Zip::ZipError) {
            oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls"),false, :warning)
          }
        }
	      prev = Dir.glob(TMP_PREFIX)
        assert_nothing_raised(TypeError) {
          assert_raises(Errno::ENOENT) {
            oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx"),false, :warning)
          }
        }
	      now = Dir.glob(TMP_PREFIX)
        assert (now-prev).empty?
      end
      if EXCEL
        prev = Dir.glob(TMP_PREFIX)
        assert_nothing_raised(TypeError) {
          assert_raises(Ole::Storage::FormatError) {
            oo = Excel.new(File.join(TESTDIR,"numbers1.ods"),false, :warning) }
        }
        assert_nothing_raised(TypeError) {
          assert_raises(Ole::Storage::FormatError) {
            oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx"),false, :warning) }
        }
        now = Dir.glob(TMP_PREFIX)
        assert (now-prev).empty?, "temporary directory was not removed"
      end
      if EXCELX
        prev = Dir.glob(TMP_PREFIX)
        assert_nothing_raised(TypeError) {
          assert_raises(Errno::ENOENT) {
            oo = Excelx.new(File.join(TESTDIR,"numbers1.ods"),false, :warning) }
        }
        assert_nothing_raised(TypeError) {
          assert_raises(Zip::ZipError) {
            oo = Excelx.new(File.join(TESTDIR,"numbers1.xls"),false, :warning) }
        }
        now = Dir.glob(TMP_PREFIX)
        assert (now-prev).empty?
      end
    end
  end

  def test_file_warning_ignore
    if OPENOFFICE
      # Files, die eigentlich Openoffice-
      # Files sind, aber die falsche Endung haben.
      # Es soll ohne Fehlermeldung oder Warnung
      # oder Abbruch die Datei geoffnet werden

      # xls
      assert_nothing_raised() {
        oo = Openoffice.new(File.join(TESTDIR,"type_openoffice.xls"),false, :ignore)
      }
      # xlsx
      assert_nothing_raised() {
        oo = Openoffice.new(File.join(TESTDIR,"type_openoffice.xlsx"),false, :ignore)
      }
    end
    if EXCEL
      assert_nothing_raised() {
        oo = Excel.new(File.join(TESTDIR,"type_excel.ods"),false, :ignore)
      }
      assert_nothing_raised() {
        oo = Excel.new(File.join(TESTDIR,"type_excel.xlsx"),false, :ignore)
      }
    end
    if EXCELX
      assert_nothing_raised() {
        oo = Excelx.new(File.join(TESTDIR,"type_excelx.ods"),false, :ignore)
      }
      assert_nothing_raised() {
        oo = Excelx.new(File.join(TESTDIR,"type_excelx.xls"),false, :ignore)
      }
    end
=begin
      if OPENOFFICE
        assert_nothing_raised() {
          assert_raises(Zip::ZipError) {
            oo = Openoffice.new(File.join(TESTDIR,"numbers1.xls"),false, :ignore) }
        }
        assert_nothing_raised() {
          assert_raises(Errno::ENOENT) {
            oo = Openoffice.new(File.join(TESTDIR,"numbers1.xlsx"),false, :ignore) }
        }
        assert Dir.glob(TMP_PREFIX).empty?
      end
      if EXCEL
        assert_nothing_raised(TypeError) {
          assert_raises(Ole::Storage::FormatError) {
            oo = Excel.new(File.join(TESTDIR,"numbers1.ods"),false, :ignore) }
        }
        assert_nothing_raised(TypeError) {
          assert_raises(Ole::Storage::FormatError) {oo = Excel.new(File.join(TESTDIR,"numbers1.xlsx"),false, :ignore) }}
        assert Dir.glob(TMP_PREFIX).empty?
      end
      if EXCELX
        assert_nothing_raised(TypeError) {
          assert_raises(Errno::ENOENT) {
            oo = Excelx.new(File.join(TESTDIR,"numbers1.ods"),false, :ignore)
          }
        }
        assert_nothing_raised(TypeError) {
          assert_raises(Zip::ZipError) {
            oo = Excelx.new(File.join(TESTDIR,"numbers1.xls"),false, :ignore)
          }
        }
        assert Dir.glob(TMP_PREFIX).empty?
      end
=end
  end

  def test_bug_last_row_excel
    with_each_spreadsheet(:name=>'time-test', :format=>:excel) do |oo|
      assert_equal 2, oo.last_row
    end
  end

  def test_bug_to_xml_with_empty_sheets
    with_each_spreadsheet(:name=>'emptysheets', :format=>[:openoffice, :excel]) do |oo|
      oo.sheets.each { |sheet|
        assert_equal nil, oo.first_row, "first_row not nil in sheet #{sheet}"
        assert_equal nil, oo.last_row, "last_row not nil in sheet #{sheet}"
        assert_equal nil, oo.first_column, "first_column not nil in sheet #{sheet}"
        assert_equal nil, oo.last_column, "last_column not nil in sheet #{sheet}"
        assert_equal nil, oo.first_row(sheet), "first_row not nil in sheet #{sheet}"
        assert_equal nil, oo.last_row(sheet), "last_row not nil in sheet #{sheet}"
        assert_equal nil, oo.first_column(sheet), "first_column not nil in sheet #{sheet}"
        assert_equal nil, oo.last_column(sheet), "last_column not nil in sheet #{sheet}"
      }
      assert_nothing_raised() { result = oo.to_xml }
    end
  end

  def test_bug_simple_spreadsheet_time_bug
    # really a bug? are cells really of type time?
    # No! :float must be the correct type
    with_each_spreadsheet(:name=>'simple_spreadsheet', :format=>:excelx) do |oo|
      # puts oo.cell('B',5).to_s
      # assert_equal :time, oo.celltype('B',5)
      assert_equal :float, oo.celltype('B',5)
      assert_equal 10.75, oo.cell('B',5)
      assert_equal 12.50, oo.cell('C',5)
      assert_equal 0, oo.cell('D',5)
      assert_equal 1.75, oo.cell('E',5)
      assert_equal 'Task 1', oo.cell('F',5)
      assert_equal Date.new(2007,5,7), oo.cell('A',5)
    end
  end

  def test_simple2_excelx
    with_each_spreadsheet(:name=>'simple_spreadsheet', :format=>:excelx) do |oo|
      assert_equal [:numeric_or_formula, "yyyy\\-mm\\-dd"], oo.excelx_type('A',4)
      assert_equal [:numeric_or_formula, "#,##0.00"], oo.excelx_type('B',4)
      assert_equal [:numeric_or_formula, "#,##0.00"], oo.excelx_type('c',4)
      assert_equal [:numeric_or_formula, "General"], oo.excelx_type('d',4)
      assert_equal [:numeric_or_formula, "General"], oo.excelx_type('e',4)
      assert_equal :string, oo.excelx_type('f',4)

      assert_equal "39209", oo.excelx_value('a',4)
      assert_equal "yyyy\\-mm\\-dd", oo.excelx_format('a',4)
      assert_equal "9.25", oo.excelx_value('b',4)
      assert_equal "10.25", oo.excelx_value('c',4)
      assert_equal "0", oo.excelx_value('d',4)
      #... Sum-Spalte
      # assert_equal "Task 1", oo.excelx_value('f',4)
      assert_equal "Task 1", oo.cell('f',4)
      assert_equal Date.new(2007,05,07), oo.cell('a',4)
      assert_equal "9.25", oo.excelx_value('b',4)
      assert_equal "#,##0.00", oo.excelx_format('b',4)
      assert_equal 9.25, oo.cell('b',4)
      assert_equal :float, oo.celltype('b',4)
      assert_equal :float, oo.celltype('d',4)
      assert_equal 0, oo.cell('d',4)
      assert_equal :formula, oo.celltype('e',4)
      assert_equal 1, oo.cell('e',4)
      assert_equal 'C4-B4-D4', oo.formula('e',4)
      assert_equal :string, oo.celltype('f',4)
      assert_equal "Task 1", oo.cell('f',4)
    end
  end

  def test_datetime
    with_each_spreadsheet(:name=>'datetime') do |oo|
      val = oo.cell('c',3)
      assert_kind_of DateTime, val
      assert_equal :datetime, oo.celltype('c',3)
      assert_equal DateTime.new(1961,11,21,12,17,18), val
      val = oo.cell('a',1)
      assert_kind_of Date, val
      assert_equal :date, oo.celltype('a',1)
      assert_equal Date.new(1961,11,21), val
      assert_equal Date.new(1961,11,21), oo.cell('a',1)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('a',3)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('b',3)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('c',3)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('a',4)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('b',4)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('c',4)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('a',5)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('b',5)
      assert_equal DateTime.new(1961,11,21,12,17,18), oo.cell('c',5)
      assert_equal Date.new(1961,11,21), oo.cell('a',6)
      assert_equal Date.new(1961,11,21), oo.cell('b',6)
      assert_equal Date.new(1961,11,21), oo.cell('c',6)
      assert_equal Date.new(1961,11,21), oo.cell('a',7)
      assert_equal Date.new(1961,11,21), oo.cell('b',7)
      assert_equal Date.new(1961,11,21), oo.cell('c',7)
    end
  end
  
  def test_cell_openoffice_html_escape
    with_each_spreadsheet(:name=>'html-escape', :format=>:openoffice) do |oo|
      assert_equal "'", oo.cell(1,1)
      assert_equal "&", oo.cell(2,1)
      assert_equal ">", oo.cell(3,1)
      assert_equal "<", oo.cell(4,1)
      assert_equal "`", oo.cell(5,1)
      # test_openoffice_zipped will catch issues with &quot;
    end
  end
  
  def test_cell_boolean
    with_each_spreadsheet(:name=>'boolean', :format=>[:openoffice, :excel, :excelx]) do |oo|
      if oo.class == Excelx
        assert_equal "TRUE", oo.cell(1,1), "failure in "+oo.class.to_s
        assert_equal "FALSE", oo.cell(2,1), "failure in "+oo.class.to_s
      else
        assert_equal "true", oo.cell(1,1), "failure in "+oo.class.to_s
        assert_equal "false", oo.cell(2,1), "failure in "+oo.class.to_s
      end
    end
  end
  
  def test_cell_multiline
    with_each_spreadsheet(:name=>'paragraph', :format=>[:openoffice, :excel, :excelx]) do |oo|
      assert_equal "This is a test\nof a multiline\nCell", oo.cell(1,1)
      assert_equal "This is a test\n¶\nof a multiline\n\nCell", oo.cell(1,2)
      assert_equal "first p\n\nsecond p\n\nlast p", oo.cell(2,1)
    end
  end
  
  def test_cell_styles
    # styles only valid in excel spreadsheets?
    # TODO: what todo with other spreadsheet types
    with_each_spreadsheet(:name=>'style', :format=>[# :openoffice,
        :excel,
        # :excelx
      ]) do |oo|
      # bold
      assert_equal true,  oo.font(1,1).bold?
      assert_equal false, oo.font(1,1).italic?
      assert_equal false, oo.font(1,1).underline?
    
      # italic
      assert_equal false, oo.font(2,1).bold?
      assert_equal true,  oo.font(2,1).italic?
      assert_equal false, oo.font(2,1).underline?

      # normal
      assert_equal false, oo.font(3,1).bold?
      assert_equal false, oo.font(3,1).italic?
      assert_equal false, oo.font(3,1).underline?

      # underline
      assert_equal false, oo.font(4,1).bold?
      assert_equal false, oo.font(4,1).italic?
      assert_equal true,  oo.font(4,1).underline?

      # bold italic
      assert_equal true,  oo.font(5,1).bold?
      assert_equal true,  oo.font(5,1).italic?
      assert_equal false, oo.font(5,1).underline?

      # bold underline
      assert_equal true,  oo.font(6,1).bold?
      assert_equal false, oo.font(6,1).italic?
      assert_equal true,  oo.font(6,1).underline?

      # italic underline
      assert_equal false, oo.font(7,1).bold?
      assert_equal true,  oo.font(7,1).italic?
      assert_equal true,  oo.font(7,1).underline?

      # bolded row
      assert_equal true, oo.font(8,1).bold?
      assert_equal false,  oo.font(8,1).italic?
      assert_equal false,  oo.font(8,1).underline?

      # bolded col
      assert_equal true, oo.font(9,2).bold?
      assert_equal false,  oo.font(9,2).italic?
      assert_equal false,  oo.font(9,2).underline?

      # bolded row, italic col
      assert_equal true, oo.font(10,3).bold?
      assert_equal true,  oo.font(10,3).italic?
      assert_equal false,  oo.font(10,3).underline?

      # normal
      assert_equal false, oo.font(11,4).bold?
      assert_equal false,  oo.font(11,4).italic?
      assert_equal false,  oo.font(11,4).underline?
    end
  end
  
  # If a cell has a date-like string but is preceeded by a '
  # to force that date to be treated like a string, we were getting an exception.
  # This test just checks for that exception to make sure it's not raised in this case
  def test_date_to_float_conversion
    with_each_spreadsheet(:name=>'datetime_floatconv', :format=>:excel) do |oo|
      assert_nothing_raised(NoMethodError) do
        oo.cell('a',1)
        oo.cell('a',2)
      end
    end
  end

  # Need to extend to other formats
  def test_row_whitespace
    # auf dieses Dokument habe ich keinen Zugriff TODO:
    with_each_spreadsheet(:name=>'whitespace') do |oo|
      oo.default_sheet = "Sheet1"
      assert_equal [nil, nil, nil, nil, nil, nil], oo.row(1)
      assert_equal [nil, nil, nil, nil, nil, nil], oo.row(2)
      assert_equal ["Date", "Start time", "End time", "Pause", "Sum", "Comment"], oo.row(3)
      assert_equal [Date.new(2007,5,7), 9.25, 10.25, 0.0, 1.0, "Task 1"], oo.row(4)
      assert_equal [nil, nil, nil, nil, nil, nil], oo.row(5)
      assert_equal [Date.new(2007,5,7), 10.75, 10.75, 0.0, 0.0, "Task 1"], oo.row(6)
      oo.default_sheet = "Sheet2"
      assert_equal ["Date", nil, "Start time"], oo.row(1)
      assert_equal [Date.new(2007,5,7), nil, 9.25], oo.row(2)
      assert_equal [Date.new(2007,5,7), nil,  10.75], oo.row(3)
    end
  end
  
  def test_col_whitespace
    #TODO:
    # kein Zugriff auf Dokument whitespace
    with_each_spreadsheet(:name=>'whitespace') do |oo|
      oo.default_sheet = "Sheet1"
      assert_equal ["Date", Date.new(2007,5,7), nil, Date.new(2007,5,7)], oo.column(1)
      assert_equal ["Start time", 9.25, nil, 10.75], oo.column(2)
      assert_equal ["End time", 10.25, nil, 10.75], oo.column(3)
      assert_equal ["Pause", 0.0, nil, 0.0], oo.column(4)
      assert_equal ["Sum", 1.0, nil, 0.0], oo.column(5)
      assert_equal ["Comment","Task 1", nil, "Task 1"], oo.column(6)
      oo.default_sheet = "Sheet2"
      assert_equal [nil, nil, nil], oo.column(1)
      assert_equal [nil, nil, nil], oo.column(2)
      assert_equal ["Date", Date.new(2007,5,7), Date.new(2007,5,7)], oo.column(3)
      assert_equal [nil, nil, nil], oo.column(4)
      assert_equal [ "Start time", 9.25, 10.75], oo.column(5)
    end
  end
  
  # Excel has two base date formats one from 1900 and the other from 1904.
  # There's a MS bug that 1900 base dates include an extra day due to erroneously
  # including 1900 as a leap yar.
  def test_base_dates_in_excel
    with_each_spreadsheet(:name=>'1900_base', :format=>:excel) do |oo|
      assert_equal Date.new(2009,06,15), oo.cell(1,1)
      #we don't want to to 'interpret' formulas  assert_equal Date.new(Time.now.year,Time.now.month,Time.now.day), oo.cell(2,1) #formula for TODAY()
      # if we test TODAY() we have also have to calculate
      # other date calculations
      #
      assert_equal :date, oo.celltype(1,1)
    end
    with_each_spreadsheet(:name=>'1904_base', :format=>:excel) do |oo|
      assert_equal Date.new(2009,06,15), oo.cell(1,1)
      # see comment above
      # assert_equal Date.new(Time.now.year,Time.now.month,Time.now.day), oo.cell(2,1) #formula for TODAY()
      assert_equal :date, oo.celltype(1,1)
    end
  end
  
  def test_bad_date
    with_each_spreadsheet(:name=>'prova', :format=>:excel) do |oo|
      assert_nothing_raised(ArgumentError) {
        assert_equal DateTime.new(2006,2,2,10,0,0), oo.cell('a',1)
      }
    end # each
  end

  def test_cell_methods
    with_each_spreadsheet(:name=>'numbers1') do |oo|
      assert_equal 10, oo.a4 # cell(4,'A')
      assert_equal 11, oo.b4 # cell(4,'B')
      assert_equal 12, oo.c4 # cell(4,'C')
      assert_equal 13, oo.d4 # cell(4,'D')
      assert_equal 14, oo.e4 # cell(4,'E')
      assert_equal 'ABC', oo.c6('Sheet5')

      #assert_raises(ArgumentError) {
      assert_raises(NoMethodError) {
        # a42a is not a valid cell name, should raise ArgumentError
        assert_equal 9999, oo.a42a
      }
    end
  end


  # compare large spreadsheets
  def test_compare_large_spreadsheets
    # problematisch, weil Formeln in Excel nicht unterstützt werden
    if LONG_RUN
      qq = Openoffice.new(File.join('test',"Bibelbund.ods"))
      with_each_spreadsheet(:name=>'Bibelbund') do |oo|
        # p "comparing Bibelbund.ods with #{oo.class}"
        oo.sheets.each do |sh|
          oo.first_row.upto(oo.last_row) do |row|
            oo.first_column.upto(oo.last_column) do |col|
              c1 = qq.cell(row,col,sh)
              c1.force_encoding("UTF-8") if c1.class == String
              c2 = oo.cell(row,col,sh)
              c2.force_encoding("UTF-8") if c2.class == String
              assert_equal c1, c2, "diff in #{sh}/#{row}/#{col}}"
              assert_equal qq.celltype(row,col,sh), oo.celltype(row,col,sh)
              assert_equal qq.formula?(row,col,sh), oo.formula?(row,col,sh) if oo.class != Excel
            end
          end
        end
      end
    end # LONG_RUN
  end

  def test_label
    with_each_spreadsheet(:name=>'named_cells', :format=>[:openoffice,:excelx,:libreoffice]) do |oo|
      # oo.default_sheet = oo.sheets.first
      begin
        row,col = oo.label('anton')
      rescue ArgumentError
        puts "labels error at #{oo.class}"
        raise
      end
      assert_equal 5, row, "error with label in class #{oo.class}"
      assert_equal 3, col, "error with label in class #{oo.class}"

      row,col = oo.label('anton')
      assert_equal 'Anton', oo.cell(row,col), "error with label in class #{oo.class}"

      row,col = oo.label('berta')
      assert_equal 'Bertha', oo.cell(row,col), "error with label in class #{oo.class}"

      row,col = oo.label('caesar')
      assert_equal 'Cäsar', oo.cell(row,col),"error with label in class #{oo.class}"

      row,col = oo.label('never')
      assert_nil row
      assert_nil col

      row,col,sheet = oo.label('anton')
      assert_equal 5, row
      assert_equal 3, col
      assert_equal "Sheet1", sheet
    end
  end

  def test_method_missing_anton
    with_each_spreadsheet(:name=>'named_cells', :format=>[:openoffice,:excelx,:libreoffice]) do |oo|
      # oo.default_sheet = oo.sheets.first
      assert_equal "Anton", oo.anton
      assert_raises(NoMethodError) {
        row,col = oo.never
      }
    end
  end

  def test_labels
    with_each_spreadsheet(:name=>'named_cells', :format=>[:openoffice,:excelx,:libreoffice]) do |oo|
      # oo.default_sheet = oo.sheets.first
      assert_equal [
	      ['anton',[5,3,'Sheet1']],
	      ['berta',[4,2,'Sheet1']],
	      ['caesar',[7,2,'Sheet1']],
      ], oo.labels, "error with labels array in class #{oo.class}"
    end
  end

  #  def test_labeled_cells
  #	   to do
  #     "more spreadsheet types"
  #	   end
  #    with_each_spreadsheet(:name=>'named_cells', :format=>[:openoffice,:excelx,:libreoffice]) do |oo|
  #      oo.default_sheet = oo.sheets.first
  #      begin
  #        row,col = oo.label('anton')
  #      rescue ArgumentError
  #        puts "labels error at #{oo.class}"
  #        raise
  #      end
  #      assert_equal 5, row
  #      assert_equal 3, col
  #
  #      row,col = oo.label('anton')
  #      assert_equal 'Anton', oo.cell(row,col)
  #
  #      row,col = oo.label('berta')
  #      assert_equal 'Bertha', oo.cell(row,col)
  #
  #      row,col = oo.label('caesar')
  #      assert_equal 'Cäsar', oo.cell(row,col)
  #
  #      row,col = oo.label('never')
  #      assert_nil row
  #      assert_nil col
  #
  #      row,col,sheet = oo.label('anton')
  #      assert_equal 5, row
  #      assert_equal 3, col
  #      assert_equal "Sheet1", sheet
  #
  #      assert_equal "Anton", oo.anton
  #      assert_raises(NoMethodError) {
  #        row,col = oo.never
  #      }
  #
  #      #assert_equal [
  ##	      ['anton',['Sheet1',5,3]],
  ##	      ['berta',['Sheet1',4,2]],
  ##	      ['caesar',['Sheet1',7,2]],
  ##      ], oo.labels, "error with labels array in class #{oo.class}"
  ## Reihenfolge row,col,sheet analog zu #label
  #      assert_equal [
  #	      ['anton',[5,3,'Sheet1']],
  #	      ['berta',[4,2,'Sheet1']],
  #	      ['caesar',[7,2,'Sheet1']],
  #      ], oo.labels, "error with labels array in class #{oo.class}"
  #    end
  #  end


  def test_bug_excel_last_row_255
    if LONG_RUN
      local_only do
        oo = Excel.new(File.join('..','confidential','ScienceStaff.xls'))
        oo.default_sheet = oo.sheets.first
        assert_equal "COMSCI", oo.cell(255,1)
        assert_equal "lala", oo.cell(256,1)
        assert_equal 1537, oo.last_row
      end
    end
  end

  def test_bug_excel_last_row_255_modified
    local_only do
      oo = Excel.new(File.join('..','confidential','ScienceStaff_modified.xls'))
      oo.default_sheet = oo.sheets.first
      assert_equal 1537, oo.last_row
    end
  end

  require 'matrix'
  def test_matrix
    with_each_spreadsheet(:name => 'matrix', :format => [:openoffice, :excel, :google]) do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal Matrix[
        [1.0, 2.0, 3.0],
        [4.0, 5.0, 6.0],
        [7.0, 8.0, 9.0] ], oo.to_matrix
    end
  end

  def test_matrix_selected_range
    with_each_spreadsheet(:name => 'matrix', :format=>[:excel,:openoffice,:google]) do |oo|
      oo.default_sheet = 'Sheet2'
      assert_equal Matrix[
        [1.0, 2.0, 3.0],
        [4.0, 5.0, 6.0],
        [7.0, 8.0, 9.0] ], oo.to_matrix(3,4,5,6)
    end
  end

  def test_matrix_all_nil
    with_each_spreadsheet(:name => 'matrix', :format=>[:excel,:openoffice,:google]) do |oo|
      oo.default_sheet = 'Sheet2'
      assert_equal Matrix[
        [nil, nil, nil],
        [nil, nil, nil],
        [nil, nil, nil] ], oo.to_matrix(10,10,12,12)
    end
  end

  def test_matrix_values_and_nil
    with_each_spreadsheet(:name => 'matrix', :format=>[:excel,:openoffice,:google]) do |oo|
      oo.default_sheet = 'Sheet3'
      assert_equal Matrix[
        [1.0, nil, 3.0],
        [4.0, 5.0, 6.0],
        [7.0, 8.0, nil] ], oo.to_matrix(1,1,3,3)
    end end

  def test_bug_date_mileszs
    after Date.new(2011,12,7) do
      # to do
      # "An richtige Stelle kopieren. Ist das Dokument vertraulich?"
      # 'ist auf dem Netbook nicht vorhanden'
      # end
      oo = Excel.new "/home/tp/Documents/feb-sales-analysis.xls"
      oo.default_sheet = oo.sheets.first
      # 2/1/2010 A2-A6 mm/dd/yyyy
      2.upto(6) do |i|
        assert_equal Date.new(2010,2,1), oo.cell('A',i)
      end
    end
  end

  #def test_no_generic_new
  #oo = GenericSpreadsheet.new
  #assert_equal GenericSpreadsheet, oo.class
  #end

  # unter Windows soll es laut Bug-Reports nicht moeglich sein, eine Excel-Datei, die
  # mit Excel.new geoeffnet wurde nach dem Processing anschliessend zu loeschen.
  # Anmerkung: Das Spreadsheet-Gem erlaubt kein explizites Close von Spreadsheet-Dateien,
  # was verhindern koennte, das die Datei geloescht werden kann.
  def test_bug_cannot_delete_opened_excel_sheet
    #    with_each_spreadsheet(:name=>'simple_spreadsheet') do |oo|
	  after Date.new(2011,7,17) do
		  # to do
      # 'kopiere  nach temporaere Datei
      # und versuche diese zu oeffnen und zu loeschen'
		  # end
    end
    #end
  end

  def test_bug_xlsx_reference_cell

    if EXCELX
=begin
    If cell A contains a string and cell B references cell A.  When reading the value of cell B, the result will be
"0.0" instead of the value of cell A.

With the attached test case, I ran the following code:
spreadsheet = Excelx.new('formula_string_error.xlsx')
spreadsheet.default_sheet = 'sheet1'
p "A: #{spreadsheet.cell(1, 1)}"
p "B: #{spreadsheet.cell(2, 1)}"

with the following results
"A: TestString"
"B: 0.0"

where the expected result is
"A: TestString"
"B: TestString"
=end
      xlsx = Excelx.new(File.join('test', "formula_string_error.xlsx"))
      xlsx.default_sheet = xlsx.sheets.first
      assert_equal 'Teststring', xlsx.cell('a',1)
      assert_equal 'Teststring', xlsx.cell('a',2)
    end
  end
  
  def test_bug_guest_list_2011_05_05
    after Date.new(2011,12,7) do
      oo = Excel.new(File.join("..","confidential","guest_list_addresses.xls"))
      oo.default_sheet = oo.sheets.first
      assert_equal "lalala", oo.cell('a',1) # anderer Inhalt im Spreadsheet
      assert_equal :string, oo.celltype('a',1)
    end
  end

  def test_bug_guest_list_2011_05_05_spreadsheet
	  # to do
    # 'wieder entfernen'
	  # end
	  require 'spreadsheet'
	  book = Spreadsheet.open File.join('..','confidential','guest_list_addresses.xls')
	  sheet1 = book.worksheet 0
	  sheet1.each do |row|
		  p row[0]
		  
	  end
  end

  # don't test it with other spreadsheet types! this was only a problem
  # with .xlsx files
  def test_bug_date_not_recognized_2011_05_21
    if EXCELX
      oo = Excelx.new(File.join('..','confidential','2011-05-21_sample_date_problem.xlsx'))
      oo.default_sheet = oo.sheets.first
      assert_equal Date.new(2011,3,24), oo.b4
      assert_equal Date.new(2011,3,25), oo.b5
      assert_equal Date.new(2011,5,5), oo.b6
      assert_equal Date.new(2012,3,23), oo.b7
    end
  end

  def test_bug_string_as_a_date_2011_05_21_spreadsheet_only
    after Date.new(2011,12,28) do
      if EXCEL
        # to do
        # 'wieder entfernen'
        # end
        require 'spreadsheet'
        book = Spreadsheet.open File.join('..','confidential','2011-05-21_sample_type_problem.xls')
        sheet1 = book.worksheet 0
        sheet1.each_with_index do |row,rownum|
          # p row[0]
          if rownum == 2
            assert_equal 68, row[6]
          end
        end
      end
    end
  end

  def test_bug_string_as_a_date_2011_05_21
    after Date.new(2011,12,7) do
      #oo = Excel.new(File.join(TESTDIR,'2011-05-21_sample_type_problem.xls'))
      oo = Excel.new(File.join('..','confidential','2011-05-21_sample_type_problem.xls'))
      oo.default_sheet = oo.sheets.first
      assert_equal 68, oo.g2
      assert_equal 72, oo.g3
      assert_equal 75, oo.g4
      assert_equal 76, oo.g5
      assert_equal 77, oo.g6
      assert_equal 78, oo.g7
    end
  end

  def test_bug_string_as_a_date_2011_05_21_saved_as_ods
	  #oo = Openoffice.new(File.join(TESTDIR,'2011-05-21_sample_type_problem.ods'))
	  oo = Openoffice.new(File.join('..','confidential','2011-05-21_sample_type_problem.ods'))
	  oo.default_sheet = oo.sheets.first
	  assert_equal 68, oo.g2
	  assert_equal 72, oo.g3
	  assert_equal 75, oo.g4
	  assert_equal 76, oo.g5
	  assert_equal 77, oo.g6
	  assert_equal 78, oo.g7
  end

  # #formulas of an empty sheet should return an empty array and not result in
  # an error message
  # 2011-06-24
  def test_bug_formulas_empty_sheet
    with_each_spreadsheet(:name =>'emptysheets',
      :format=>[:openoffice,:excelx,:google]) do |oo|
      assert_nothing_raised(NoMethodError) {
        oo.default_sheet = oo.sheets.first
        result = oo.formulas
      }
      assert_equal([], oo.formulas)
    end
  end

  # #to_yaml of an empty sheet should return an empty string and not result in
  # an error message
  # 2011-06-24
  def test_bug_to_yaml_empty_sheet
    with_each_spreadsheet(:name =>'emptysheets',
      :format=>[:openoffice,:excelx,:google]) do |oo|
      assert_nothing_raised(NoMethodError) {
        oo.default_sheet = oo.sheets.first
        result = oo.to_yaml
      }
      assert_equal('', oo.to_yaml)
    end
  end

  # #to_matrix of an empty sheet should return an empty matrix and not result in
  # an error message
  # 2011-06-25
  def test_bug_to_matrix_empty_sheet
    with_each_spreadsheet(:name =>'emptysheets',
      :format=>[:openoffice,:excelx,:google]) do |oo|
      assert_nothing_raised(NoMethodError) {
        oo.default_sheet = oo.sheets.first
        result = oo.to_matrix
      }
      assert_equal(Matrix.empty(0,0), oo.to_matrix)
    end
  end

  # 2011-08-03
  def test_bug_datetime_to_csv
    with_each_spreadsheet(:name=>'datetime') do |oo|
      assert oo.to_csv("datetime.csv")
      assert File.exists?("datetime.csv")
      assert_equal "", `diff test/so_datetime.csv datetime.csv`
    end
  end

  # 2011-08-11
  def test_bug_openoffice_formula_missing_letters
    if LIBREOFFICE
      # Dieses Dokument wurde mit LibreOffice angelegt.
      # Keine Ahnung, ob es damit zusammenhaengt, das diese
      # Formeln anders sind, als in der Datei formula.ods, welche
      # mit Openoffice angelegt wurde.
      # Bei den Openoffice-Dateien ist in diesem Feld in der XML-
      # Datei of: als Prefix enthalten, waehrend in dieser Datei
      # irgendetwas mit oooc: als Prefix verwendet wird.
      oo = Openoffice.new(File.join(TESTDIR,'dreimalvier.ods'))
      oo.default_sheet = oo.sheets.first
      assert_equal '=SUM([.A1:.D1])', oo.formula('e',1)
      assert_equal '=SUM([.A2:.D2])', oo.formula('e',2)
      assert_equal '=SUM([.A3:.D3])', oo.formula('e',3)
      assert_equal [ 
       [1,5,'=SUM([.A1:.D1])'],
        [2,5,'=SUM([.A2:.D2])'],
        [3,5,'=SUM([.A3:.D3])'],
      ], oo.formulas

    end
  end

=begin
  def test_postprocessing_and_types_in_csv
    if CSV
      oo = Csv.new(File.join(TESTDIR,'csvtypes.csv'))
      oo.default_sheet = oo.sheets.first
      assert_equal(1,oo.a1)
      assert_equal(:float,oo.celltype('A',1))
      assert_equal("2",oo.b1)
      assert_equal(:string,oo.celltype('B',1))
      assert_equal("Mayer",oo.c1)
      assert_equal(:string,oo.celltype('C',1))
    end
  end
=end

=begin
  def test_postprocessing_with_callback_function
    if CSV
      oo = Csv.new(File.join(TESTDIR,'csvtypes.csv'))
      oo.default_sheet = oo.sheets.first

      #
      assert_equal(1, oo.last_column)
    end
  end
=end

=begin
  def x_123
  class ::Csv
    def cell_postprocessing(row,col,value)
      if row < 3
        return nil
      end
      return value
    end
  end
  end
=end

  def test_nil_rows_and_lines_csv
	  to do
		  'wieder aktivieren'
	  end
=begin
	  x_123
	  if CSV
		  oo = Csv.new(File.join(TESTDIR,'Bibelbund.csv'))
		  oo.default_sheet = oo.sheets.first
		  assert_equal 3, oo.first_row
	  end
=end
  end

  def test_bug_pfand_from_windows_phone_xlsx
    with_each_spreadsheet(:name=>'Pfand_from_windows_phone', :format=>:excelx) do |oo|
	    oo.default_sheet = oo.sheets.first
	    assert_equal ['Blatt1','Blatt2','Blatt3'], oo.sheets
	    assert_equal 'Summe', oo.cell('b',1)

	    assert_equal Date.new(2011,9,14), oo.cell('a',2)
	    assert_equal :date, oo.celltype('a',2)
	    assert_equal Date.new(2011,9,15), oo.cell('a',3)
	    assert_equal :date, oo.celltype('a',3)

	    assert_equal 3.81, oo.cell('b',2)
	    assert_equal "SUM(C2:L2)", oo.formula('b',2)
	    assert_equal 0.7, oo.cell('c',2)
    end # each
  end

  def test_comment
    to do
      "more spreadsheet types"
    end
    with_each_spreadsheet(:name=>'comments', :format=>[:openoffice,:libreoffice,
        :excelx]) do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal 'Kommentar fuer B4',oo.comment('b',4)
      assert_equal 'Kommentar fuer B5',oo.comment('b',5)
      assert_nil oo.comment('b',99)
      # no comment at the second page
      oo.default_sheet = oo.sheets[1]
      assert_nil oo.comment('b',4)
    end
  end

  def test_comment?
    with_each_spreadsheet(:name=>'comments', :format=>[:openoffice,:libreoffice,
        :excelx]) do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal true, oo.comment?('b',4)
      assert_equal false, oo.comment?('b',99)
    end
  end

  def test_comments
    with_each_spreadsheet(:name=>'comments', :format=>[:openoffice,:libreoffice,
        :excelx]) do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal [
        [4, 2, "Kommentar fuer B4"],
        [5, 2, "Kommentar fuer B5"],
      ], oo.comments(oo.sheets.first), "comments error in class #{oo.class}"
      # no comments at the second page
      oo.default_sheet = oo.sheets[1]
      assert_equal [], oo.comments, "comments error in class #{oo.class}"
    end
  end

end # class