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

  1 import pandas as pd
  2 import os
  3 import xlwt
  4 
  5 # 提取invoiceHeadExcel1内数据
  6 file11 = invoiceHeadExcel1
  7 filePath11 = file11.replace("\\", "\\\\")
  8 file11 = eval(repr(filePath11).replace('\\\\', '\\'))
  9 excel11 = pd.read_excel(file11)
 10 invoiceHeadData = []
 11 for row11 in excel11.values:
 12     temp_dict11 = {
 13         "InvoiceNo": "",
 14         "issueDate": "",
 15         "invoiceStatus": "",
 16         "totalAmount": "",
 17         "glDate": "",
 18         "Confirmation Status": "",
 19         "Customer Invoice ID": ""
 20     }
 21     InvoiceNo = str(row11[3])
 22     issueDate = str(row11[9])
 23     invoiceStatus = str(row11[13])
 24     totalAmount = str(row11[26])
 25     glDate = str(row11[95])
 26     temp_dict11["InvoiceNo"] = InvoiceNo
 27     temp_dict11["issueDate"] = issueDate
 28     temp_dict11["invoiceStatus"] = invoiceStatus
 29     temp_dict11["totalAmount"] = totalAmount
 30     temp_dict11["glDate"] = glDate
 31     invoiceHeadData.append(temp_dict11)
 32 print("提取invoiceHeadExcel1内数据完成")
 33 
 34 # 提取invoiceHeadExcel2内数据
 35 file21 = invoiceHeadExcel2
 36 filePath21 = file21.replace("\\", "\\\\")
 37 file21 = eval(repr(filePath21).replace('\\\\', '\\'))
 38 excel21 = pd.read_excel(file21)
 39 for row21 in excel21.values:
 40     temp_dict21 = {
 41         "InvoiceNo": "",
 42         "issueDate": "",
 43         "invoiceStatus": "",
 44         "totalAmount": "",
 45         "glDate": "",
 46         "Confirmation Status": "",
 47         "Customer Invoice ID": ""
 48     }
 49     InvoiceNo = str(row21[3])
 50     issueDate = str(row21[9])
 51     invoiceStatus = str(row21[13])
 52     totalAmount = str(row21[26])
 53     glDate = str(row21[95])
 54     temp_dict21["InvoiceNo"] = InvoiceNo
 55     temp_dict21["issueDate"] = issueDate
 56     temp_dict21["invoiceStatus"] = invoiceStatus
 57     temp_dict21["totalAmount"] = totalAmount
 58     temp_dict21["glDate"] = glDate
 59     invoiceHeadData.append(temp_dict21)
 60 print("提取invoiceHeadExcel2内数据完成")
 61 
 62 # 提取invoiceExcel1内数据
 63 file12 = invoiceExcel1
 64 filePath12 = file12.replace("\\", "\\\\")
 65 file12 = eval(repr(filePath12).replace('\\\\', '\\'))
 66 excel12 = pd.read_excel(file12)
 67 invoiceData = []
 68 for row12 in excel12.values:
 69     temp_dict12 = {
 70         "Invoice No.": "",
 71         "Confirmation Status": "",
 72         "Customer Invoice ID": "",
 73     }
 74     Invoice_No = str(row12[2])
 75     Confirmation_Status = str(row12[11])
 76     Customer_Invoice_ID = str(row12[24])
 77     temp_dict12["Invoice No."] = Invoice_No
 78     temp_dict12["Confirmation Status"] = Confirmation_Status
 79     temp_dict12["Customer Invoice ID"] = Customer_Invoice_ID
 80     invoiceData.append(temp_dict12)
 81 print("提取invoiceExcel1内数据完成")
 82 
 83 # 提取invoiceExcel2内数据
 84 file22 = invoiceExcel2
 85 filePath22 = file22.replace("\\", "\\\\")
 86 file22 = eval(repr(filePath22).replace('\\\\', '\\'))
 87 excel22 = pd.read_excel(file22)
 88 for row22 in excel22.values:
 89     temp_dict22 = {
 90         "Invoice No.": "",
 91         "Confirmation Status": "",
 92         "Customer Invoice ID": "",
 93     }
 94     Invoice_No = str(row22[2])
 95     Confirmation_Status = str(row22[11])
 96     Customer_Invoice_ID = str(row22[24])
 97     temp_dict22["Invoice No."] = Invoice_No
 98     temp_dict22["Confirmation Status"] = Confirmation_Status
 99     temp_dict22["Customer Invoice ID"] = Customer_Invoice_ID
100     invoiceData.append(temp_dict22)
101 print("提取invoiceExcel2内数据完成")
102 
103 # 合并数据到invoiceHeadData
104 for item1 in invoiceHeadData:
105     for item2 in invoiceData:
106         if item1['InvoiceNo'] == item2['Invoice No.']:
107             item1['Confirmation Status'] = item2['Confirmation Status']
108             item1['Customer Invoice ID'] = item2['Customer Invoice ID']
109         else:
110             continue
111 print("合并数据到invoiceHeadData完成")
112 
113 # 将以上数据填入新建的Excel文件
114 folder = os.path.dirname(file12)
115 file_path = folder + "\\" + "葡萄牙税票监控" + ".xls"
116 workbook = xlwt.Workbook()
117 sheet1 = workbook.add_sheet('Sheet1')
118 index = 0
119 sheet1.write(index, 0, "Invoice No.")
120 sheet1.write(index, 1, "Local Invoice No")
121 sheet1.write(index, 2, "Invoice Date")
122 sheet1.write(index, 3, "Invoice status")
123 sheet1.write(index, 4, "Amount")
124 sheet1.write(index, 5, "GL Date")
125 sheet1.write(index, 6, "B2B Status")
126 for item in invoiceHeadData:
127     index = index + 1
128     sheet1.write(index, 0, item["InvoiceNo"])
129     sheet1.write(index, 1, item["Customer Invoice ID"])
130     sheet1.write(index, 2, item["issueDate"])
131     sheet1.write(index, 3, item["invoiceStatus"])
132     sheet1.write(index, 4, item["totalAmount"])
133     sheet1.write(index, 5, item["glDate"])
134     sheet1.write(index, 6, item["Confirmation Status"])
135 workbook.save(file_path)

 

posted @ 2022-03-31 19:45  MING5135  阅读(567)  评论(0)    收藏  举报