Test_book.xlsx是一个已存在的Excel文件,应该显示员工姓名、工时、费率和总付款。我已经完成了一部分工作,将员工姓名和工时显示在一个表格中,将员工姓名和费率显示在第二个表格中,最后在最后一个表格中显示员工姓名和总付款;然而,我无法弄清楚如何在每个表格上放置列名(员工姓名、工时、每小时费率、总付款)。
这是我一直在努力的代码:
import openpyxl
from itertools import chain
from collections import defaultdict
wb = openpyxl.load_workbook("Test_book.xlsx")
sheet=wb.get_sheet_by_name('Hours')
employee_names=[]
employee_hours=[]
for row in sheet['A']:
employee_names.append(row.value)
for row in sheet['B']:
employee_hours.append(row.value)
print(employee_names)
print(employee_hours)
my_dict=dict(zip(employee_names,employee_hours))
print(my_dict)
sheet2=wb.get_sheet_by_name('Rate')
employee_names2=[]
employee_Rate=[]
for row in sheet2['A']:
employee_names2.append(row.value)
for row in sheet2['B']:
employee_Rate.append(row.value)
print(employee_names2)
print(employee_Rate)
my_dict2=dict(zip(employee_names2,employee_Rate))
print(my_dict2)
sheet3=wb.get_sheet_by_name('payable')
sheet3rows=[]
#pulls key from dictionary, multiples it by other number
for row in sheet['A']:
if row.value in my_dict:
gross=(float(my_dict[row.value]*float(my_dict2[row.value])))
#creates list
sheet3rows.append(gross)
print(format(gross,'.2f'))
print(sheet3rows)
my_dict3=dict(zip(employee_names2,sheet3rows))
print(my_dict3)
#Print gross to payable sheet
r=1
for x in sheet3rows:
sheet3.cell(row=r,column=2).value=x
r+=1
#wb.save("Test_book.xlsx")
my_dict4 = defaultdict(list)
for k, v in chain(my_dict.items(), my_dict2.items(), my_dict3.items()):
my_dict4[k].append(v)
for k, v in my_dict4.items():
print(k, v)
从这张附加的图片中可以看出,那里没有列:
![enter image description here](https://istack.dev59.com/DEA1H.webp)