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