certflow.handlers.excel_styler 源代码
"""Excel 样式处理器模块
提供单元格颜色、字体、批注等格式信息的读取功能。
"""
from __future__ import annotations
from collections.abc import Iterator
from contextlib import contextmanager
from pathlib import Path
from typing import Any
import pandas as pd
from loguru import logger
from openpyxl import Workbook, load_workbook
from openpyxl.cell.cell import Cell
from openpyxl.styles import Border
[文档]
class ExcelStyler:
"""Excel 样式处理器
负责读取 Excel 单元格的格式信息：
- 背景色
- 字体色
- 批注内容
- 其他样式属性
"""
# 默认颜色映射（当配置不存在时使用）
_DEFAULT_FONT_COLORS = {
"FF0000": "已开票",
"00B050": "已发货",
"0070C0": "已完成",
"FFC000": "待确认",
}
_DEFAULT_BG_COLORS = {
"FFC000": "已取消订单",
"92D050": "已发货",
"FF0000": "紧急订单",
"FFFF00": "待审核",
}
@classmethod
def _get_font_color_map(cls) -> dict[str, str]:
"""获取字体颜色映射（支持配置覆盖）"""
try:
from certflow.config.settings import FONT_COLOR_STATUS_MAP
if FONT_COLOR_STATUS_MAP:
return FONT_COLOR_STATUS_MAP
except ImportError:
pass
return cls._DEFAULT_FONT_COLORS
@classmethod
def _get_bg_color_map(cls) -> dict[str, str]:
"""获取背景颜色映射（支持配置覆盖）"""
try:
from certflow.config.settings import BG_COLOR_STATUS_MAP
if BG_COLOR_STATUS_MAP:
return BG_COLOR_STATUS_MAP
except ImportError:
pass
return cls._DEFAULT_BG_COLORS
[文档]
@classmethod
def get_status_from_color(cls, color: str, color_type: str = "bg") -> str | None:
"""根据颜色获取对应的业务状态（配置驱动）
Args:
color: 原始颜色值（如 "FF0000"、"00B050" 或 "THEME_1"），可为空
color_type: 颜色类型，"font" 表示字体色，"bg" 表示背景色
Returns:
str | None: 对应的业务状态文本（如 "已开票"、"已发货"），无匹配时返回 None
"""
if not color:
return None
# 标准化颜色值
color_upper = color.upper()
if color_upper.startswith("FF"):
color_upper = color_upper[2:]
# 根据类型获取映射
color_map = cls._get_font_color_map() if color_type == "font" else cls._get_bg_color_map()
return color_map.get(color_upper)
[文档]
@staticmethod
@contextmanager
def open_workbook(file_path: Path, data_only: bool = True) -> Iterator[Workbook]:
"""打开工作簿的上下文管理器
使用上下文管理器自动管理Excel工作簿的打开和关闭，
确保资源正确释放。
Args:
file_path: Excel文件路径
data_only: 是否只读取数据值(不读取公式)，默认为True
Yields:
Workbook: openpyxl工作簿对象
Examples:
>>> with ExcelStyler.open_workbook(Path("data.xlsx")) as wb:
... ws = wb.active
... cell = ws["A1"]
... print(cell.value)
"""
wb = load_workbook(file_path, data_only=data_only)
try:
yield wb
finally:
wb.close()
logger.debug(f"关闭工作簿: {file_path}")
@staticmethod
def _get_bg_color(cell: Cell) -> str | None:
"""获取单元格背景色（无填充时返回默认白色）
注意：openpyxl 的 ``cell.fill`` 返回的是 ``StyleProxy`` 包装对象，
而非直接的 ``PatternFill``，因此不能用 ``isinstance(fill, PatternFill)``
判定——该判断恒为 False，会导致所有背景色被误判为“无填充”而返回白色。
这里改为直接依据 ``patternType`` 判断是否真的有填充。
"""
fill = cell.fill
if fill is None or getattr(fill, "patternType", None) is None:
return "FFFFFF" # 无填充时默认为白色
color = fill.fgColor
if color.type == "rgb" and color.rgb:
rgb_str = str(color.rgb)
return rgb_str[2:] if len(rgb_str) > 6 else rgb_str
if color.type == "indexed":
return f"INDEXED_{color.index}"
if color.type == "theme":
return f"THEME_{color.theme}"
return "FFFFFF" # 无法识别时也默认为白色
@staticmethod
def _get_font_color(cell: Cell) -> str | None:
"""获取单元格字体色（无显式颜色时返回默认黑色）"""
font = cell.font
if not (font and font.color):
return "000000" # 无颜色信息时默认为黑色
color = font.color
if color.type == "rgb" and color.rgb:
rgb_str = str(color.rgb)
return rgb_str[2:] if len(rgb_str) > 6 else rgb_str
if color.type == "theme":
return f"THEME_{color.theme}"
# indexed 等其他类型（通常为默认颜色）也返回黑色
return "000000"
[文档]
@staticmethod
def get_cell_color(cell: Cell, color_type: str = "bg") -> str | None:
"""获取单元格颜色
支持获取背景色和字体色，返回标准化的颜色值。
"""
try:
if color_type == "bg":
return ExcelStyler._get_bg_color(cell)
if color_type == "font":
return ExcelStyler._get_font_color(cell)
return None
except Exception as e:
logger.debug(f"获取单元格颜色失败: {e}")
return None
[文档]
@staticmethod
def get_cell_comment(cell: Cell) -> str | None:
"""获取单元格批注内容
Args:
cell: openpyxl单元格对象
Returns:
Optional[str]: 批注文本内容，没有批注时返回None
Examples:
>>> with ExcelStyler.open_workbook(Path("data.xlsx")) as wb:
... ws = wb.active
... comment = ExcelStyler.get_cell_comment(ws["A1"])
... if comment:
... print(f"批注内容: {comment}")
"""
if cell.comment:
return cell.comment.text
return None
[文档]
@staticmethod
def get_cell_style_info(cell: Cell) -> dict[str, Any]:
"""获取单元格完整样式信息
一次性获取单元格的值、背景色、字体色、状态、批注和字体属性。
Args:
cell: openpyxl单元格对象
Returns:
Dict[str, Any]: 包含以下键的字典:
- value: 单元格值
- bg_color: 背景色
- font_color: 字体色
- status: 根据颜色推断的业务状态
- comment: 批注内容
- font_name: 字体名称
- font_size: 字体大小
- bold: 是否粗体
- italic: 是否斜体
Examples:
>>> with ExcelStyler.open_workbook(Path("data.xlsx")) as wb:
... ws = wb.active
... info = ExcelStyler.get_cell_style_info(ws["A1"])
... print(f"值: {info['value']}, 状态: {info['status']}")
"""
bg_color = ExcelStyler.get_cell_color(cell, "bg")
font_color = ExcelStyler.get_cell_color(cell, "font")
return {
"value": cell.value,
"bg_color": bg_color,
"font_color": font_color,
"status": ExcelStyler.get_status_from_color(bg_color, "bg")
or ExcelStyler.get_status_from_color(font_color, "font"),
"comment": ExcelStyler.get_cell_comment(cell),
"font_name": cell.font.name if cell.font else None,
"font_size": cell.font.size if cell.font else None,
"bold": cell.font.bold if cell.font else None,
"italic": cell.font.italic if cell.font else None,
}
[文档]
@staticmethod
def read_with_styles(
file_path: Path,
sheet_name: str | int = 0,
include_styles: bool = True,
include_comments: bool = True,
) -> tuple[
pd.DataFrame,
pd.DataFrame | None,
pd.DataFrame | None,
pd.DataFrame | None,
pd.DataFrame | None,
]:
"""读取 Excel 并返回数据和样式信息
同时读取单元格的值和样式信息，返回多个DataFrame。
Args:
file_path: Excel文件路径
sheet_name: 工作表名称或索引，默认为0(第一个工作表)
include_styles: 是否包含样式信息(背景色、字体色)，默认为True
include_comments: 是否包含批注信息，默认为True
Returns:
Tuple: 包含5个元素的元组:
- df_data: 数据值DataFrame
- bg_colors: 背景色DataFrame(可选)
- font_colors: 字体色DataFrame(可选)
- comments: 批注DataFrame(可选)
- statuses: 状态DataFrame(可选)
Examples:
>>> data, bg, font, comments, status = ExcelStyler.read_with_styles(
... Path("data.xlsx"),
... sheet_name="Sheet1"
... )
>>> print(f"数据形状: {data.shape}")
>>> print(f"状态统计: {status.iloc[0, 0]}")
"""
# 1. 用 pandas 读取数据值
df_data = pd.read_excel(file_path, sheet_name=sheet_name, header=None, dtype=str)
# 用 pandas 的列数作为实际列数（pandas 会自动截掉尾部全空列）
# 避免 openpyxl ws.max_column 巨大导致遍历数万空列
actual_cols = df_data.shape[1]
# 2. 用 openpyxl 读取样式
with ExcelStyler.open_workbook(file_path) as wb:
# 处理工作表名称
ws = wb.worksheets[sheet_name] if isinstance(sheet_name, int) else wb[sheet_name]
bg_colors = None
font_colors = None
comments = None
statuses = None
if include_styles or include_comments:
bg_data = []
font_data = []
comment_data = []
status_data = []
# 只遍历实际有数据的列范围（min_col=1, max_col=actual_cols）
for _row_idx, row in enumerate(ws.iter_rows(min_col=1, max_col=actual_cols), 1):
bg_row = []
font_row = []
comment_row = []
status_row = []
for _col_idx, cell in enumerate(row, 1):
if include_styles:
bg_color = ExcelStyler.get_cell_color(cell, "bg")
font_color = ExcelStyler.get_cell_color(cell, "font")
bg_row.append(bg_color)
font_row.append(font_color)
# 获取状态
status = ExcelStyler.get_status_from_color(
bg_color, "bg"
) or ExcelStyler.get_status_from_color(font_color, "font")
status_row.append(status)
if include_comments:
comment_row.append(ExcelStyler.get_cell_comment(cell))
if include_styles:
bg_data.append(bg_row)
font_data.append(font_row)
status_data.append(status_row)
if include_comments:
comment_data.append(comment_row)
if include_styles:
bg_colors = pd.DataFrame(bg_data)
font_colors = pd.DataFrame(font_data)
statuses = pd.DataFrame(status_data)
if include_comments:
comments = pd.DataFrame(comment_data)
return df_data, bg_colors, font_colors, comments, statuses
[文档]
@staticmethod
def get_comments_summary(file_path: Path) -> pd.DataFrame:
"""获取批注汇总表
扫描整个工作簿，返回所有批注的结构化摘要。
Args:
file_path: Excel文件路径
Returns:
pd.DataFrame: 包含以下列的DataFrame:
- 工作表: 批注所在工作表名称
- 单元格: 批注所在单元格坐标(如"A1")
- 批注内容: 批注的文本内容
Examples:
>>> summary = ExcelStyler.get_comments_summary(Path("data.xlsx"))
>>> print(summary)
>>> # 输出示例:
>>> # 工作表 单元格 批注内容
>>> # 0 Sheet1 A1 请注意此单元格
>>> # 1 Sheet1 B5 需要审核
"""
all_comments = ExcelStyler.get_all_comments(file_path)
rows = []
for sheet, cells in all_comments.items():
for cell, comment in cells.items():
rows.append({"工作表": sheet, "单元格": cell, "批注内容": comment})
return pd.DataFrame(rows)
[文档]
@staticmethod
def get_all_comments(file_path: Path) -> dict[str, dict[str, str]]:
"""获取整个工作簿的所有批注
遍历所有工作表和单元格，收集所有批注信息。
Args:
file_path: Excel文件路径
Returns:
Dict[str, Dict[str, str]]: 嵌套字典结构
外层键为工作表名称，内层键为单元格坐标，值为批注内容
Examples:
>>> all_comments = ExcelStyler.get_all_comments(Path("data.xlsx"))
>>> for sheet, cells in all_comments.items():
... print(f"工作表: {sheet}")
... for cell, comment in cells.items():
... print(f" {cell}: {comment}")
"""
with ExcelStyler.open_workbook(file_path) as wb:
result = {}
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
# 探测实际列数：从第1行找到最后一个非空单元格
actual_cols = 1
for cell in ws[1]:
if cell.value is not None:
actual_cols = max(actual_cols, cell.column)
sheet_comments = {}
for row in ws.iter_rows(min_col=1, max_col=actual_cols):
for cell in row:
if cell.comment:
sheet_comments[cell.coordinate] = cell.comment.text
if sheet_comments:
result[sheet_name] = sheet_comments
return result
# ============================================================
# 批注便捷方法
# ============================================================
[文档]
@staticmethod
def get_comment_at(file_path: Path, sheet_name: str | int, row: int, col: int) -> str | None:
"""获取指定单元格的批注内容
Args:
file_path: Excel 文件路径
sheet_name: 工作表名称或索引
row: 行号（1 基）
col: 列号（1 基）
Returns:
Optional[str]: 批注文本，无批注时返回 None
"""
with ExcelStyler.open_workbook(file_path) as wb:
ws = wb.worksheets[sheet_name] if isinstance(sheet_name, int) else wb[sheet_name]
cell = ws.cell(row=row, column=col)
return ExcelStyler.get_cell_comment(cell)
[文档]
@staticmethod
def get_comments_in_column(
file_path: Path, sheet_name: str | int, column: int
) -> dict[int, str]:
"""获取指定列的所有批注
Args:
file_path: Excel 文件路径
sheet_name: 工作表名称或索引
column: 列号（1 基）
Returns:
Dict[int, str]: 键为行号（1 基），值为批注文本；
工作表不存在时返回空字典。
"""
try:
with ExcelStyler.open_workbook(file_path) as wb:
ws = wb.worksheets[sheet_name] if isinstance(sheet_name, int) else wb[sheet_name]
except (KeyError, ValueError):
return {}
result: dict[int, str] = {}
for row in ws.iter_rows(min_col=column, max_col=column):
cell = row[0]
comment = ExcelStyler.get_cell_comment(cell)
if comment:
result[cell.row] = comment
return result
[文档]
@staticmethod
def find_comments_by_keyword(file_path: Path, keyword: str) -> pd.DataFrame:
"""按关键词搜索批注
Args:
file_path: Excel 文件路径
keyword: 搜索关键词
Returns:
pd.DataFrame: 包含 工作表/单元格/批注内容 三列，仅保留
批注内容中包含关键词的行。
"""
summary = ExcelStyler.get_comments_summary(file_path)
if summary.empty:
return summary
mask = summary["批注内容"].astype(str).str.contains(keyword, na=False)
return summary[mask].reset_index(drop=True)
[文档]
@staticmethod
def read_with_comments_merged(
file_path: Path,
sheet_name: str | int = 0,
comment_column: int | None = None,
) -> pd.DataFrame:
"""读取数据并将指定列的批注合并为新列
Args:
file_path: Excel 文件路径
sheet_name: 工作表名称或索引，默认为 0
comment_column: 批注所在列号（1 基）；为 None 时汇总所有批注
Returns:
pd.DataFrame: 数据 DataFrame，额外包含 `comment` 列
"""
df = pd.read_excel(file_path, sheet_name=sheet_name, header=None, dtype=str)
comments_by_row: dict[int, str] = {}
with ExcelStyler.open_workbook(file_path) as wb:
ws = wb.worksheets[sheet_name] if isinstance(sheet_name, int) else wb[sheet_name]
if comment_column is not None:
for row in ws.iter_rows(min_col=comment_column, max_col=comment_column):
cell = row[0]
comment = ExcelStyler.get_cell_comment(cell)
if comment:
# Excel 行号从 1 开始；pandas 行号从 0 开始，且第 1 行未被跳过
comments_by_row[cell.row - 1] = comment
else:
for row in ws.iter_rows():
for cell in row:
comment = ExcelStyler.get_cell_comment(cell)
if comment:
comments_by_row.setdefault(cell.row - 1, comment)
df["comment"] = [comments_by_row.get(i) for i in range(len(df))]
return df
# ============================================================
# 边框 / 超链接方法
# ============================================================
[文档]
@staticmethod
def get_cell_border(cell: Cell) -> dict[str, Any] | None:
"""获取单元格四边边框信息
Args:
cell: openpyxl 单元格对象
Returns:
Optional[Dict]: 包含 top/bottom/left/right 的字典，每条边为
{"style": ..., "color": ...}；无任何边框时返回 None
"""
border = cell.border
sides = {
"top": border.top,
"bottom": border.bottom,
"left": border.left,
"right": border.right,
}
has_border = any(s and s.style for s in sides.values())
if not has_border:
return None
result: dict[str, Any] = {}
for name, side in sides.items():
if side and side.style:
color = side.color
color_value = None
if color is not None:
color_value = color.rgb if color.type == "rgb" else str(color.index)
result[name] = {"style": side.style, "color": color_value}
else:
result[name] = None
return result
[文档]
@staticmethod
def set_cell_border(cell: Cell, style: str = "thin", color: str = "000000") -> None:
"""为单元格四边设置统一边框
Args:
cell: openpyxl 单元格对象
style: 边框样式（如 thin/medium/thick）
color: 边框颜色（ARGB 或 RGB 十六进制）
"""
from openpyxl.styles import Side
side = Side(style=style, color=color)
cell.border = Border(left=side, right=side, top=side, bottom=side)
[文档]
@staticmethod
def add_border_to_range(
ws: Any,
min_row: int,
min_col: int,
max_row: int,
max_col: int,
style: str = "thin",
color: str = "000000",
) -> None:
"""为矩形区域的所有单元格添加统一边框
Args:
ws: openpyxl 工作表对象
min_row: 起始行（1 基）
min_col: 起始列（1 基）
max_row: 结束行（1 基）
max_col: 结束列（1 基）
style: 边框样式
color: 边框颜色
"""
from openpyxl.styles import Side
side = Side(style=style, color=color)
for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
for cell in row:
cell.border = Border(left=side, right=side, top=side, bottom=side)
[文档]
@staticmethod
def get_range_borders(
ws: Any,
min_row: int,
min_col: int,
max_row: int,
max_col: int,
) -> dict[str, dict[str, Any]]:
"""获取矩形区域内所有单元格的边框信息
Args:
ws: openpyxl 工作表对象
min_row: 起始行（1 基）
min_col: 起始列（1 基）
max_row: 结束行（1 基）
max_col: 结束列（1 基）
Returns:
Dict[str, Dict]: 键为单元格坐标（如 "A1"），值为
get_cell_border 返回的四边边框信息（含 None 边）。
"""
result: dict[str, dict[str, Any]] = {}
for row in ws.iter_rows(min_row=min_row, max_row=max_row, min_col=min_col, max_col=max_col):
for cell in row:
result[cell.coordinate] = ExcelStyler.get_cell_border(cell) or {
"top": None,
"bottom": None,
"left": None,
"right": None,
}
return result
[文档]
@staticmethod
def get_cell_hyperlink(cell: Cell) -> str | None:
"""获取单元格超链接目标地址
Args:
cell: openpyxl 单元格对象
Returns:
Optional[str]: 超链接地址，无超链接时返回 None
"""
if cell.hyperlink and cell.hyperlink.target:
return cell.hyperlink.target
return None
# ============================================================
# 样式 DataFrame
# ============================================================
[文档]
@staticmethod
def get_style_dataframe(
file_path: Path,
sheet_name: str | int = 0,
) -> pd.DataFrame:
"""读取并返回单元格样式 DataFrame
逐行读取背景色、字体色与批注，组成与数据等形的 DataFrame。
Args:
file_path: Excel 文件路径
sheet_name: 工作表名称或索引，默认为 0
Returns:
pd.DataFrame: 包含 bg_color / font_color / comment 等列的样式表
"""
with ExcelStyler.open_workbook(file_path) as wb:
ws = wb.worksheets[sheet_name] if isinstance(sheet_name, int) else wb[sheet_name]
rows = []
for row in ws.iter_rows():
bg_row = []
font_row = []
comment_row = []
for cell in row:
bg_row.append(ExcelStyler.get_cell_color(cell, "bg"))
font_row.append(ExcelStyler.get_cell_color(cell, "font"))
comment_row.append(ExcelStyler.get_cell_comment(cell))
rows.append(
{
"bg_color": bg_row,
"font_color": font_row,
"comment": comment_row,
}
)
return pd.DataFrame(rows)