1.统计员工信息表中男生和女生的数量分别是多少
- wb1 = openpyxl.load_workbook("files/员工信息.xlsx")
- yuanGong = wb1["员工信息"]
- nan = 0
- nv = 0
- mr = yuanGong.max_row
- for row in range(2,mr+1):
- if yuanGong.cell(row,3).value == "男":
- nan += 1
- else:
- nv += 1
- print(f"男员工的数量是{nan},女员工的数量是{nv}")
2.在销售数据文件中的data表的后面添加‘销售额’ 对应的列
- wb = openpyxl.load_workbook('files/2020年销售数据.xlsx')
- sheet1 = wb['data']
- mr1 = sheet1.max_row
- mc1 = sheet1.max_column
-
- sheet1.cell(2,8,"销售额")
-
- for row in range(3,mr1+1):
- shoujia = int(sheet1.cell(row,6).value)
- shuliang = int(sheet1.cell(row,7).value)
- xiaoshoue = shoujia * shuliang
- sheet1.cell(row,8).value = xiaoshoue
3.提取销售数据文件中data中和’八匹马’相关的所有数据保存到’八匹马’对应的工作表中
- if "八匹马" not in sheet1:
- sheet3 = wb.create_sheet('八匹马')
- row2 = 0
- for row1 in range(2, mr1+1):
- if row1 == 2 or sheet1.cell(row1, 5).value == '八匹马':
- row2 += 1
- for col1 in range(1, mc1+1):
- value = sheet1.cell(row1, col1).value
- sheet3.cell(row2, col1, value)
-
-
-
- wb.save('files/2020年销售数据.xlsx')
