Coverage for src / ezxl / io / _formatters.py: 96.57%
131 statements
« prev ^ index » next coverage.py v7.13.5, created at 2026-10-17 10:25 +0000
1# ///////////////////////////////////////////////////////////////
2# _formatters - Closed-file Excel formatting via openpyxl
3# Project: EzXl
4# ///////////////////////////////////////////////////////////////
6"""
7ExcelFormatter — fluent API for formatting Excel files without COM.
9Operates exclusively on **closed** workbook files using openpyxl. All
10formatting operations are buffered internally and applied in a single
11pass when ``save()`` is called.
13This module has no dependency on pywin32 or a running Excel process.
14"""
16from __future__ import annotations
18# ///////////////////////////////////////////////////////////////
19# IMPORTS
20# ///////////////////////////////////////////////////////////////
21# Standard library imports
22import os
23from copy import copy
24from dataclasses import dataclass
25from pathlib import Path
26from typing import Any, cast
28# Third-party imports
29import openpyxl
30from ezplog.lib_mode import get_logger, get_printer
31from openpyxl.styles import (
32 Alignment,
33 Border,
34 Color,
35 PatternFill,
36 Side,
37)
39# ///////////////////////////////////////////////////////////////
40# CONSTANTS
41# ///////////////////////////////////////////////////////////////
43logger = get_logger(__name__)
44printer = get_printer()
46# ///////////////////////////////////////////////////////////////
47# INTERNAL DATA STRUCTURES
48# ///////////////////////////////////////////////////////////////
49# Each operation is stored as a dataclass so that save() can apply them
50# in insertion order without repeatedly touching the openpyxl workbook.
53@dataclass
54class _ColumnWidthOp:
55 col: str
56 width: float
59@dataclass
60class _RowHeightOp:
61 row: int
62 height: float
65@dataclass
66class _FontOp:
67 ref: str
68 bold: bool | None
69 italic: bool | None
70 size: int | None
71 color: str | None # hex without '#'
74@dataclass
75class _FillOp:
76 ref: str
77 color: str # hex without '#'
80@dataclass
81class _BorderOp:
82 ref: str
83 style: str
86@dataclass
87class _AlignOp:
88 ref: str
89 horizontal: str | None
90 vertical: str | None
91 wrap: bool
94_Operation = _ColumnWidthOp | _RowHeightOp | _FontOp | _FillOp | _BorderOp | _AlignOp
96# ///////////////////////////////////////////////////////////////
97# CLASSES
98# ///////////////////////////////////////////////////////////////
101class ExcelFormatter:
102 """Fluent formatter for closed Excel workbook files.
104 All formatting operations are buffered and applied in a single write
105 pass when ``save()`` is called. The workbook is opened with openpyxl
106 only at save time, minimising I/O overhead.
108 The API is intentionally flat: no sheet selector is exposed here. The
109 formatter operates on the **active sheet** of the workbook. Consumer
110 libraries that need multi-sheet formatting should instantiate one
111 ``ExcelFormatter`` per sheet operation.
113 Warning:
114 Saving round-trips the file through openpyxl, which does not model
115 every part of an Excel package. Charts survive and still render, but
116 lose their theme styling (``colors*.xml`` / ``style*.xml`` are not
117 rewritten). Pivot tables and embedded objects openpyxl cannot parse
118 are not preserved either. VBA macros **are** kept for ``.xlsm``
119 files. When a workbook carries content that matters, format a copy
120 via ``save(dest)`` rather than overwriting the original.
122 Args:
123 path: Path to an existing ``.xlsx`` workbook file.
125 Raises:
126 FileNotFoundError: If ``path`` does not exist.
127 ImportError: If openpyxl is not installed.
129 Example:
130 >>> (
131 ... ExcelFormatter("report.xlsx")
132 ... .column_width("A", 20)
133 ... .font("A1", bold=True, size=14, color="FFFFFF")
134 ... .fill("A1", "4F81BD")
135 ... .save()
136 ... )
137 """
139 # ///////////////////////////////////////////////////////////////
140 # INIT
141 # ///////////////////////////////////////////////////////////////
143 def __init__(self, path: str | Path) -> None:
144 self._path = Path(path).resolve()
145 if not self._path.exists():
146 raise FileNotFoundError(f"ExcelFormatter: file not found: {self._path}")
147 # Buffer of pending operations applied in order at save().
148 self._ops: list[_Operation] = []
150 # ///////////////////////////////////////////////////////////////
151 # PUBLIC METHODS — formatting operations (all return self for chaining)
152 # ///////////////////////////////////////////////////////////////
154 def column_width(self, col: str, width: float) -> ExcelFormatter:
155 """Set the width of a column.
157 Args:
158 col: Column letter (e.g. ``"A"``, ``"BC"``).
159 width: Column width in Excel character units.
161 Returns:
162 ExcelFormatter: ``self`` for method chaining.
164 Example:
165 >>> formatter.column_width("A", 20).column_width("B", 15)
166 """
167 self._ops.append(_ColumnWidthOp(col=col, width=width))
168 return self
170 def row_height(self, row: int, height: float) -> ExcelFormatter:
171 """Set the height of a row.
173 Args:
174 row: 1-based row index.
175 height: Row height in points.
177 Returns:
178 ExcelFormatter: ``self`` for method chaining.
180 Example:
181 >>> formatter.row_height(1, 30)
182 """
183 self._ops.append(_RowHeightOp(row=row, height=height))
184 return self
186 def font(
187 self,
188 ref: str,
189 *,
190 bold: bool | None = None,
191 italic: bool | None = None,
192 size: int | None = None,
193 color: str | None = None,
194 ) -> ExcelFormatter:
195 """Apply font formatting to a cell or range.
197 Every argument left at ``None`` keeps the cell's current value, so
198 properties can be set one call at a time without resetting the others.
200 Args:
201 ref: Cell or range address in A1 notation (e.g. ``"A1"`` or
202 ``"A1:D1"``).
203 bold: Apply (``True``) or remove (``False``) bold weight.
204 ``None`` leaves the weight unchanged.
205 italic: Apply (``True``) or remove (``False``) italic style.
206 ``None`` leaves the style unchanged.
207 size: Font size in points. ``None`` leaves the size unchanged.
208 color: Font colour as a 6-character hex string without ``#``
209 (e.g. ``"FF0000"`` for red). ``None`` leaves colour unchanged.
211 Returns:
212 ExcelFormatter: ``self`` for method chaining.
214 Example:
215 >>> formatter.font("A1", bold=True, size=12, color="FF0000")
216 """
217 self._ops.append(
218 _FontOp(ref=ref, bold=bold, italic=italic, size=size, color=color)
219 )
220 return self
222 def fill(self, ref: str, color: str) -> ExcelFormatter:
223 """Apply a solid background fill to a cell or range.
225 Args:
226 ref: Cell or range address in A1 notation.
227 color: Background colour as a 6-character hex string without
228 ``#`` (e.g. ``"4F81BD"`` for a medium blue).
230 Returns:
231 ExcelFormatter: ``self`` for method chaining.
233 Example:
234 >>> formatter.fill("A1:D1", "4F81BD")
235 """
236 self._ops.append(_FillOp(ref=ref, color=color))
237 return self
239 def border(self, ref: str, style: str = "thin") -> ExcelFormatter:
240 """Apply a border to all edges of a cell or range.
242 Args:
243 ref: Cell or range address in A1 notation.
244 style: Border style name as understood by openpyxl
245 (e.g. ``"thin"``, ``"medium"``, ``"thick"``, ``"dashed"``).
246 Defaults to ``"thin"``.
248 Returns:
249 ExcelFormatter: ``self`` for method chaining.
251 Example:
252 >>> formatter.border("A1:D5", style="thin")
253 """
254 self._ops.append(_BorderOp(ref=ref, style=style))
255 return self
257 def align(
258 self,
259 ref: str,
260 *,
261 horizontal: str | None = None,
262 vertical: str | None = None,
263 wrap: bool = False,
264 ) -> ExcelFormatter:
265 """Apply alignment to a cell or range.
267 Args:
268 ref: Cell or range address in A1 notation.
269 horizontal: Horizontal alignment. Accepted values:
270 ``"left"``, ``"center"``, ``"right"``, ``"fill"``,
271 ``"justify"``, ``"centerContinuous"``, ``"distributed"``.
272 ``None`` leaves the setting unchanged.
273 vertical: Vertical alignment. Accepted values:
274 ``"top"``, ``"center"``, ``"bottom"``, ``"justify"``,
275 ``"distributed"``. ``None`` leaves the setting unchanged.
276 wrap: Enable text wrapping. Defaults to ``False``.
278 Returns:
279 ExcelFormatter: ``self`` for method chaining.
281 Example:
282 >>> formatter.align("A1", horizontal="center", vertical="top", wrap=True)
283 """
284 self._ops.append(
285 _AlignOp(ref=ref, horizontal=horizontal, vertical=vertical, wrap=wrap)
286 )
287 return self
289 def save(self, dest: str | Path | None = None) -> None:
290 """Apply all buffered operations and write the workbook.
292 Args:
293 dest: Destination path. Pass ``None`` to overwrite the source
294 file in place. Parent directories of ``dest`` must exist.
296 Raises:
297 FormatterError: If any openpyxl operation fails.
298 ImportError: If openpyxl is not installed.
300 Example:
301 >>> formatter.save() # overwrite source
302 >>> formatter.save("output/report.xlsx") # write to new path
303 """
304 from ..exceptions import FormatterError
306 logger.debug(
307 "ExcelFormatter.save: applying %d operations to '%s'.",
308 len(self._ops),
309 self._path,
310 )
312 try:
313 # keep_vba defaults to False, which would drop vbaProject.bin and
314 # silently de-macro an .xlsm on save.
315 wb = openpyxl.load_workbook(
316 self._path, keep_vba=self._path.suffix.lower() == ".xlsm"
317 )
318 except Exception as exc:
319 raise FormatterError(
320 f"Failed to open workbook for formatting: {self._path} — {exc}",
321 cause=exc,
322 ) from exc
324 try:
325 ws = wb.active
326 if ws is None:
327 raise FormatterError(
328 f"Workbook '{self._path}' has no active sheet.",
329 cause=None,
330 )
332 for op in self._ops:
333 if isinstance(op, _ColumnWidthOp):
334 ws.column_dimensions[op.col].width = op.width
336 elif isinstance(op, _RowHeightOp):
337 ws.row_dimensions[op.row].height = op.height
339 elif isinstance(op, _FontOp):
340 # Copy the cell's existing font and override only what was
341 # asked for. Building a fresh Font() would reset every
342 # property left unspecified — name, underline, strike —
343 # which contradicts what the public method documents.
344 for cell in _iter_cells(ws, op.ref):
345 font_style = copy(cell.font)
346 if op.bold is not None:
347 font_style.bold = op.bold
348 if op.italic is not None:
349 font_style.italic = op.italic
350 if op.size is not None: 350 ↛ 352line 350 didn't jump to line 352 because the condition on line 350 was always true
351 font_style.size = op.size
352 if op.color is not None:
353 font_style.color = Color(rgb=op.color)
354 cell.font = font_style
356 elif isinstance(op, _FillOp):
357 fill_style = PatternFill(fill_type="solid", fgColor=op.color)
358 for cell in _iter_cells(ws, op.ref):
359 cell.fill = fill_style
361 elif isinstance(op, _BorderOp):
362 side = Side(border_style=cast(Any, op.style))
363 border_style = Border(left=side, right=side, top=side, bottom=side)
364 for cell in _iter_cells(ws, op.ref):
365 cell.border = border_style
367 elif isinstance(op, _AlignOp): 367 ↛ 332line 367 didn't jump to line 332 because the condition on line 367 was always true
368 align_kwargs: dict[str, Any] = {"wrap_text": op.wrap}
369 if op.horizontal is not None: 369 ↛ 371line 369 didn't jump to line 371 because the condition on line 369 was always true
370 align_kwargs["horizontal"] = op.horizontal
371 if op.vertical is not None: 371 ↛ 373line 371 didn't jump to line 373 because the condition on line 371 was always true
372 align_kwargs["vertical"] = op.vertical
373 align_style = Alignment(**align_kwargs)
374 for cell in _iter_cells(ws, op.ref):
375 cell.alignment = align_style
377 except Exception as exc:
378 raise FormatterError(
379 f"Error applying formatting operation to '{self._path}': {exc}",
380 cause=exc,
381 ) from exc
383 out_path = Path(dest).resolve() if dest is not None else self._path
385 # openpyxl opens the destination with ZipFile(path, "w"), which
386 # truncates it before writing a byte: an interrupted in-place save
387 # would leave no original and no usable copy. Write beside the target
388 # and swap atomically instead.
389 tmp_path = out_path.with_name(f"{out_path.name}.ezxl-tmp")
390 try:
391 wb.save(str(tmp_path))
392 os.replace(tmp_path, out_path)
393 except Exception as exc:
394 tmp_path.unlink(missing_ok=True)
395 raise FormatterError(
396 f"Failed to save formatted workbook to '{out_path}': {exc}",
397 cause=exc,
398 ) from exc
400 logger.debug("ExcelFormatter.save: written to '%s'.", out_path)
401 printer.success(
402 f"ExcelFormatter: formatting applied and saved to '{out_path}'."
403 )
406# ///////////////////////////////////////////////////////////////
407# HELPERS
408# ///////////////////////////////////////////////////////////////
411def _iter_cells(ws: Any, ref: str):
412 """Yield individual Cell objects from a worksheet reference.
414 Handles both single-cell references (``"A1"``) and ranges (``"A1:D5"``).
416 Args:
417 ws: An openpyxl Worksheet object.
418 ref: Cell or range address in A1 notation.
420 Yields:
421 openpyxl Cell objects.
422 """
423 cell_range = ws[ref]
424 # openpyxl returns a single Cell for a single address, or a tuple-of-tuples
425 # for a range reference.
426 if hasattr(cell_range, "__iter__") and not hasattr(cell_range, "value"):
427 # Range: iterate rows then cells.
428 for row in cell_range:
429 if hasattr(row, "__iter__") and not hasattr(row, "value"): 429 ↛ 432line 429 didn't jump to line 432 because the condition on line 429 was always true
430 yield from row
431 else:
432 yield row
433 else:
434 yield cell_range