使用Openpyxl复制工作表时如何同步Named Ranges避免NAME错误？

问题根源 #

openpyxl的copy_worksheet方法仅复制工作表的单元格数据、格式，不会自动同步复制工作表级的命名区域，而你的公式依赖这些命名区域计算，因此新工作表会出现#NAME?错误。

解决方案 #

手动遍历模板工作表的所有命名区域，将地址中的原表名替换为新表名，然后在新工作表中创建对应的命名区域。以下是修改后的完整代码：

import streamlit as st
import openpyxl

def write_to_template_sheet(input_filename, value_to_write):
    try:
        # 加载Excel文件
        wb = openpyxl.load_workbook(input_filename)
        template_sheet = wb["Template"]
        
        # 复制模板工作表
        new_sheet = wb.copy_worksheet(template_sheet)
        
        # 统计以"P"开头的工作表数量并命名新表
        p_sheets_count = len([name for name in wb.sheetnames if name.startswith('P')])
        new_sheet.title = f"P{p_sheets_count + 1}"
        
        # 复制并适配模板工作表的命名区域到新表
        template_name = template_sheet.title
        new_sheet_name = new_sheet.title
        
        # 筛选出属于模板工作表的命名区域
        for defined_name in wb.defined_names.definedName:
            # 检查命名区域的作用域是否为模板工作表
            if defined_name.localSheetId == template_sheet._id:
                # 获取命名区域的地址
                for dest in defined_name.destinations:
                    _, old_address = dest
                    # 将地址中的原表名替换为新表名
                    new_address = old_address.replace(f"'{template_name}'!", f"'{new_sheet_name}'!")
                    # 在工作簿中添加新的命名区域，作用域设为新工作表
                    wb.defined_names.add(
                        name=defined_name.name,
                        attr_text=new_address,
                        scope=new_sheet
                    )
        
        # 在新表指定位置写入值
        new_sheet.cell(row=31, column=2, value=value_to_write)
        
        # 保存文件
        wb.save(input_filename)
        st.success('保存成功')
    
    except Exception as e:
        st.error(f'错误: {str(e)}')

st.title("创建项目")
value_to_write = st.text_input("项目名称", "", key="Project_Name")

if st.button("保存"):
    input_filename = "wbtool.xlsx"
    write_to_template_sheet(input_filename, value_to_write)

关键代码说明 #

1. 筛选工作表级命名区域：通过defined_name.localSheetId == template_sheet._id判断命名区域是否属于模板工作表，避免误处理工作簿级的全局命名区域。
2. 替换地址中的表名：将原地址里的模板表名替换为新表名，确保命名区域指向新工作表的对应单元格。
3. 创建新命名区域：使用wb.defined_names.add添加命名区域，指定scope=new_sheet将其绑定到新工作表，保持和模板表相同的作用域规则。

额外优化 #

原代码中手动遍历单元格复制值的循环可以删除，因为copy_worksheet已经完成了数据和格式的复制，重复操作会冗余且降低效率。

内容的提问来源于stack exchange，提问作者atallpa