提取Excel中数据，合并，生成新的Excel

import pandas as pd
import os
import xlwt

file = invoiceHeadExcel
filePath = file.replace("\\", "\\\\")
file = eval(repr(filePath).replace('\\\\', '\\'))

excel = pd.read_excel(file)
invoiceHeadData = []
for row in excel.values:
    temp_dict = {
        "InvoiceNo": "",
        "issueDate": "",
        "invoiceStatus": "",
        "totalAmount": "",
        "glDate": "",
        "Confirmation Status": ""
    }
    InvoiceNo = str(row[3])
    issueDate = str(row[9])
    invoiceStatus = str(row[13])
    totalAmount = str(row[26])
    glDate = str(row[95])

    temp_dict["InvoiceNo"] = InvoiceNo
    temp_dict["issueDate"] = issueDate
    temp_dict["invoiceStatus"] = invoiceStatus
    temp_dict["totalAmount"] = totalAmount
    temp_dict["glDate"] = glDate
    invoiceHeadData.append(temp_dict)

file2 = invoiceExcel
filePath2 = file2.replace("\\", "\\\\")
file2 = eval(repr(filePath2).replace('\\\\', '\\'))

excel2 = pd.read_excel(file2)
invoiceData = []
for row2 in excel2.values:
    temp_dict2 = {
        "Invoice No.": "",
        "Confirmation Status": "",
    }
    Invoice_No = str(row2[2])
    Confirmation_Status = str(row2[11])

    temp_dict2["Invoice No."] = Invoice_No
    temp_dict2["Confirmation Status"] = Confirmation_Status
    invoiceData.append(temp_dict2)

for item1 in invoiceHeadData:
    for item2 in invoiceData:
        if item1['InvoiceNo'] == item2['Invoice No.']:
            item1['Confirmation Status'] = item2['Confirmation Status']
        else:
            continue

folder = os.path.dirname(file2)
# “关联与第三方发票_生成系统时间日期+时间小时”，例如2022032108
file_path = folder + "\\" + ${v2Currentdate}
workbook = xlwt.Workbook()
sheet1 = workbook.add_sheet('Sheet1')
index = 0
sheet1.write(index, 0, "InvoiceNo")
sheet1.write(index, 1, "issueDate")
sheet1.write(index, 2, "invoiceStatus")
sheet1.write(index, 3, "totalAmount")
sheet1.write(index, 4, "glDate")
sheet1.write(index, 5, "Confirmation Status")
for item in invoiceHeadData:
    index = index + 1
    sheet1.write(index, 0, item["InvoiceNo"])
    sheet1.write(index, 1, item["issueDate"])
    sheet1.write(index, 2, item["invoiceStatus"])
    sheet1.write(index, 3, item["totalAmount"])
    sheet1.write(index, 4, item["glDate"])
    sheet1.write(index, 5, item["Confirmation Status"])

workbook.save(file_path)