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

import pandas as pd
import os
import xlwt

# 提取invoiceHeadExcel1内数据
file11 = invoiceHeadExcel1
filePath11 = file11.replace("\\", "\\\\")
file11 = eval(repr(filePath11).replace('\\\\', '\\'))
excel11 = pd.read_excel(file11)
invoiceHeadData = []
for row11 in excel11.values:
    temp_dict11 = {
        "InvoiceNo": "",
        "issueDate": "",
        "invoiceStatus": "",
        "totalAmount": "",
        "glDate": "",
        "Confirmation Status": "",
        "Customer Invoice ID": ""
    }
    InvoiceNo = str(row11[3])
    issueDate = str(row11[9])
    invoiceStatus = str(row11[13])
    totalAmount = str(row11[26])
    glDate = str(row11[95])
    temp_dict11["InvoiceNo"] = InvoiceNo
    temp_dict11["issueDate"] = issueDate
    temp_dict11["invoiceStatus"] = invoiceStatus
    temp_dict11["totalAmount"] = totalAmount
    temp_dict11["glDate"] = glDate
    invoiceHeadData.append(temp_dict11)
print("提取invoiceHeadExcel1内数据完成")

# 提取invoiceHeadExcel2内数据
file21 = invoiceHeadExcel2
filePath21 = file21.replace("\\", "\\\\")
file21 = eval(repr(filePath21).replace('\\\\', '\\'))
excel21 = pd.read_excel(file21)
for row21 in excel21.values:
    temp_dict21 = {
        "InvoiceNo": "",
        "issueDate": "",
        "invoiceStatus": "",
        "totalAmount": "",
        "glDate": "",
        "Confirmation Status": "",
        "Customer Invoice ID": ""
    }
    InvoiceNo = str(row21[3])
    issueDate = str(row21[9])
    invoiceStatus = str(row21[13])
    totalAmount = str(row21[26])
    glDate = str(row21[95])
    temp_dict21["InvoiceNo"] = InvoiceNo
    temp_dict21["issueDate"] = issueDate
    temp_dict21["invoiceStatus"] = invoiceStatus
    temp_dict21["totalAmount"] = totalAmount
    temp_dict21["glDate"] = glDate
    invoiceHeadData.append(temp_dict21)
print("提取invoiceHeadExcel2内数据完成")

# 提取invoiceExcel1内数据
file12 = invoiceExcel1
filePath12 = file12.replace("\\", "\\\\")
file12 = eval(repr(filePath12).replace('\\\\', '\\'))
excel12 = pd.read_excel(file12)
invoiceData = []
for row12 in excel12.values:
    temp_dict12 = {
        "Invoice No.": "",
        "Confirmation Status": "",
        "Customer Invoice ID": "",
    }
    Invoice_No = str(row12[2])
    Confirmation_Status = str(row12[11])
    Customer_Invoice_ID = str(row12[24])
    temp_dict12["Invoice No."] = Invoice_No
    temp_dict12["Confirmation Status"] = Confirmation_Status
    temp_dict12["Customer Invoice ID"] = Customer_Invoice_ID
    invoiceData.append(temp_dict12)
print("提取invoiceExcel1内数据完成")

# 提取invoiceExcel2内数据
file22 = invoiceExcel2
filePath22 = file22.replace("\\", "\\\\")
file22 = eval(repr(filePath22).replace('\\\\', '\\'))
excel22 = pd.read_excel(file22)
for row22 in excel22.values:
    temp_dict22 = {
        "Invoice No.": "",
        "Confirmation Status": "",
        "Customer Invoice ID": "",
    }
    Invoice_No = str(row22[2])
    Confirmation_Status = str(row22[11])
    Customer_Invoice_ID = str(row22[24])
    temp_dict22["Invoice No."] = Invoice_No
    temp_dict22["Confirmation Status"] = Confirmation_Status
    temp_dict22["Customer Invoice ID"] = Customer_Invoice_ID
    invoiceData.append(temp_dict22)
print("提取invoiceExcel2内数据完成")

# 合并数据到invoiceHeadData
for item1 in invoiceHeadData:
    for item2 in invoiceData:
        if item1['InvoiceNo'] == item2['Invoice No.']:
            item1['Confirmation Status'] = item2['Confirmation Status']
            item1['Customer Invoice ID'] = item2['Customer Invoice ID']
        else:
            continue
print("合并数据到invoiceHeadData完成")

# 将以上数据填入新建的Excel文件
folder = os.path.dirname(file12)
file_path = folder + "\\" + "葡萄牙税票监控" + ".xls"
workbook = xlwt.Workbook()
sheet1 = workbook.add_sheet('Sheet1')
index = 0
sheet1.write(index, 0, "Invoice No.")
sheet1.write(index, 1, "Local Invoice No")
sheet1.write(index, 2, "Invoice Date")
sheet1.write(index, 3, "Invoice status")
sheet1.write(index, 4, "Amount")
sheet1.write(index, 5, "GL Date")
sheet1.write(index, 6, "B2B Status")
for item in invoiceHeadData:
    index = index + 1
    sheet1.write(index, 0, item["InvoiceNo"])
    sheet1.write(index, 1, item["Customer Invoice ID"])
    sheet1.write(index, 2, item["issueDate"])
    sheet1.write(index, 3, item["invoiceStatus"])
    sheet1.write(index, 4, item["totalAmount"])
    sheet1.write(index, 5, item["glDate"])
    sheet1.write(index, 6, item["Confirmation Status"])
workbook.save(file_path)