Import Upstream version 2.7.1upstream/2.7.1

5 files changed, 865 insertions, 0 deletions

diff --git a/test/roo/test_base.rb b/test/roo/test_base.rb
new file mode 100644
index 0000000..31ddb09
--- /dev/null
+++ b/test/roo/test_base.rb
@@ -0,0 +1,182 @@
+require "test_helper"
+
+class TestRooBase < Minitest::Test
+  def test_info
+    # NOTE: unfortunately, the ods and xlsx versions of numbers1 are not
+    #       identical, so this test fails for Open Office.
+    expected_templ = File.read("#{TESTDIR}/expected_results/numbers_info.yml")
+    with_each_spreadsheet(name: "numbers1", format: [:excelx]) do |workbook|
+      ext = get_extension(workbook)
+      expected = Kernel.format(expected_templ, ext)
+      assert_equal expected.strip, workbook.info.strip
+    end
+  end
+
+  def test_column
+    with_each_spreadsheet(name: "numbers1") do |workbook|
+      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, workbook.column(1)
+      assert_equal expected, workbook.column("a")
+    end
+  end
+
+  def test_column_huge_document
+    skip_long_test
+    with_each_spreadsheet(name: "Bibelbund", format: [:openoffice, :excelx]) do |workbook|
+      workbook.default_sheet = workbook.sheets.first
+      assert_equal 3735, workbook.column("a").size
+    end
+  end
+
+  def test_simple_spreadsheet_find_by_condition
+    with_each_spreadsheet(name: "simple_spreadsheet") do |workbook|
+      workbook.header_line = 3
+      results = workbook.find(:all, conditions: { "Comment" => "Task 1" })
+      assert_equal Date.new(2007, 05, 07), results[1]["Date"]
+      assert_equal 10.75, results[1]["Start time"]
+      assert_equal 12.50, results[1]["End time"]
+      assert_equal 0, results[1]["Pause"]
+      assert_equal 1.75, results[1]["Sum"]
+      assert_equal "Task 1", results[1]["Comment"]
+    end
+  end
+
+  def test_bug_bbu
+    expected_templ = File.read("#{TESTDIR}/expected_results/bbu_info.txt")
+    with_each_spreadsheet(name: "bbu", format: [:openoffice, :excelx]) do |workbook|
+      ext = get_extension(workbook)
+      expected_result = Kernel.format(expected_templ, ext)
+      assert_equal expected_result.strip, workbook.info.strip
+
+      workbook.default_sheet = workbook.sheets[1] # empty sheet
+      assert_nil workbook.first_row
+      assert_nil workbook.last_row
+      assert_nil workbook.first_column
+      assert_nil workbook.last_column
+    end
+  end
+
+  def test_find_by_row_huge_document
+    skip_long_test
+    options = { name: "Bibelbund", format: [:openoffice, :excelx] }
+    with_each_spreadsheet(options) do |workbook|
+      workbook.default_sheet = workbook.sheets.first
+      result = workbook.find 20
+      assert result
+      assert_equal "Brief aus dem Sekretariat", result[0]["TITEL"]
+
+      result = workbook.find 22
+      assert result
+      assert_equal "Brief aus dem Skretariat. Tagung in Amberg/Opf.", result[0]["TITEL"]
+    end
+  end
+
+  def test_find_by_row
+    with_each_spreadsheet(name: "numbers1") do |workbook|
+      workbook.header_line = nil
+      result = workbook.find 16
+      assert result
+      assert_nil workbook.header_line
+      # keine Headerlines in diesem Beispiel definiert
+      assert_equal "einundvierzig", result[0]
+      # assert_equal false, results
+      result = workbook.find 15
+      assert result
+      assert_equal 41, result[0]
+    end
+  end
+
+  def test_find_by_row_if_header_line_is_not_nil
+    with_each_spreadsheet(name: "numbers1") do |workbook|
+      workbook.header_line = 2
+      refute_nil workbook.header_line
+      results = workbook.find 1
+      assert results
+      assert_equal 5, results[0]
+      assert_equal 6, results[1]
+      results = workbook.find 15
+      assert results
+      assert_equal "einundvierzig", results[0]
+    end
+  end
+
+  def test_find_by_conditions
+    skip_long_test
+    expected_results = [
+      {
+        "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"
+      }
+    ]
+
+    expected_results_size = 2
+    options = { name: "Bibelbund", format: [:openoffice, :excelx] }
+    with_each_spreadsheet(options) do |workbook|
+      results = workbook.find(:all, conditions: { "TITEL" => "Brief aus dem Sekretariat" })
+      assert_equal expected_results_size, results.size
+      assert_equal expected_results, results
+
+      conditions = {
+        "TITEL" => "Brief aus dem Sekretariat",
+        "VERFASSER" => "Almassy, Annelene von"
+      }
+      results = workbook.find(:all, conditions: conditions)
+      assert_equal expected_results, results
+      assert_equal expected_results_size, results.size
+
+      results = workbook.find(:all, conditions: { "VERFASSER" => "Almassy, Annelene von" })
+      assert_equal 13, results.size
+    end
+  end
+
+  def test_find_by_conditions_with_array_option
+    expected_results = [
+      [
+        "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",
+      ]
+    ]
+    options = { name: "Bibelbund", format: [:openoffice, :excelx] }
+    with_each_spreadsheet(options) do |workbook|
+      conditions = {
+        "TITEL" => "Brief aus dem Sekretariat",
+        "VERFASSER" => "Almassy, Annelene von"
+      }
+      results = workbook.find(:all, conditions: conditions, array: true)
+      assert_equal 2, results.size
+      assert_equal expected_results, results
+    end
+  end
+end
diff --git a/test/roo/test_csv.rb b/test/roo/test_csv.rb
new file mode 100644
index 0000000..c5e251d
--- /dev/null
+++ b/test/roo/test_csv.rb
@@ -0,0 +1,60 @@
+require "test_helper"
+
+class TestRooCSV < Minitest::Test
+  def test_sheets
+    file = filename("numbers1")
+    workbook = roo_class.new(File.join(TESTDIR, file))
+    assert_equal ["default"], workbook.sheets
+    assert_raises(RangeError) { workbook.default_sheet = "no_sheet" }
+    assert_raises(TypeError)  { workbook.default_sheet = [1, 2, 3] }
+    workbook.sheets.each do |sh|
+      workbook.default_sheet = sh
+      assert_equal sh, workbook.default_sheet
+    end
+  end
+
+  def test_nil_rows_and_lines_csv
+    # x_123
+    oo = Roo::CSV.new(File.join(TESTDIR,'Bibelbund.csv'))
+    oo.default_sheet = oo.sheets.first
+    assert_equal 1, oo.first_row
+    assert_equal 3735, oo.last_row
+    assert_equal 1, oo.first_column
+    assert_equal 8, oo.last_column
+  end
+
+  def test_empty_csv
+    # x_123
+    oo = Roo::CSV.new(File.join(TESTDIR,'emptysheets.csv'))
+    oo.default_sheet = oo.sheets.first
+    assert_equal 1, oo.first_row
+    assert_equal 1, oo.last_row
+    assert_equal 1, oo.first_column
+    assert_equal 1, oo.last_column
+  end
+
+  def test_csv_parsing_with_headers
+    return unless CSV
+    headers = ["TITEL", "VERFASSER", "OBJEKT", "NUMMER", "SEITE", "INTERNET", "PC", "KENNUNG"]
+
+    oo = Roo::Spreadsheet.open(File.join(TESTDIR, "Bibelbund.csv"))
+    parsed = oo.parse(headers: true)
+    assert_equal headers, parsed[1].keys
+  end
+
+  def test_iso_8859_1
+    file = File.open(File.join(TESTDIR, "iso_8859_1.csv"))
+    options = { csv_options: { col_sep: ";", encoding: Encoding::ISO_8859_1 } }
+    workbook = Roo::CSV.new(file.path, options)
+    result = workbook.last_column
+    assert_equal(19, result)
+  end
+
+  def roo_class
+    Roo::CSV
+  end
+
+  def filename(name)
+    "#{name}.csv"
+  end
+end
diff --git a/test/roo/test_excelx.rb b/test/roo/test_excelx.rb
new file mode 100644
index 0000000..75ecfa2
--- /dev/null
+++ b/test/roo/test_excelx.rb
@@ -0,0 +1,325 @@
+require "test_helper"
+
+class TestRworkbookExcelx < Minitest::Test
+  def test_download_uri_with_invalid_host
+    assert_raises(RuntimeError) do
+      Roo::Excelx.new("http://example.com/file.xlsx")
+    end
+  end
+
+  def test_download_uri_with_query_string
+    file = filename("simple_spreadsheet")
+    port = 12_344
+    url = "#{local_server(port)}/#{file}?query-param=value"
+
+    start_local_server(file, port) do
+      spreadsheet = roo_class.new(url)
+      assert_equal "Task 1", spreadsheet.cell("f", 4)
+    end
+  end
+
+  def test_should_raise_file_not_found_error
+    assert_raises(IOError) do
+      roo_class.new(File.join("testnichtvorhanden", "Bibelbund.xlsx"))
+    end
+  end
+
+  def test_file_warning_default
+    assert_raises(TypeError) { roo_class.new(File.join(TESTDIR, "numbers1.ods")) }
+    assert_raises(TypeError) { roo_class.new(File.join(TESTDIR, "numbers1.xls")) }
+  end
+
+  def test_file_warning_error
+    %w(ods xls).each do |extension|
+      assert_raises(TypeError) do
+        options = { packed: false, file_warning: :error }
+        roo_class.new(File.join(TESTDIR, "numbers1. #{extension}"), options)
+      end
+    end
+  end
+
+  def test_file_warning_warning
+    options = { packed: false, file_warning: :warning }
+    assert_raises(ArgumentError) do
+      roo_class.new(File.join(TESTDIR, "numbers1.ods"), options)
+    end
+  end
+
+  def test_file_warning_ignore
+    options = { packed: false, file_warning: :ignore }
+    sheet = roo_class.new(File.join(TESTDIR, "type_excelx.ods"), options)
+    assert sheet, "Should not throw an error"
+  end
+
+  def test_bug_xlsx_reference_cell
+    # NOTE: 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.
+    #
+    # Before this test case, the following code:
+    #
+    # spreadsheet = Roo::Excelx.new("formula_string_error.xlsx")
+    # spreadsheet.default_sheet = "sheet1"
+    # p "A: #{spreadsheet.cell(1, 1)}" #=> "A: TestString"
+    # p "B: #{spreadsheet.cell(2, 1)}" #=> "B: 0.0"
+    #
+    # where the expected result is
+    # "A: TestString"
+    # "B: TestString"
+    xlsx = roo_class.new(File.join(TESTDIR, "formula_string_error.xlsx"))
+    assert_equal "Teststring", xlsx.cell("a", 1)
+    assert_equal "Teststring", xlsx.cell("a", 2)
+  end
+
+  def test_parsing_xslx_from_numbers
+    xlsx = roo_class.new(File.join(TESTDIR, "numbers-export.xlsx"))
+
+    xlsx.default_sheet = xlsx.sheets.first
+    assert_equal "Sheet 1", xlsx.cell("a", 1)
+
+    # Another buggy behavior of Numbers 3.1: if a warkbook has more than a
+    # single sheet, all sheets except the first one will have an extra row and
+    # column added to the beginning. That's why we assert against cell B2 and
+    # not A1
+    xlsx.default_sheet = xlsx.sheets.last
+    assert_equal "Sheet 2", xlsx.cell("b", 2)
+  end
+
+  def assert_cell_range_values(sheet, row_range, column_range, is_merged_range, expected_value)
+    row_range.each do |row|
+      column_range.each do |col|
+        value = sheet.cell(col, row)
+        if is_merged_range.call(row, col)
+          assert_equal expected_value, value
+        else
+          assert_nil value
+        end
+      end
+    end
+  end
+
+  def test_expand_merged_range
+    options = { expand_merged_ranges: true }
+    xlsx = roo_class.new(File.join(TESTDIR, "merged_ranges.xlsx"), options)
+
+    [
+      {
+        rows: (3..7),
+        columns: ("a".."b"),
+        conditional: ->(row, col) { row > 3 && row < 7 && col == "a" },
+        expected_value: "vertical1"
+      },
+      {
+        rows: (3..11),
+        columns: ("f".."h"),
+        conditional: ->(row, col) { row > 3 && row < 11 && col == "g" },
+        expected_value: "vertical2"
+      },
+      {
+        rows: (3..5),
+        columns: ("b".."f"),
+        conditional: ->(row, col) { row == 4 && col > "b" && col < "f" },
+        expected_value: "horizontal"
+      },
+      {
+        rows: (8..13),
+        columns: ("a".."e"),
+        conditional: ->(row, col) { row > 8 && row < 13 && col > "a" && col < "e" },
+        expected_value: "block"
+      }
+    ].each do |data|
+      rows, cols, conditional, expected_value = data.values
+      assert_cell_range_values(xlsx, rows, cols, conditional, expected_value)
+    end
+  end
+
+  def test_noexpand_merged_range
+    xlsx = roo_class.new(File.join(TESTDIR, "merged_ranges.xlsx"))
+
+    [
+      {
+        rows: (3..7),
+        columns: ("a".."b"),
+        conditional: ->(row, col) { row == 4 && col == "a" },
+        expected_value: "vertical1"
+      },
+      {
+        rows: (3..11),
+        columns: ("f".."h"),
+        conditional: ->(row, col) { row == 4 && col == "g" },
+        expected_value: "vertical2"
+      },
+      {
+        rows: (3..5),
+        columns: ("b".."f"),
+        conditional: ->(row, col) { row == 4 && col == "c" },
+        expected_value: "horizontal"
+      },
+      {
+        rows: (8..13),
+        columns: ("a".."e"),
+        conditional: ->(row, col) { row == 9 && col == "b" },
+        expected_value: "block"
+      }
+    ].each do |data|
+      rows, cols, conditional, expected_value = data.values
+      assert_cell_range_values(xlsx, rows, cols, conditional, expected_value)
+    end
+  end
+
+  def test_open_stream
+    file = filename(:numbers1)
+    file_contents = File.read File.join(TESTDIR, file), encoding: "BINARY"
+    stream = StringIO.new(file_contents)
+    xlsx = roo_class.new(stream)
+    expected_sheet_names = ["Tabelle1", "Name of Sheet 2", "Sheet3", "Sheet4", "Sheet5"]
+    assert_equal expected_sheet_names, xlsx.sheets
+  end
+
+  def test_header_offset
+    xlsx = roo_class.new(File.join(TESTDIR, "header_offset.xlsx"))
+    data = xlsx.parse(column_1: "Header A1", column_2: "Header B1")
+    assert_equal "Data A2", data[0][:column_1]
+    assert_equal "Data B2", data[0][:column_2]
+  end
+
+  def test_formula_excelx
+    with_each_spreadsheet(name: "formula", format: :excelx) do |workbook|
+      assert_equal 1, workbook.cell("A", 1)
+      assert_equal 2, workbook.cell("A", 2)
+      assert_equal 3, workbook.cell("A", 3)
+      assert_equal 4, workbook.cell("A", 4)
+      assert_equal 5, workbook.cell("A", 5)
+      assert_equal 6, workbook.cell("A", 6)
+      assert_equal 21, workbook.cell("A", 7)
+      assert_equal :formula, workbook.celltype("A", 7)
+      assert_nil workbook.formula("A", 6)
+
+      expected_result = [
+        [7, 1, "SUM(A1:A6)"],
+        [7, 2, "SUM($A$1:B6)"],
+      ]
+      assert_equal expected_result, workbook.formulas(workbook.sheets.first)
+
+      # setting a cell
+      workbook.set("A", 15, 41)
+      assert_equal 41, workbook.cell("A", 15)
+      workbook.set("A", 16, "41")
+      assert_equal "41", workbook.cell("A", 16)
+      workbook.set("A", 17, 42.5)
+      assert_equal 42.5, workbook.cell("A", 17)
+    end
+  end
+
+  # TODO: temporaerer Test
+  def test_seiten_als_date
+    skip_long_test
+
+    with_each_spreadsheet(name: "Bibelbund", format: :excelx) do |workbook|
+      assert_equal "Bericht aus dem Sekretariat", workbook.cell(13, 1)
+      assert_equal "1981-4", workbook.cell(13, "D")
+      assert_equal String, workbook.excelx_type(13, "E")[1].class
+      assert_equal [:numeric_or_formula, "General"], workbook.excelx_type(13, "E")
+      assert_equal "428", workbook.excelx_value(13, "E")
+      assert_equal 428.0, workbook.cell(13, "E")
+    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 |workbook|
+      # puts workbook.cell("B", 5).to_s
+      # assert_equal :time, workbook.celltype("B", 5)
+      assert_equal :float, workbook.celltype("B", 5)
+      assert_equal 10.75, workbook.cell("B", 5)
+      assert_equal 12.50, workbook.cell("C", 5)
+      assert_equal 0, workbook.cell("D", 5)
+      assert_equal 1.75, workbook.cell("E", 5)
+      assert_equal "Task 1", workbook.cell("F", 5)
+      assert_equal Date.new(2007, 5, 7), workbook.cell("A", 5)
+    end
+  end
+
+  def test_simple2_excelx
+    with_each_spreadsheet(name: "simple_spreadsheet", format: :excelx) do |workbook|
+      assert_equal [:numeric_or_formula, "yyyy\\-mm\\-dd"], workbook.excelx_type("A", 4)
+      assert_equal [:numeric_or_formula, "#,##0.00"], workbook.excelx_type("B", 4)
+      assert_equal [:numeric_or_formula, "#,##0.00"], workbook.excelx_type("c", 4)
+      assert_equal [:numeric_or_formula, "General"], workbook.excelx_type("d", 4)
+      assert_equal [:numeric_or_formula, "General"], workbook.excelx_type("e", 4)
+      assert_equal :string, workbook.excelx_type("f", 4)
+
+      assert_equal "39209", workbook.excelx_value("a", 4)
+      assert_equal "yyyy\\-mm\\-dd", workbook.excelx_format("a", 4)
+      assert_equal "9.25", workbook.excelx_value("b", 4)
+      assert_equal "10.25", workbook.excelx_value("c", 4)
+      assert_equal "0", workbook.excelx_value("d", 4)
+      # ... Sum-Spalte
+      # assert_equal "Task 1", workbook.excelx_value("f", 4)
+      assert_equal "Task 1", workbook.cell("f", 4)
+      assert_equal Date.new(2007, 05, 07), workbook.cell("a", 4)
+      assert_equal "9.25", workbook.excelx_value("b", 4)
+      assert_equal "#,##0.00", workbook.excelx_format("b", 4)
+      assert_equal 9.25, workbook.cell("b", 4)
+      assert_equal :float, workbook.celltype("b", 4)
+      assert_equal :float, workbook.celltype("d", 4)
+      assert_equal 0, workbook.cell("d", 4)
+      assert_equal :formula, workbook.celltype("e", 4)
+      assert_equal 1, workbook.cell("e", 4)
+      assert_equal "C4-B4-D4", workbook.formula("e", 4)
+      assert_equal :string, workbook.celltype("f", 4)
+      assert_equal "Task 1", workbook.cell("f", 4)
+    end
+  end
+
+  def test_bug_pfand_from_windows_phone_xlsx
+    # skip_jruby_incompatible_test
+    # TODO: Does JRUBY need to skip this test
+    return if defined? JRUBY_VERSION
+
+    options = { name: "Pfand_from_windows_phone", format: :excelx }
+    with_each_spreadsheet(options) do |workbook|
+      workbook.default_sheet = workbook.sheets.first
+      assert_equal ["Blatt1", "Blatt2", "Blatt3"], workbook.sheets
+      assert_equal "Summe", workbook.cell("b", 1)
+
+      assert_equal Date.new(2011, 9, 14), workbook.cell("a", 2)
+      assert_equal :date, workbook.celltype("a", 2)
+      assert_equal Date.new(2011, 9, 15), workbook.cell("a", 3)
+      assert_equal :date, workbook.celltype("a", 3)
+
+      assert_equal 3.81, workbook.cell("b", 2)
+      assert_equal "SUM(C2:L2)", workbook.formula("b", 2)
+      assert_equal 0.7, workbook.cell("c", 2)
+    end # each
+  end
+
+  def test_excelx_links
+    with_each_spreadsheet(name: "link", format: :excelx) do |workbook|
+      assert_equal "Google", workbook.cell(1, 1)
+      assert_equal "http://www.google.com", workbook.cell(1, 1).href
+    end
+  end
+
+  # Excel has two base date formats one from 1900 and the other from 1904.
+  # see #test_base_dates_in_excel
+  def test_base_dates_in_excelx
+    with_each_spreadsheet(name: "1900_base", format: :excelx) do |workbook|
+      assert_equal Date.new(2009, 06, 15), workbook.cell(1, 1)
+      assert_equal :date, workbook.celltype(1, 1)
+    end
+    with_each_spreadsheet(name: "1904_base", format: :excelx) do |workbook|
+      assert_equal Date.new(2009, 06, 15), workbook.cell(1, 1)
+      assert_equal :date, workbook.celltype(1, 1)
+    end
+  end
+
+  def roo_class
+    Roo::Excelx
+  end
+
+  def filename(name)
+    "#{name}.xlsx"
+  end
+end
diff --git a/test/roo/test_libre_office.rb b/test/roo/test_libre_office.rb
new file mode 100644
index 0000000..7bdac45
--- /dev/null
+++ b/test/roo/test_libre_office.rb
@@ -0,0 +1,9 @@
+require "test_helper"
+
+class TestRooOpenOffice < Minitest::Test
+  def test_libre_office
+    oo = Roo::LibreOffice.new(File.join(TESTDIR, "numbers1.ods"))
+    oo.default_sheet = oo.sheets.first
+    assert_equal 41, oo.cell("a", 12)
+  end
+end
diff --git a/test/roo/test_open_office.rb b/test/roo/test_open_office.rb
new file mode 100644
index 0000000..e6118b6
--- /dev/null
+++ b/test/roo/test_open_office.rb
@@ -0,0 +1,289 @@
+# encoding: utf-8
+require "test_helper"
+
+class TestRooOpenOffice < Minitest::Test
+  def test_openoffice_download_uri_and_zipped
+    port = 12_345
+    file = "rata.ods.zip"
+    start_local_server(file, port) do
+      url = "#{local_server(port)}/#{file}"
+
+      workbook = roo_class.new(url, packed: :zip)
+      assert_in_delta 0.001, 505.14, workbook.cell("c", 33).to_f
+    end
+  end
+
+  def test_download_uri_with_invalid_host
+    assert_raises(RuntimeError) do
+      roo_class.new("http://example.com/file.ods")
+    end
+  end
+
+  def test_download_uri_with_query_string
+    file = filename("simple_spreadsheet")
+    port = 12_346
+    url = "#{local_server(port)}/#{file}?query-param=value"
+    start_local_server(file, port) do
+      spreadsheet = roo_class.new(url)
+      assert_equal "Task 1", spreadsheet.cell("f", 4)
+    end
+  end
+
+  def test_openoffice_zipped
+    workbook = roo_class.new(File.join(TESTDIR, "bode-v1.ods.zip"), packed: :zip)
+    assert workbook
+    assert_equal 'ist "e" im Nenner von H(s)', workbook.cell("b", 5)
+  end
+
+  def test_should_raise_file_not_found_error
+    assert_raises(IOError) do
+      roo_class.new(File.join("testnichtvorhanden", "Bibelbund.ods"))
+    end
+  end
+
+  def test_file_warning_default_is_error
+    expected_message = "test/files/numbers1.xls is not an openoffice spreadsheet"
+    assert_raises(TypeError, expected_message) do
+      roo_class.new(File.join(TESTDIR, "numbers1.xls"))
+    end
+
+    assert_raises(TypeError) do
+      roo_class.new(File.join(TESTDIR, "numbers1.xlsx"))
+    end
+  end
+
+  def test_file_warning_error
+    options = { packed: false, file_warning: :error }
+
+    assert_raises(TypeError) do
+      roo_class.new(File.join(TESTDIR, "numbers1.xls"), options)
+    end
+
+    assert_raises(TypeError) do
+      roo_class.new(File.join(TESTDIR, "numbers1.xlsx"), options)
+    end
+  end
+
+  def test_file_warning_warning
+    assert_raises(ArgumentError) do
+      options = { packed: false, file_warning: :warning }
+      roo_class.new(File.join(TESTDIR, "numbers1.xlsx"), options)
+    end
+  end
+
+  def test_file_warning_ignore
+    options = { packed: false, file_warning: :ignore }
+    assert roo_class.new(File.join(TESTDIR, "type_openoffice.xlsx"), options), "Should not throw an error"
+  end
+
+  def test_encrypted_file
+    workbook = roo_class.new(File.join(TESTDIR, "encrypted-letmein.ods"), password: "letmein")
+    assert_equal "Hello World", workbook.cell("a", 1)
+  end
+
+  def test_encrypted_file_requires_password
+    assert_raises(ArgumentError) do
+      roo_class.new(File.join(TESTDIR, "encrypted-letmein.ods"))
+    end
+  end
+
+  def test_encrypted_file_with_incorrect_password
+    assert_raises(ArgumentError) do
+      roo_class.new(File.join(TESTDIR, "encrypted-letmein.ods"), password: "badpassword")
+    end
+  end
+
+  # 2011-08-11
+  def test_bug_openoffice_formula_missing_letters
+    # NOTE: This document was created using LibreOffice. The formulas seem
+    # different from a document created using OpenOffice.
+    #
+    # TODO: translate
+    # 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.
+    workbook = roo_class.new(File.join(TESTDIR, "dreimalvier.ods"))
+    assert_equal "=SUM([.A1:.D1])", workbook.formula("e", 1)
+    assert_equal "=SUM([.A2:.D2])", workbook.formula("e", 2)
+    assert_equal "=SUM([.A3:.D3])", workbook.formula("e", 3)
+    expected_formulas = [
+      [1, 5, "=SUM([.A1:.D1])"],
+      [2, 5, "=SUM([.A2:.D2])"],
+      [3, 5, "=SUM([.A3:.D3])"],
+    ]
+    assert_equal expected_formulas, workbook.formulas
+  end
+
+  def test_header_with_brackets_open_office
+    options = { name: "advanced_header", format: :openoffice }
+    with_each_spreadsheet(options) do |workbook|
+      parsed_head = workbook.parse(headers: true)
+      assert_equal "Date(yyyy-mm-dd)", workbook.cell("A", 1)
+      assert_equal parsed_head[0].keys, ["Date(yyyy-mm-dd)"]
+      assert_equal parsed_head[0].values, ["Date(yyyy-mm-dd)"]
+    end
+  end
+
+  def test_office_version
+    with_each_spreadsheet(name: "numbers1", format: :openoffice) do |workbook|
+      assert_equal "1.0", workbook.officeversion
+    end
+  end
+
+  def test_bug_contiguous_cells
+    with_each_spreadsheet(name: "numbers1", format: :openoffice) do |workbook|
+      workbook.default_sheet = "Sheet4"
+      assert_equal Date.new(2007, 06, 16), workbook.cell("a", 1)
+      assert_equal 10, workbook.cell("b", 1)
+      assert_equal 10, workbook.cell("c", 1)
+      assert_equal 10, workbook.cell("d", 1)
+      assert_equal 10, workbook.cell("e", 1)
+    end
+  end
+
+  def test_italo_table
+    with_each_spreadsheet(name: "simple_spreadsheet_from_italo", format: :openoffice) do |workbook|
+      assert_equal  "1", workbook.cell("A", 1)
+      assert_equal  "1", workbook.cell("B", 1)
+      assert_equal  "1", workbook.cell("C", 1)
+      assert_equal  1, workbook.cell("A", 2).to_i
+      assert_equal  2, workbook.cell("B", 2).to_i
+      assert_equal  1, workbook.cell("C", 2).to_i
+      assert_equal  1, workbook.cell("A", 3)
+      assert_equal  3, workbook.cell("B", 3)
+      assert_equal  1, workbook.cell("C", 3)
+      assert_equal  "A", workbook.cell("A", 4)
+      assert_equal  "A", workbook.cell("B", 4)
+      assert_equal  "A", workbook.cell("C", 4)
+      assert_equal  0.01, workbook.cell("A", 5)
+      assert_equal  0.01, workbook.cell("B", 5)
+      assert_equal  0.01, workbook.cell("C", 5)
+      assert_equal 0.03, workbook.cell("a", 5) + workbook.cell("b", 5) + workbook.cell("c", 5)
+
+      # Cells values in row 1:
+      assert_equal "1:string", [workbook.cell(1, 1), workbook.celltype(1, 1)].join(":")
+      assert_equal "1:string", [workbook.cell(1, 2), workbook.celltype(1, 2)].join(":")
+      assert_equal "1:string", [workbook.cell(1, 3), workbook.celltype(1, 3)].join(":")
+
+      # Cells values in row 2:
+      assert_equal "1:string", [workbook.cell(2, 1), workbook.celltype(2, 1)].join(":")
+      assert_equal "2:string", [workbook.cell(2, 2), workbook.celltype(2, 2)].join(":")
+      assert_equal "1:string", [workbook.cell(2, 3), workbook.celltype(2, 3)].join(":")
+
+      # Cells values in row 3:
+      assert_equal "1:float", [workbook.cell(3, 1), workbook.celltype(3, 1)].join(":")
+      assert_equal "3:float", [workbook.cell(3, 2), workbook.celltype(3, 2)].join(":")
+      assert_equal "1:float", [workbook.cell(3, 3), workbook.celltype(3, 3)].join(":")
+
+      # Cells values in row 4:
+      assert_equal "A:string", [workbook.cell(4, 1), workbook.celltype(4, 1)].join(":")
+      assert_equal "A:string", [workbook.cell(4, 2), workbook.celltype(4, 2)].join(":")
+      assert_equal "A:string", [workbook.cell(4, 3), workbook.celltype(4, 3)].join(":")
+
+      # Cells values in row 5:
+      assert_equal "0.01:percentage", [workbook.cell(5, 1), workbook.celltype(5, 1)].join(":")
+      assert_equal "0.01:percentage", [workbook.cell(5, 2), workbook.celltype(5, 2)].join(":")
+      assert_equal "0.01:percentage", [workbook.cell(5, 3), workbook.celltype(5, 3)].join(":")
+    end
+  end
+
+  def test_formula_openoffice
+    with_each_spreadsheet(name: "formula", format: :openoffice) do |workbook|
+      assert_equal 1, workbook.cell("A", 1)
+      assert_equal 2, workbook.cell("A", 2)
+      assert_equal 3, workbook.cell("A", 3)
+      assert_equal 4, workbook.cell("A", 4)
+      assert_equal 5, workbook.cell("A", 5)
+      assert_equal 6, workbook.cell("A", 6)
+      assert_equal 21, workbook.cell("A", 7)
+      assert_equal :formula, workbook.celltype("A", 7)
+      assert_equal "=[Sheet2.A1]", workbook.formula("C", 7)
+      assert_nil workbook.formula("A", 6)
+      expected_formulas = [
+        [7, 1, "=SUM([.A1:.A6])"],
+        [7, 2, "=SUM([.$A$1:.B6])"],
+        [7, 3, "=[Sheet2.A1]"],
+        [8, 2, "=SUM([.$A$1:.B7])"],
+      ]
+      assert_equal expected_formulas, workbook.formulas(workbook.sheets.first)
+
+      # setting a cell
+      workbook.set("A", 15, 41)
+      assert_equal 41, workbook.cell("A", 15)
+      workbook.set("A", 16, "41")
+      assert_equal "41", workbook.cell("A", 16)
+      workbook.set("A", 17, 42.5)
+      assert_equal 42.5, workbook.cell("A", 17)
+    end
+  end
+
+  def test_bug_ric
+    with_each_spreadsheet(name: "ric", format: :openoffice) do |workbook|
+      assert workbook.empty?("A", 1)
+      assert workbook.empty?("B", 1)
+      assert workbook.empty?("C", 1)
+      assert workbook.empty?("D", 1)
+      expected = 1
+      letter = "e"
+      while letter <= "u"
+        assert_equal expected, workbook.cell(letter, 1)
+        letter.succ!
+        expected += 1
+      end
+      assert_equal "J", workbook.cell("v", 1)
+      assert_equal "P", workbook.cell("w", 1)
+      assert_equal "B", workbook.cell("x", 1)
+      assert_equal "All", workbook.cell("y", 1)
+      assert_equal 0, workbook.cell("a", 2)
+      assert workbook.empty?("b", 2)
+      assert workbook.empty?("c", 2)
+      assert workbook.empty?("d", 2)
+      assert_equal "B", workbook.cell("e", 2)
+      assert_equal "B", workbook.cell("f", 2)
+      assert_equal "B", workbook.cell("g", 2)
+      assert_equal "B", workbook.cell("h", 2)
+      assert_equal "B", workbook.cell("i", 2)
+      assert_equal "B", workbook.cell("j", 2)
+      assert_equal "B", workbook.cell("k", 2)
+      assert_equal "B", workbook.cell("l", 2)
+      assert_equal "B", workbook.cell("m", 2)
+      assert_equal "B", workbook.cell("n", 2)
+      assert_equal "B", workbook.cell("o", 2)
+      assert_equal "B", workbook.cell("p", 2)
+      assert_equal "B", workbook.cell("q", 2)
+      assert_equal "B", workbook.cell("r", 2)
+      assert_equal "B", workbook.cell("s", 2)
+      assert workbook.empty?("t", 2)
+      assert workbook.empty?("u", 2)
+      assert_equal 0, workbook.cell("v", 2)
+      assert_equal 0, workbook.cell("w", 2)
+      assert_equal 15, workbook.cell("x", 2)
+      assert_equal 15, workbook.cell("y", 2)
+    end
+  end
+
+  def test_mehrteilig
+    with_each_spreadsheet(name: "Bibelbund1", format: :openoffice) do |workbook|
+      assert_equal "Tagebuch des Sekret\303\244rs.    Letzte Tagung 15./16.11.75 Schweiz", workbook.cell(45, "A")
+    end
+  end
+
+  def test_cell_openoffice_html_escape
+    with_each_spreadsheet(name: "html-escape", format: :openoffice) do |workbook|
+      assert_equal "'", workbook.cell(1, 1)
+      assert_equal "&", workbook.cell(2, 1)
+      assert_equal ">", workbook.cell(3, 1)
+      assert_equal "<", workbook.cell(4, 1)
+      assert_equal "`", workbook.cell(5, 1)
+      # test_openoffice_zipped will catch issues with &quot;
+    end
+  end
+
+  def roo_class
+    Roo::OpenOffice
+  end
+
+  def filename(name)
+    "#{name}.ods"
+  end
+end