91 lines
3.1 KiB
Python
91 lines
3.1 KiB
Python
import shutil
|
|
import pandas as pd
|
|
from openpyxl import load_workbook
|
|
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, Color
|
|
from datetime import datetime
|
|
|
|
IE_FILE = "EAT and Timely Filing Inclusion Exclusion List v27.0.xlsm"
|
|
EV_FILE = "Timely Filing Evidence Report_.xlsx"
|
|
timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
|
|
OUT_FILE = f"Timely Filing Evidence Report_with_flag_{timestamp}.xlsx"
|
|
|
|
# --- build lookup from IE ---
|
|
ie = pd.read_excel(IE_FILE, sheet_name="JRD TMF Content Map")
|
|
lookup = {}
|
|
for _, row in ie.iterrows():
|
|
key = (
|
|
str(row["Type/Zone"]).strip().lower(),
|
|
str(row["SubType/Section"]).strip().lower(),
|
|
str(row["Classification/Artifact"]).strip().lower(),
|
|
)
|
|
lookup[key] = str(row["Include in Timely Filing Report"]).strip()
|
|
|
|
# --- copy Evidence Report ---
|
|
shutil.copy(EV_FILE, OUT_FILE)
|
|
wb = load_workbook(OUT_FILE)
|
|
ws = wb["Report"]
|
|
|
|
# header row is row 4 (1-indexed); data starts row 5
|
|
# current cols: A=DocNum, B=Version, ..., F=Type(6), G=Subtype(7), H=Classification(8)
|
|
HEADER_ROW = 4
|
|
DATA_START = 5
|
|
COL_TYPE = 6 # F
|
|
COL_SUBTYPE = 7 # G
|
|
COL_CLASS = 8 # H
|
|
INSERT_COL = 2 # insert as new column B
|
|
|
|
# read key columns before inserting
|
|
row_keys = {}
|
|
for r in range(DATA_START, ws.max_row + 1):
|
|
t = ws.cell(r, COL_TYPE).value
|
|
s = ws.cell(r, COL_SUBTYPE).value
|
|
c = ws.cell(r, COL_CLASS).value
|
|
if t or s or c:
|
|
row_keys[r] = (str(t).strip().lower() if t else "", str(s).strip().lower() if s else "", str(c).strip().lower() if c else "")
|
|
|
|
# insert column B (shifts everything right)
|
|
ws.insert_cols(INSERT_COL)
|
|
|
|
# style helpers
|
|
header_font = Font(bold=True, name="Calibri", size=11, color=Color(theme=0))
|
|
yes_fill = PatternFill("solid", start_color="C6EFCE") # light green
|
|
no_fill = PatternFill("solid", start_color="FFC7CE") # light red
|
|
na_fill = PatternFill("solid", start_color="FFEB9C") # light yellow
|
|
thin = Side(style="thin")
|
|
border = Border(left=thin, right=thin, top=thin, bottom=thin)
|
|
|
|
# write header
|
|
hcell = ws.cell(HEADER_ROW, INSERT_COL, "Include in Timely Filing")
|
|
hcell.font = header_font
|
|
hcell.fill = PatternFill("solid", start_color="0070C0")
|
|
hcell.alignment = Alignment(horizontal="center", wrap_text=True)
|
|
hcell.border = border
|
|
|
|
# write values
|
|
for r, key in row_keys.items():
|
|
val = lookup.get(key, "N/A")
|
|
cell = ws.cell(r, INSERT_COL, val)
|
|
cell.font = Font(name="Calibri", size=11)
|
|
cell.alignment = Alignment(horizontal="center")
|
|
cell.border = border
|
|
if val == "Yes":
|
|
cell.fill = yes_fill
|
|
elif val == "No":
|
|
cell.fill = no_fill
|
|
else:
|
|
cell.fill = na_fill
|
|
|
|
# set column width
|
|
ws.column_dimensions[ws.cell(1, INSERT_COL).column_letter].width = 18
|
|
|
|
wb._external_links = []
|
|
wb.defined_names = wb.defined_names.__class__()
|
|
wb.save(OUT_FILE)
|
|
print(f"Saved: {OUT_FILE}")
|
|
|
|
# quick stats
|
|
total = len(row_keys)
|
|
matched = sum(1 for k in row_keys.values() if lookup.get(k, "N/A") != "N/A")
|
|
na_count = sum(1 for k in row_keys.values() if lookup.get(k, "N/A") == "N/A")
|
|
print(f"Rows: {total}, matched: {matched}, N/A (no match): {na_count}")
|