我写了一个Python脚本，它将数据插入到Excel文件中。我的问题是，每次保存后，我的下拉列表都会消失。我的Excel文件中确实有多个工作表，不仅我编辑的当前页面受到影响，其他所有页面也受到影响。
这是我的代码：
def openExcelFile(path: str = None) -> openpyxl:
if path is None:
raise ValueError("path is None!")
return openpyxl.load_workbook(path)
def saveToExcelFile(wb_object: openpyxl = None, path: str = None) -> None:
if wb_object is None or path is None:
raise ValueError("wb_object or path is None!")
wb_object.save(path)
wb_object.close()
def findDuplicate(sheet_object, dataset: LandRegister = None, column: int = 0, row: int = 0, index: int = 0):
if index == 0 and row == 1:
return (-1, -1, -1)
duplicate_found = False
family_found = False
for i in range(0, index + 1):
cell_firstname = sheet_object.cell(row=row - i, column=column).value
cell_lastname = sheet_object.cell(row=row - i, column=column + 1).value
cell_address = sheet_object.cell(row=row - i, column=column + 2).value
cell_zipcode = sheet_object.cell(row=row - i, column=column + 3).value
if cell_lastname == dataset.lastname[index] and
cell_address == dataset.address[index] and
cell_zipcode == dataset.zipcode[index]:
if cell_firstname == dataset.firstname[index]:
return (-2, -2, -2)
return (row - 1, column, index)
if duplicate_found:
return (-2, -2, -2)
elif family_found:
return (row - 1, column, index)
else:
return (-1, -1, -1)
def insertNewRow(sheet, row, nr):
sheet.insert_rows(row, amount=1)
cell_object = sheet.cell(
row=row, column=2)
cell_object.value = nr
def insertData(sheet_object, dataset: LandRegister = None, column: int = 0, row: int = 0, index: int = 0):
# Insert the first name.
cell_object = sheet_object.cell(
row=row, column=column)
cell_object.value = dataset.firstname[index]
# Insert the last name.
cell_object = sheet_object.cell(
row=row, column=column + 1)
cell_object.value = dataset.lastname[index]
# Insert the location
cell_object = sheet_object.cell(
row=row, column=column + 2)
cell_object.value = dataset.address[index]
# Insert the zipcode
cell_object = sheet_object.cell(
row=row, column=column + 3)
tmp_zipcode = dataset.zipcode[index]
cell_object.value = tmp_zipcode
def insertMetaData(sheet_object, row: int = 0, column: int = 0):
kg_nr_p = sheet_object.cell(
row=row, column=column)
kg_name_p = sheet_object.cell(
row=row, column=column + 1)
grst_nr_p = sheet_object.cell(
row=row, column=column + 2)
cell_object = sheet_object.cell(
row=row+1, column=column)
cell_object.value = kg_nr_p.value
cell_object = sheet_object.cell(
row=row+1, column=column + 1)
cell_object.value = kg_name_p.value
cell_object = sheet_object.cell(
row=row+1, column=column + 2)
cell_object.value = grst_nr_p.value
def writeToExcelFile(wb_object, dataset: LandRegister = None, sheet_name: str = None, cell: str = 'A0'):
if dataset is None or sheet_name is None or wb_object is None:
raise ValueError("dataset, sheet_name or wb_object is None!")
start_row = SETTINGS[sheet_name]['row']
start_col = SETTINGS[sheet_name]['col']
sheet_names = wb_object.sheetnames
if sheet_name not in sheet_names:
raise ValueError("No matching sheet name found!")
sheet_object = wb_object[sheet_name]
row = 0
for index in range(len(dataset.firstname)):
found_row, found_colum, found_index = findDuplicate(
sheet_object, dataset, column=start_col, row=(row + start_row), index=index)
if found_colum == -1 and found_row == -1 and found_index == -1:
if index > 0:
cell_object = sheet_object.cell(
row=row + start_row-1, column=start_col-10)
insertNewRow(sheet_object, row + start_row, cell_object.value)
insertMetaData(sheet_object, row + start_row-1, start_col-9)
insertData(sheet_object, dataset,
column=start_col, row=(row + start_row), index=index)
row += 1
elif found_colum == -2 and found_row == -2 and found_index == -2:
continue
else:
cell_object = sheet_object.cell(
row=found_row, column=found_colum)
cell_object.value += " und " + dataset.firstname[index]
SETTINGS[sheet_name]['row'] = start_row + row
def loadSettings():
global SETTINGS
with open(os.path.join(DIRPATH, 'settings.json'), 'r') as file:
SETTINGS = json.load(file)
def saveSettings():
with open(os.path.join(DIRPATH, 'settings.json'), 'w') as file:
json.dump(SETTINGS, file)
def run(files: List[str], landregiser: LandRegister = None, extractor: ExtractorLandRegister = None) -> None:
path = os.path.join(DIRPATH, EXCEL_FILE_NAME)
excel_file = openExcelFile(path)
sheet_name = SETTINGS["Sheets"][SETTINGS["Source"]['4']]
main_logger.info(f'Insert in {sheet_name}')
counter = 1
for key in files:
print_message = f'==={files[key]:<2}===n'
try:
txt = extractor.extractTextFromPdf(files[key])
landregiser.dataset = txt
landregiser.execute()
writeToExcelFile(excel_file, landregiser, sheet_name)
main_logger.info(
f'Counter {counter:<2}: | {files[key]:<75} | {len(landregiser.firstname):<2}')
counter += 1
except Exception as exce:
print(exce)
print_message += str(exce)
main_logger.error("Error")
counter += 1
SETTINGS[sheet_name]['row'] = SETTINGS[sheet_name]['row'] + 1
saveToExcelFile(excel_file, path)
# saveSettings()
def main():
loadSettings()
ex = ExtractorLandRegister(SETTINGS["Source"]['4'])
lr = LandRegister()
files = ex.getPdfFiles()
run(files, landregiser=lr, extractor=ex)
if __name__ == '__main__':
main()
我是以错误的方式使用openpyxl，还是它不支持加载现有的下拉列表？
好的，加载数据验证不起作用，但可以插入数据，然后相应地格式化所有内容。
要使用openpyxl进行验证，请使用此链接进行验证。