import pandas as pd
import os
import json
from typing import List, Dict, Union, Any, Optional, Iterable, Tuple
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.styles.borders import Border, Side
from openpyxl.utils import column_index_from_string
from openpyxl.formatting.rule import ColorScaleRule, CellIsRule, FormulaRule
class ExcelManager:
@staticmethod
def _ensure_parent_dir(filepath: str) -> None:
os.makedirs(os.path.dirname(os.path.abspath(filepath)), exist_ok=True)
@staticmethod
def _normalize_color(color: Optional[str]) -> Optional[str]:
if color is None:
return None
c = str(color).strip()
if not c:
return None
if c.startswith("#"):
c = c[1:]
c = c.upper()
# Accept RGB (6) or ARGB (8). For RGB, add FF alpha.
if len(c) == 6:
return "FF" + c
if len(c) == 8:
return c
raise ValueError("Color must be hex RGB(6) or ARGB(8), e.g. '#RRGGBB' or 'FFRRGGBB'.")
@staticmethod
def _resolve_sheets(wb, sheet_names: Optional[List[str]]):
if sheet_names is None:
return [wb[s] for s in wb.sheetnames]
if isinstance(sheet_names, list):
missing = [s for s in sheet_names if s not in wb.sheetnames]
if missing:
raise ValueError(f"Sheet(s) not found: {missing}")
return [wb[s] for s in sheet_names]
raise ValueError("sheet_names must be a list of sheet names or null.")
@staticmethod
def _iter_target_cells(ws, targets: List[str]):
if not targets:
raise ValueError("targets must be a non-empty list.")
for t in targets:
if ":" in t:
for row in ws[t]:
for cell in row:
yield cell
else:
yield ws[t]
@staticmethod
def _col_to_index(col: str) -> int:
c = str(col).strip()
if not c:
raise ValueError("Column cannot be empty")
if c.isdigit():
idx = int(c)
if idx <= 0:
raise ValueError("Column index must be >= 1")
return idx
return column_index_from_string(c.upper())
@staticmethod
def get_sheet_names(filepath: str) -> List[str]:
"""Get the list of worksheet names from an Excel file."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
# Use openpyxl engine specifically as it's in requirements
xl = pd.ExcelFile(filepath, engine='openpyxl')
return xl.sheet_names
@staticmethod
def read_sheet(filepath: str, sheet_name: str = None) -> Union[Dict[str, Any], List[Dict[str, Any]]]:
"""
Read data from a specific sheet or all sheets.
If sheet_name is provided, returns list of records.
If sheet_name is None, returns dict {sheet_name: records}.
"""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if sheet_name:
df = pd.read_excel(filepath, sheet_name=sheet_name, engine='openpyxl')
# Convert NaNs to None for valid JSON serialization
df = df.where(pd.notnull(df), None)
return df.to_dict(orient='records')
else:
# Read all sheets
dfs = pd.read_excel(filepath, sheet_name=None, engine='openpyxl')
result = {}
for name, df in dfs.items():
df = df.where(pd.notnull(df), None)
result[name] = df.to_dict(orient='records')
return result
@staticmethod
def create_file(filepath: str, data: List[Dict[str, Any]], sheet_name: str = "Sheet1"):
"""Create a new Excel file with initial data."""
df = pd.DataFrame(data)
ExcelManager._ensure_parent_dir(filepath)
with pd.ExcelWriter(filepath, engine='openpyxl', mode='w') as writer:
df.to_excel(writer, sheet_name=sheet_name, index=False)
@staticmethod
def add_sheet(filepath: str, data: List[Dict[str, Any]], sheet_name: str):
"""Add a worksheet to an existing Excel file."""
if not os.path.exists(filepath):
# Fallback to create if not exists
ExcelManager.create_file(filepath, data, sheet_name)
return
df = pd.DataFrame(data)
with pd.ExcelWriter(filepath, engine='openpyxl', mode='a', if_sheet_exists='replace') as writer:
df.to_excel(writer, sheet_name=sheet_name, index=False)
@staticmethod
def analyze_structure(filepath: str) -> Dict[str, Any]:
"""Analyze the structure of an Excel file (columns, types, row counts)."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
xl = pd.ExcelFile(filepath, engine='openpyxl')
analysis = {
"filename": os.path.basename(filepath),
"total_sheets": len(xl.sheet_names),
"sheets": {}
}
for sheet in xl.sheet_names:
df = pd.read_excel(filepath, sheet_name=sheet, engine='openpyxl')
sheet_info = {
"row_count": len(df),
"column_count": len(df.columns),
"columns": []
}
for col in df.columns:
col_info = {
"name": str(col),
"type": str(df[col].dtype),
"non_null_count": int(df[col].count()),
"null_count": int(df[col].isnull().sum())
}
sheet_info["columns"].append(col_info)
analysis["sheets"][sheet] = sheet_info
return analysis
@staticmethod
def export_structure(filepath: str, output_path: str):
"""Analyze structure and save to a JSON file."""
structure = ExcelManager.analyze_structure(filepath)
ExcelManager._ensure_parent_dir(output_path)
with open(output_path, 'w', encoding='utf-8') as f:
json.dump(structure, f, indent=2, ensure_ascii=False)
return structure
@staticmethod
def set_dimensions(
filepath: str,
sheet_names: Optional[List[str]] = None,
column_widths: Optional[Dict[str, float]] = None,
row_heights: Optional[Dict[Union[str, int], float]] = None,
) -> str:
"""Set column widths and/or row heights. If sheet_names is None, applies to all sheets."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
wb = load_workbook(filepath)
try:
wss = ExcelManager._resolve_sheets(wb, sheet_names)
for ws in wss:
if column_widths:
for col, width in column_widths.items():
if width is None:
continue
idx = ExcelManager._col_to_index(col)
letter = ws.cell(row=1, column=idx).column_letter
ws.column_dimensions[letter].width = float(width)
if row_heights:
for row, height in row_heights.items():
if height is None:
continue
r = int(row)
if r <= 0:
raise ValueError("Row index must be >= 1")
ws.row_dimensions[r].height = float(height)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def merge_cells(
filepath: str,
sheet_names: Optional[List[str]] = None,
ranges: Optional[List[str]] = None,
unmerge: bool = False,
) -> str:
"""Merge or unmerge cell ranges like ['A1:C1', 'D2:D5'] across selected sheets."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not ranges:
raise ValueError("ranges must be a non-empty list")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
for r in ranges:
if unmerge:
ws.unmerge_cells(r)
else:
ws.merge_cells(r)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_font(
filepath: str,
sheet_names: Optional[List[str]] = None,
targets: Optional[List[str]] = None,
name: Optional[str] = None,
size: Optional[float] = None,
bold: Optional[bool] = None,
italic: Optional[bool] = None,
underline: Optional[str] = None,
color: Optional[str] = None,
) -> str:
"""Update font style for target cells or ranges (e.g. ['A1', 'B2:C3'])."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not targets:
raise ValueError("targets must be a non-empty list")
argb = ExcelManager._normalize_color(color)
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
for cell in ExcelManager._iter_target_cells(ws, targets):
old = cell.font or Font()
cell.font = Font(
name=name if name is not None else old.name,
size=size if size is not None else old.size,
bold=bold if bold is not None else old.bold,
italic=italic if italic is not None else old.italic,
underline=underline if underline is not None else old.underline,
color=argb if argb is not None else (old.color.rgb if old.color else None),
)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_fill(
filepath: str,
sheet_names: Optional[List[str]] = None,
targets: Optional[List[str]] = None,
fill_color: Optional[str] = None,
pattern: str = "solid",
clear: bool = False,
) -> str:
"""Set or clear cell background fill."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not targets:
raise ValueError("targets must be a non-empty list")
argb = ExcelManager._normalize_color(fill_color) if not clear else None
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
for cell in ExcelManager._iter_target_cells(ws, targets):
if clear:
cell.fill = PatternFill()
else:
cell.fill = PatternFill(patternType=pattern, fgColor=argb)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_number_format(
filepath: str,
sheet_names: Optional[List[str]] = None,
targets: Optional[List[str]] = None,
number_format: str = "General",
) -> str:
"""Set number format string (e.g. '0.00', 'yyyy-mm-dd', '@')."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not targets:
raise ValueError("targets must be a non-empty list")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
for cell in ExcelManager._iter_target_cells(ws, targets):
cell.number_format = number_format
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_alignment(
filepath: str,
sheet_names: Optional[List[str]] = None,
targets: Optional[List[str]] = None,
horizontal: Optional[str] = None,
vertical: Optional[str] = None,
wrap_text: Optional[bool] = None,
text_rotation: Optional[int] = None,
) -> str:
"""Set alignment options for cells."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not targets:
raise ValueError("targets must be a non-empty list")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
for cell in ExcelManager._iter_target_cells(ws, targets):
old = cell.alignment or Alignment()
cell.alignment = Alignment(
horizontal=horizontal if horizontal is not None else old.horizontal,
vertical=vertical if vertical is not None else old.vertical,
wrap_text=wrap_text if wrap_text is not None else old.wrap_text,
text_rotation=text_rotation if text_rotation is not None else old.text_rotation,
)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_border(
filepath: str,
sheet_names: Optional[List[str]] = None,
targets: Optional[List[str]] = None,
style: str = "thin",
color: Optional[str] = None,
sides: Optional[List[str]] = None,
remove: bool = False,
) -> str:
"""Add or remove borders for target cells. sides defaults to all: left/right/top/bottom."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not targets:
raise ValueError("targets must be a non-empty list")
allowed_sides = {"left", "right", "top", "bottom", "diagonal"}
if sides is None:
sides = ["left", "right", "top", "bottom"]
bad = [s for s in sides if s not in allowed_sides]
if bad:
raise ValueError(f"Invalid border sides: {bad}. Allowed: {sorted(allowed_sides)}")
argb = ExcelManager._normalize_color(color)
side_obj = None if remove else Side(style=style, color=argb)
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
for cell in ExcelManager._iter_target_cells(ws, targets):
old = cell.border or Border()
kwargs = {
"left": old.left,
"right": old.right,
"top": old.top,
"bottom": old.bottom,
"diagonal": old.diagonal,
}
for s in sides:
kwargs[s] = side_obj
cell.border = Border(
left=kwargs["left"],
right=kwargs["right"],
top=kwargs["top"],
bottom=kwargs["bottom"],
diagonal=kwargs["diagonal"],
)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def freeze_panes(
filepath: str,
sheet_names: Optional[List[str]] = None,
cell: Optional[str] = None,
) -> str:
"""Freeze panes at a cell, e.g. 'B2'. Use cell=None to clear freeze panes."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
ws.freeze_panes = cell
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_auto_filter(
filepath: str,
sheet_names: Optional[List[str]] = None,
ref: Optional[str] = None,
clear: bool = False,
) -> str:
"""Set or clear auto-filter range like 'A1:D200'."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
if clear:
ws.auto_filter.ref = None
else:
if not ref:
raise ValueError("ref is required unless clear=true")
ws.auto_filter.ref = ref
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_sheet_protection(
filepath: str,
sheet_names: Optional[List[str]] = None,
protect: bool = True,
password: Optional[str] = None,
) -> str:
"""Enable/disable worksheet protection. If password is provided, sets protection password."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
ws.protection.sheet = bool(protect)
if password is not None:
# openpyxl stores hash; this API exists in modern versions
try:
ws.protection.set_password(password)
except Exception:
# Fallback: assign raw password (may be ignored depending on version)
ws.protection.password = password
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def set_hidden(
filepath: str,
sheet_names: Optional[List[str]] = None,
hide_rows: Optional[List[int]] = None,
hide_cols: Optional[List[Union[str, int]]] = None,
hidden: bool = True,
) -> str:
"""Hide/unhide rows and/or columns."""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
if hide_rows:
for r in hide_rows:
rr = int(r)
if rr <= 0:
raise ValueError("Row index must be >= 1")
ws.row_dimensions[rr].hidden = bool(hidden)
if hide_cols:
for c in hide_cols:
idx = ExcelManager._col_to_index(c)
letter = ws.cell(row=1, column=idx).column_letter
ws.column_dimensions[letter].hidden = bool(hidden)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def add_conditional_formatting(
filepath: str,
sheet_names: Optional[List[str]] = None,
ranges: Optional[List[str]] = None,
rule: Optional[Dict[str, Any]] = None,
clear: bool = False,
) -> str:
"""Add (or clear) conditional formatting.
Supported rule types:
- color_scale: {"type":"color_scale","start":"#63BE7B","mid":"#FFEB84","end":"#F8696B"}
- cell_is: {"type":"cell_is","operator":"greaterThan","formula":["0"],"fill":"#FFF2CC"}
- formula: {"type":"formula","formula":["$A1>0"],"fill":"#C6EFCE"}
"""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not ranges:
raise ValueError("ranges must be a non-empty list")
wb = load_workbook(filepath)
try:
for ws in ExcelManager._resolve_sheets(wb, sheet_names):
if clear:
ws.conditional_formatting._cf_rules.clear() # best-effort clear for the sheet
continue
if not rule or "type" not in rule:
raise ValueError("rule must be an object containing 'type'")
rtype = rule["type"]
if rtype == "color_scale":
start = ExcelManager._normalize_color(rule.get("start") or "#63BE7B")
mid = ExcelManager._normalize_color(rule.get("mid") or "#FFEB84")
end = ExcelManager._normalize_color(rule.get("end") or "#F8696B")
cf_rule = ColorScaleRule(
start_type="min",
start_value=None,
start_color=start,
mid_type="percentile",
mid_value=50,
mid_color=mid,
end_type="max",
end_value=None,
end_color=end,
)
elif rtype == "cell_is":
operator = rule.get("operator")
formulas = rule.get("formula")
if not operator or not formulas:
raise ValueError("cell_is rule requires 'operator' and 'formula'")
fill = rule.get("fill")
fill_argb = ExcelManager._normalize_color(fill) if fill else None
fill_obj = PatternFill(patternType="solid", fgColor=fill_argb) if fill_argb else None
cf_rule = CellIsRule(operator=operator, formula=formulas, fill=fill_obj)
elif rtype == "formula":
formulas = rule.get("formula")
if not formulas:
raise ValueError("formula rule requires 'formula'")
fill = rule.get("fill")
fill_argb = ExcelManager._normalize_color(fill) if fill else None
fill_obj = PatternFill(patternType="solid", fgColor=fill_argb) if fill_argb else None
cf_rule = FormulaRule(formula=formulas, fill=fill_obj)
else:
raise ValueError(f"Unsupported conditional formatting rule type: {rtype}")
for rng in ranges:
ws.conditional_formatting.add(rng, cf_rule)
wb.save(filepath)
return "OK"
finally:
wb.close()
@staticmethod
def apply_operations(filepath: str, operations: List[Dict[str, Any]]) -> str:
"""Apply a batch of operations in one workbook load/save.
Each operation is a dict with key 'op' and other params.
Supported ops: set_dimensions, merge_cells, set_font, set_fill, set_number_format, set_alignment, set_border.
"""
if not os.path.exists(filepath):
raise FileNotFoundError(f"File not found: {filepath}")
if not isinstance(operations, list) or not operations:
raise ValueError("operations must be a non-empty list")
wb = load_workbook(filepath)
try:
for operation in operations:
if not isinstance(operation, dict) or "op" not in operation:
raise ValueError("Each operation must be an object containing 'op'.")
op = operation["op"]
sheet_names = operation.get("sheet_names")
wss = ExcelManager._resolve_sheets(wb, sheet_names)
if op == "set_dimensions":
colw = operation.get("column_widths")
rowh = operation.get("row_heights")
for ws in wss:
if colw:
for col, width in colw.items():
idx = ExcelManager._col_to_index(col)
letter = ws.cell(row=1, column=idx).column_letter
ws.column_dimensions[letter].width = float(width)
if rowh:
for row, height in rowh.items():
r = int(row)
ws.row_dimensions[r].height = float(height)
elif op == "merge_cells":
ranges = operation.get("ranges")
if not ranges:
raise ValueError("merge_cells requires 'ranges'")
unmerge = bool(operation.get("unmerge", False))
for ws in wss:
for r in ranges:
ws.unmerge_cells(r) if unmerge else ws.merge_cells(r)
elif op == "set_font":
targets = operation.get("targets")
if not targets:
raise ValueError("set_font requires 'targets'")
argb = ExcelManager._normalize_color(operation.get("color"))
for ws in wss:
for cell in ExcelManager._iter_target_cells(ws, targets):
old = cell.font or Font()
cell.font = Font(
name=operation.get("name") if operation.get("name") is not None else old.name,
size=operation.get("size") if operation.get("size") is not None else old.size,
bold=operation.get("bold") if operation.get("bold") is not None else old.bold,
italic=operation.get("italic") if operation.get("italic") is not None else old.italic,
underline=operation.get("underline") if operation.get("underline") is not None else old.underline,
color=argb if argb is not None else (old.color.rgb if old.color else None),
)
elif op == "set_fill":
targets = operation.get("targets")
if not targets:
raise ValueError("set_fill requires 'targets'")
clear = bool(operation.get("clear", False))
if clear:
argb = None
else:
argb = ExcelManager._normalize_color(operation.get("fill_color"))
pattern = operation.get("pattern", "solid")
for ws in wss:
for cell in ExcelManager._iter_target_cells(ws, targets):
cell.fill = PatternFill() if clear else PatternFill(patternType=pattern, fgColor=argb)
elif op == "set_number_format":
targets = operation.get("targets")
if not targets:
raise ValueError("set_number_format requires 'targets'")
nf = operation.get("number_format", "General")
for ws in wss:
for cell in ExcelManager._iter_target_cells(ws, targets):
cell.number_format = nf
elif op == "set_alignment":
targets = operation.get("targets")
if not targets:
raise ValueError("set_alignment requires 'targets'")
for ws in wss:
for cell in ExcelManager._iter_target_cells(ws, targets):
old = cell.alignment or Alignment()
cell.alignment = Alignment(
horizontal=operation.get("horizontal") if operation.get("horizontal") is not None else old.horizontal,
vertical=operation.get("vertical") if operation.get("vertical") is not None else old.vertical,
wrap_text=operation.get("wrap_text") if operation.get("wrap_text") is not None else old.wrap_text,
text_rotation=operation.get("text_rotation") if operation.get("text_rotation") is not None else old.text_rotation,
)
elif op == "set_border":
targets = operation.get("targets")
if not targets:
raise ValueError("set_border requires 'targets'")
allowed_sides = {"left", "right", "top", "bottom", "diagonal"}
sides = operation.get("sides") or ["left", "right", "top", "bottom"]
bad = [s for s in sides if s not in allowed_sides]
if bad:
raise ValueError(f"Invalid border sides: {bad}")
remove = bool(operation.get("remove", False))
style = operation.get("style", "thin")
argb = ExcelManager._normalize_color(operation.get("color"))
side_obj = None if remove else Side(style=style, color=argb)
for ws in wss:
for cell in ExcelManager._iter_target_cells(ws, targets):
old = cell.border or Border()
kwargs = {
"left": old.left,
"right": old.right,
"top": old.top,
"bottom": old.bottom,
"diagonal": old.diagonal,
}
for s in sides:
kwargs[s] = side_obj
cell.border = Border(
left=kwargs["left"],
right=kwargs["right"],
top=kwargs["top"],
bottom=kwargs["bottom"],
diagonal=kwargs["diagonal"],
)
elif op == "freeze_panes":
cell = operation.get("cell")
for ws in wss:
ws.freeze_panes = cell
elif op == "set_auto_filter":
clear = bool(operation.get("clear", False))
ref = operation.get("ref")
for ws in wss:
ws.auto_filter.ref = None if clear else ref
elif op == "set_sheet_protection":
protect = bool(operation.get("protect", True))
password = operation.get("password")
for ws in wss:
ws.protection.sheet = protect
if password is not None:
try:
ws.protection.set_password(password)
except Exception:
ws.protection.password = password
elif op == "set_hidden":
hidden = bool(operation.get("hidden", True))
rows = operation.get("rows") or []
cols = operation.get("cols") or []
for ws in wss:
for r in rows:
rr = int(r)
ws.row_dimensions[rr].hidden = hidden
for c in cols:
idx = ExcelManager._col_to_index(c)
letter = ws.cell(row=1, column=idx).column_letter
ws.column_dimensions[letter].hidden = hidden
elif op == "conditional_formatting":
ranges = operation.get("ranges")
if not ranges:
raise ValueError("conditional_formatting requires 'ranges'")
clear = bool(operation.get("clear", False))
if clear:
for ws in wss:
ws.conditional_formatting._cf_rules.clear()
else:
rule = operation.get("rule")
if not rule or "type" not in rule:
raise ValueError("conditional_formatting requires 'rule.type'")
rtype = rule["type"]
if rtype == "color_scale":
start = ExcelManager._normalize_color(rule.get("start") or "#63BE7B")
mid = ExcelManager._normalize_color(rule.get("mid") or "#FFEB84")
end = ExcelManager._normalize_color(rule.get("end") or "#F8696B")
cf_rule = ColorScaleRule(
start_type="min",
start_value=None,
start_color=start,
mid_type="percentile",
mid_value=50,
mid_color=mid,
end_type="max",
end_value=None,
end_color=end,
)
elif rtype == "cell_is":
operator = rule.get("operator")
formulas = rule.get("formula")
if not operator or not formulas:
raise ValueError("cell_is requires 'operator' and 'formula'")
fill = rule.get("fill")
fill_argb = ExcelManager._normalize_color(fill) if fill else None
fill_obj = PatternFill(patternType="solid", fgColor=fill_argb) if fill_argb else None
cf_rule = CellIsRule(operator=operator, formula=formulas, fill=fill_obj)
elif rtype == "formula":
formulas = rule.get("formula")
if not formulas:
raise ValueError("formula requires 'formula'")
fill = rule.get("fill")
fill_argb = ExcelManager._normalize_color(fill) if fill else None
fill_obj = PatternFill(patternType="solid", fgColor=fill_argb) if fill_argb else None
cf_rule = FormulaRule(formula=formulas, fill=fill_obj)
else:
raise ValueError(f"Unsupported conditional formatting rule type: {rtype}")
for ws in wss:
for rng in ranges:
ws.conditional_formatting.add(rng, cf_rule)
else:
raise ValueError(f"Unsupported op: {op}")
wb.save(filepath)
return "OK"
finally:
wb.close()
