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 collections import defaultdict
4import datetime
5import json
6from typing import (
7 TYPE_CHECKING,
8 Any,
9 overload,
10)
12from pandas.io.excel._base import ExcelWriter
13from pandas.io.excel._util import (
14 combine_kwargs,
15 validate_freeze_panes,
16)
18if TYPE_CHECKING:
19 from odf.opendocument import OpenDocumentSpreadsheet
21 from pandas._typing import (
22 ExcelWriterIfSheetExists,
23 FilePath,
24 StorageOptions,
25 WriteExcelBuffer,
26 )
28 from pandas.io.formats.excel import ExcelCell
31class ODSWriter(ExcelWriter["OpenDocumentSpreadsheet"]):
32 _engine = "odf"
33 _supported_extensions = (".ods",)
35 def __init__( # pyright: ignore[reportInconsistentConstructor]
36 self,
37 path: FilePath | WriteExcelBuffer | ExcelWriter[Any],
38 engine: str | None = None,
39 date_format: str | None = None,
40 datetime_format: str | None = None,
41 mode: str = "w",
42 storage_options: StorageOptions | None = None,
43 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
44 engine_kwargs: dict[str, Any] | None = None,
45 **kwargs: Any,
46 ) -> None:
47 from odf.opendocument import OpenDocumentSpreadsheet
49 if mode == "a":
50 raise ValueError("Append mode is not supported with odf!")
52 engine_kwargs = combine_kwargs(engine_kwargs, kwargs)
53 self._book = OpenDocumentSpreadsheet(**engine_kwargs)
55 super().__init__(
56 path,
57 mode=mode,
58 storage_options=storage_options,
59 if_sheet_exists=if_sheet_exists,
60 engine_kwargs=engine_kwargs,
61 )
63 self._style_dict: dict[str, str] = {}
65 @property
66 def book(self) -> OpenDocumentSpreadsheet:
67 """
68 Book instance of class odf.opendocument.OpenDocumentSpreadsheet.
70 This attribute can be used to access engine-specific features.
71 """
72 return self._book
74 @property
75 def sheets(self) -> dict[str, Any]:
76 """Mapping of sheet names to sheet objects."""
77 from odf.table import Table
79 result = {
80 sheet.getAttribute("name"): sheet
81 for sheet in self.book.getElementsByType(Table)
82 }
83 return result
85 def _save(self) -> None:
86 """
87 Save workbook to disk.
88 """
89 for sheet in self.sheets.values():
90 self.book.spreadsheet.addElement(sheet)
91 self.book.save(self._handles.handle)
93 def _write_cells(
94 self,
95 cells: list[ExcelCell],
96 sheet_name: str | None = None,
97 startrow: int = 0,
98 startcol: int = 0,
99 freeze_panes: tuple[int, int] | None = None,
100 autofilter_range: str | None = None,
101 ) -> None:
102 """
103 Write the frame cells using odf
104 """
106 if autofilter_range:
107 raise ValueError("Autofilter is not supported with odf!")
109 from odf.table import (
110 Table,
111 TableCell,
112 TableRow,
113 )
114 from odf.text import (
115 LineBreak,
116 P,
117 )
119 sheet_name = self._get_sheet_name(sheet_name)
120 assert sheet_name is not None
122 if sheet_name in self.sheets:
123 wks = self.sheets[sheet_name]
124 else:
125 wks = Table(name=sheet_name)
126 self.book.spreadsheet.addElement(wks)
128 if validate_freeze_panes(freeze_panes):
129 self._create_freeze_panes(sheet_name, freeze_panes)
131 for _ in range(startrow):
132 wks.addElement(TableRow())
134 rows: defaultdict[int, TableRow] = defaultdict(TableRow)
135 col_count: defaultdict[int, int] = defaultdict(int)
137 for cell in sorted(cells, key=lambda cell: (cell.row, cell.col)):
138 # only add empty cells if the row is still empty
139 if not col_count[cell.row]:
140 for _ in range(startcol):
141 rows[cell.row].addElement(TableCell())
143 # fill with empty cells if needed
144 for _ in range(cell.col - col_count[cell.row]):
145 rows[cell.row].addElement(TableCell())
146 col_count[cell.row] += 1
148 pvalue, tc = self._make_table_cell(cell)
149 rows[cell.row].addElement(tc)
150 col_count[cell.row] += 1
151 if isinstance(pvalue, str) and "\n" in pvalue:
152 # GH#55728: a newline inside the paragraph text is collapsed to
153 # a space by ODF consumers, so each line break is written as a
154 # <text:line-break/> element instead.
155 lines = pvalue.split("\n")
156 p = P(text=lines[0])
157 for line in lines[1:]:
158 p.addElement(LineBreak())
159 p.addText(line)
160 else:
161 p = P(text=pvalue)
162 tc.addElement(p)
164 # add all rows to the sheet
165 if len(rows) > 0:
166 for row_nr in range(max(rows.keys()) + 1):
167 wks.addElement(rows[row_nr])
169 def _make_table_cell_attributes(self, cell: ExcelCell) -> dict[str, int | str]:
170 """Convert cell attributes to OpenDocument attributes
172 Parameters
173 ----------
174 cell : ExcelCell
175 Spreadsheet cell data
177 Returns
178 -------
179 attributes : Dict[str, Union[int, str]]
180 Dictionary with attributes and attribute values
181 """
182 attributes: dict[str, int | str] = {}
183 style_name = self._process_style(cell.style)
184 if style_name is not None:
185 attributes["stylename"] = style_name
186 if cell.mergestart is not None and cell.mergeend is not None:
187 attributes["numberrowsspanned"] = max(1, cell.mergestart)
188 attributes["numbercolumnsspanned"] = cell.mergeend
189 return attributes
191 def _make_table_cell(self, cell: ExcelCell) -> tuple[object, Any]:
192 """Convert cell data to an OpenDocument spreadsheet cell
194 Parameters
195 ----------
196 cell : ExcelCell
197 Spreadsheet cell data
199 Returns
200 -------
201 pvalue, cell : Tuple[str, TableCell]
202 Display value, Cell value
203 """
204 from odf.table import TableCell
206 attributes = self._make_table_cell_attributes(cell)
207 val, fmt = self._value_with_fmt(cell.val)
208 pvalue = value = val
209 if isinstance(val, bool):
210 value = str(val).lower()
211 pvalue = str(val).upper()
212 return (
213 pvalue,
214 TableCell(
215 valuetype="boolean",
216 booleanvalue=value,
217 attributes=attributes,
218 ),
219 )
220 elif isinstance(val, datetime.datetime):
221 # Fast formatting
222 value = val.isoformat()
223 # Slow but locale-dependent
224 pvalue = val.strftime("%c")
225 return (
226 pvalue,
227 TableCell(valuetype="date", datevalue=value, attributes=attributes),
228 )
229 elif isinstance(val, datetime.date):
230 # Fast formatting
231 value = f"{val.year}-{val.month:02d}-{val.day:02d}"
232 # Slow but locale-dependent
233 pvalue = val.strftime("%x")
234 return (
235 pvalue,
236 TableCell(valuetype="date", datevalue=value, attributes=attributes),
237 )
238 elif isinstance(val, str):
239 return (
240 pvalue,
241 TableCell(
242 valuetype="string",
243 stringvalue=value,
244 attributes=attributes,
245 ),
246 )
247 else:
248 return (
249 pvalue,
250 TableCell(
251 valuetype="float",
252 value=value,
253 attributes=attributes,
254 ),
255 )
257 @overload
258 def _process_style(self, style: dict[str, Any]) -> str: ...
260 @overload
261 def _process_style(self, style: None) -> None: ...
263 def _process_style(self, style: dict[str, Any] | None) -> str | None:
264 """Convert a style dictionary to an OpenDocument style sheet
266 Parameters
267 ----------
268 style : Dict
269 Style dictionary
271 Returns
272 -------
273 style_key : str
274 Unique style key for later reference in sheet
275 """
276 from odf.style import (
277 ParagraphProperties,
278 Style,
279 TableCellProperties,
280 TextProperties,
281 )
283 if style is None:
284 return None
285 style_key = json.dumps(style)
286 if style_key in self._style_dict:
287 return self._style_dict[style_key]
288 name = f"pd{len(self._style_dict) + 1}"
289 self._style_dict[style_key] = name
290 odf_style = Style(name=name, family="table-cell")
291 if "font" in style:
292 font = style["font"]
293 if font.get("bold", False):
294 odf_style.addElement(TextProperties(fontweight="bold"))
295 if "borders" in style:
296 borders = style["borders"]
297 for side, thickness in borders.items():
298 thickness_translation = {"thin": "0.75pt solid #000000"}
299 odf_style.addElement(
300 TableCellProperties(
301 attributes={f"border{side}": thickness_translation[thickness]}
302 )
303 )
304 if "alignment" in style:
305 alignment = style["alignment"]
306 horizontal = alignment.get("horizontal")
307 if horizontal:
308 odf_style.addElement(ParagraphProperties(textalign=horizontal))
309 vertical = alignment.get("vertical")
310 if vertical:
311 odf_style.addElement(TableCellProperties(verticalalign=vertical))
312 self.book.styles.addElement(odf_style)
313 return name
315 def _create_freeze_panes(
316 self, sheet_name: str, freeze_panes: tuple[int, int]
317 ) -> None:
318 """
319 Create freeze panes in the sheet.
321 Parameters
322 ----------
323 sheet_name : str
324 Name of the spreadsheet
325 freeze_panes : tuple of (int, int)
326 Freeze pane location x and y
327 """
328 from odf.config import (
329 ConfigItem,
330 ConfigItemMapEntry,
331 ConfigItemMapIndexed,
332 ConfigItemMapNamed,
333 ConfigItemSet,
334 )
336 config_item_set = ConfigItemSet(name="ooo:view-settings")
337 self.book.settings.addElement(config_item_set)
339 config_item_map_indexed = ConfigItemMapIndexed(name="Views")
340 config_item_set.addElement(config_item_map_indexed)
342 config_item_map_entry = ConfigItemMapEntry()
343 config_item_map_indexed.addElement(config_item_map_entry)
345 config_item_map_named = ConfigItemMapNamed(name="Tables")
346 config_item_map_entry.addElement(config_item_map_named)
348 config_item_map_entry = ConfigItemMapEntry(name=sheet_name)
349 config_item_map_named.addElement(config_item_map_entry)
351 config_item_map_entry.addElement(
352 ConfigItem(name="HorizontalSplitMode", type="short", text="2")
353 )
354 config_item_map_entry.addElement(
355 ConfigItem(name="VerticalSplitMode", type="short", text="2")
356 )
357 config_item_map_entry.addElement(
358 ConfigItem(
359 name="HorizontalSplitPosition", type="int", text=str(freeze_panes[0])
360 )
361 )
362 config_item_map_entry.addElement(
363 ConfigItem(
364 name="VerticalSplitPosition", type="int", text=str(freeze_panes[1])
365 )
366 )
367 config_item_map_entry.addElement(
368 ConfigItem(name="PositionRight", type="int", text=str(freeze_panes[0]))
369 )
370 config_item_map_entry.addElement(
371 ConfigItem(name="PositionBottom", type="int", text=str(freeze_panes[1]))
372 )