python - 根据列比较 xlsx 和 xlsm 文件并将数据保存到新的 xlsx

Question

我正在编写 python 代码来获取两个 xls 文件中的公共行并将结果保存到新的 xlsx 文件中。

我可以从两张表中获取数据，但我最终陷入了无限循环。

     # loading the Headcount excel
hc_wb = openpyxl.load_workbook(filename='Headcount.xlsm')

# reading the worksheet required
hc_ws = hc_wb.get_sheet_by_name('Headcount')
rowmax = hc_ws.max_row
staffID = 8

# create new workbook
modified_wb = openpyxl.Workbook()
modified_ws = modified_wb.worksheets[0]
modified_ws.cell(column=1, row=1).value = "StaffID"
modified_ws.cell(column=2, row=1).value = "Name"
modified_ws.cell(column=3, row=1).value = "Department Code"
modified_ws.cell(column=4, row=1).value = "Department Name"
modified_ws.cell(column=5, row=1).value = "Finance Managed Entity Level 1"



# loading login report data excel
login_wb = openpyxl.load_workbook(filename='login_report.xlsx')
login_ws = login_wb.get_sheet_by_name('sheet1')
login_rowmax = login_ws.max_row

print("crossed 3rd step------------------")


# looping through the rows and add the missing '0's to the StaffID
for rown in range(2,rowmax+1):

    for login_rows in range(2, login_rowmax + 1):
        if (str(login_ws.cell(row=login_rows, column=1).value).strip().zfill(staffID)) == str(hc_ws.cell(row=rown, column=1).value).strip().zfill(staffID):
            modified_ws.cell(row=rown, column=1).value = str(hc_ws.cell(row=rown, column=1).value).strip().zfill(staffID)
            modified_ws.cell(row=rown, column=2).value = str(hc_ws.cell(row=rown, column=23).value)
            modified_ws.cell(row=rown, column=3).value = str(hc_ws.cell(row=rown, column=24).value)
            modified_ws.cell(row=rown, column=4).value = str(hc_ws.cell(row=rown, column=25).value)
            modified_ws.cell(row=rown, column=5).value = str(hc_ws.cell(row=rown, column=26).value)


modified_wb.save('ModifiedData.xlsx')

标签： pythonexcelopenpyxl