通过pandas保留Excel下拉列表

pip install pandas openpyxl

import pandas as pd
from openpyxl import load_workbook

# 读取Excel文件
df = pd.read_excel('input.xlsx')

# 加载工作簿
book = load_workbook('input.xlsx')
writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')
writer.book = book

# 创建下拉列表的数据
dropdown_values = ['Option 1', 'Option 2', 'Option 3']

# 获取当前工作表
sheet = book.active

# 获取下拉列表的单元格范围
cell_range = 'A2:A10'

# 创建下拉列表
data_validation = DataValidation(type="list", formula1='"{}"'.format(','.join(dropdown_values)), showDropDown=True)

# 将下拉列表应用于单元格范围
sheet.add_data_validation(data_validation)
data_validation.add(sheet[cell_range])

# 保存修改后的Excel文件
writer.save()
writer.close()

import pandas as pd
from openpyxl import load_workbook
from openpyxl.worksheet.datavalidation import DataValidation

# 读取Excel文件
df = pd.read_excel('input.xlsx')

# 加载工作簿
book = load_workbook('input.xlsx')
writer = pd.ExcelWriter('output.xlsx', engine='openpyxl')
writer.book = book

# 创建下拉列表的数据
dropdown_values = ['Option 1', 'Option 2', 'Option 3']

# 获取当前工作表
sheet = book.active

# 获取下拉列表的单元格范围
cell_range = 'A2:A10'

# 创建下拉列表
data_validation = DataValidation(type="list", formula1='"{}"'.format(','.join(dropdown_values)), showDropDown=True)

# 将下拉列表应用于单元格范围
sheet.add_data_validation(data_validation)
data_validation.add(sheet[cell_range])

# 保存修改后的Excel文件
writer.save()
writer.close()