# -*- coding: utf-8 -*-
import openpyxl, io
from openpyxl.styles import PatternFill
BK="한국임업진흥원_backup_tab01전개전.xlsx"
FN="한국임업진흥원.xlsx"
# original K -> (N, Gval) using merge-fill
def load(fn):
    wb=openpyxl.load_workbook(fn); ws=wb.active
    filled={}
    for m in ws.merged_cells.ranges:
        top=ws.cell(m.min_row,m.min_col).value
        for r in range(m.min_row,m.max_row+1):
            for c in range(m.min_col,m.max_col+1): filled[(r,c)]=top
    def gv(r,c):
        v=filled.get((r,c),ws.cell(r,c).value); return v if (v is not None and str(v).strip()!="") else None
    d={}
    for r in range(3,ws.max_row+1):
        k=ws.cell(r,11).value
        if not k: continue
        k=str(k).strip()
        d[k]=dict(N=ws.cell(r,14).value, G=gv(r,7))
    return d
orig=load(BK)
wb=openpyxl.load_workbook(FN); ws=wb.active
SKY=PatternFill(start_color="FFBDD7EE", end_color="FFBDD7EE", fill_type="solid")
newrows=0; nchg=0; gadd=0
for r in range(3,ws.max_row+1):
    k=ws.cell(r,11).value
    k=str(k).strip() if k else ""
    if k not in orig:
        newrows+=1
        for c in range(4,16):  # D..O
            v=ws.cell(r,c).value
            if v is not None and str(v).strip()!="":
                ws.cell(r,c).fill=SKY
    else:
        o=orig[k]
        if (ws.cell(r,14).value or "")!=(o['N'] or ""):
            ws.cell(r,14).fill=SKY; nchg+=1
        # G newly added on anchor-self rows (orig had no G at this K, now has G)
        gv=ws.cell(r,7).value
        if gv is not None and str(gv).strip()!="" and not o['G']:
            ws.cell(r,7).fill=SKY; gadd+=1
wb.save(FN)
print("new rows blued:",newrows,"N changed:",nchg,"G added:",gadd)