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
3import mmap
4from typing import (
5 TYPE_CHECKING,
6 Any,
7 cast,
8)
10import numpy as np
12from pandas.compat._optional import import_optional_dependency
13from pandas.util._decorators import doc
15from pandas.core.shared_docs import _shared_docs
17from pandas.io.excel._base import (
18 BaseExcelReader,
19 ExcelWriter,
20)
21from pandas.io.excel._util import (
22 combine_kwargs,
23 validate_freeze_panes,
24)
26if TYPE_CHECKING:
27 from openpyxl import Workbook
28 from openpyxl.descriptors.serialisable import Serialisable
29 from openpyxl.styles import Fill
31 from pandas._typing import (
32 ExcelWriterIfSheetExists,
33 FilePath,
34 ReadBuffer,
35 Scalar,
36 StorageOptions,
37 WriteExcelBuffer,
38 )
41class OpenpyxlWriter(ExcelWriter):
42 _engine = "openpyxl"
43 _supported_extensions = (".xlsx", ".xlsm")
45 def __init__( # pyright: ignore[reportInconsistentConstructor]
46 self,
47 path: FilePath | WriteExcelBuffer | ExcelWriter,
48 engine: str | None = None,
49 date_format: str | None = None,
50 datetime_format: str | None = None,
51 mode: str = "w",
52 storage_options: StorageOptions | None = None,
53 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
54 engine_kwargs: dict[str, Any] | None = None,
55 **kwargs,
56 ) -> None:
57 # Use the openpyxl module as the Excel writer.
58 from openpyxl.workbook import Workbook
60 engine_kwargs = combine_kwargs(engine_kwargs, kwargs)
62 super().__init__(
63 path,
64 mode=mode,
65 storage_options=storage_options,
66 if_sheet_exists=if_sheet_exists,
67 engine_kwargs=engine_kwargs,
68 )
70 # ExcelWriter replaced "a" by "r+" to allow us to first read the excel file from
71 # the file and later write to it
72 if "r+" in self._mode: # Load from existing workbook
73 from openpyxl import load_workbook
75 try:
76 self._book = load_workbook(self._handles.handle, **engine_kwargs)
77 except TypeError:
78 self._handles.handle.close()
79 raise
80 self._handles.handle.seek(0)
81 else:
82 # Create workbook object with default optimized_write=True.
83 try:
84 self._book = Workbook(**engine_kwargs)
85 except TypeError:
86 self._handles.handle.close()
87 raise
89 if self.book.worksheets:
90 self.book.remove(self.book.worksheets[0])
92 @property
93 def book(self) -> Workbook:
94 """
95 Book instance of class openpyxl.workbook.Workbook.
97 This attribute can be used to access engine-specific features.
98 """
99 return self._book
101 @property
102 def sheets(self) -> dict[str, Any]:
103 """Mapping of sheet names to sheet objects."""
104 result = {name: self.book[name] for name in self.book.sheetnames}
105 return result
107 def _save(self) -> None:
108 """
109 Save workbook to disk.
110 """
111 self.book.save(self._handles.handle)
112 if "r+" in self._mode and not isinstance(self._handles.handle, mmap.mmap):
113 # truncate file to the written content
114 self._handles.handle.truncate()
116 @classmethod
117 def _convert_to_style_kwargs(
118 cls, style_dict: dict[str, Serialisable]
119 ) -> dict[str, Serialisable]:
120 """
121 Convert a style_dict to a set of kwargs suitable for initializing
122 or updating-on-copy an openpyxl v2 style object.
124 Parameters
125 ----------
126 style_dict : dict
127 A dict with zero or more of the following keys (or their synonyms).
128 'font'
129 'fill'
130 'border' ('borders')
131 'alignment'
132 'number_format'
133 'protection'
135 Returns
136 -------
137 style_kwargs : dict
138 A dict with the same, normalized keys as ``style_dict`` but each
139 value has been replaced with a native openpyxl style object of the
140 appropriate class.
141 """
142 _style_key_map = {"borders": "border"}
144 style_kwargs: dict[str, Serialisable] = {}
145 for k, v in style_dict.items():
146 k = _style_key_map.get(k, k)
147 _conv_to_x = getattr(cls, f"_convert_to_{k}", lambda x: None)
148 new_v = _conv_to_x(v)
149 if new_v:
150 style_kwargs[k] = new_v
152 return style_kwargs
154 @classmethod
155 def _convert_to_color(cls, color_spec):
156 """
157 Convert ``color_spec`` to an openpyxl v2 Color object.
159 Parameters
160 ----------
161 color_spec : str, dict
162 A 32-bit ARGB hex string, or a dict with zero or more of the
163 following keys.
164 'rgb'
165 'indexed'
166 'auto'
167 'theme'
168 'tint'
169 'index'
170 'type'
172 Returns
173 -------
174 color : openpyxl.styles.Color
175 """
176 from openpyxl.styles import Color
178 if isinstance(color_spec, str):
179 return Color(color_spec)
180 else:
181 return Color(**color_spec)
183 @classmethod
184 def _convert_to_font(cls, font_dict):
185 """
186 Convert ``font_dict`` to an openpyxl v2 Font object.
188 Parameters
189 ----------
190 font_dict : dict
191 A dict with zero or more of the following keys (or their synonyms).
192 'name'
193 'size' ('sz')
194 'bold' ('b')
195 'italic' ('i')
196 'underline' ('u')
197 'strikethrough' ('strike')
198 'color'
199 'vertAlign' ('vertalign')
200 'charset'
201 'scheme'
202 'family'
203 'outline'
204 'shadow'
205 'condense'
207 Returns
208 -------
209 font : openpyxl.styles.Font
210 """
211 from openpyxl.styles import Font
213 _font_key_map = {
214 "sz": "size",
215 "b": "bold",
216 "i": "italic",
217 "u": "underline",
218 "strike": "strikethrough",
219 "vertalign": "vertAlign",
220 }
222 font_kwargs = {}
223 for k, v in font_dict.items():
224 k = _font_key_map.get(k, k)
225 if k == "color":
226 v = cls._convert_to_color(v)
227 font_kwargs[k] = v
229 return Font(**font_kwargs)
231 @classmethod
232 def _convert_to_stop(cls, stop_seq):
233 """
234 Convert ``stop_seq`` to a list of openpyxl v2 Color objects,
235 suitable for initializing the ``GradientFill`` ``stop`` parameter.
237 Parameters
238 ----------
239 stop_seq : iterable
240 An iterable that yields objects suitable for consumption by
241 ``_convert_to_color``.
243 Returns
244 -------
245 stop : list of openpyxl.styles.Color
246 """
247 return map(cls._convert_to_color, stop_seq)
249 @classmethod
250 def _convert_to_fill(cls, fill_dict: dict[str, Any]) -> Fill:
251 """
252 Convert ``fill_dict`` to an openpyxl v2 Fill object.
254 Parameters
255 ----------
256 fill_dict : dict
257 A dict with one or more of the following keys (or their synonyms),
258 'fill_type' ('patternType', 'patterntype')
259 'start_color' ('fgColor', 'fgcolor')
260 'end_color' ('bgColor', 'bgcolor')
261 or one or more of the following keys (or their synonyms).
262 'type' ('fill_type')
263 'degree'
264 'left'
265 'right'
266 'top'
267 'bottom'
268 'stop'
270 Returns
271 -------
272 fill : openpyxl.styles.Fill
273 """
274 from openpyxl.styles import (
275 GradientFill,
276 PatternFill,
277 )
279 _pattern_fill_key_map = {
280 "patternType": "fill_type",
281 "patterntype": "fill_type",
282 "fgColor": "start_color",
283 "fgcolor": "start_color",
284 "bgColor": "end_color",
285 "bgcolor": "end_color",
286 }
288 _gradient_fill_key_map = {"fill_type": "type"}
290 pfill_kwargs = {}
291 gfill_kwargs = {}
292 for k, v in fill_dict.items():
293 pk = _pattern_fill_key_map.get(k)
294 gk = _gradient_fill_key_map.get(k)
295 if pk in ["start_color", "end_color"]:
296 v = cls._convert_to_color(v)
297 if gk == "stop":
298 v = cls._convert_to_stop(v)
299 if pk:
300 pfill_kwargs[pk] = v
301 elif gk:
302 gfill_kwargs[gk] = v
303 else:
304 pfill_kwargs[k] = v
305 gfill_kwargs[k] = v
307 try:
308 return PatternFill(**pfill_kwargs)
309 except TypeError:
310 return GradientFill(**gfill_kwargs)
312 @classmethod
313 def _convert_to_side(cls, side_spec):
314 """
315 Convert ``side_spec`` to an openpyxl v2 Side object.
317 Parameters
318 ----------
319 side_spec : str, dict
320 A string specifying the border style, or a dict with zero or more
321 of the following keys (or their synonyms).
322 'style' ('border_style')
323 'color'
325 Returns
326 -------
327 side : openpyxl.styles.Side
328 """
329 from openpyxl.styles import Side
331 _side_key_map = {"border_style": "style"}
333 if isinstance(side_spec, str):
334 return Side(style=side_spec)
336 side_kwargs = {}
337 for k, v in side_spec.items():
338 k = _side_key_map.get(k, k)
339 if k == "color":
340 v = cls._convert_to_color(v)
341 side_kwargs[k] = v
343 return Side(**side_kwargs)
345 @classmethod
346 def _convert_to_border(cls, border_dict):
347 """
348 Convert ``border_dict`` to an openpyxl v2 Border object.
350 Parameters
351 ----------
352 border_dict : dict
353 A dict with zero or more of the following keys (or their synonyms).
354 'left'
355 'right'
356 'top'
357 'bottom'
358 'diagonal'
359 'diagonal_direction'
360 'vertical'
361 'horizontal'
362 'diagonalUp' ('diagonalup')
363 'diagonalDown' ('diagonaldown')
364 'outline'
366 Returns
367 -------
368 border : openpyxl.styles.Border
369 """
370 from openpyxl.styles import Border
372 _border_key_map = {"diagonalup": "diagonalUp", "diagonaldown": "diagonalDown"}
374 border_kwargs = {}
375 for k, v in border_dict.items():
376 k = _border_key_map.get(k, k)
377 if k == "color":
378 v = cls._convert_to_color(v)
379 if k in ["left", "right", "top", "bottom", "diagonal"]:
380 v = cls._convert_to_side(v)
381 border_kwargs[k] = v
383 return Border(**border_kwargs)
385 @classmethod
386 def _convert_to_alignment(cls, alignment_dict):
387 """
388 Convert ``alignment_dict`` to an openpyxl v2 Alignment object.
390 Parameters
391 ----------
392 alignment_dict : dict
393 A dict with zero or more of the following keys (or their synonyms).
394 'horizontal'
395 'vertical'
396 'text_rotation'
397 'wrap_text'
398 'shrink_to_fit'
399 'indent'
400 Returns
401 -------
402 alignment : openpyxl.styles.Alignment
403 """
404 from openpyxl.styles import Alignment
406 return Alignment(**alignment_dict)
408 @classmethod
409 def _convert_to_number_format(cls, number_format_dict):
410 """
411 Convert ``number_format_dict`` to an openpyxl v2.1.0 number format
412 initializer.
414 Parameters
415 ----------
416 number_format_dict : dict
417 A dict with zero or more of the following keys.
418 'format_code' : str
420 Returns
421 -------
422 number_format : str
423 """
424 return number_format_dict["format_code"]
426 @classmethod
427 def _convert_to_protection(cls, protection_dict):
428 """
429 Convert ``protection_dict`` to an openpyxl v2 Protection object.
431 Parameters
432 ----------
433 protection_dict : dict
434 A dict with zero or more of the following keys.
435 'locked'
436 'hidden'
438 Returns
439 -------
440 """
441 from openpyxl.styles import Protection
443 return Protection(**protection_dict)
445 def _write_cells(
446 self,
447 cells,
448 sheet_name: str | None = None,
449 startrow: int = 0,
450 startcol: int = 0,
451 freeze_panes: tuple[int, int] | None = None,
452 autofilter_range: str | None = None,
453 ) -> None:
454 # Write the frame cells using openpyxl.
455 sheet_name = self._get_sheet_name(sheet_name)
457 _style_cache: dict[str, dict[str, Serialisable]] = {}
459 if sheet_name in self.sheets and self._if_sheet_exists != "new":
460 if "r+" in self._mode:
461 if self._if_sheet_exists == "replace":
462 old_wks = self.sheets[sheet_name]
463 target_index = self.book.index(old_wks)
464 del self.book[sheet_name]
465 wks = self.book.create_sheet(sheet_name, target_index)
466 elif self._if_sheet_exists == "error":
467 raise ValueError(
468 f"Sheet '{sheet_name}' already exists and "
469 f"if_sheet_exists is set to 'error'."
470 )
471 elif self._if_sheet_exists == "overlay":
472 wks = self.sheets[sheet_name]
473 else:
474 raise ValueError(
475 f"'{self._if_sheet_exists}' is not valid for if_sheet_exists. "
476 "Valid options are 'error', 'new', 'replace' and 'overlay'."
477 )
478 else:
479 wks = self.sheets[sheet_name]
480 else:
481 wks = self.book.create_sheet()
482 wks.title = sheet_name
484 if validate_freeze_panes(freeze_panes):
485 freeze_panes = cast(tuple[int, int], freeze_panes)
486 wks.freeze_panes = wks.cell(
487 row=freeze_panes[0] + 1, column=freeze_panes[1] + 1
488 )
490 for cell in cells:
491 xcell = wks.cell(
492 row=startrow + cell.row + 1, column=startcol + cell.col + 1
493 )
494 xcell.value, fmt = self._value_with_fmt(cell.val)
495 if fmt:
496 xcell.number_format = fmt
498 style_kwargs: dict[str, Serialisable] | None = {}
499 if cell.style:
500 key = str(cell.style)
501 style_kwargs = _style_cache.get(key)
502 if style_kwargs is None:
503 style_kwargs = self._convert_to_style_kwargs(cell.style)
504 _style_cache[key] = style_kwargs
506 if style_kwargs:
507 for k, v in style_kwargs.items():
508 setattr(xcell, k, v)
510 if cell.mergestart is not None and cell.mergeend is not None:
511 wks.merge_cells(
512 start_row=startrow + cell.row + 1,
513 start_column=startcol + cell.col + 1,
514 end_column=startcol + cell.mergeend + 1,
515 end_row=startrow + cell.mergestart + 1,
516 )
518 # When cells are merged only the top-left cell is preserved
519 # The behaviour of the other cells in a merged range is
520 # undefined
521 if style_kwargs:
522 first_row = startrow + cell.row + 1
523 last_row = startrow + cell.mergestart + 1
524 first_col = startcol + cell.col + 1
525 last_col = startcol + cell.mergeend + 1
527 for row in range(first_row, last_row + 1):
528 for col in range(first_col, last_col + 1):
529 if row == first_row and col == first_col:
530 # Ignore first cell. It is already handled.
531 continue
532 xcell = wks.cell(column=col, row=row)
533 for k, v in style_kwargs.items():
534 setattr(xcell, k, v)
536 if autofilter_range:
537 wks.auto_filter.ref = autofilter_range
540class OpenpyxlReader(BaseExcelReader["Workbook"]):
541 @doc(storage_options=_shared_docs["storage_options"])
542 def __init__(
543 self,
544 filepath_or_buffer: FilePath | ReadBuffer[bytes],
545 storage_options: StorageOptions | None = None,
546 engine_kwargs: dict | None = None,
547 ) -> None:
548 """
549 Reader using openpyxl engine.
551 Parameters
552 ----------
553 filepath_or_buffer : str, path object or Workbook
554 Object to be parsed.
555 {storage_options}
556 engine_kwargs : dict, optional
557 Arbitrary keyword arguments passed to excel engine.
558 """
559 import_optional_dependency("openpyxl")
560 super().__init__(
561 filepath_or_buffer,
562 storage_options=storage_options,
563 engine_kwargs=engine_kwargs,
564 )
566 @property
567 def _workbook_class(self) -> type[Workbook]:
568 from openpyxl import Workbook
570 return Workbook
572 def load_workbook(
573 self, filepath_or_buffer: FilePath | ReadBuffer[bytes], engine_kwargs
574 ) -> Workbook:
575 from openpyxl import load_workbook
577 default_kwargs = {"read_only": True, "data_only": True, "keep_links": False}
579 return load_workbook(
580 filepath_or_buffer,
581 **(default_kwargs | engine_kwargs),
582 )
584 @property
585 def sheet_names(self) -> list[str]:
586 return [sheet.title for sheet in self.book.worksheets]
588 def get_sheet_by_name(self, name: str):
589 self.raise_if_bad_sheet_by_name(name)
590 return self.book[name]
592 def get_sheet_by_index(self, index: int):
593 self.raise_if_bad_sheet_by_index(index)
594 return self.book.worksheets[index]
596 def _convert_cell(self, cell) -> Scalar:
597 from openpyxl.cell.cell import (
598 TYPE_ERROR,
599 TYPE_NUMERIC,
600 )
602 if cell.value is None:
603 return "" # compat with xlrd
604 elif cell.data_type == TYPE_ERROR:
605 return np.nan
606 elif cell.data_type == TYPE_NUMERIC:
607 val = int(cell.value)
608 if val == cell.value:
609 return val
610 return float(cell.value)
612 return cell.value
614 def get_sheet_data(
615 self, sheet, file_rows_needed: int | None = None
616 ) -> list[list[Scalar]]:
617 if self.book.read_only:
618 sheet.reset_dimensions()
620 data: list[list[Scalar]] = []
621 last_row_with_data = -1
622 for row_number, row in enumerate(sheet.rows):
623 converted_row = [self._convert_cell(cell) for cell in row]
624 while converted_row and converted_row[-1] == "":
625 # trim trailing empty elements
626 converted_row.pop()
627 if converted_row:
628 last_row_with_data = row_number
629 data.append(converted_row)
630 if file_rows_needed is not None and len(data) >= file_rows_needed:
631 break
633 # Trim trailing empty rows
634 data = data[: last_row_with_data + 1]
636 if len(data) > 0:
637 # extend rows to max width
638 max_width = max(len(data_row) for data_row in data)
639 if min(len(data_row) for data_row in data) < max_width:
640 empty_cell: list[Scalar] = [""]
641 data = [
642 data_row + (max_width - len(data_row)) * empty_cell
643 for data_row in data
644 ]
646 return data