Hide keyboard shortcuts

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 

2 

3import numpy as np 

4 

5from pandas._typing import FilePathOrBuffer, Scalar 

6from pandas.compat._optional import import_optional_dependency 

7 

8from pandas.io.excel._base import ExcelWriter, _BaseExcelReader 

9from pandas.io.excel._util import _validate_freeze_panes 

10 

11 

12class _OpenpyxlWriter(ExcelWriter): 

13 engine = "openpyxl" 

14 supported_extensions = (".xlsx", ".xlsm") 

15 

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 

19 

20 super().__init__(path, mode=mode, **engine_kwargs) 

21 

22 if self.mode == "a": # Load from existing workbook 

23 from openpyxl import load_workbook 

24 

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() 

30 

31 if self.book.worksheets: 

32 try: 

33 self.book.remove(self.book.worksheets[0]) 

34 except AttributeError: 

35 

36 # compat - for openpyxl <= 2.4 

37 self.book.remove_sheet(self.book.worksheets[0]) 

38 

39 def save(self): 

40 """ 

41 Save workbook to disk. 

42 """ 

43 return self.book.save(self.path) 

44 

45 @classmethod 

46 def _convert_to_style(cls, style_dict): 

47 """ 

48 Converts a style_dict to an openpyxl style object. 

49 

50 Parameters 

51 ---------- 

52 style_dict : style dictionary to convert 

53 """ 

54 

55 from openpyxl.style import Style 

56 

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) 

68 

69 return xls_style 

70 

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. 

76 

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' 

87 

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 """ 

95 

96 _style_key_map = {"borders": "border"} 

97 

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 

106 

107 return style_kwargs 

108 

109 @classmethod 

110 def _convert_to_color(cls, color_spec): 

111 """ 

112 Convert ``color_spec`` to an openpyxl v2 Color object. 

113 

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' 

126 

127 Returns 

128 ------- 

129 color : openpyxl.styles.Color 

130 """ 

131 

132 from openpyxl.styles import Color 

133 

134 if isinstance(color_spec, str): 

135 return Color(color_spec) 

136 else: 

137 return Color(**color_spec) 

138 

139 @classmethod 

140 def _convert_to_font(cls, font_dict): 

141 """ 

142 Convert ``font_dict`` to an openpyxl v2 Font object. 

143 

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' 

162 

163 Returns 

164 ------- 

165 font : openpyxl.styles.Font 

166 """ 

167 

168 from openpyxl.styles import Font 

169 

170 _font_key_map = { 

171 "sz": "size", 

172 "b": "bold", 

173 "i": "italic", 

174 "u": "underline", 

175 "strike": "strikethrough", 

176 "vertalign": "vertAlign", 

177 } 

178 

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 

186 

187 return Font(**font_kwargs) 

188 

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. 

194 

195 Parameters 

196 ---------- 

197 stop_seq : iterable 

198 An iterable that yields objects suitable for consumption by 

199 ``_convert_to_color``. 

200 

201 Returns 

202 ------- 

203 stop : list of openpyxl.styles.Color 

204 """ 

205 

206 return map(cls._convert_to_color, stop_seq) 

207 

208 @classmethod 

209 def _convert_to_fill(cls, fill_dict): 

210 """ 

211 Convert ``fill_dict`` to an openpyxl v2 Fill object. 

212 

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' 

228 

229 Returns 

230 ------- 

231 fill : openpyxl.styles.Fill 

232 """ 

233 

234 from openpyxl.styles import PatternFill, GradientFill 

235 

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 } 

244 

245 _gradient_fill_key_map = {"fill_type": "type"} 

246 

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 

266 

267 try: 

268 return PatternFill(**pfill_kwargs) 

269 except TypeError: 

270 return GradientFill(**gfill_kwargs) 

271 

272 @classmethod 

273 def _convert_to_side(cls, side_spec): 

274 """ 

275 Convert ``side_spec`` to an openpyxl v2 Side object. 

276 

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' 

284 

285 Returns 

286 ------- 

287 side : openpyxl.styles.Side 

288 """ 

289 

290 from openpyxl.styles import Side 

291 

292 _side_key_map = {"border_style": "style"} 

293 

294 if isinstance(side_spec, str): 

295 return Side(style=side_spec) 

296 

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 

304 

305 return Side(**side_kwargs) 

306 

307 @classmethod 

308 def _convert_to_border(cls, border_dict): 

309 """ 

310 Convert ``border_dict`` to an openpyxl v2 Border object. 

311 

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' 

327 

328 Returns 

329 ------- 

330 border : openpyxl.styles.Border 

331 """ 

332 

333 from openpyxl.styles import Border 

334 

335 _border_key_map = {"diagonalup": "diagonalUp", "diagonaldown": "diagonalDown"} 

336 

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 

346 

347 return Border(**border_kwargs) 

348 

349 @classmethod 

350 def _convert_to_alignment(cls, alignment_dict): 

351 """ 

352 Convert ``alignment_dict`` to an openpyxl v2 Alignment object. 

353 

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 """ 

368 

369 from openpyxl.styles import Alignment 

370 

371 return Alignment(**alignment_dict) 

372 

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"] 

388 

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 """ 

402 

403 from openpyxl.styles import Protection 

404 

405 return Protection(**protection_dict) 

406 

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) 

412 

413 _style_cache = {} 

414 

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 

421 

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 ) 

426 

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 

434 

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 

442 

443 if style_kwargs: 

444 for k, v in style_kwargs.items(): 

445 setattr(xcell, k, v) 

446 

447 if cell.mergestart is not None and cell.mergeend is not None: 

448 

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 ) 

455 

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 

464 

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) 

473 

474 

475class _OpenpyxlReader(_BaseExcelReader): 

476 def __init__(self, filepath_or_buffer: FilePathOrBuffer) -> None: 

477 """Reader using openpyxl engine. 

478 

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) 

486 

487 @property 

488 def _workbook_class(self): 

489 from openpyxl import Workbook 

490 

491 return Workbook 

492 

493 def load_workbook(self, filepath_or_buffer: FilePathOrBuffer): 

494 from openpyxl import load_workbook 

495 

496 return load_workbook( 

497 filepath_or_buffer, read_only=True, data_only=True, keep_links=False 

498 ) 

499 

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() 

504 

505 @property 

506 def sheet_names(self) -> List[str]: 

507 return self.book.sheetnames 

508 

509 def get_sheet_by_name(self, name: str): 

510 return self.book[name] 

511 

512 def get_sheet_by_index(self, index: int): 

513 return self.book.worksheets[index] 

514 

515 def _convert_cell(self, cell, convert_float: bool) -> Scalar: 

516 

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) 

534 

535 return cell.value 

536 

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]) 

541 

542 return data