在一个excel文件中,存在两个sheet页,通过字段关联取值,举一反三也可以多个excel文件实现关联
需求场景:
excel表中有两个sheet页,一个是学生基本信息表,包含选课编号,另一个sheet页为选课编号和课程名,需要生成一个新sheet,包含学生姓名和选课名称表。
数据库实现:
这个在数据库里通过两张表左连接,可以实现,sql如下:
select stu.Name,curs.Course_Name from stu left join curs on stu.Course_ID = curs.Course_ID;
python实现:
在Python中实现的思路如下:
主要用到openpyxl这个库
- 第一步:封装excel处理的代码
from openpyxl import load_workbook
class HandleExcel:
def __init__(self, file_path, sheet_name=None):
# 打开一个工作簿,选定一个表单
self.wb = load_workbook(file_path)
self.sh = self.wb[sheet_name]
self.filename = file_path
self.sheetname = sheet_name
def read_titles(self):
titles = []
for item in list(self.sh.rows)[0]:
titles.append(item.value)
return titles
def read_all_datas(self):
all_datas = []
titles = self.read_titles()
for item in list(self.sh.rows)[1:]:
values = []
for val in item:
values.append(val.value)
datas = dict(zip(titles, values))
all_datas.append(datas)
return all_datas
def write_datas(self, row, column, data):
"""
写操作
:param row: 指定在某一行写
:param column: 指定在某一列写
:param data: 待写入的数据
:return:
"""
# 将数据写入到excel中,不能与读取操作公用一个Workbook对象
# 如果使用同一个Workbook对象,只能将最后一次写入成功
wb_w = load_workbook(self.filename)
if self.sheetname is None:
ws = wb_w.active
else:
ws = wb_w[self.sheetname]
ws.cell(row, column, value=data)
# 写入后save保存
wb_w.save(self.filename)
wb_w.close()
def close_file(self):
self.wb.close()
- 第二步:关联查询,并写入目标sheet
from Common.handle_excel import HandleExcel
from Common.handle_logger import logger
def test_filed_connect():
"""实现excel字段关联"""
fp = r"D:\PycharmProjects\Data_statistics\TestData\python_excel关联.xlsx"
student = HandleExcel(fp, "学生表")
course = HandleExcel(fp, "选课表")
result = HandleExcel(fp, "学生选课结果表")
stu = student.read_all_datas()
cour = course.read_all_datas()
for i in stu:
ID = i["ID"]
name = i["Name"]
cid = i["Course_ID"]
Course_ID = int(cid) - 1 # 关联获取选课表ID,excel读取行索引从1开始,而读取出来的excel表数据索引是从0开始
Course_Name = cour[Course_ID]["Course_Name"]
row = ID + 1
result.write_datas(row, 1, ID)
result.write_datas(row, 2, name)
result.write_datas(row, 3, Course_Name)
logger.info("当前执行到第{}行-学生:{} 选择的课程为:{}".format(ID, name, Course_Name))
- 执行结果:
这里介绍的是一个excel表,多个sheet页面关联查询,如果存在多个excel表,也可以直接实例化多个对象,实现该需求~