"""Unit tests for the spreadsheet parser: rows, positions, and the two renders."""
from __future__ import annotations
import asyncio
import io
import shutil
import zipfile
from pathlib import Path
import pytest
from raven.knowledge.parser.excel_parser import ExcelParser
_MAIN = "http://schemas.openxmlformats.org/spreadsheetml/2006/main"
_RELS = "http://schemas.openxmlformats.org/officeDocument/2006/relationships"
def _xlsx(sheets: dict[str, list[list[str]]]) -> bytes:
"""A minimal but well-formed workbook, strings written inline.
Inline rather than shared so the fixture says what a cell holds where the
cell is; the shared table has its own test.
"""
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, "w") as package:
package.writestr(
"[Content_Types].xml",
'',
)
entries, rels = [], []
for index, (name, rows) in enumerate(sheets.items(), start=1):
entries.append(f'')
rels.append(f'')
body = ""
for row_at, row in enumerate(rows, start=1):
cells = ""
for col_at, cell in enumerate(row):
ref = f"{chr(65 + col_at)}{row_at}"
cells += f'{cell}' if cell else ""
body += f'{cells}
'
package.writestr(
f"xl/worksheets/sheet{index}.xml",
f'{body}',
)
package.writestr(
"xl/workbook.xml",
f'{"".join(entries)}',
)
package.writestr(
"xl/_rels/workbook.xml.rels",
''
+ "".join(rels)
+ "",
)
return buffer.getvalue()
def _parse(payload: bytes | str, filename: str = "book.xlsx"):
return asyncio.run(ExcelParser().parse(payload, filename))
def _texts(sections) -> list[str]:
return [section.content.text for section in sections]
# -- a row is the unit ----------------------------------------------
def test_a_row_carries_the_header_that_names_its_values() -> None:
"""A value without its column name is a number nobody can ask about."""
book = _xlsx({"Sheet1": [["Region", "Revenue"], ["EU", "1.2M"], ["APAC", "0.9M"]]})
assert _texts(_parse(book)) == ["Region: EU; Revenue: 1.2M", "Region: APAC; Revenue: 0.9M"]
def test_a_row_says_where_it_came_from() -> None:
"""Sheet, row and the columns it spans -- what a reader needs to find it
again in the file they uploaded."""
book = _xlsx({"Sheet1": [["A", "B", "C"], ["", "two", "three"]]})
metadata = _parse(book)[0].metadata
assert metadata["sheet_idx"] == 0
assert metadata["sheet_name"] == "Sheet1"
assert metadata["row_idx"] == 2, "the row number the spreadsheet shows, not the offset"
assert metadata["col_idx"] == 2 and metadata["col_end"] == 3
def test_an_empty_cell_does_not_shift_the_ones_after_it() -> None:
"""A sheet stores only the cells that hold something, so the gaps have to
be put back or a row's third value reads as its second."""
book = _xlsx({"Sheet1": [["A", "B", "C"], ["one", "", "three"]]})
assert _texts(_parse(book)) == ["A: one; C: three"]
def test_a_column_with_no_header_keeps_its_value_alone() -> None:
book = _xlsx({"Sheet1": [["A"], ["one", "loose"]]})
assert _texts(_parse(book)) == ["A: one; loose"]
def test_every_sheet_is_read_and_numbered() -> None:
book = _xlsx({"First": [["A"], ["one"]], "Second": [["B"], ["two"]]})
sections = _parse(book)
assert [s.metadata["sheet_idx"] for s in sections] == [0, 1]
assert [s.metadata["sheet_name"] for s in sections] == ["First", "Second"]
def test_a_named_sheet_travels_with_the_row() -> None:
"""A row reading `Region: EU` in a sheet called `2024 Actuals` loses the
year the moment it is embedded, and the vector is all the search has."""
book = _xlsx({"2024 Actuals": [["Region"], ["EU"]]})
assert _texts(_parse(book)) == ["Region: EU -- 2024 Actuals"]
def test_a_sheet_still_called_sheet1_names_nothing() -> None:
for name in ("Sheet1", "sheet 2", "Sheet"):
book = _xlsx({name: [["Region"], ["EU"]]})
assert _texts(_parse(book)) == ["Region: EU"], name
def test_a_sheet_of_one_row_is_read_as_data() -> None:
"""It has no header to label anything with. RAGFlow drops such a sheet; a
row nobody can search is worse than a row with no labels."""
book = _xlsx({"Sheet1": [["alpha", "beta"]]})
assert _texts(_parse(book)) == ["alpha; beta"]
assert _parse(book)[0].metadata["row_idx"] == 1
def test_a_shared_string_is_read_from_its_table() -> None:
"""The common case in a real workbook: repeated text is stored once."""
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, "w") as package:
package.writestr(
"xl/workbook.xml",
f''
'',
)
package.writestr(
"xl/_rels/workbook.xml.rels",
''
'',
)
package.writestr(
"xl/sharedStrings.xml",
f'RegionEU',
)
package.writestr(
"xl/worksheets/sheet1.xml",
f''
'0
'
'1
'
"",
)
assert _texts(_parse(buffer.getvalue())) == ["Region: EU"], "a string split into runs is still one string"
def test_numbers_keep_the_spelling_the_sheet_stored() -> None:
"""A count of 3 is not improved by being indexed as 3.0."""
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, "w") as package:
package.writestr(
"xl/workbook.xml",
f''
'',
)
package.writestr(
"xl/_rels/workbook.xml.rels",
''
'',
)
package.writestr(
"xl/worksheets/sheet1.xml",
f''
'Count
'
'3.0
'
'1
'
"",
)
assert _texts(_parse(buffer.getvalue())) == ["Count: 3", "Count: TRUE"]
# -- separated values -----------------------------------------------
def test_a_csv_is_read_as_the_table_it_is() -> None:
body = b"Region,Revenue\nEU,1.2M\nAPAC,0.9M\n"
assert _texts(_parse(body, "book.csv")) == ["Region: EU; Revenue: 1.2M", "Region: APAC; Revenue: 0.9M"]
def test_a_csv_row_is_not_labelled_with_its_filename() -> None:
"""`Section.source` already carries it into every chunk."""
assert "book.csv" not in _texts(_parse(b"A,B\n1,2\n", "book.csv"))[0]
def test_semicolons_are_a_separator_where_a_locale_says_so() -> None:
"""A spreadsheet in a locale that writes decimals with a comma exports
semicolons, and reading it as commas gives one column of joined text."""
body = b"Region;Revenue\nEU;1,2\n"
assert _texts(_parse(body, "book.csv")) == ["Region: EU; Revenue: 1,2"]
def test_a_byte_order_mark_is_not_part_of_the_first_header() -> None:
body = "Region,Revenue\nEU,1.2M\n".encode()
assert _texts(_parse(body, "book.csv")) == ["Region: EU; Revenue: 1.2M"]
# -- the two renders, kept for a surface that shows a sheet ----------
def test_markdown_renders_every_sheet_under_its_own_heading() -> None:
book = _xlsx({"First": [["A", "B"], ["1", "2"]], "Second": [["C"], ["3"]]})
out = asyncio.run(ExcelParser().markdown(book, "book.xlsx"))
assert "## First" in out and "## Second" in out
assert "| A | B |" in out and "| 1 | 2 |" in out
def test_markdown_escapes_a_pipe_that_would_end_a_column() -> None:
book = _xlsx({"Sheet1": [["A"], ["one|two"]]})
assert "one\\|two" in asyncio.run(ExcelParser().markdown(book, "book.xlsx"))
def test_html_repeats_the_header_on_every_chunk() -> None:
"""A table of fifty thousand rows is a page nothing lays out, and a piece
without the header is a grid of unlabelled values."""
rows = [["A", "B"]] + [[str(n), "x"] for n in range(5)]
book = _xlsx({"Sheet1": rows})
tables = asyncio.run(ExcelParser().html(book, "book.xlsx", chunk_rows=2))
assert len(tables) == 3
assert all("
A | B | " in table for table in tables)
assert all(table.startswith("Sheet1") for table in tables)
def test_html_escapes_what_would_otherwise_be_markup() -> None:
"""The cell below holds the three characters `` -- the fixture writes
them as XML entities because that is how a cell holds them. What comes back
must be escaped again, or a spreadsheet decides what the page renders."""
book = _xlsx({"Sheet1": [["A"], ["<b>"]]})
table = asyncio.run(ExcelParser().html(book, "book.xlsx"))[0]
assert "| <b> | " in table
assert " | " not in table
# -- what it claims, and what it refuses ----------------------------
def test_the_parser_claims_the_three_spreadsheet_types() -> None:
assert ExcelParser.supported_extensions() == [".csv", ".xls", ".xlsx"]
assert "text/csv" in ExcelParser.supported_media_types
def test_a_missing_path_is_reported_as_missing() -> None:
with pytest.raises(FileNotFoundError):
_parse("/nonexistent/book.xlsx")
def test_a_zip_that_is_not_a_workbook_is_refused() -> None:
buffer = io.BytesIO()
with zipfile.ZipFile(buffer, "w") as package:
package.writestr("mimetype", "application/zip")
with pytest.raises(ValueError, match="as a spreadsheet"):
_parse(buffer.getvalue())
@pytest.mark.skipif(shutil.which("soffice") is None, reason="LibreOffice is not installed here")
def test_a_legacy_workbook_is_converted_and_read(tmp_path) -> None:
"""The one format with no reader here. Against the real converter, because
the stubs elsewhere prove the wiring and only this proves the argv."""
from raven.utils.office import find_soffice, to_pdf
(tmp_path / "book.csv").write_text("Region,Revenue\nEU,1.2M\n")
staged = tmp_path / "out"
staged.mkdir()
to_pdf(tmp_path / "book.csv", staged, executable=find_soffice(), timeout_s=120.0, fmt="xls")
legacy = next(Path(staged).glob("*.xls"))
assert _texts(_parse(legacy.read_bytes(), "book.xls")) == ["Region: EU; Revenue: 1.2M -- book"]