Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1from __future__ import annotations
3from typing import (
4 TYPE_CHECKING,
5 Any,
6 cast,
7)
9import numpy as np
11from pandas.compat._optional import import_optional_dependency
13import pandas as pd
15from pandas.io.excel._base import BaseExcelReader
17if TYPE_CHECKING:
18 from odf.opendocument import OpenDocument
20 from pandas._libs.tslibs.nattype import NaTType
21 from pandas._typing import (
22 FilePath,
23 ReadBuffer,
24 Scalar,
25 StorageOptions,
26 )
29class ODFReader(BaseExcelReader["OpenDocument"]):
30 def __init__(
31 self,
32 filepath_or_buffer: FilePath | ReadBuffer[bytes],
33 storage_options: StorageOptions | None = None,
34 engine_kwargs: dict[str, Any] | None = None,
35 ) -> None:
36 """
37 Read tables out of OpenDocument formatted files.
39 Parameters
40 ----------
41 filepath_or_buffer : str, path to be parsed or
42 an open readable stream.
43 storage_options : dict, optional
44 Extra options that make sense for a particular storage connection,
45 e.g. host, port, username, password, etc. For HTTP(S) URLs the
46 key-value pairs are forwarded to ``urllib.request.Request`` as
47 header options. For other URLs (e.g. starting with "s3://", and
48 "gcs://") the key-value pairs are forwarded to ``fsspec.open``.
49 Please see ``fsspec`` and ``urllib`` for more details, and for
50 more examples on storage options refer `here
51 <https://pandas.pydata.org/docs/user_guide/io.html?#reading-writing-remote-files>`__.
52 engine_kwargs : dict, optional
53 Arbitrary keyword arguments passed to excel engine.
54 """
55 import_optional_dependency("odf")
56 super().__init__(
57 filepath_or_buffer,
58 storage_options=storage_options,
59 engine_kwargs=engine_kwargs,
60 )
62 @property
63 def _workbook_class(self) -> type[OpenDocument]:
64 from odf.opendocument import OpenDocument
66 return OpenDocument
68 def load_workbook(
69 self, filepath_or_buffer: FilePath | ReadBuffer[bytes], engine_kwargs
70 ) -> OpenDocument:
71 from odf.opendocument import load
73 return load(filepath_or_buffer, **engine_kwargs)
75 @property
76 def empty_value(self) -> str:
77 """Property for compat with other readers."""
78 return ""
80 @property
81 def sheet_names(self) -> list[str]:
82 """Return a list of sheet names present in the document"""
83 from odf.table import Table
85 tables = self.book.getElementsByType(Table)
86 return [t.getAttribute("name") for t in tables]
88 def get_sheet_by_index(self, index: int):
89 from odf.table import Table
91 self.raise_if_bad_sheet_by_index(index)
92 tables = self.book.getElementsByType(Table)
93 return tables[index]
95 def get_sheet_by_name(self, name: str):
96 from odf.table import Table
98 self.raise_if_bad_sheet_by_name(name)
99 tables = self.book.getElementsByType(Table)
101 for table in tables:
102 if table.getAttribute("name") == name:
103 return table
105 self.close()
106 raise ValueError(f"sheet {name} not found")
108 def get_sheet_data(
109 self, sheet, file_rows_needed: int | None = None
110 ) -> list[list[Scalar | NaTType]]:
111 """
112 Parse an ODF Table into a list of lists
113 """
114 from odf.namespaces import OFFICENS
115 from odf.table import (
116 CoveredTableCell,
117 TableCell,
118 TableRow,
119 )
121 covered_cell_name = CoveredTableCell().qname
122 table_cell_name = TableCell().qname
123 cell_names = {covered_cell_name, table_cell_name}
125 sheet_rows = sheet.getElementsByType(TableRow)
126 empty_rows = 0
127 max_row_len = 0
129 table: list[list[Scalar | NaTType]] = []
131 for sheet_row in sheet_rows:
132 empty_cells = 0
133 table_row: list[Scalar | NaTType] = []
135 for sheet_cell in sheet_row.childNodes:
136 if hasattr(sheet_cell, "qname") and sheet_cell.qname in cell_names:
137 value: Scalar | NaTType
138 if (
139 sheet_cell.qname == covered_cell_name
140 and (OFFICENS, "value") not in sheet_cell.attributes
141 ):
142 value = self.empty_value
143 else:
144 value = self._get_cell_value(sheet_cell)
146 column_repeat = self._get_column_repeat(sheet_cell)
148 # Queue up empty values, writing only if content succeeds them
149 if value == self.empty_value:
150 empty_cells += column_repeat
151 else:
152 table_row.extend([self.empty_value] * empty_cells)
153 empty_cells = 0
154 table_row.extend([value] * column_repeat)
156 if max_row_len < len(table_row):
157 max_row_len = len(table_row)
159 row_repeat = self._get_row_repeat(sheet_row)
160 if len(table_row) == 0:
161 empty_rows += row_repeat
162 else:
163 # add blank rows to our table
164 table.extend([[self.empty_value]] * empty_rows)
165 empty_rows = 0
166 table.extend(table_row for _ in range(row_repeat))
167 if file_rows_needed is not None and len(table) >= file_rows_needed:
168 break
170 # Make our table square
171 for row in table:
172 if len(row) < max_row_len:
173 row.extend([self.empty_value] * (max_row_len - len(row)))
175 return table
177 def _get_row_repeat(self, row) -> int:
178 """
179 Return number of times this row was repeated
180 Repeating an empty row appeared to be a common way
181 of representing sparse rows in the table.
182 """
183 from odf.namespaces import TABLENS
185 return int(row.attributes.get((TABLENS, "number-rows-repeated"), 1))
187 def _get_column_repeat(self, cell) -> int:
188 from odf.namespaces import TABLENS
190 return int(cell.attributes.get((TABLENS, "number-columns-repeated"), 1))
192 def _get_cell_value(self, cell) -> Scalar | NaTType:
193 from odf.namespaces import OFFICENS
195 if str(cell) == "#N/A":
196 return np.nan
198 cell_type = cell.attributes.get((OFFICENS, "value-type"))
199 if cell_type == "boolean":
200 if str(cell) == "TRUE":
201 return True
202 return False
203 if cell_type is None:
204 return self.empty_value
205 elif cell_type == "float":
206 # GH5394
207 cell_value = float(cell.attributes.get((OFFICENS, "value")))
208 val = int(cell_value)
209 if val == cell_value:
210 return val
211 return cell_value
212 elif cell_type == "percentage":
213 cell_value = cell.attributes.get((OFFICENS, "value"))
214 return float(cell_value)
215 elif cell_type == "string":
216 return self._get_cell_string_value(cell)
217 elif cell_type == "currency":
218 cell_value = cell.attributes.get((OFFICENS, "value"))
219 return float(cell_value)
220 elif cell_type == "date":
221 cell_value = cell.attributes.get((OFFICENS, "date-value"))
222 return pd.Timestamp(cell_value)
223 elif cell_type == "time":
224 stamp = pd.Timestamp(str(cell))
225 # cast needed here because Scalar doesn't include datetime.time
226 return cast("Scalar", stamp.time())
227 else:
228 self.close()
229 raise ValueError(f"Unrecognized type {cell_type}")
231 def _get_cell_string_value(self, cell) -> str:
232 """
233 Find and decode OpenDocument text:s tags that represent
234 a run length encoded sequence of space characters.
235 """
236 from odf.element import Element
237 from odf.namespaces import (
238 OFFICENS,
239 TEXTNS,
240 )
242 # NB: built as plain tuples rather than from odf element instances,
243 # since this runs once per cell.
244 office_annotation = (OFFICENS, "annotation")
245 text_s = (TEXTNS, "s")
246 text_p = (TEXTNS, "p")
247 text_line_break = (TEXTNS, "line-break")
249 value = []
250 seen_paragraph = False
252 for fragment in cell.childNodes:
253 if isinstance(fragment, Element):
254 if fragment.qname == text_s:
255 spaces = int(fragment.attributes.get((TEXTNS, "c"), 1))
256 value.append(" " * spaces)
257 elif fragment.qname == text_line_break:
258 # GH#55728: an explicit line break within a paragraph.
259 value.append("\n")
260 elif fragment.qname == office_annotation:
261 continue
262 else:
263 if fragment.qname == text_p:
264 # GH#53924: a cell holding several paragraphs shows them
265 # on separate lines, which is how LibreOffice and Excel
266 # store a multi-line cell.
267 if seen_paragraph:
268 value.append("\n")
269 seen_paragraph = True
270 # recursive impl needed in case of nested fragments
271 # with multiple spaces
272 # https://github.com/pandas-dev/pandas/pull/36175#discussion_r484639704
273 value.append(self._get_cell_string_value(fragment))
274 else:
275 value.append(str(fragment).strip("\n"))
276 return "".join(value)