7.1 编写程序,随机生成员工月奖金文件monthlyBonus.xslx。文件包含职工号、姓名和奖金;记录条数是10到20之间的随机数;职工号是长度为四的字符串,依次为e001、e002、…;姓名为随机的中国人名字;奖金为0到100000的随机整数。(10分)
7.2 写出程序,完成如下功能(20分):
(1)连接MongoDB,指定test数据库和Employees数据集合,把monthlyBonus.xslx中记录插入到Employees数据集合。
(2)创建一个关系数据库表Employees,并把monthlyBonus.xslx中记录插入到该表。
7.3 编写程序,修改books.docx:把记录的编号依次修改为b001、b002、…,去掉各条记录的书名的书引号。(10分)
7.4假定一个单位有3个部门,每个部门有登记本部门员工月奖金的xslx文件。月末每个部门统计本部门的奖金总额和人数,然后发送给单位,单位计算出本单位的平均奖金,并返回给各部门,各部门再计算每个员工的奖金与单位平均奖金的比例,并将结果写入部门员工月奖金文件。编写程序,完成如下任务(25分):
(1)生成3个部门的员工月奖金文件monthlyBonus1.xslx、monthlyBonus2.xslx和monthlyBonus.xslx3,每个文件包含职工号、姓名、奖金和占比4个列,占比列 将用于填写: 每个员工的奖金/单位平均奖金。其他要求与7.1相同。(5分)
(2)创建3个线程,每个线程负责计算本部门的奖金总额和人数,等待单位的平均奖金被计算出来以后,再计算每个员工的奖金与单位平均奖金之比,并将计算结果写入相应文件。
(3)计算单位的平均奖金。(10分)
7.5 假定道路分街道、高速公路、铁道和步行街,交通工具分小货车、小汽车、巴士和火车,只有火车才能在铁道上通行,只有小货车才能在步行街上通行。请用面向对象程序设计方法设计(要求使用抽象类、继承、多态和某种设计模式)(25分):
(1)交通工具类及其子类小货车、小汽车、巴士和火车。
(2)道路类及其子类步行街、街道、高速公路和铁道。
(3)说明使用的设计模式,并定义不同道路和车辆,对上述类进行测试,显示不同车辆在不同道路上的通行情况。
7.6给出截屏,展示项目结构和main.py 、requirements.txt和 readme.txt文件内容(10分)
#7.1题代码:
import openpyxl
import random
# 生成随机的中国人名字
def generate_chinese_name():
first_names = ["张", "王", "李", "赵", "刘", "陈", "杨", "黄", "周", "吴"]
last_names = ["伟", "芳", "娜", "秀英", "敏", "静", "丽", "强", "磊", "军"]
return random.choice(first_names) + random.choice(last_names)
# 生成员工月奖金文件
def generate_monthly_bonus_file():
wb = openpyxl.Workbook()
sheet = wb.active
sheet.title = "Monthly Bonus"
num_records = random.randint(10, 20)
for i in range(num_records):
employee_id = "e" + str(i+1).zfill(3)
employee_name = generate_chinese_name()
bonus = random.randint(0, 100000)
sheet.cell(row=i+1, column=1, value=employee_id)
sheet.cell(row=i+1, column=2, value=employee_name)
sheet.cell(row=i+1, column=3, value=bonus)
wb.save("monthlyBonus.xlsx")
generate_monthly_bonus_file()
#7.2题代码:
import pymongo
import pandas as pd
import openpyxl
import random
import sqlite3
# 生成随机的中国人名字
def generate_chinese_name():
first_names = ["张", "王", "李", "赵", "刘", "陈", "杨", "黄", "周", "吴"]
last_names = ["伟", "芳", "娜", "秀英", "敏", "静", "丽", "强", "磊", "军"]
return random.choice(first_names) + random.choice(last_names)
# 生成员工月奖金文件
def generate_monthly_bonus_file():
records = []
num_records = random.randint(10, 20)
for i in range(num_records):
employee_id = "e" + str(i+1).zfill(3)
employee_name = generate_chinese_name()
bonus = random.randint(0, 100000)
records.append((employee_id, employee_name, bonus))
df = pd.DataFrame(records, columns=["EmployeeID", "Name", "Bonus"])
df.to_excel("monthlyBonus.xlsx", index=False)
# 连接MongoDB并插入记录
def insert_into_mongodb():
client = pymongo.MongoClient("mongodb://localhost:27017/")
db = client["test"]
collection = db["Employees"]
df = pd.read_excel("monthlyBonus.xlsx")
records = df.to_dict(orient="records")
collection.insert_many(records)
# 创建关系数据库表并插入记录
def create_and_insert_into_sqlite():
conn = sqlite3.connect('employees.db')
c = conn.cursor()
c.execute('''CREATE TABLE Employees
(EmployeeID text, Name text, Bonus integer)''')
df = pd.read_excel("monthlyBonus.xlsx")
df.to_sql('Employees', conn, if_exists='replace', index=False)
conn.commit()
conn.close()
# 生成Excel文件
generate_monthly_bonus_file()
# 插入记录到MongoDB
insert_into_mongodb()
# 创建关系数据库表并插入记录
create_and_insert_into_sqlite()
#7.3题代码:
import docx
def modify_docx_file(file_name):
doc = docx.Document(file_name)
# 修改记录编号和去掉书名的书名号
for row in doc.tables[0].rows[1:]:
# 修改记录编号
row.cells[0].text = f"b{int(row.cells[0].text[1:]):03d}"
# 去掉书名的书名号
row.cells[1].text = row.cells[1].text.replace("《", "").replace("》", "")
# 保存修改后的文档
doc.save("modified_books.docx")
modify_docx_file("books.docx")
#7.4题代码:
import pandas as pd
import threading
import random
import string
# 生成随机的中国人名字
def generate_chinese_name():
first_names = ["赵", "钱", "孙", "李", "周", "吴", "郑", "王", "冯", "陈"]
last_names = ["芳", "婷", "梅", "华", "敏", "丽", "萍", "玲", "秀", "静"]
return random.choice(first_names) + random.choice(last_names)
# 生成随机的员工月奖金文件
def generate_monthly_bonus_file(file_name):
num_records = random.randint(10, 20)
employee_ids = [f"e{i+1:03d}" for i in range(num_records)]
names = [generate_chinese_name() for _ in range(num_records)]
bonuses = [random.randint(0, 100000) for _ in range(num_records)]
data = {"职工号": employee_ids, "姓名": names, "奖金": bonuses}
df = pd.DataFrame(data)
df.to_excel(file_name, index=False)
# 生成3个部门的员工月奖金文件
for i in range(3):
file_name = f"monthlyBonus{i + 1}.xlsx"
generate_monthly_bonus_file(file_name)
# 加载数据
department_data = []
for i in range(3):
department = pd.read_excel(f"monthlyBonus{i + 1}.xlsx")
department_data.append(department)
# 计算每个部门的奖金总额和员工人数
department_totals = []
for department in department_data:
total_bonus = department["奖金"].sum()
total_people = len(department)
department_totals.append((total_bonus, total_people))
# 计算单位的平均奖金
total_bonus_all_departments = sum(total_bonus for total_bonus, _ in department_totals)
total_people_all_departments = sum(total_people for _, total_people in department_totals)
if total_people_all_departments != 0:
average_bonus = total_bonus_all_departments / total_people_all_departments
else:
average_bonus = 0
# 计算每个员工的奖金与单位平均奖金之比,并将结果写入相应文件
def calculate_and_write_ratio(department_index):
department = department_data[department_index]
department["占比"] = department["奖金"] / average_bonus
department.to_excel(f"monthlyBonus{department_index + 1}.xlsx", index=False)
# 创建3个线程,每个线程计算一个部门的奖金与单位平均奖金之比
threads = []
for i in range(3):
thread = threading.Thread(target=calculate_and_write_ratio, args=(i,))
threads.append(thread)
thread.start()
# 等待所有线程完成
for thread in threads:
thread.join()
# 打印单位的平均奖金
print(f"单位的平均奖金为: {average_bonus}")
#7.5题代码:
from abc import ABC, abstractmethod
# 抽象类:交通工具
class Vehicle(ABC):
def __init__(self, name):
self.name = name
@abstractmethod
def can_travel_on(self, road):
pass
# 道路策略接口
class RoadStrategy(ABC):
@abstractmethod
def can_travel(self, vehicle):
pass
# 街道
class Street(RoadStrategy):
def can_travel(self, vehicle):
return isinstance(vehicle, Car) or isinstance(vehicle, Bus)
# 高速公路
class Highway(RoadStrategy):
def can_travel(self, vehicle):
return isinstance(vehicle, Car) or isinstance(vehicle, Bus)
# 铁道
class Railway(RoadStrategy):
def can_travel(self, vehicle):
return isinstance(vehicle, Train)
# 步行街
class PedestrianStreet(RoadStrategy):
def can_travel(self, vehicle):
return isinstance(vehicle, Truck)
# 小货车
class Truck(Vehicle):
def can_travel_on(self, road):
return road.can_travel(self)
# 小汽车
class Car(Vehicle):
def can_travel_on(self, road):
return road.can_travel(self)
# 巴士
class Bus(Vehicle):
def can_travel_on(self, road):
return road.can_travel(self)
# 火车
class Train(Vehicle):
def can_travel_on(self, road):
return road.can_travel(self)
# 测试
roads = [Street(), Highway(), Railway(), PedestrianStreet()]
vehicles = [Truck("Truck"), Car("Car"), Bus("Bus"), Train("Train")]
for vehicle in vehicles:
for road in roads:
if vehicle.can_travel_on(road):
print(f"{vehicle.name} can travel on {type(road).__name__}")