Coverage for src / ezxl / io / _formatters.py: 96.93%
123 statements
« prev ^ index » next coverage.py v7.13.5, created at 2026-04-29 15:53 +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
22from dataclasses import dataclass
23from pathlib import Path
24from typing import Any, cast
26# Third-party imports
27import openpyxl
28from ezplog.lib_mode import get_logger, get_printer
29from openpyxl.styles import (
30 Alignment,
31 Border,
32 Font,
33 PatternFill,
34 Side,
35)
37# ///////////////////////////////////////////////////////////////
38# CONSTANTS
39# ///////////////////////////////////////////////////////////////
41logger = get_logger(__name__)
42printer = get_printer()
44# ///////////////////////////////////////////////////////////////
45# INTERNAL DATA STRUCTURES
46# ///////////////////////////////////////////////////////////////
47# Each operation is stored as a dataclass so that save() can apply them
48# in insertion order without repeatedly touching the openpyxl workbook.
51@dataclass
52class _ColumnWidthOp:
53 col: str
54 width: float
57@dataclass
58class _RowHeightOp:
59 row: int
60 height: float
63@dataclass
64class _FontOp:
65 ref: str
66 bold: bool
67 italic: bool
68 size: int | None
69 color: str | None # hex without '#'
72@dataclass
73class _FillOp:
74 ref: str
75 color: str # hex without '#'
78@dataclass
79class _BorderOp:
80 ref: str
81 style: str
84@dataclass
85class _AlignOp:
86 ref: str
87 horizontal: str | None
88 vertical: str | None
89 wrap: bool
92_Operation = _ColumnWidthOp | _RowHeightOp | _FontOp | _FillOp | _BorderOp | _AlignOp
94# ///////////////////////////////////////////////////////////////
95# CLASSES
96# ///////////////////////////////////////////////////////////////
99class ExcelFormatter:
100 """Fluent formatter for closed Excel workbook files.
102 All formatting operations are buffered and applied in a single write
103 pass when ``save()`` is called. The workbook is opened with openpyxl
104 only at save time, minimising I/O overhead.
106 The API is intentionally flat: no sheet selector is exposed here. The
107 formatter operates on the **active sheet** of the workbook. Consumer
108 libraries that need multi-sheet formatting should instantiate one
109 ``ExcelFormatter`` per sheet operation.
111 Args:
112 path: Path to an existing ``.xlsx`` workbook file.
114 Raises:
115 FileNotFoundError: If ``path`` does not exist.
116 ImportError: If openpyxl is not installed.
118 Example:
119 >>> (
120 ... ExcelFormatter("report.xlsx")
121 ... .column_width("A", 20)
122 ... .font("A1", bold=True, size=14, color="FFFFFF")
123 ... .fill("A1", "4F81BD")
124 ... .save()
125 ... )
126 """
128 # ///////////////////////////////////////////////////////////////
129 # INIT
130 # ///////////////////////////////////////////////////////////////
132 def __init__(self, path: str | Path) -> None:
133 self._path = Path(path).resolve()
134 if not self._path.exists():
135 raise FileNotFoundError(f"ExcelFormatter: file not found: {self._path}")
136 # Buffer of pending operations applied in order at save().
137 self._ops: list[_Operation] = []
139 # ///////////////////////////////////////////////////////////////
140 # PUBLIC METHODS — formatting operations (all return self for chaining)
141 # ///////////////////////////////////////////////////////////////
143 def column_width(self, col: str, width: float) -> ExcelFormatter:
144 """Set the width of a column.
146 Args:
147 col: Column letter (e.g. ``"A"``, ``"BC"``).
148 width: Column width in Excel character units.
150 Returns:
151 ExcelFormatter: ``self`` for method chaining.
153 Example:
154 >>> formatter.column_width("A", 20).column_width("B", 15)
155 """
156 self._ops.append(_ColumnWidthOp(col=col, width=width))
157 return self
159 def row_height(self, row: int, height: float) -> ExcelFormatter:
160 """Set the height of a row.
162 Args:
163 row: 1-based row index.
164 height: Row height in points.
166 Returns:
167 ExcelFormatter: ``self`` for method chaining.
169 Example:
170 >>> formatter.row_height(1, 30)
171 """
172 self._ops.append(_RowHeightOp(row=row, height=height))
173 return self
175 def font(
176 self,
177 ref: str,
178 *,
179 bold: bool = False,
180 italic: bool = False,
181 size: int | None = None,
182 color: str | None = None,
183 ) -> ExcelFormatter:
184 """Apply font formatting to a cell or range.
186 Args:
187 ref: Cell or range address in A1 notation (e.g. ``"A1"`` or
188 ``"A1:D1"``).
189 bold: Apply bold weight. Defaults to ``False``.
190 italic: Apply italic style. Defaults to ``False``.
191 size: Font size in points. ``None`` leaves the size unchanged.
192 color: Font colour as a 6-character hex string without ``#``
193 (e.g. ``"FF0000"`` for red). ``None`` leaves colour unchanged.
195 Returns:
196 ExcelFormatter: ``self`` for method chaining.
198 Example:
199 >>> formatter.font("A1", bold=True, size=12, color="FF0000")
200 """
201 self._ops.append(
202 _FontOp(ref=ref, bold=bold, italic=italic, size=size, color=color)
203 )
204 return self
206 def fill(self, ref: str, color: str) -> ExcelFormatter:
207 """Apply a solid background fill to a cell or range.
209 Args:
210 ref: Cell or range address in A1 notation.
211 color: Background colour as a 6-character hex string without
212 ``#`` (e.g. ``"4F81BD"`` for a medium blue).
214 Returns:
215 ExcelFormatter: ``self`` for method chaining.
217 Example:
218 >>> formatter.fill("A1:D1", "4F81BD")
219 """
220 self._ops.append(_FillOp(ref=ref, color=color))
221 return self
223 def border(self, ref: str, style: str = "thin") -> ExcelFormatter:
224 """Apply a border to all edges of a cell or range.
226 Args:
227 ref: Cell or range address in A1 notation.
228 style: Border style name as understood by openpyxl
229 (e.g. ``"thin"``, ``"medium"``, ``"thick"``, ``"dashed"``).
230 Defaults to ``"thin"``.
232 Returns:
233 ExcelFormatter: ``self`` for method chaining.
235 Example:
236 >>> formatter.border("A1:D5", style="thin")
237 """
238 self._ops.append(_BorderOp(ref=ref, style=style))
239 return self
241 def align(
242 self,
243 ref: str,
244 *,
245 horizontal: str | None = None,
246 vertical: str | None = None,
247 wrap: bool = False,
248 ) -> ExcelFormatter:
249 """Apply alignment to a cell or range.
251 Args:
252 ref: Cell or range address in A1 notation.
253 horizontal: Horizontal alignment. Accepted values:
254 ``"left"``, ``"center"``, ``"right"``, ``"fill"``,
255 ``"justify"``, ``"centerContinuous"``, ``"distributed"``.
256 ``None`` leaves the setting unchanged.
257 vertical: Vertical alignment. Accepted values:
258 ``"top"``, ``"center"``, ``"bottom"``, ``"justify"``,
259 ``"distributed"``. ``None`` leaves the setting unchanged.
260 wrap: Enable text wrapping. Defaults to ``False``.
262 Returns:
263 ExcelFormatter: ``self`` for method chaining.
265 Example:
266 >>> formatter.align("A1", horizontal="center", vertical="top", wrap=True)
267 """
268 self._ops.append(
269 _AlignOp(ref=ref, horizontal=horizontal, vertical=vertical, wrap=wrap)
270 )
271 return self
273 def save(self, dest: str | Path | None = None) -> None:
274 """Apply all buffered operations and write the workbook.
276 Args:
277 dest: Destination path. Pass ``None`` to overwrite the source
278 file in place. Parent directories of ``dest`` must exist.
280 Raises:
281 FormatterError: If any openpyxl operation fails.
282 ImportError: If openpyxl is not installed.
284 Example:
285 >>> formatter.save() # overwrite source
286 >>> formatter.save("output/report.xlsx") # write to new path
287 """
288 from ..exceptions import FormatterError
290 logger.debug(
291 "ExcelFormatter.save: applying %d operations to '%s'.",
292 len(self._ops),
293 self._path,
294 )
296 try:
297 wb = openpyxl.load_workbook(self._path)
298 except Exception as exc:
299 raise FormatterError(
300 f"Failed to open workbook for formatting: {self._path} — {exc}",
301 cause=exc,
302 ) from exc
304 try:
305 ws = wb.active
306 if ws is None:
307 raise FormatterError(
308 f"Workbook '{self._path}' has no active sheet.",
309 cause=None,
310 )
312 for op in self._ops:
313 if isinstance(op, _ColumnWidthOp):
314 ws.column_dimensions[op.col].width = op.width
316 elif isinstance(op, _RowHeightOp):
317 ws.row_dimensions[op.row].height = op.height
319 elif isinstance(op, _FontOp):
320 font_kwargs: dict[str, Any] = {
321 "bold": op.bold,
322 "italic": op.italic,
323 }
324 if op.size is not None:
325 font_kwargs["size"] = op.size
326 if op.color is not None:
327 font_kwargs["color"] = op.color
328 font_style = Font(**font_kwargs)
329 for cell in _iter_cells(ws, op.ref):
330 cell.font = font_style
332 elif isinstance(op, _FillOp):
333 fill_style = PatternFill(fill_type="solid", fgColor=op.color)
334 for cell in _iter_cells(ws, op.ref):
335 cell.fill = fill_style
337 elif isinstance(op, _BorderOp):
338 side = Side(border_style=cast(Any, op.style))
339 border_style = Border(left=side, right=side, top=side, bottom=side)
340 for cell in _iter_cells(ws, op.ref):
341 cell.border = border_style
343 elif isinstance(op, _AlignOp): 343 ↛ 312line 343 didn't jump to line 312 because the condition on line 343 was always true
344 align_kwargs: dict[str, Any] = {"wrap_text": op.wrap}
345 if op.horizontal is not None: 345 ↛ 347line 345 didn't jump to line 347 because the condition on line 345 was always true
346 align_kwargs["horizontal"] = op.horizontal
347 if op.vertical is not None: 347 ↛ 349line 347 didn't jump to line 349 because the condition on line 347 was always true
348 align_kwargs["vertical"] = op.vertical
349 align_style = Alignment(**align_kwargs)
350 for cell in _iter_cells(ws, op.ref):
351 cell.alignment = align_style
353 except Exception as exc:
354 raise FormatterError(
355 f"Error applying formatting operation to '{self._path}': {exc}",
356 cause=exc,
357 ) from exc
359 out_path = Path(dest).resolve() if dest is not None else self._path
361 try:
362 wb.save(str(out_path))
363 except Exception as exc:
364 raise FormatterError(
365 f"Failed to save formatted workbook to '{out_path}': {exc}",
366 cause=exc,
367 ) from exc
369 logger.debug("ExcelFormatter.save: written to '%s'.", out_path)
370 printer.success(
371 f"ExcelFormatter: formatting applied and saved to '{out_path}'."
372 )
375# ///////////////////////////////////////////////////////////////
376# HELPERS
377# ///////////////////////////////////////////////////////////////
380def _iter_cells(ws: Any, ref: str):
381 """Yield individual Cell objects from a worksheet reference.
383 Handles both single-cell references (``"A1"``) and ranges (``"A1:D5"``).
385 Args:
386 ws: An openpyxl Worksheet object.
387 ref: Cell or range address in A1 notation.
389 Yields:
390 openpyxl Cell objects.
391 """
392 cell_range = ws[ref]
393 # openpyxl returns a single Cell for a single address, or a tuple-of-tuples
394 # for a range reference.
395 if hasattr(cell_range, "__iter__") and not hasattr(cell_range, "value"):
396 # Range: iterate rows then cells.
397 for row in cell_range:
398 if hasattr(row, "__iter__") and not hasattr(row, "value"): 398 ↛ 401line 398 didn't jump to line 401 because the condition on line 398 was always true
399 yield from row
400 else:
401 yield row
402 else:
403 yield cell_range