调整Python脚本以向Excel表格添加数据并继承格式与结构化引用公式

阿华AIGC实验室

2026-4-15

Hey there! Let's tackle those two pain points you're facing—getting the formatting to carry over to your new row and making sure those structured reference formulas work without breaking your Excel file. I've worked with openpyxl on similar Excel table tasks, so here's how we can adjust your script:

修改后的完整脚本

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
import sys

# Get input data from command-line arguments
input_data = sys.argv[1:]  # Example: ["Value1", "Value2", "Value3"]

# Ensure there are exactly three inputs
if len(input_data) != 3:
    print("Error: Please provide exactly three input values.")
    sys.exit(1)

# Define the full path to your Excel file
excel_file = "/path_to_my_file/dummy.xlsx"

try:
    # Load workbook with safe settings to avoid corruption risks
    # data_only=False keeps formulas instead of converting them to static values
    wb = load_workbook(excel_file, keep_vba=False, data_only=False)
    ws = wb['Expenses']

    # Identify the table range
    table_name = 'tbl_expenses'
    if table_name not in ws.tables:
        print(f"Table '{table_name}' not found in worksheet.")
        sys.exit(1)

    table = ws.tables[table_name]
    start_cell, end_cell = table.ref.split(':')
    start_row = ws[start_cell].row
    end_row = ws[end_cell].row
    end_col = ws[end_cell].column

    # Determine the next row index
    new_row_idx = end_row + 1

    # Step 1: Insert input data starting from column B
    for col_index, value in enumerate(input_data, start=2):  # Start at column B
        ws.cell(row=new_row_idx, column=col_index, value=value)

    # Step 2: Copy formatting AND formulas from the last table row to the new row
    for col in range(1, end_col + 1):
        source_cell = ws.cell(row=end_row, column=col)
        target_cell = ws.cell(row=new_row_idx, column=col)
        
        # Copy base style (font, borders, alignment, number format)
        target_cell.style = source_cell.style
        # Explicitly copy fill properties to fix your earlier formatting issue
        target_cell.fill = source_cell.fill.copy()
        
        # Preserve structured reference formulas
        if source_cell.data_type == 'f':
            target_cell.value = source_cell.value

    # Step 3: Update the table's range to include the new row
    new_end_cell = f"{get_column_letter(end_col)}{new_row_idx}"
    table.ref = f"{start_cell}:{new_end_cell}"

    # Save and close properly to avoid file locking/corruption
    wb.save(excel_file)
    wb.close()
    print("Row added successfully with formatting and structured reference formulas preserved!")
except Exception as e:
    print(f"An unexpected error occurred: {e}")
    # Ensure workbook is closed even if an error happens
    if 'wb' in locals():
        wb.close()

关键修改说明

完整样式复制：
- target_cell.style = source_cell.style复制基础样式（字体、边框、对齐、数字格式）
- 额外添加target_cell.fill = source_cell.fill.copy()来解决你之前复制填充属性失败的问题，确保颜色、图案等复杂填充样式被完整继承
结构化引用公式处理：
- 直接复制源单元格的公式值即可，openpyxl会保留[@[My Net]]这类结构化引用。当表格范围扩展后，Excel会自动识别这些引用指向新行的对应列，包括你的LAMBDA自定义函数引用
避免文件损坏：
- 加载工作簿时设置keep_vba=False（你不再使用VBA，这减少了格式冲突风险）
- 无论成功还是出错都调用wb.close()，确保文件资源被正确释放，避免iCloud同步时的文件锁定问题
- 保留data_only=False确保公式不会被转换成静态值