Original xls Format-Preserving Edit (Excel COM)

What does Original xls Format-Preserving Edit (Excel COM) do?

Trigger Conditions

• User/client requires "modify on the original table, keep the format/design exactly the same, do not adjust other tables"
• Financial three-statement adjustments: adjust undistributed profit/total profit while maintaining asset = liability + equity balance
• Received an old .xls format report that needs numeric changes, cannot be rebuilt

Hard Rule: Do NOT rebuild with openpyxl

Rebuilding xlsx with openpyxl will lose the original table's format/style/layout — the client will see it's "not the original table" as soon as they open it. Must use Excel COM to open the original file copy, only modify the target cells, keep everything else as-is.

Environment (Windows)

# Install pywin32 in hermes venv (system Python does NOT have it!)
python -m pip install pywin32
# Run scripts with hermes venv's python (system Python cannot import win32com)

Core Workflow

1. Copy the original file first, then modify

import shutil
shutil.copy2(SRC, OUT)   # Keep the original untouched, modify OUT

2. Open with COM, scan to locate rows/columns (DO NOT guess row numbers!)

import win32com.client
excel = win32com.client.Dispatch('Excel.Application')
excel.Visible = False; excel.DisplayAlerts = False
wb = excel.Workbooks.Open(OUT)
ws = wb.Sheets('Balance Sheet')
# Scan column A/E to find account names, print actual row/column
for r in range(1, 60):
    a = str(ws.Cells(r, 1).Value or ''); e = str(ws.Cells(r, 5).Value or '')
    if 'Undistributed Profit' in a or 'Undistributed Profit' in e:
        print(r, a, e, ws.Cells(r,7).Value)  # G/H columns are right-side values

⚠️ For dual-column reports (balance sheet), account names are in column E, values in G/H — "Undistributed Profit" is at right-side G34/H34, not C/D! ⚠️ xlrd 0-indexed vs COM 1-indexed, row numbers differ by 1 — must scan using labels to confirm.

3. Only modify target cells

ws.Cells(13, 3).Value = 4307698.97    # Other receivables ending (left column C)
ws.Cells(34, 7).Value = 1052736.98    # Undistributed profit ending (right column G)
wb.Application.Calculate()
wb.Save(); wb.Close(True); excel.Quit()

4. ⚠️ Original .xls often has no formulas — totals must be calculated manually and written in

XLS exported from small business standards often have totals as values, not SUM formulas — after modifying details, totals won't update automatically. After adjustment, must calculate manually:

• Total assets = current assets + non-current assets
• Total equity = paid-in capital + undistributed profit
• Liabilities + equity = total liabilities + total equity = total assets ✅ Verify balance
• Income statement: operating profit = revenue - cost - selling expenses - financial expenses; total profit = operating profit + non-operating income; net profit = total profit - income tax

5. Verify balance

assert abs(ws.Cells(36,3).Value - ws.Cells(36,7).Value) < 0.01  # Assets = Liabilities + Equity

Common Financial Three-Statement Adjustment Plan (Tested 2026-08)

Goal: Correct undistributed profit (e.g., ending 1.05M / beginning 0.79M), total profit = difference between ending and beginning.

• Balance Sheet: Only adjust "Other Receivables" (asset side) by the same amount → assets = liabilities + equity automatically balanced
• Income Statement: Adjust "Operating Revenue" (or administrative expenses) so total profit = undistributed profit difference; current period profit set separately
• Cash Flow Statement: No cash receipt/payment profit adjustments, usually left unchanged
• Balance verification: Ending undistributed - Beginning undistributed == cumulative total profit

Pitfalls and Solutions

1. pywin32 only installed in hermes venv → system Python import win32com gives "No module named 'pywintypes'"; use venv python
2. python -I isolated mode cannot read site-packages → run directly without -I
3. COM reads original value as 0 → wrong row number (xlrd 0-indexed vs COM 1-indexed), scan by label to locate
4. Modification doesn't take effect after verification → check whether you changed left or right column (C/D vs G/H)
5. Before killing a Python process, confirm its CommandLine (webapp/gateway may also be Python processes)

---

🙋 About the Author

九品锦锂e ｜ Packaging the pitfalls I've stepped in into "grab and run" skills, not writing textbooks. This skill is the version I use daily.

WeChat: ly5419495 (Add with remark "SkillHub" for priority approval) Official Account: 初五Agent (Search on WeChat; reviews and methods are written there, no need to add WeChat to read)

Other tools I've built that can run directly are all on this shelf page (copy and open in browser): https://skillpay.alipay.com/public/jiupinjinlie

If you get stuck or have another scenario you'd like me to package into a skill, reach me via any method above.