Python操作Excel之分组排序

标签：zhang   home   header   load   book   pen   hang   group   append   

功能：依照工作表中的不同部分对整张表进行分组排序

#!/usr/bin/env python
# --*-- coding:utf8 --*--
# Author: ZHangbb

import openpyxl
import time

t1 = time.time()

wb1 = openpyxl.load_workbook(r‘/home/wzr/音乐/leili.xlsx‘)
sh1 = wb1[‘诚利、科技‘]
# create a new sheet
wb1.create_sheet(‘诚利、科技-1‘)
sh2 = wb1[‘诚利、科技-1‘]
# department list
dept_list = []
# generate data of department
for cell in [col for col in sh1.columns][4]:
    dept_list.append(cell.value)
# get the unique value
dept_list = list(set(dept_list))
dept_list.sort()
dept_list.remove(‘部门‘)

# print department information
for dept in dept_list:
    print(dept, end="   ")

# write the table header to the first of the new sheet
for col in range(1, 15):
    sh2[chr(64+col)+‘1‘] = sh1[chr(64+col)+"1"].value

row_sh2 = 2
# group contents by dept & write to new sheet
for dept in dept_list:
    for i in range(2, sh1.max_row+1):
        if sh1[f"E{i}"].value == dept:
            col = 1
            for cell in [row for row in sh1.rows][i-1]:
                sh2[chr(64+col)+str(row_sh2)] = cell.value
                col += 1
            row_sh2 += 1 

# save data
wb1.save(‘/home/wzr/音乐/leili.xlsx‘)

t2 = time.time()
print(f"总共用时{int(t2-t1)}秒")

Python操作Excel之分组排序

标签：zhang   home   header   load   book   pen   hang   group   append   

原文地址：https://blog.51cto.com/3108485/2497857