# -- encoding : utf-8 --
require 'test_helper'
require 'stringio'

class TestRooExcel < MiniTest::Test
  def with_spreadsheet(name)
    yield ::Roo::Spreadsheet.open(File.join(TESTDIR, "#{name}.xls"))
  end

  # Excel can only read the cell's value
  def test_formula_excel
    with_spreadsheet('formula') do |oo|
      assert_equal 21, oo.cell('A', 7)
      assert_equal 21, oo.cell('B', 7)
    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_spreadsheet('false_encoding') 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_spreadsheet('false_encoding') 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_spreadsheet('false_encoding') do |oo|
      assert_raises(NotImplementedError) { oo.formula('a', 1) }
      assert_raises(NotImplementedError) { oo.formula?('a', 1) }
      assert_raises(NotImplementedError) { oo.formulas(oo.sheets.first) }
    end
  end

  def test_bug_excel_numbers1_sheet5_last_row
    with_spreadsheet('numbers1') do |oo|
      oo.default_sheet = 'Tabelle1'
      assert_equal 1, oo.first_row
      assert_equal 18, oo.last_row
      assert_equal ::Roo::Utils.letter_to_number('A'), oo.first_column
      assert_equal ::Roo::Utils.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 ::Roo::Utils.letter_to_number('B'), oo.first_column
      assert_equal ::Roo::Utils.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 ::Roo::Utils.letter_to_number('A'), oo.first_column
      assert_equal ::Roo::Utils.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 ::Roo::Utils.letter_to_number('A'), oo.first_column
      assert_equal ::Roo::Utils.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 ::Roo::Utils.letter_to_number('A'), oo.first_column
      assert_equal ::Roo::Utils.letter_to_number('E'), oo.last_column
    end
  end

  def test_bug_row_column_fixnum_float
    with_spreadsheet('bug-row-column-fixnum-float') 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_ignore
    Roo::Excel.new(File.join(TESTDIR, 'type_excel.ods'),
                   packed: false,
                   file_warning: :ignore)
    Roo::Excel.new(File.join(TESTDIR, 'type_excel.xlsx'),
                   packed: false,
                   file_warning: :ignore)
  end

  def test_bug_last_row_excel
    with_spreadsheet('time-test') do |oo|
      assert_equal 2, oo.last_row
    end
  end

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

  def test_excel_zipped
    oo = Roo::Excel.new(File.join(TESTDIR, 'bode-v1.xls.zip'), packed: :zip)
    assert oo
    assert_equal 'ist "e" im Nenner von H(s)', oo.cell('b', 5)
  end

  def test_should_raise_file_not_found_error
    assert_raises(IOError) do
      Roo::Excel.new(File.join('testnichtvorhanden', 'Bibelbund.xls'))
    end
  end

  def test_file_warning_default
    assert_raises(TypeError) { Roo::Excel.new(File.join(TESTDIR, 'numbers1.ods')) }
    assert_raises(TypeError) { Roo::Excel.new(File.join(TESTDIR, 'numbers1.xlsx')) }
  end

  def test_file_warning_error
    assert_raises(TypeError) do
      Roo::Excel.new(File.join(TESTDIR, 'numbers1.ods'),
                     packed: false,
                     file_warning: :error)
    end
    assert_raises(TypeError) do
      Roo::Excel.new(File.join(TESTDIR, 'numbers1.xlsx'),
                     packed: false,
                     file_warning: :error)
    end
  end

  def test_file_warning_warning
    assert_raises(Ole::Storage::FormatError) do
      Roo::Excel.new(File.join(TESTDIR, 'numbers1.ods'),
                     packed: false,
                     file_warning: :warning)
    end
    assert_raises(Ole::Storage::FormatError) do
      Roo::Excel.new(File.join(TESTDIR, 'numbers1.xlsx'),
                     packed: false,
                     file_warning: :warning)
    end
  end

  def test_download_uri
    if ONLINE
      assert_raises(RuntimeError) do
        Roo::Excel.new('http://gibbsnichtdomainxxxxx.com/file.xls')
      end
    end
  end

  def test_download_uri_with_query_string
    dir = File.expand_path("#{File.dirname __FILE__}/files")
    file = "#{dir}/simple_spreadsheet.xls"
    url = 'http://test.example.com/simple_spreadsheet.xls?query-param=value'
    stub_request(:any, url).to_return(body: File.read(file))
    spreadsheet = Roo::Excel.new(url)
    spreadsheet.default_sheet = spreadsheet.sheets.first
    assert_equal 'Task 1', spreadsheet.cell('f', 4)
  end

  def test_italo_table
    with_spreadsheet('simple_spreadsheet_from_italo') 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:
      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

  # "/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_spreadsheet('Bibelbund') do |oo|
        Dir.mktmpdir do |tempdir|
          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.exist?(File.join(tempdir, 'Bibelbund.csv'))
          assert_equal '', file_diff(File.join(TESTDIR, 'Bibelbund.csv'), File.join(tempdir, 'Bibelbund.csv'))
        end
      end
    end
  end

  def test_bug_quotes_excelx
    if LONG_RUN
      with_spreadsheet('Bibelbund') 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)
        oo.to_csv("csv#{$PROCESS_ID}")
        assert_equal 'Einflüsse der neuen Theologie in "de gereformeerde Kerken van Nederland"',
                     oo.cell('a', 78)
        File.delete_if_exist("csv#{$PROCESS_ID}")
      end
    end
  end

  def test_find_by_row_huge_document
    if LONG_RUN
      with_spreadsheet('Bibelbund') do |oo|
        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

  def test_find_by_row
    with_spreadsheet('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_row_if_header_line_is_not_nil
    with_spreadsheet('numbers1') do |oo|
      oo.header_line = 2
      refute_nil oo.header_line
      rec = oo.find 1
      assert rec
      assert_equal 5, rec[0]
      assert_equal 6, rec[1]
      rec = oo.find 15
      assert rec
      assert_equal 'einundvierzig', rec[0]
    end
  end

  def test_find_by_conditions
    if LONG_RUN
      with_spreadsheet('Bibelbund') do |oo|
        #-----------------------------------------------------------------
        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
    end
  end

  # TODO: temporaerer Test
  def test_seiten_als_date
    if LONG_RUN
      with_spreadsheet('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_spreadsheet('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_spreadsheet('Bibelbund') do |oo|
        oo.default_sheet = oo.sheets.first
        assert_equal 3735, oo.column('a').size
        # assert_equal 499, oo.column('a').size
      end
    end
  end

  def test_simple_spreadsheet_find_by_condition
    with_spreadsheet('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']
      assert_equal 'Task 1', erg[1]['Comment']
    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_spreadsheet('numbers1') do |oo|
      expected = sprintf(expected_templ, '.xls')
      begin
        assert_equal expected, oo.info
      rescue NameError
        #
      end
    end
  end

  def test_info_doesnt_set_default_sheet
    with_spreadsheet('numbers1') do |oo|
      oo.default_sheet = 'Sheet3'
      oo.info
      assert_equal 'Sheet3', oo.default_sheet
    end
  end

  def test_bug_bbu
    with_spreadsheet('bbu') do |oo|
      assert_equal "File: bbu.xls
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_spreadsheet('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_spreadsheet('time-test') do |oo|
      Dir.mktmpdir do |tempdir|
        csv_output = File.join(tempdir, 'time_test.csv')
        assert oo.to_csv(csv_output)
        assert File.exist?(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
      end
    end
  end

  def test_boolean_to_csv
    with_spreadsheet('boolean') do |oo|
      Dir.mktmpdir do |tempdir|
        csv_output = File.join(tempdir, 'boolean.csv')
        assert oo.to_csv(csv_output)
        assert File.exist?(csv_output)
        assert_equal '', `diff --strip-trailing-cr #{TESTDIR}/boolean.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
      end
    end
  end

  def test_link_to_csv
    with_spreadsheet('link') do |oo|
      Dir.mktmpdir do |tempdir|
        csv_output = File.join(tempdir, 'link.csv')
        assert oo.to_csv(csv_output)
        assert File.exist?(csv_output)
        assert_equal '', `diff --strip-trailing-cr #{TESTDIR}/link.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
      end
    end
  end

  def test_date_time_yaml
    with_spreadsheet('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

  # 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_spreadsheet('numbers1') do |oo|
      oo.to_xml
      sheetname = oo.sheets.first
      doc = Nokogiri::XML(oo.to_xml)
      sheet_count = 0
      doc.xpath('//spreadsheet/sheet').each do|_tmpelem|
        sheet_count += 1
      end
      assert_equal 5, sheet_count
      doc.xpath('//spreadsheet/sheet').each do |xml_sheet|
        all_cells = init_all_cells(oo, sheetname)
        x = 0
        assert_equal sheetname, xml_sheet.attributes['name'].value
        xml_sheet.children.each do|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 # end of sheet
        sheetname = oo.sheets[oo.sheets.index(sheetname) + 1]
      end
    end
  end

  def test_bug_to_xml_with_empty_sheets
    with_spreadsheet('emptysheets') do |oo|
      oo.sheets.each do |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}"
      end
      oo.to_xml
    end
  end

  def test_datetime
    with_spreadsheet('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)
      assert_equal DateTime.new(2013, 11, 5, 11, 45, 00), oo.cell('a', 8)
      assert_equal DateTime.new(2013, 11, 5, 11, 45, 00), oo.cell('b', 8)
      assert_equal DateTime.new(2013, 11, 5, 11, 45, 00), oo.cell('c', 8)
    end
  end

  def test_cell_boolean
    with_spreadsheet('boolean') do |oo|
      assert_equal 'true', oo.cell(1, 1)
      assert_equal 'false', oo.cell(2, 1)
    end
  end

  def test_cell_multiline
    with_spreadsheet('paragraph') 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_spreadsheet('style') 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_spreadsheet('datetime_floatconv') do |oo|
      oo.cell('a', 1)
      oo.cell('a', 2)
    end
  end

  # Need to extend to other formats
  def test_row_whitespace
    # auf dieses Dokument habe ich keinen Zugriff TODO:
    with_spreadsheet('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_spreadsheet('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

  def test_ruby_spreadsheet_formula_bug
    with_spreadsheet('formula_parse_error') do |oo|
      assert_equal '5026', oo.cell(2, 3)
      assert_equal '5026', oo.cell(3, 3)
    end
  end

  def test_excel_links
    with_spreadsheet('link') do |oo|
      assert_equal 'Google', oo.cell(1, 1)
      assert_equal 'http://www.google.com', oo.cell(1, 1).url
    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
    with_spreadsheet('1900_base') 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_spreadsheet('1904_base') 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_spreadsheet('prova') do |oo|
      assert_equal DateTime.new(2006, 2, 2, 10, 0, 0), oo.cell('a', 1)
    end
  end

  def test_bad_excel_date
    with_spreadsheet('bad_excel_date') do |oo|
      assert_equal DateTime.new(2006, 2, 2, 10, 0, 0), oo.cell('a', 1)
    end
  end

  def test_cell_methods
    with_spreadsheet('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) do
        # a42a is not a valid cell name, should raise ArgumentError
        assert_equal 9999, oo.a42a
      end
    end
  end

  # compare large spreadsheets
  def test_compare_large_spreadsheets
    # problematisch, weil Formeln in Excel nicht unterstützt werden
    if LONG_RUN
      qq = Roo::OpenOffice.new(File.join('test', 'Bibelbund.ods'))
      with_spreadsheet('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)
            end
          end
        end
      end
    end # LONG_RUN
  end

  require 'matrix'
  def test_matrix
    with_spreadsheet('matrix') 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_spreadsheet('matrix') 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_spreadsheet('matrix') 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_spreadsheet('matrix') 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_matrix_specifying_sheet
    with_spreadsheet('matrix') do |oo|
      oo.default_sheet = oo.sheets.first
      assert_equal Matrix[
        [1.0, nil, 3.0],
        [4.0, 5.0, 6.0],
        [7.0, 8.0, nil]], oo.to_matrix(nil, nil, nil, nil, 'Sheet3')
    end
  end

  # 2011-08-03
  def test_bug_datetime_to_csv
    with_spreadsheet('datetime') do |oo|
      Dir.mktmpdir do |tempdir|
        datetime_csv_file = File.join(tempdir, 'datetime.csv')

        assert oo.to_csv(datetime_csv_file)
        assert File.exist?(datetime_csv_file)
        assert_equal '', file_diff('test/files/so_datetime.csv', datetime_csv_file)
      end
    end
  end

  def common_possible_bug_snowboard_cells(ss)
    assert_equal 'A.', ss.cell(13, 'A'), ss.class
    assert_equal 147, ss.cell(13, 'f'), ss.class
    assert_equal 152, ss.cell(13, 'g'), ss.class
    assert_equal 156, ss.cell(13, 'h'), ss.class
    assert_equal 158, ss.cell(13, 'i'), ss.class
    assert_equal 160, ss.cell(13, 'j'), ss.class
    assert_equal 164, ss.cell(13, 'k'), ss.class
    assert_equal 168, ss.cell(13, 'l'), ss.class
    assert_equal :string, ss.celltype(13, 'm'), ss.class
    assert_equal '159W', ss.cell(13, 'm'), ss.class
    assert_equal '164W', ss.cell(13, 'n'), ss.class
    assert_equal '168W', ss.cell(13, 'o'), ss.class
  end

  # def test_false_encoding
  #   ex = Roo::Excel.new(File.join(TESTDIR,'false_encoding.xls'))
  #   ex.default_sheet = ex.sheets.first
  #   assert_equal "Sheet1", ex.sheets.first
  #   ex.first_row.upto(ex.last_row) do |row|
  #     ex.first_column.upto(ex.last_column) do |col|
  #       content = ex.cell(row,col)
  #       puts "#{row}/#{col}"
  #       #puts content if ! ex.empty?(row,col) or ex.formula?(row,col)
  #       if ex.formula?(row,col)
  #         #! ex.empty?(row,col)
  #         puts content
  #       end
  #     end
  #   end
  # end

  # 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

  def split_coord(s)
    letter = ''
    number = 0
    i = 0
    while i < s.length && 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'.include?(s[i, 1])
      letter += s[i, 1]
      i += 1
    end
    while i < s.length && '01234567890'.include?(s[i, 1])
      number = number * 10 + s[i, 1].to_i
      i += 1
    end
    if letter == '' || number == 0
      raise ArgumentError
    end
    [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_create_spreadsheet1
  #  name = File.join(TESTDIR,'createdspreadsheet.ods')
  #  rm(name) if File.exists?(File.join(TESTDIR,'createdspreadsheet.ods'))
  #  # anlegen, falls noch nicht existierend
  #  s = OpenOffice.new(name,true)
  #  assert File.exists?(name)
  # end

  # We don't have the bode-v1.xlsx test file
  # #TODO: xlsx-Datei anpassen!
  # def test_excelx_download_uri_and_zipped
  #   #TODO: gezippte xlsx Datei online zum Testen suchen
  #   if EXCELX
  #     if ONLINE
  #       url = 'http://stiny-leonhard.de/bode-v1.xlsx.zip'
  #       excel = Roo::Excelx.new(url, :zip)
  #       assert_equal 'ist "e" im Nenner von H(s)', excel.cell('b', 5)
  #     end
  #   end
  # end

  # def test_excelx_zipped
  #   # TODO: bode...xls bei Gelegenheit nach .xlsx konverieren lassen und zippen!
  #   if EXCELX
  #     # diese Datei gibt es noch nicht gezippt
  #     excel = Roo::Excelx.new(File.join(TESTDIR,"bode-v1.xlsx.zip"), :zip)
  #     assert excel
  #     assert_raises(ArgumentError) {
  #       assert_equal 'ist "e" im Nenner von H(s)', excel.cell('b', 5)
  #     }
  #     excel.default_sheet = excel.sheets.first
  #     assert_equal 'ist "e" im Nenner von H(s)', excel.cell('b', 5)
  #   end
  # end

  def test_excel_via_stringio
    io = StringIO.new(
      File.read(File.join(TESTDIR, 'simple_spreadsheet.xls')))
    spreadsheet = ::Roo::Spreadsheet.open(io, extension: '.xls')
    spreadsheet.default_sheet = spreadsheet.sheets.first
    assert_equal 'Task 1', spreadsheet.cell('f', 4)
  end
end