将Excel大表按某列单元格的值拆分成小表（openpyxl）

原文：Python应用： 将Excel大表按某列单元格的值拆分成小表

有时候需要将Excel大表，按某列单元格的值拆分成许多个小表。

如把年度总表按月份拆分成小表，把所有账户的流水表拆成单个账户的流水表，诸如此类。

如果是数据库倒也方便，但对Excel来说，当工作量大的时候，这无疑是件折磨人的事情。

于是尝试用Python写了一段代码，因为还在学习中，写得很糙，但调试后，感觉还能用。

生成的小表单元格格式完全是“素颜”，待有空再完善吧。

---------------------------------------------------------------------------------------------------------------------------

---

代码如下：

#!python3

# -*- coding:utf-8 -*-

# 将Excel大表按某列单元格的值拆分成小表

# 1. 首行为字段名（表头）

# 2. 表中不能有合并的单元格

# By Zero, 2023/3/22

import openpyxl

from openpyxl.utils import get_column_letter

def copyrow(sheet,rownum,fname):

    f=openpyxl.load_workbook(fname)

    if rownum==1:

        rownum2=1

    else:

        rownum2=f._sheets[0].max_row+1

        

    for col in range(1,sheet.max_column+1):

        n=get_column_letter(col)

        

        i='%s%d'%(n,rownum)#大表单元格编号

        cell1=sheet[i].value

        j='%s%d'%(n,rownum2)#小表单元格编号

        f._sheets[0][j].value=cell1

    f.save(fname)

    f.close

    return()

    

#Excel所在绝对路径

fPath='E:\\py3\\myapp\\'    

#Excel文件名

fName='test.xlsx'                  

wb=openpyxl.load_workbook(fPath+fName) 

sheet=wb['Sheet1'] #将要拆分的大表

#首行(标题行）

xh=[]

print('序号  字段（表头）\n---------------------')

for i in range(1,sheet.max_column+1):

    c=sheet.cell(1,i).value

    xh.append([i,c])

    print(i,c)

    

while True:

    x=input('\n请输入字段的序号:')

    if x.isdecimal():

        if int(x)   in range(1,sheet.max_column+1):

            break

        else:

            print('\nERROR :输入的数字超范围，请正确输入！')    

    else:

        print('\nERROR :输入的不是数字，请正确输入！')

sliceWord=xh[int(x)-1][1]   #要拆分的列

print ('\n将按字段  '+sliceWord+'  进行拆分表………………\n\n')

pieces=[]   #小表表名列表

for row in range(2,sheet.max_row +1):

    c=sheet.cell(row,int(x)).value

    if c=='#N/A' or c==None:        #处理空值或无效值

        c='NA'

    fname=fPath+'out_'+sliceWord+'_'+c+'.xlsx' #小表表名

    if c not in pieces:

        pieces.append(c)

        openpyxl.Workbook().save(fname)

        copyrow(sheet,1,fname)#复制首行（表头）

        

    copyrow(sheet,row,fname)#复制行到小表

    print ('复制第 '+str(row)+' 行到 '+fname)

print('\n-------------------拆分表已完成！-------------------')

补充：在之后的实战中，发现openpyxl模块在处理 Excel行数超过1w 的时候就力不从心了，速度会越来越慢，甚至半天不动。做了优化（参见官方文档Optimised Modes,打开文件时为只读模式）但效果甚微。

查阅了许多资料，说这就是openpyxl的短板，每次读取数据都要从首行首列开始，逐个单元格地读，意味着离单元格A1越远，花费的时间就越长。

有文章说，在遍历excel时，用ws.cell.value来遍历单元格的值速度慢，用ws.rows来遍历单元格速度更快（文见解决openpyxl读取数据量较大的excel时速度缓慢的问题_openpyxl写入excel多行非常慢_酱酱小可爱的博客-CSDN博客）。试了一下，处理6w行时，还是不尽人意。

后来又查阅了一些资料，做了一些对比，发现在处理大表时，还是用xlrd 和xlwt配合效果好一些。修改后的代码见：将Excel大表按某列单元格的值拆分成小表（xlrd与xlwt）_dlmyang的博客-CSDN博客