对于 Excel 的操作,除了日常可以通过录制宏或者直接用 VBA代码去实现,但经过观察和测试,同执行一个方法,它的代码长度和运行的速度,远比 Python 低效。
以按列的类别拆分成多个sheets为例,假设有如图1 这样的一个工作表 Sheet 1 ,可见数据结构为 9 行 4列,以C列职称为最终拆分对象。
1. 导入目标文件
import openpyxl
workbook = openpyxl.load_workbook('xxx') # 目标excel的位置
s = workbook['Sheet1']
2. 获取目标列C的所有单元格的值,并撇除重复值。可能根据一开始对C列的观察,这短短的9行里,不难看出其实只有 “经理” 和 ” 助理“ 这两个值,那直接把这两个值作为元素写在一个 titles 的 lst 不就行了吗? 程序的原则的就是从简和方便日后维护,虽然如果直接把元素写进lst比较简单,但日后如果这列存在更多的不同元素,就要手动添加。因此,为日后方便维护,免去手动添加,通过循环取值,然后用set()函数去重,再转换回 lst, 这样 titles 的 lst 就只有 “经理” 和 “助理” 。 当然方法不是唯一的,也可以用循环加条件去实现把lst直接去重。
titles = []
for i in range(1,s.max_row): # s.max_row = 9, range(1,9) 就相当于 1,2,3,4,5,6,7,8
titles.append(s['C'][i].value) # 添加 从C2 - C9单元格的值, sC1 其实就等于 Sheet 的 C2
# s['C'] 等于获取整列数据
titles = list(set(titles)) # 用 set()函数,去重,就剩下 经理 和 助理 2个元素,再转换成 lst
3. 创建 titles_linenum 为 dict
titles_linenum = {} # 新建空白的 dict
for title in titles:
titles_linenum[title] = [1] # 设置Value初始值为1,为后面的循环做铺垫
# titles_linenum 为 {'助理': [1], '经理': [1]}
4. 获取“经理” 和“助理”所对应的行号
for i in range(1,s.max_row): # s.max_row = 9, range(1,9) 1,2,3,4,5,6,7,8
title = s['C'][i].value # 指定E列 从E2 - E9单元格的值, E1 其实就等于 sheet 的 E2
titles_linenum[title].append(i+1)
'''
当 i = 1 时, s['C'][i].value = 经理
titles_linenum['经理'].append(1+1)
titles_linenum = {'助理': [1], '经理': [1,2]}
当 i = 2 时....如此类推
titles_linenum = {'助理': [1, 3, 7, 8, 9], '经理': [1, 2, 4, 5, 6]}
'''
5. 最后通过三循环嵌套,实现对原工作表的遍历操作。
for depart in list(departs_linenum.keys()): # loop1
new_sheet = workbook.create_sheet(depart)
i = 1
for linenum in departs_linenum[depart]: # loop2
for j in range(s.max_column): # loop3
new_sheet.cell(i,j+1).value = s[linenum][j].value
i +=1es_linenum[title].append(i+1)
'''
loop 1: 当 depart = 助理时,
创建新的sheet 名为 助理
departs_linenum[depart] 想当于 [1,3,7,8,9], linenum = 1,3,7,8,9
loop2: 当 linenum = 1时,
loop3: range(s.max_column) = range(9), j = 0,1,2,3,4,5,6,7,8
当 j = 0 时,
new_sheet.cell(i,j+1).value = cell(1,0+1) 即1行1列的单元格(A1)的值 = s[1][0].value
s[1][0] 相当于取 原sheet 第1行,第1列的值,就是'姓名',[0] 表示第1列
所以助理这张sheet 的 A1 = 姓名
当 j = 1 时,
new_sheet.cell(i,j+1).value = cell(1,1+1) 即 1行2列的单元格(B1)的值 = s[1][1].value = 部门
如此类推, 当完成 loop3 当 j=0到8 的这轮循环后,打印出 助理 sheet 的第1行 4个格单元格内容(即:表头)
然后执行 i+=1
因此当执行 loop2 第2轮循环时 (即当linenum = 3时), i = i + 1 = 1 + 1 = 2
loop2: 当 linenum = 3时,
loop3 : 当 j = 0时,
new_sheet.cell(i,j+1).value = cell(2,0+1) 即2行1列的单元格(A2)的值 = s[3][0].value
s[3][0] 相当于取 原sheet 第3行,第1列的值, 就是姓名"B",[0] 表示第1列
当 j = 1时,
new_sheet.cell(i,j+1).value = cell(2,1+1) 即2行2列的单元格(B2)的值 = s[3][1].value
s[3][1] 相当于取 原sheet 第3行,第2列的值, 就是部门的"销售部", [1] 表示第2列
如此类推, 当完成 loop3 当 j=0到8 的这一轮循环后,打印出 助理 sheet 的第2行 4个格单元格内容
之后的也是同理的循环,不逐一举例了
'''
6. 保存文件, 拆分结果如 图2,图3
workbook.save('xxx.xlsx') # 保存文件所在的指定路径
图1
图2
图3