现有如下选课学生名单
![](https://i-blog.csdnimg.cn/blog_migrate/db11c40baed7147972d7eea6f01a409b.png)
问题:将学生名单按“学生班级“列拆分为不同的Excel文件,并保保其格式,即类似如下的格式
![](https://i-blog.csdnimg.cn/blog_migrate/e09220a433e338afa71f4d34a114d33a.png)
![](https://i-blog.csdnimg.cn/blog_migrate/9746a727136e165e9d3bd656158ede67.png)
附源码:
运行环境:Python 3.12 + openpyxl 3.1.2 + windows 11 专业版 21H2,
import os.path
from openpyxl import Workbook, load_workbook
from copy import copy
wb_ori = load_workbook("选课学生名单.xlsx")
ws_ori = wb_ori["Sheet1"]
# 设置单元格格式
def setStyle(ori_cell, dist_cell):
if ori_cell.has_style:
dist_cell.font = copy(ori_cell.font)
dist_cell.border = copy(ori_cell.border)
dist_cell.border = copy(ori_cell.border)
dist_cell.fill = copy(ori_cell.fill)
dist_cell.number_format = copy(ori_cell.number_format)
dist_cell.alignment = copy(ori_cell.alignment)
# 有效数据从第3行开始,第1行为表格标题,第2行为表头(即每列放什么内容)
# 下一行的range从3开始,以第5列的信息作为单独保存文件的名字(分组)
for i in range(3, ws_ori.max_row + 1):
filename_dist = "d:/tmp/拆分的文件/" + ws_ori.cell(i, 5).value + ".xlsx"
if os.path.isfile(filename_dist):
wb_dist = load_workbook(filename_dist)
ws_dist = wb_dist.worksheets[0]
else:
# 如果是新建文件,则把第1行和第2行都设置成想要的样式,包括列宽
# 以及第1行的单独格合并
wb_dist = Workbook()
ws_dist = wb_dist.worksheets[0]
ws_dist.cell(1,1).value = ws_ori.cell(1,1).value
# 合并第1行中的单元格
ws_dist.merge_cells(range_string='A1:E1')
setStyle(ws_ori.cell(1, 1), ws_dist.cell(1, 1))
ws_dist.row_dimensions[1].height = ws_ori.row_dimensions[1].height
# 设置第2行
for cl in range(1, ws_ori.max_column + 1):
ws_dist.cell(2, cl).value = ws_ori.cell(2, cl).value
ws_dist.column_dimensions[chr(65 + cl - 1)].width = ws_ori.column_dimensions[chr(65 + cl - 1)].width
setStyle(ws_ori.cell(2, cl), ws_dist.cell(2, cl))
ws_dist.row_dimensions[2].height = ws_ori.row_dimensions[2].height
# 获取已经存放了数据的行号
maxrow1 = ws_dist.max_row
id = maxrow1 - 1
# 逐一把当前列的数据填写到目标文件中
for cl in range(1, ws_ori.max_column + 1):
ws_dist.cell(maxrow1 + 1, cl).value = ws_ori.cell(i, cl).value
setStyle(ws_ori.cell(i, cl), ws_dist.cell(maxrow1 + 1, cl))
ws_dist.row_dimensions[maxrow1 + 1].height = ws_ori.row_dimensions[i].height
ws_dist.cell(maxrow1 + 1, 1).value = id
wb_dist.save(filename_dist)
wb_dist.close()
print(ws_ori.cell(i, 5).value)
wb_ori.close()