Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/pandas/io/excel/_openpyxl.py : 17%

Hot-keys on this page
r m x p toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1from typing import List
3import numpy as np
5from pandas._typing import FilePathOrBuffer, Scalar
6from pandas.compat._optional import import_optional_dependency
8from pandas.io.excel._base import ExcelWriter, _BaseExcelReader
9from pandas.io.excel._util import _validate_freeze_panes
12class _OpenpyxlWriter(ExcelWriter):
13 engine = "openpyxl"
14 supported_extensions = (".xlsx", ".xlsm")
16 def __init__(self, path, engine=None, mode="w", **engine_kwargs):
17 # Use the openpyxl module as the Excel writer.
18 from openpyxl.workbook import Workbook
20 super().__init__(path, mode=mode, **engine_kwargs)
22 if self.mode == "a": # Load from existing workbook
23 from openpyxl import load_workbook
25 book = load_workbook(self.path)
26 self.book = book
27 else:
28 # Create workbook object with default optimized_write=True.
29 self.book = Workbook()
31 if self.book.worksheets:
32 try:
33 self.book.remove(self.book.worksheets[0])
34 except AttributeError:
36 # compat - for openpyxl <= 2.4
37 self.book.remove_sheet(self.book.worksheets[0])
39 def save(self):
40 """
41 Save workbook to disk.
42 """
43 return self.book.save(self.path)
45 @classmethod
46 def _convert_to_style(cls, style_dict):
47 """
48 Converts a style_dict to an openpyxl style object.
50 Parameters
51 ----------
52 style_dict : style dictionary to convert
53 """
55 from openpyxl.style import Style
57 xls_style = Style()
58 for key, value in style_dict.items():
59 for nk, nv in value.items():
60 if key == "borders":
61 (
62 xls_style.borders.__getattribute__(nk).__setattr__(
63 "border_style", nv
64 )
65 )
66 else:
67 xls_style.__getattribute__(key).__setattr__(nk, nv)
69 return xls_style
71 @classmethod
72 def _convert_to_style_kwargs(cls, style_dict):
73 """
74 Convert a style_dict to a set of kwargs suitable for initializing
75 or updating-on-copy an openpyxl v2 style object.
77 Parameters
78 ----------
79 style_dict : dict
80 A dict with zero or more of the following keys (or their synonyms).
81 'font'
82 'fill'
83 'border' ('borders')
84 'alignment'
85 'number_format'
86 'protection'
88 Returns
89 -------
90 style_kwargs : dict
91 A dict with the same, normalized keys as ``style_dict`` but each
92 value has been replaced with a native openpyxl style object of the
93 appropriate class.
94 """
96 _style_key_map = {"borders": "border"}
98 style_kwargs = {}
99 for k, v in style_dict.items():
100 if k in _style_key_map:
101 k = _style_key_map[k]
102 _conv_to_x = getattr(cls, f"_convert_to_{k}", lambda x: None)
103 new_v = _conv_to_x(v)
104 if new_v:
105 style_kwargs[k] = new_v
107 return style_kwargs
109 @classmethod
110 def _convert_to_color(cls, color_spec):
111 """
112 Convert ``color_spec`` to an openpyxl v2 Color object.
114 Parameters
115 ----------
116 color_spec : str, dict
117 A 32-bit ARGB hex string, or a dict with zero or more of the
118 following keys.
119 'rgb'
120 'indexed'
121 'auto'
122 'theme'
123 'tint'
124 'index'
125 'type'
127 Returns
128 -------
129 color : openpyxl.styles.Color
130 """
132 from openpyxl.styles import Color
134 if isinstance(color_spec, str):
135 return Color(color_spec)
136 else:
137 return Color(**color_spec)
139 @classmethod
140 def _convert_to_font(cls, font_dict):
141 """
142 Convert ``font_dict`` to an openpyxl v2 Font object.
144 Parameters
145 ----------
146 font_dict : dict
147 A dict with zero or more of the following keys (or their synonyms).
148 'name'
149 'size' ('sz')
150 'bold' ('b')
151 'italic' ('i')
152 'underline' ('u')
153 'strikethrough' ('strike')
154 'color'
155 'vertAlign' ('vertalign')
156 'charset'
157 'scheme'
158 'family'
159 'outline'
160 'shadow'
161 'condense'
163 Returns
164 -------
165 font : openpyxl.styles.Font
166 """
168 from openpyxl.styles import Font
170 _font_key_map = {
171 "sz": "size",
172 "b": "bold",
173 "i": "italic",
174 "u": "underline",
175 "strike": "strikethrough",
176 "vertalign": "vertAlign",
177 }
179 font_kwargs = {}
180 for k, v in font_dict.items():
181 if k in _font_key_map:
182 k = _font_key_map[k]
183 if k == "color":
184 v = cls._convert_to_color(v)
185 font_kwargs[k] = v
187 return Font(**font_kwargs)
189 @classmethod
190 def _convert_to_stop(cls, stop_seq):
191 """
192 Convert ``stop_seq`` to a list of openpyxl v2 Color objects,
193 suitable for initializing the ``GradientFill`` ``stop`` parameter.
195 Parameters
196 ----------
197 stop_seq : iterable
198 An iterable that yields objects suitable for consumption by
199 ``_convert_to_color``.
201 Returns
202 -------
203 stop : list of openpyxl.styles.Color
204 """
206 return map(cls._convert_to_color, stop_seq)
208 @classmethod
209 def _convert_to_fill(cls, fill_dict):
210 """
211 Convert ``fill_dict`` to an openpyxl v2 Fill object.
213 Parameters
214 ----------
215 fill_dict : dict
216 A dict with one or more of the following keys (or their synonyms),
217 'fill_type' ('patternType', 'patterntype')
218 'start_color' ('fgColor', 'fgcolor')
219 'end_color' ('bgColor', 'bgcolor')
220 or one or more of the following keys (or their synonyms).
221 'type' ('fill_type')
222 'degree'
223 'left'
224 'right'
225 'top'
226 'bottom'
227 'stop'
229 Returns
230 -------
231 fill : openpyxl.styles.Fill
232 """
234 from openpyxl.styles import PatternFill, GradientFill
236 _pattern_fill_key_map = {
237 "patternType": "fill_type",
238 "patterntype": "fill_type",
239 "fgColor": "start_color",
240 "fgcolor": "start_color",
241 "bgColor": "end_color",
242 "bgcolor": "end_color",
243 }
245 _gradient_fill_key_map = {"fill_type": "type"}
247 pfill_kwargs = {}
248 gfill_kwargs = {}
249 for k, v in fill_dict.items():
250 pk = gk = None
251 if k in _pattern_fill_key_map:
252 pk = _pattern_fill_key_map[k]
253 if k in _gradient_fill_key_map:
254 gk = _gradient_fill_key_map[k]
255 if pk in ["start_color", "end_color"]:
256 v = cls._convert_to_color(v)
257 if gk == "stop":
258 v = cls._convert_to_stop(v)
259 if pk:
260 pfill_kwargs[pk] = v
261 elif gk:
262 gfill_kwargs[gk] = v
263 else:
264 pfill_kwargs[k] = v
265 gfill_kwargs[k] = v
267 try:
268 return PatternFill(**pfill_kwargs)
269 except TypeError:
270 return GradientFill(**gfill_kwargs)
272 @classmethod
273 def _convert_to_side(cls, side_spec):
274 """
275 Convert ``side_spec`` to an openpyxl v2 Side object.
277 Parameters
278 ----------
279 side_spec : str, dict
280 A string specifying the border style, or a dict with zero or more
281 of the following keys (or their synonyms).
282 'style' ('border_style')
283 'color'
285 Returns
286 -------
287 side : openpyxl.styles.Side
288 """
290 from openpyxl.styles import Side
292 _side_key_map = {"border_style": "style"}
294 if isinstance(side_spec, str):
295 return Side(style=side_spec)
297 side_kwargs = {}
298 for k, v in side_spec.items():
299 if k in _side_key_map:
300 k = _side_key_map[k]
301 if k == "color":
302 v = cls._convert_to_color(v)
303 side_kwargs[k] = v
305 return Side(**side_kwargs)
307 @classmethod
308 def _convert_to_border(cls, border_dict):
309 """
310 Convert ``border_dict`` to an openpyxl v2 Border object.
312 Parameters
313 ----------
314 border_dict : dict
315 A dict with zero or more of the following keys (or their synonyms).
316 'left'
317 'right'
318 'top'
319 'bottom'
320 'diagonal'
321 'diagonal_direction'
322 'vertical'
323 'horizontal'
324 'diagonalUp' ('diagonalup')
325 'diagonalDown' ('diagonaldown')
326 'outline'
328 Returns
329 -------
330 border : openpyxl.styles.Border
331 """
333 from openpyxl.styles import Border
335 _border_key_map = {"diagonalup": "diagonalUp", "diagonaldown": "diagonalDown"}
337 border_kwargs = {}
338 for k, v in border_dict.items():
339 if k in _border_key_map:
340 k = _border_key_map[k]
341 if k == "color":
342 v = cls._convert_to_color(v)
343 if k in ["left", "right", "top", "bottom", "diagonal"]:
344 v = cls._convert_to_side(v)
345 border_kwargs[k] = v
347 return Border(**border_kwargs)
349 @classmethod
350 def _convert_to_alignment(cls, alignment_dict):
351 """
352 Convert ``alignment_dict`` to an openpyxl v2 Alignment object.
354 Parameters
355 ----------
356 alignment_dict : dict
357 A dict with zero or more of the following keys (or their synonyms).
358 'horizontal'
359 'vertical'
360 'text_rotation'
361 'wrap_text'
362 'shrink_to_fit'
363 'indent'
364 Returns
365 -------
366 alignment : openpyxl.styles.Alignment
367 """
369 from openpyxl.styles import Alignment
371 return Alignment(**alignment_dict)
373 @classmethod
374 def _convert_to_number_format(cls, number_format_dict):
375 """
376 Convert ``number_format_dict`` to an openpyxl v2.1.0 number format
377 initializer.
378 Parameters
379 ----------
380 number_format_dict : dict
381 A dict with zero or more of the following keys.
382 'format_code' : str
383 Returns
384 -------
385 number_format : str
386 """
387 return number_format_dict["format_code"]
389 @classmethod
390 def _convert_to_protection(cls, protection_dict):
391 """
392 Convert ``protection_dict`` to an openpyxl v2 Protection object.
393 Parameters
394 ----------
395 protection_dict : dict
396 A dict with zero or more of the following keys.
397 'locked'
398 'hidden'
399 Returns
400 -------
401 """
403 from openpyxl.styles import Protection
405 return Protection(**protection_dict)
407 def write_cells(
408 self, cells, sheet_name=None, startrow=0, startcol=0, freeze_panes=None
409 ):
410 # Write the frame cells using openpyxl.
411 sheet_name = self._get_sheet_name(sheet_name)
413 _style_cache = {}
415 if sheet_name in self.sheets:
416 wks = self.sheets[sheet_name]
417 else:
418 wks = self.book.create_sheet()
419 wks.title = sheet_name
420 self.sheets[sheet_name] = wks
422 if _validate_freeze_panes(freeze_panes):
423 wks.freeze_panes = wks.cell(
424 row=freeze_panes[0] + 1, column=freeze_panes[1] + 1
425 )
427 for cell in cells:
428 xcell = wks.cell(
429 row=startrow + cell.row + 1, column=startcol + cell.col + 1
430 )
431 xcell.value, fmt = self._value_with_fmt(cell.val)
432 if fmt:
433 xcell.number_format = fmt
435 style_kwargs = {}
436 if cell.style:
437 key = str(cell.style)
438 style_kwargs = _style_cache.get(key)
439 if style_kwargs is None:
440 style_kwargs = self._convert_to_style_kwargs(cell.style)
441 _style_cache[key] = style_kwargs
443 if style_kwargs:
444 for k, v in style_kwargs.items():
445 setattr(xcell, k, v)
447 if cell.mergestart is not None and cell.mergeend is not None:
449 wks.merge_cells(
450 start_row=startrow + cell.row + 1,
451 start_column=startcol + cell.col + 1,
452 end_column=startcol + cell.mergeend + 1,
453 end_row=startrow + cell.mergestart + 1,
454 )
456 # When cells are merged only the top-left cell is preserved
457 # The behaviour of the other cells in a merged range is
458 # undefined
459 if style_kwargs:
460 first_row = startrow + cell.row + 1
461 last_row = startrow + cell.mergestart + 1
462 first_col = startcol + cell.col + 1
463 last_col = startcol + cell.mergeend + 1
465 for row in range(first_row, last_row + 1):
466 for col in range(first_col, last_col + 1):
467 if row == first_row and col == first_col:
468 # Ignore first cell. It is already handled.
469 continue
470 xcell = wks.cell(column=col, row=row)
471 for k, v in style_kwargs.items():
472 setattr(xcell, k, v)
475class _OpenpyxlReader(_BaseExcelReader):
476 def __init__(self, filepath_or_buffer: FilePathOrBuffer) -> None:
477 """Reader using openpyxl engine.
479 Parameters
480 ----------
481 filepath_or_buffer : string, path object or Workbook
482 Object to be parsed.
483 """
484 import_optional_dependency("openpyxl")
485 super().__init__(filepath_or_buffer)
487 @property
488 def _workbook_class(self):
489 from openpyxl import Workbook
491 return Workbook
493 def load_workbook(self, filepath_or_buffer: FilePathOrBuffer):
494 from openpyxl import load_workbook
496 return load_workbook(
497 filepath_or_buffer, read_only=True, data_only=True, keep_links=False
498 )
500 def close(self):
501 # https://stackoverflow.com/questions/31416842/
502 # openpyxl-does-not-close-excel-workbook-in-read-only-mode
503 self.book.close()
505 @property
506 def sheet_names(self) -> List[str]:
507 return self.book.sheetnames
509 def get_sheet_by_name(self, name: str):
510 return self.book[name]
512 def get_sheet_by_index(self, index: int):
513 return self.book.worksheets[index]
515 def _convert_cell(self, cell, convert_float: bool) -> Scalar:
517 # TODO: replace with openpyxl constants
518 if cell.is_date:
519 return cell.value
520 elif cell.data_type == "e":
521 return np.nan
522 elif cell.data_type == "b":
523 return bool(cell.value)
524 elif cell.value is None:
525 return "" # compat with xlrd
526 elif cell.data_type == "n":
527 # GH5394
528 if convert_float:
529 val = int(cell.value)
530 if val == cell.value:
531 return val
532 else:
533 return float(cell.value)
535 return cell.value
537 def get_sheet_data(self, sheet, convert_float: bool) -> List[List[Scalar]]:
538 data: List[List[Scalar]] = []
539 for row in sheet.rows:
540 data.append([self._convert_cell(cell, convert_float) for cell in row])
542 return data