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)
9import numpy as np
11from pandas.compat._optional import import_optional_dependency
13from pandas.io.excel._base import (
14 BaseExcelReader,
15 ExcelWriter,
16)
17from pandas.io.excel._util import (
18 combine_kwargs,
19 validate_freeze_panes,
20)
22if TYPE_CHECKING:
23 from openpyxl import Workbook
24 from openpyxl.descriptors.serialisable import Serialisable
25 from openpyxl.styles import Fill
27 from pandas._typing import (
28 ExcelWriterIfSheetExists,
29 FilePath,
30 ReadBuffer,
31 Scalar,
32 StorageOptions,
33 WriteExcelBuffer,
34 )
37class OpenpyxlWriter(ExcelWriter["Workbook"]):
38 _engine = "openpyxl"
39 _supported_extensions = (".xlsx", ".xlsm")
41 def __init__( # pyright: ignore[reportInconsistentConstructor]
42 self,
43 path: FilePath | WriteExcelBuffer | ExcelWriter[Any],
44 engine: str | None = None,
45 date_format: str | None = None,
46 datetime_format: str | None = None,
47 mode: str = "w",
48 storage_options: StorageOptions | None = None,
49 if_sheet_exists: ExcelWriterIfSheetExists | None = None,
50 engine_kwargs: dict[str, Any] | None = None,
51 **kwargs,
52 ) -> None:
53 # Use the openpyxl module as the Excel writer.
54 from openpyxl.workbook import Workbook
56 engine_kwargs = combine_kwargs(engine_kwargs, kwargs)
58 super().__init__(
59 path,
60 mode=mode,
61 storage_options=storage_options,
62 if_sheet_exists=if_sheet_exists,
63 engine_kwargs=engine_kwargs,
64 )
66 # ExcelWriter replaced "a" by "r+" to allow us to first read the excel file from
67 # the file and later write to it
68 if "r+" in self._mode: # Load from existing workbook
69 from openpyxl import load_workbook
71 try:
72 self._book = load_workbook(self._handles.handle, **engine_kwargs)
73 except TypeError:
74 self._handles.handle.close()
75 raise
76 self._handles.handle.seek(0)
77 else:
78 # Create workbook object. Users can opt into write_only mode
79 # for better memory efficiency with
80 # engine_kwargs={"write_only": True} (GH#41681).
81 try:
82 self._book = Workbook(**engine_kwargs)
83 except TypeError:
84 self._handles.handle.close()
85 raise
87 if self.book.worksheets:
88 self.book.remove(self.book.worksheets[0])
90 @property
91 def book(self) -> Workbook:
92 """
93 Book instance of class openpyxl.workbook.Workbook.
95 This attribute can be used to access engine-specific features.
96 """
97 return self._book
99 @property
100 def sheets(self) -> dict[str, Any]:
101 """Mapping of sheet names to sheet objects."""
102 result = {name: self.book[name] for name in self.book.sheetnames}
103 return result
105 def _save(self) -> None:
106 """
107 Save workbook to disk.
108 """
109 self.book.save(self._handles.handle)
110 if "r+" in self._mode and not isinstance(self._handles.handle, mmap.mmap):
111 # truncate file to the written content
112 self._handles.handle.truncate()
114 @classmethod
115 def _convert_to_style_kwargs(
116 cls, style_dict: dict[str, Serialisable]
117 ) -> dict[str, Serialisable]:
118 """
119 Convert a style_dict to a set of kwargs suitable for initializing
120 or updating-on-copy an openpyxl v2 style object.
122 Parameters
123 ----------
124 style_dict : dict
125 A dict with zero or more of the following keys (or their synonyms).
126 'font'
127 'fill'
128 'border' ('borders')
129 'alignment'
130 'number_format'
131 'protection'
133 Returns
134 -------
135 style_kwargs : dict
136 A dict with the same, normalized keys as ``style_dict`` but each
137 value has been replaced with a native openpyxl style object of the
138 appropriate class.
139 """
140 _style_key_map = {"borders": "border"}
142 style_kwargs: dict[str, Serialisable] = {}
143 for k, v in style_dict.items():
144 k = _style_key_map.get(k, k)
145 _conv_to_x = getattr(cls, f"_convert_to_{k}", lambda x: None)
146 new_v = _conv_to_x(v)
147 if new_v:
148 style_kwargs[k] = new_v
150 return style_kwargs
152 @classmethod
153 def _convert_to_color(cls, color_spec):
154 """
155 Convert ``color_spec`` to an openpyxl v2 Color object.
157 Parameters
158 ----------
159 color_spec : str, dict
160 A 32-bit ARGB hex string, or a dict with zero or more of the
161 following keys.
162 'rgb'
163 'indexed'
164 'auto'
165 'theme'
166 'tint'
167 'index'
168 'type'
170 Returns
171 -------
172 color : openpyxl.styles.Color
173 """
174 from openpyxl.styles import Color
176 if isinstance(color_spec, str):
177 return Color(color_spec)
178 else:
179 return Color(**color_spec)
181 @classmethod
182 def _convert_to_font(cls, font_dict):
183 """
184 Convert ``font_dict`` to an openpyxl v2 Font object.
186 Parameters
187 ----------
188 font_dict : dict
189 A dict with zero or more of the following keys (or their synonyms).
190 'name'
191 'size' ('sz')
192 'bold' ('b')
193 'italic' ('i')
194 'underline' ('u')
195 'strikethrough' ('strike')
196 'color'
197 'vertAlign' ('vertalign')
198 'charset'
199 'scheme'
200 'family'
201 'outline'
202 'shadow'
203 'condense'
205 Returns
206 -------
207 font : openpyxl.styles.Font
208 """
209 from openpyxl.styles import Font
211 _font_key_map = {
212 "sz": "size",
213 "b": "bold",
214 "i": "italic",
215 "u": "underline",
216 "strike": "strikethrough",
217 "vertalign": "vertAlign",
218 }
220 font_kwargs = {}
221 for k, v in font_dict.items():
222 k = _font_key_map.get(k, k)
223 if k == "color":
224 v = cls._convert_to_color(v)
225 font_kwargs[k] = v
227 return Font(**font_kwargs)
229 @classmethod
230 def _convert_to_stop(cls, stop_seq):
231 """
232 Convert ``stop_seq`` to a list of openpyxl v2 Color objects,
233 suitable for initializing the ``GradientFill`` ``stop`` parameter.
235 Parameters
236 ----------
237 stop_seq : iterable
238 An iterable that yields objects suitable for consumption by
239 ``_convert_to_color``.
241 Returns
242 -------
243 stop : list of openpyxl.styles.Color
244 """
245 return map(cls._convert_to_color, stop_seq)
247 @classmethod
248 def _convert_to_fill(cls, fill_dict: dict[str, Any]) -> Fill:
249 """
250 Convert ``fill_dict`` to an openpyxl v2 Fill object.
252 Parameters
253 ----------
254 fill_dict : dict
255 A dict with one or more of the following keys (or their synonyms),
256 'fill_type' ('patternType', 'patterntype')
257 'start_color' ('fgColor', 'fgcolor')
258 'end_color' ('bgColor', 'bgcolor')
259 or one or more of the following keys (or their synonyms).
260 'type' ('fill_type')
261 'degree'
262 'left'
263 'right'
264 'top'
265 'bottom'
266 'stop'
268 Returns
269 -------
270 fill : openpyxl.styles.Fill
271 """
272 from openpyxl.styles import (
273 GradientFill,
274 PatternFill,
275 )
277 _pattern_fill_key_map = {
278 "patternType": "fill_type",
279 "patterntype": "fill_type",
280 "fgColor": "start_color",
281 "fgcolor": "start_color",
282 "bgColor": "end_color",
283 "bgcolor": "end_color",
284 }
286 _gradient_fill_key_map = {"fill_type": "type"}
288 pfill_kwargs = {}
289 gfill_kwargs = {}
290 for k, v in fill_dict.items():
291 pk = _pattern_fill_key_map.get(k)
292 gk = _gradient_fill_key_map.get(k)
293 if pk in ["start_color", "end_color"]:
294 v = cls._convert_to_color(v)
295 if gk == "stop":
296 v = cls._convert_to_stop(v)
297 if pk:
298 pfill_kwargs[pk] = v
299 elif gk:
300 gfill_kwargs[gk] = v
301 else:
302 pfill_kwargs[k] = v
303 gfill_kwargs[k] = v
305 try:
306 return PatternFill(**pfill_kwargs)
307 except TypeError:
308 return GradientFill(**gfill_kwargs)
310 @classmethod
311 def _convert_to_side(cls, side_spec):
312 """
313 Convert ``side_spec`` to an openpyxl v2 Side object.
315 Parameters
316 ----------
317 side_spec : str, dict
318 A string specifying the border style, or a dict with zero or more
319 of the following keys (or their synonyms).
320 'style' ('border_style')
321 'color'
323 Returns
324 -------
325 side : openpyxl.styles.Side
326 """
327 from openpyxl.styles import Side
329 _side_key_map = {"border_style": "style"}
331 if isinstance(side_spec, str):
332 return Side(style=side_spec)
334 side_kwargs = {}
335 for k, v in side_spec.items():
336 k = _side_key_map.get(k, k)
337 if k == "color":
338 v = cls._convert_to_color(v)
339 side_kwargs[k] = v
341 return Side(**side_kwargs)
343 @classmethod
344 def _convert_to_border(cls, border_dict):
345 """
346 Convert ``border_dict`` to an openpyxl v2 Border object.
348 Parameters
349 ----------
350 border_dict : dict
351 A dict with zero or more of the following keys (or their synonyms).
352 'left'
353 'right'
354 'top'
355 'bottom'
356 'diagonal'
357 'diagonal_direction'
358 'vertical'
359 'horizontal'
360 'diagonalUp' ('diagonalup')
361 'diagonalDown' ('diagonaldown')
362 'outline'
364 Returns
365 -------
366 border : openpyxl.styles.Border
367 """
368 from openpyxl.styles import Border
370 _border_key_map = {"diagonalup": "diagonalUp", "diagonaldown": "diagonalDown"}
372 border_kwargs = {}
373 for k, v in border_dict.items():
374 k = _border_key_map.get(k, k)
375 if k == "color":
376 v = cls._convert_to_color(v)
377 if k in ["left", "right", "top", "bottom", "diagonal"]:
378 v = cls._convert_to_side(v)
379 border_kwargs[k] = v
381 return Border(**border_kwargs)
383 @classmethod
384 def _convert_to_alignment(cls, alignment_dict):
385 """
386 Convert ``alignment_dict`` to an openpyxl v2 Alignment object.
388 Parameters
389 ----------
390 alignment_dict : dict
391 A dict with zero or more of the following keys (or their synonyms).
392 'horizontal'
393 'vertical'
394 'text_rotation'
395 'wrap_text'
396 'shrink_to_fit'
397 'indent'
398 Returns
399 -------
400 alignment : openpyxl.styles.Alignment
401 """
402 from openpyxl.styles import Alignment
404 return Alignment(**alignment_dict)
406 @classmethod
407 def _convert_to_number_format(cls, number_format_dict):
408 """
409 Convert ``number_format_dict`` to an openpyxl v2.1.0 number format
410 initializer.
412 Parameters
413 ----------
414 number_format_dict : dict
415 A dict with zero or more of the following keys.
416 'format_code' : str
418 Returns
419 -------
420 number_format : str
421 """
422 return number_format_dict["format_code"]
424 @classmethod
425 def _convert_to_protection(cls, protection_dict):
426 """
427 Convert ``protection_dict`` to an openpyxl v2 Protection object.
429 Parameters
430 ----------
431 protection_dict : dict
432 A dict with zero or more of the following keys.
433 'locked'
434 'hidden'
436 Returns
437 -------
438 """
439 from openpyxl.styles import Protection
441 return Protection(**protection_dict)
443 def _write_cells(
444 self,
445 cells,
446 sheet_name: str | None = None,
447 startrow: int = 0,
448 startcol: int = 0,
449 freeze_panes: tuple[int, int] | None = None,
450 autofilter_range: str | None = None,
451 ) -> None:
452 # Write the frame cells using openpyxl.
453 sheet_name = self._get_sheet_name(sheet_name)
455 _style_cache: dict[str, dict[str, Serialisable]] = {}
457 if sheet_name in self.sheets and self._if_sheet_exists != "new":
458 if "r+" in self._mode:
459 if self._if_sheet_exists == "replace":
460 old_wks = self.sheets[sheet_name]
461 target_index = self.book.index(old_wks)
462 del self.book[sheet_name]
463 wks = self.book.create_sheet(sheet_name, target_index)
464 elif self._if_sheet_exists == "error":
465 raise ValueError(
466 f"Sheet '{sheet_name}' already exists and "
467 f"if_sheet_exists is set to 'error'."
468 )
469 elif self._if_sheet_exists == "overlay":
470 wks = self.sheets[sheet_name]
471 else:
472 raise ValueError(
473 f"'{self._if_sheet_exists}' is not valid for if_sheet_exists. "
474 "Valid options are 'error', 'new', 'replace' and 'overlay'."
475 )
476 else:
477 wks = self.sheets[sheet_name]
478 else:
479 wks = self.book.create_sheet()
480 wks.title = sheet_name
482 if self.book.write_only:
483 self._write_cells_write_only(
484 cells,
485 wks,
486 startrow,
487 startcol,
488 freeze_panes,
489 autofilter_range,
490 _style_cache,
491 )
492 else:
493 self._write_cells_normal(
494 cells,
495 wks,
496 startrow,
497 startcol,
498 freeze_panes,
499 autofilter_range,
500 _style_cache,
501 )
503 def _write_cells_write_only(
504 self,
505 cells,
506 wks,
507 startrow: int,
508 startcol: int,
509 freeze_panes: tuple[int, int] | None,
510 autofilter_range: str | None,
511 _style_cache: dict[str, dict[str, Serialisable]],
512 ) -> None:
513 # Write-only mode: buffer cells by row then append for memory
514 # efficiency (GH#41681). Cells may arrive column-by-column rather
515 # than row-by-row, so we must buffer all cells before writing.
516 from openpyxl.cell.cell import Cell
517 from openpyxl.utils import get_column_letter
519 if validate_freeze_panes(freeze_panes):
520 col_letter = get_column_letter(freeze_panes[1] + 1)
521 wks.freeze_panes = f"{col_letter}{freeze_panes[0] + 1}"
523 # Buffer all cells indexed by (row, col)
524 rows: dict[int, dict[int, Cell]] = {}
525 merge_ranges: list[str] = []
526 max_col = -1
528 for cell in cells:
529 row_idx = startrow + cell.row
530 col_idx = startcol + cell.col
531 if col_idx > max_col:
532 max_col = col_idx
534 xcell = Cell(wks, row=1, column=1)
535 xcell.value, fmt = self._value_with_fmt(cell.val)
536 if fmt:
537 xcell.number_format = fmt
539 style_kwargs: dict[str, Serialisable] | None = {}
540 if cell.style:
541 key = str(cell.style)
542 style_kwargs = _style_cache.get(key)
543 if style_kwargs is None:
544 style_kwargs = self._convert_to_style_kwargs(cell.style)
545 _style_cache[key] = style_kwargs
547 if style_kwargs:
548 for attr, value in style_kwargs.items():
549 setattr(xcell, attr, value)
551 rows.setdefault(row_idx, {})[col_idx] = xcell
553 if cell.mergestart is not None and cell.mergeend is not None:
554 # Build cell range string like "A1:B2" for merged_cells.add()
555 # since WriteOnlyWorksheet does not have merge_cells()
556 start_col_letter = get_column_letter(startcol + cell.col + 1)
557 end_col_letter = get_column_letter(startcol + cell.mergeend + 1)
558 merge_range = (
559 f"{start_col_letter}{startrow + cell.row + 1}:"
560 f"{end_col_letter}{startrow + cell.mergestart + 1}"
561 )
562 merge_ranges.append(merge_range)
564 # Write rows in order, filling gaps with empty rows
565 if rows:
566 max_row = max(rows)
567 for row_idx in range(max_row + 1):
568 if row_idx not in rows:
569 wks.append([])
570 continue
571 row_data = rows[row_idx]
572 row_cells: list[Cell | None] = [None] * (max_col + 1)
573 for col_idx, xcell in row_data.items():
574 row_cells[col_idx] = xcell
575 wks.append(row_cells)
577 # Apply merge ranges via merged_cells.add() (WriteOnlyWorksheet
578 # does not support the merge_cells() method)
579 for merge_range in merge_ranges:
580 wks.merged_cells.add(merge_range)
582 if autofilter_range:
583 wks.auto_filter.ref = autofilter_range
585 def _write_cells_normal(
586 self,
587 cells,
588 wks,
589 startrow: int,
590 startcol: int,
591 freeze_panes: tuple[int, int] | None,
592 autofilter_range: str | None,
593 _style_cache: dict[str, dict[str, Serialisable]],
594 ) -> None:
595 if validate_freeze_panes(freeze_panes):
596 wks.freeze_panes = wks.cell(
597 row=freeze_panes[0] + 1, column=freeze_panes[1] + 1
598 )
600 for cell in cells:
601 xcell = wks.cell(
602 row=startrow + cell.row + 1, column=startcol + cell.col + 1
603 )
604 xcell.value, fmt = self._value_with_fmt(cell.val)
605 if fmt:
606 xcell.number_format = fmt
608 style_kwargs: dict[str, Serialisable] | None = {}
609 if cell.style:
610 key = str(cell.style)
611 style_kwargs = _style_cache.get(key)
612 if style_kwargs is None:
613 style_kwargs = self._convert_to_style_kwargs(cell.style)
614 _style_cache[key] = style_kwargs
616 if style_kwargs:
617 for attr, value in style_kwargs.items():
618 setattr(xcell, attr, value)
620 if cell.mergestart is not None and cell.mergeend is not None:
621 wks.merge_cells(
622 start_row=startrow + cell.row + 1,
623 start_column=startcol + cell.col + 1,
624 end_column=startcol + cell.mergeend + 1,
625 end_row=startrow + cell.mergestart + 1,
626 )
628 # When cells are merged only the top-left cell is preserved
629 # The behaviour of the other cells in a merged range is
630 # undefined
631 if style_kwargs:
632 first_row = startrow + cell.row + 1
633 last_row = startrow + cell.mergestart + 1
634 first_col = startcol + cell.col + 1
635 last_col = startcol + cell.mergeend + 1
637 for row in range(first_row, last_row + 1):
638 for col in range(first_col, last_col + 1):
639 if row == first_row and col == first_col:
640 # Ignore first cell. It is already handled.
641 continue
642 xcell = wks.cell(column=col, row=row)
643 for attr, value in style_kwargs.items():
644 setattr(xcell, attr, value)
646 if autofilter_range:
647 wks.auto_filter.ref = autofilter_range
650class OpenpyxlReader(BaseExcelReader["Workbook"]):
651 def __init__(
652 self,
653 filepath_or_buffer: FilePath | ReadBuffer[bytes],
654 storage_options: StorageOptions | None = None,
655 engine_kwargs: dict[str, Any] | None = None,
656 ) -> None:
657 """
658 Reader using openpyxl engine.
660 Parameters
661 ----------
662 filepath_or_buffer : str, path object or Workbook
663 Object to be parsed.
664 storage_options : dict, optional
665 Extra options that make sense for a particular storage connection, e.g.
666 host, port, username, password, etc. For HTTP(S) URLs the key-value pairs
667 are forwarded to ``urllib.request.Request`` as header options. For other
668 URLs (e.g. starting with "s3://", and "gcs://") the key-value pairs are
669 forwarded to ``fsspec.open``. Please see ``fsspec`` and ``urllib`` for more
670 details, and for more examples on storage options refer `here
671 <https://pandas.pydata.org/docs/user_guide/io.html?
672 highlight=storage_options#reading-writing-remote-files>`_.
673 engine_kwargs : dict, optional
674 Arbitrary keyword arguments passed to excel engine.
675 """
676 import_optional_dependency("openpyxl")
677 super().__init__(
678 filepath_or_buffer,
679 storage_options=storage_options,
680 engine_kwargs=engine_kwargs,
681 )
683 @property
684 def _workbook_class(self) -> type[Workbook]:
685 from openpyxl import Workbook
687 return Workbook
689 def load_workbook(
690 self, filepath_or_buffer: FilePath | ReadBuffer[bytes], engine_kwargs
691 ) -> Workbook:
692 from openpyxl import load_workbook
694 default_kwargs = {"read_only": True, "data_only": True, "keep_links": False}
696 return load_workbook(
697 filepath_or_buffer,
698 **(default_kwargs | engine_kwargs),
699 )
701 @property
702 def sheet_names(self) -> list[str]:
703 return [sheet.title for sheet in self.book.worksheets]
705 def get_sheet_by_name(self, name: str):
706 self.raise_if_bad_sheet_by_name(name)
707 return self.book[name]
709 def get_sheet_by_index(self, index: int):
710 self.raise_if_bad_sheet_by_index(index)
711 return self.book.worksheets[index]
713 def _convert_cell(self, cell) -> Scalar:
714 from openpyxl.cell.cell import (
715 TYPE_ERROR,
716 TYPE_NUMERIC,
717 )
719 if cell.value is None:
720 return "" # compat with xlrd
721 elif cell.data_type == TYPE_ERROR:
722 return np.nan
723 elif cell.data_type == TYPE_NUMERIC:
724 val = int(cell.value)
725 if val == cell.value:
726 return val
727 return float(cell.value)
729 return cell.value
731 def get_sheet_data(
732 self, sheet, file_rows_needed: int | None = None
733 ) -> list[list[Scalar]]:
734 saved_dimensions: tuple[int | None, int | None] | None = None
735 if self.book.read_only:
736 # Declared sheet dimensions can be incorrect, so have openpyxl
737 # infer the real extent while iterating (GH 39486); save them to
738 # restore the user's worksheet handle afterwards (GH 63010)
739 saved_dimensions = (sheet.max_row, sheet.max_column)
740 sheet.reset_dimensions()
742 data: list[list[Scalar]] = []
743 last_row_with_data = -1
744 try:
745 for row_number, row in enumerate(sheet.rows):
746 converted_row = [self._convert_cell(cell) for cell in row]
747 while converted_row and converted_row[-1] == "":
748 # trim trailing empty elements
749 converted_row.pop()
750 if converted_row:
751 last_row_with_data = row_number
752 data.append(converted_row)
753 if file_rows_needed is not None and len(data) >= file_rows_needed:
754 break
755 finally:
756 if saved_dimensions is not None:
757 # max_row/max_column of a read-only worksheet have no setters
758 sheet._max_row, sheet._max_column = saved_dimensions
760 # Trim trailing empty rows
761 data = data[: last_row_with_data + 1]
763 if len(data) > 0:
764 # extend rows to max width
765 max_width = max(len(data_row) for data_row in data)
766 if min(len(data_row) for data_row in data) < max_width:
767 empty_cell: list[Scalar] = [""]
768 data = [
769 data_row + (max_width - len(data_row)) * empty_cell
770 for data_row in data
771 ]
773 return data