[Python] Excel file corrupted after writing with openpyxl in Python

I am working on a project in Python where I need to transfer data from one Excel file to another using the openpyxl library. However, I am facing an issue — after completing the openpyxl operations and saving the file, the Excel file gets corrupted or crashes when I try to open it. The data transfer process seems to complete, but the output file is not opening properly in Excel. I would like guidance on why this issue occurs and how it can be resolved.

its gives below error This XML file does not appear to have any style information associated with it. The document tree is shown below. error300160_01.xml

Errors were detected in file 'https://enelcom-my.sharepoint.com/personal/mugdha_gaonkar_enel_com/Documents/Documents/Recon_Auto upload_Orion/GE_Vernova/20250903-UPDATE_BillPaymentDetails.xlsx' Repaired Records: External formula reference from /xl/externalLinks/externalLink1.xml part (Cached values from external formula reference)
below is my python code

# ===Recon file to ENC template===
# PATHS
base_folder = Path(r"C:\Users\14507020\OneDrive - Enel Spa\Gaonkar Mugdha's files - Recon_Auto upload_Orion\GE_vernova")

# Build today’s filename -> YYYYMMDD
today_str = datetime.today().strftime("%Y%m%d")
template_name = f"{today_str}-UPDATE_BillPaymentDetails.xlsx"
template_path = base_folder / template_name
output_path = base_folder / f"Test-GE_Vernova_BillPaymentDetails_{today_str}.xlsx"

# Adjust path as needed
recon_file = base_folder / f"Test-GE_Vernova_ENCRecon.xlsx"

df_ver = pd.read_excel(recon_file, sheet_name="Sheet1")

# HELPERS
def parse_amount(x):
"""
Robustly parse Payment Amount from strings like '1,234.56', '**439.86*', '(123.45)'
into a float. Non-numeric or blanks -> 0.0
"""
if x is None:
return 0.0
s = str(x).strip()
# handle '(123.45)' negatives
neg = False
if s.startswith("(") and s.endswith(")"):
neg = True
s = s[1:-1]
# strip all but digits, dot, minus
s = re.sub(r"[^0-9.\-]", "", s)
if s in ("", ".", "-"):
val = 0.0
else:
try:
val = float(s)
except Exception:
val = 0.0
return -val if neg else val

def to_mmddyyyy(x):
"""
Convert many date shapes to MM/DD/YYYY.
Accepts 'YYYYMMDD', 'DD-MMM-YY', 'MM/DD/YYYY', 'YYYY-MM-DD', etc.
Returns '' if cannot parse.
"""
if x is None:
return ""
s = str(x).strip()
if s == "":
return ""
# Try common formats
fmts = ["%Y%m%d", "%d-%b-%y", "%m/%d/%Y", "%Y-%m-%d", "%d-%b-%Y"]
for fmt in fmts:
try:
dt = datetime.strptime(s, fmt)
return dt.strftime("%m/%d/%Y")
except Exception:
continue
# If it's 8 consecutive digits (e.g., 20250502) not caught above
if re.fullmatch(r"\d{8}", s):
try:
dt = datetime.strptime(s, "%Y%m%d")
return dt.strftime("%m/%d/%Y")
except Exception:
pass
return ""

# REQUIRE df_ver with 5 columns
required = {"Bill ID", "Pay Status", "Payment Sent Date", "Payment Type Number", "Payment Amount"}
missing = required - set(df_ver.columns)
if missing:
raise ValueError(f"df_ver is missing required columns: {missing}")

# 1) Clean Bill IDs in recon (digits only)
dfv = df_ver.copy()
dfv["Bill ID Clean"] = dfv["Bill ID"].astype(str).str.extract(r"(\d+)", expand=False).fillna("")

# 2) Normalize Payment Amount to float for correct summation
dfv["Payment Amount Num"] = dfv["Payment Amount"].apply(parse_amount)

# 3) Normalize date to MM/DD/YYYY BEFORE aggregation (we keep last anyway)
dfv["Payment Sent Date (norm)"] = dfv["Payment Sent Date"].apply(to_mmddyyyy)

# 4) Group by Bill ID Clean:
# - Pay Status: last non-null
# - Payment Sent Date: last non-null (already normalized)
# - Payment Type Number: last non-null
# - Payment Amount: sum of numeric amounts (correctly sums only duplicates)
def last_nonnull(series):
s = series.dropna()
return s.iloc[-1] if not s.empty else ""

agg = (
dfv.groupby("Bill ID Clean", as_index=False)
.agg({
"Pay Status": last_nonnull,
"Payment Sent Date (norm)": last_nonnull,
"Payment Type Number": last_nonnull,
"Payment Amount Num": "sum"
})
.rename(columns={
"Payment Sent Date (norm)": "Payment Sent Date",
"Payment Amount Num": "Payment Amount"
})
)

# 5) Build lookup map keyed by cleaned Bill ID
recon_map = agg.set_index("Bill ID Clean").to_dict(orient="index")

# ==============================
# LOAD TEMPLATE AND FILL
# ==============================
wb = load_workbook(template_path)
ws = wb["DataEntry"]

# Column indexes (0-based):
# C=2 Bill ID
# G=6 Pay Status
# I=8 Payment Sent Date
# K=10 Payment Type Number
# N=13 Payment Amount
filled, missing_ids = 0, []
for row in ws.iter_rows(min_row=3, max_row=ws.max_row): # skip header (row1) + instructions (row2)
bill_id_cell = row[2] # Column C
if not bill_id_cell.value:
continue

bill_id_clean = re.sub(r"\D", "", str(bill_id_cell.value).strip())
if not bill_id_clean:
continue

rec = recon_map.get(bill_id_clean)
if rec is None:
missing_ids.append(bill_id_clean)
continue

row[6].value = rec.get("Pay Status", "") # G
row[8].value = rec.get("Payment Sent Date", "") # I (MM/DD/YYYY)
row[10].value = rec.get("Payment Type Number", "") # K

amt = rec.get("Payment Amount", 0.0)
try:
row[13].value = float(f"{float(amt):.2f}") # N numeric with 2 decimals
except Exception:
row[13].value = amt # fallback

filled += 1

wb.save(output_path)

print(f"Template filled: {output_path}")
print(f" Rows filled: {filled}")
if missing_ids:
print(f" Bill IDs in template with no recon match (first 20): {missing_ids[:20]}")

Continue reading...