使用Openpyxl添加工作表时如何保留Excel数据验证规则？

方案1：直接用openpyxl写入DataFrame，绕过pandas ExcelWriter #

pandas的ExcelWriter在复用现有工作簿时，可能导致openpyxl无法正确保留数据验证规则。可以直接通过openpyxl的API添加工作表并写入DataFrame数据：

import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import pandas as pd

# 加载工作簿，确保保留原文件的格式规则
excel_book = openpyxl.load_workbook('SomeSpreadsheet.xlsx', data_only=False)

# 创建新工作表
sheet_b = excel_book.create_sheet(title='sheetB')

# 准备DataFrame数据
secondMockData = {'c': [10,20], 'd': [30,40]}
secondMockDF = pd.DataFrame(secondMockData)

# 将DataFrame写入新工作表，保留表头
for r_idx, row in enumerate(dataframe_to_rows(secondMockDF, index=False, header=True), 1):
    for c_idx, value in enumerate(row, 1):
        sheet_b.cell(row=r_idx, column=c_idx, value=value)

# 保存工作簿
excel_book.save('SomeSpreadsheet.xlsx')

这种方式直接操作openpyxl工作簿对象，避免了ExcelWriter带来的兼容性问题，能最大程度保留原有工作表的数据验证规则。

方案2：使用xlwings库（推荐处理复杂Excel文件） #

xlwings直接对接本地Excel应用程序，完全保留Excel的所有原生特性（包括数据验证、宏、条件格式等），适合处理包含复杂格式或规则的工作簿：

import xlwings as xw
import pandas as pd

secondMockData = {'c': [10,20], 'd': [30,40]}
secondMockDF = pd.DataFrame(secondMockData)

# 打开现有工作簿
with xw.Book('SomeSpreadsheet.xlsx') as book:
    # 添加新工作表
    sheet_b = book.sheets.add('sheetB')
    # 将DataFrame写入新表（从A1单元格开始，不写入索引）
    sheet_b.range('A1').options(index=False).value = secondMockDF
    # 自动调整列宽（可选）
    sheet_b.autofit()
    # 工作簿会自动保存并关闭

注意：使用xlwings需要本地安装Excel（Windows/macOS），但它对Excel原生特性的支持是最完善的。

方案3：升级openpyxl并检查数据验证类型 #

如果坚持使用pandas的ExcelWriter，可以先升级openpyxl到最新版本，部分标准数据验证规则在新版本中已被支持：

pip install --upgrade openpyxl

另外，警告中提到的"Data Validation extension"通常指非标准的自定义数据验证（比如依赖外部公式或扩展功能的验证），这类规则openpyxl目前无法支持，此时更推荐使用方案2。

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