python解析excel文件多级标题嵌套行

一、 本文实现内容
用于解析excel2013数据文件的标题行，有下面需求
1、 标题行可能处在页面的任何位置
2、 标题行只能是字符串类型，最少有2个或以上字段
3、 标题行上边，或左边可能有任意多个空行、空列
4、 标题行可能是多行，一个字段可能有N级子字段，可以是任意复杂的数据报表标题，例如财务数据的损益表
5、 标题行不能独立存在，最少有一个数据行，最少有一个字段不为空
6、 页面可能任何多个数据表格，可以自动识别标题行与数据行

二、 说明
限于篇幅，本文只作标题行解析，数据行解析更加复杂，见另一篇文档。
三、 实现原理
1、 采用xlrd工具读取excel2013的xlsx格式数据
2、 excel格子的数据类型有5种，无论是那一种工具都相同，包括java的poi
1) ctype=0
empty,为空
2) ctype=1
字符串,要能识别空字符串
3) ctype=2
数字，所有整数自动为浮点数xxx.00，读取回来要转换
空格，可能读取为0.0,需要自动识别，并转换
4) ctype=3
为日期，返回的数据为xxxx.yyy
需要采用python的datetime进行自动转换，可能有时间，也可能没有时间，需要自动识别
5) ctype=4
boolean类型
6) ctype=5
error类型

3、 多级字段，通过递归实现
4、 多级字段显示输出
覆盖str,并递归实现
要按逐级显示输出，类似xml数据树输出
5、 先识别标题行，后识别数据行
标题行数据格数据类型ctype=1，采用str.strip()清首尾空格
一个标题字段可能占用N行，M列，需要自动识别单元格合并

四、 最终解析效果如下

runfile('D:/python/workspace/excel2013/ParseExcelTitleRow.py', wdir='D:/python/workspace/excel2013')
tirowNum=5 endCol=31 ncols=31
字段序号=1
field:字段A,fieldPos=[5, 5, 5, 1]
字段序号=2
field:字段B,fieldPos=[5, 6, 5, 1]
字段序号=3
field:字段1,fieldPos=[5, 7, 5, 5]
字段序号=4
field:字段2,fieldPos=[5, 12, 1, 6]
subField,field:字段21,fieldPos=[6, 12, 1, 4]
subField,field:字211,fieldPos=[7, 12, 1, 2]
subField,field:字2111,fieldPos=[8, 12, 2, 1]
subField,field:字2112,fieldPos=[8, 13, 2, 1]
subField,field:字212,fieldPos=[7, 14, 3, 1]
subField,field:字213,fieldPos=[7, 15, 3, 1]
subField,field:字22,fieldPos=[6, 16, 4, 1]
subField,field:字段23,fieldPos=[6, 17, 4, 1]
字段序号=5
field:字段3,fieldPos=[5, 18, 1, 2]
subField,field:字段31,fieldPos=[6, 18, 4, 1]
subField,field:字段32,fieldPos=[6, 19, 4, 1]
字段序号=6
field:字段3A,fieldPos=[5, 20, 5, 1]
字段序号=7
field:字段3B,fieldPos=[5, 21, 5, 1]
字段序号=8
field:字段4,fieldPos=[5, 22, 1, 7]
subField,field:字段41,fieldPos=[6, 22, 1, 4]
subField,field:字段411,fieldPos=[7, 22, 1, 3]
subField,field:字段4111,fieldPos=[8, 22, 1, 2]
subField,field:字段41111,fieldPos=[9, 22, 1, 1]
subField,field:字段41112,fieldPos=[9, 23, 1, 1]
subField,field:字段4112,fieldPos=[8, 24, 2, 1]
subField,field:字段412,fieldPos=[7, 25, 3, 1]
subField,field:字段42,fieldPos=[6, 26, 1, 2]
subField,field:字段413,fieldPos=[7, 26, 3, 2]
subField,field:字段43,fieldPos=[6, 28, 4, 1]
字段序号=9
field:字段5,fieldPos=[5, 29, 5, 1]
字段序号=10
field:字段6,fieldPos=[5, 30, 5, 1]
下面输出统计可用字段
0 col=5 fieldName=字段A
1 col=6 fieldName=字段B
2 col=7 fieldName=字段1
3 col=12 fieldName=字2111
4 col=13 fieldName=字2112
5 col=14 fieldName=字212
6 col=15 fieldName=字213
7 col=16 fieldName=字22
8 col=17 fieldName=字段23
9 col=18 fieldName=字段31
10 col=19 fieldName=字段32
11 col=20 fieldName=字段3A
12 col=21 fieldName=字段3B
13 col=22 fieldName=字段41111
14 col=23 fieldName=字段41112
15 col=24 fieldName=字段4112
16 col=25 fieldName=字段412
17 col=26 fieldName=字段413
18 col=28 fieldName=字段43
19 col=29 fieldName=字段5
20 col=30 fieldName=字段6

五、 全部源代码，包括测试代码

# -*- coding: utf-8 -*-
"""
2018-05-25 15:52:39
作者: 刘明
"""
import xlrd
from datetime import date,datetime
from xlrd import xldate_as_tuple
'''
merged_cells返回的这四个参数的含义是：
(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样，即(1, 3, 4, 5)的含义是：第1到2行（不包括3）合并，(7, 8, 2, 5)的含义是：第2到4列合并。
'''
class FieldSchema:
tabNum=0 #每次求一个字段时，将此值清0
field='' #字段名称
fieldPos=[-1,-1,-1,-1] #(rowStart,colStart,rowNum,colNum)
subFields=None
#一个字段，可能有多个子字段，这是一个子标题数组, 这是一个类嵌套，可以生成多级子标题，用类嵌套实现
#下面是一个递归实现
'''
最终输出效果如下，子字段平级垂直方向左边对齐，下一级子字段，往内部缩进入几个空格，可以无穷多级子字段，每个子字段有N个直接子字段
下面输出要out()函数配合
#下面是字段显示生成原理
field:1-字段1,fieldPos=(2, 3, 3, 2)
{1}subField,field:11-字段11,fieldPos=(11, 111, 3, 2)
{2}subField,field:111-字段111,fieldPos=(111, 111, 3, 2)
{2}subField,field:112-字段112,fieldPos=(112, 112, 3, 2)
{3}subField,field:1121-字段1121,fieldPos=(1121, 1121, 3, 2)
{3}subField,field:1122-字段1122,fieldPos=(1122, 1122, 3, 2)
{3}subField,field:1123-字段1123,fieldPos=(-1, -1, -1, -1)
{3}subField,field:1124-字段1124,fieldPos=(1121, 1121, 3, 2)
{2}subField,field:111-字段111,fieldPos=(111, 111, 3, 2)
{1}subField,field:12-字段12,fieldPos=(11, 111, 3, 2)
{2}subField,field:121-字段121,fieldPos=(121, 121, 3, 2)
{2}subField,field:122-字段122,fieldPos=(122, 122, 3, 2)
{2}subField,field:123-字段123,fieldPos=(123, 123, 3, 2)
{2}subField,field:124-字段124,fieldPos=(124, 124, 3, 2)
{1}subField,field:13-字段13,fieldPos=(11, 111, 3, 2)
{2}subField,field:2-字段2,fieldPos=(2, 4, -1, -1)
{3}subField,field:3-手机号,fieldPos=(3, 5, 2, 1)
{4}subField,field:4-微信号,fieldPos=(3, 6, 1, 1)
#下面是字段显示生成结果
field:1-字段1,fieldPos=(2, 3, 3, 2)
subField,field:11-字段11,fieldPos=(11, 111, 3, 2)
subField,field:111-字段111,fieldPos=(111, 111, 3, 2)
subField,field:112-字段112,fieldPos=(112, 112, 3, 2)
subField,field:1121-字段1121,fieldPos=(1121, 1121, 3, 2)
subField,field:1122-字段1122,fieldPos=(1122, 1122, 3, 2)
subField,field:1123-字段1123,fieldPos=(-1, -1, -1, -1)
subField,field:1124-字段1124,fieldPos=(1121, 1121, 3, 2)
subField,field:111-字段111,fieldPos=(111, 111, 3, 2)
subField,field:12-字段12,fieldPos=(11, 111, 3, 2)
subField,field:121-字段121,fieldPos=(121, 121, 3, 2)
subField,field:122-字段122,fieldPos=(122, 122, 3, 2)
subField,field:123-字段123,fieldPos=(123, 123, 3, 2)
subField,field:124-字段124,fieldPos=(124, 124, 3, 2)
subField,field:13-字段13,fieldPos=(11, 111, 3, 2)
subField,field:2-字段2,fieldPos=(2, 4, -1, -1)
subField,field:3-手机号,fieldPos=(3, 5, 2, 1)
subField,field:4-微信号,fieldPos=(3, 6, 1, 1)
'''
def __str__(self,n=0):
s= str('field:%s,fieldPos=%s'%(self.field,str(self.fieldPos)))
n=n+1 #下一级子字段，再缩进
FieldSchema.tabNum=max(n,FieldSchema.tabNum)
if(self.subFields!=None):
for sub in self.subFields:
s1=sub.__str__(n)
s+='n{%d}subField,%s'%(n,s1)
#平级子字段，有相同的缩进
#print('n=%d,field=%s,s=%s'%(n,self.field,s))

return s
#打印输出当前字段，及所有子字段的全部输出，每下一级子字段，都要加一个制表符
#下面用到了动态参数的动态参数
def out(self):
s1=str(self)
#下面生成字符中format的动态参数的动态参数，str.format(s1,s2,s3),用*rn,或*tp表示
s=''
rn=[]
for i in range(0,FieldSchema.tabNum):
rn.append(s)
s+="t"
tp=tuple(rn)
#s2=s1.format(*tp) #OK 
s2=s1.format(*rn) #OK 
print(s2)
#字段多级嵌套测试
def test():
FieldSchema.tabNum=0
f1=FieldSchema()
f1.field='1-字段1'
f1.fieldPos=(2,3,3,2)
f11=FieldSchema()
f11.field='11-字段11'
f11.fieldPos=(11,111,3,2)
f111=FieldSchema()
f111.field='111-字段111'
f111.fieldPos=(111,111,3,2)
f112=FieldSchema()
f112.field='112-字段112'
f112.fieldPos=(112,112,3,2)
f11.subFields=[f111,f112]
f1121=FieldSchema()
f1121.field='1121-字段1121'
f1121.fieldPos=(1121,1121,3,2)
f1122=FieldSchema()
f1122.field='1122-字段1122'
f1122.fieldPos=(1122,1122,3,2)
f1123=FieldSchema()
f1123.field='1123-字段1123'
f1124=FieldSchema()
f1124.field='1124-字段1124'
f1124.fieldPos=(1121,1121,3,2)
f112.subFields=[f1121,f1122,f1123,f1124]
f113=FieldSchema()
f113.field='111-字段111'
f113.fieldPos=(111,111,3,2)
subFields=[f111,f112,f113]
f11.subFields=subFields
f12=FieldSchema()
f12.field='12-字段12'
f12.fieldPos=(11,111,3,2)
f121=FieldSchema()
f121.field='121-字段121'
f121.fieldPos=(121,121,3,2)
f122=FieldSchema()
f122.field='122-字段122'
f122.fieldPos=(122,122,3,2)
f123=FieldSchema()
f123.field='123-字段123'
f123.fieldPos=(123,123,3,2)
f124=FieldSchema()
f124.field='124-字段124'
f124.fieldPos=(124,124,3,2)
f12.subFields=[f121,f122,f123,f124]
f13=FieldSchema()
f13.field='13-字段13'
f13.fieldPos=(11,111,3,2)
subFields=[]
subFields.append(f11)
subFields.append(f12)
subFields.append(f13)
f1.subFields=subFields
f2=FieldSchema()
f2.field='2-字段2'
f2.fieldPos=(2,4,-1,-1)
f13.subFields=[f2]
f3=FieldSchema()
f3.field='3-手机号'
f3.fieldPos=(3,5,2,1)
f2.subFields=[f3]
f4=FieldSchema()
f4.field='4-微信号'
f4.fieldPos=(3,6,1,1)
f3.subFields=[f4]
print(f1)
f1.out()
f1.out()
f2.out()
print(f1.tabNum)
print(FieldSchema.tabNum)
print(f1)
print(f2)
print(f3)
print(f4)
print(f1.__dict__)
s1=str(f1)
s1=str(f2)
s1=str(f3)
#下面测试动态参数的动态参数
class ParseExcel:
posSchema=None
def __init__(self,xlsxFile,sheetName):
xlsxFile=xlsxFile.strip()
sheetName=sheetName.strip()
workbook = xlrd.open_workbook(xlsxFile)
self.sheet = workbook.sheet_by_name(sheetName)
self.nrows=self.sheet.nrows
self.ncols=self.sheet.ncols
self.mgcells=self.sheet.merged_cells
check=self.parseTitleRow()
if(check==-1):
return
def outFields(self):
if self.posSchema==None:
print('outFields--字段标题不存在。。。')
return
fields=self.posSchema['fields']
for i,f in enumerate(fields):
print('字段序号=%d'%(i+1))
f.out()
print('下面输出统计可用字段')
colfields=self.posSchema['colFields']
keys=colfields.keys()
for i,key in enumerate( keys):
field=colfields[key]
print(i,'col=%d'%key,'fieldName=%s'%field.field)
'''
for i,field in enumerate(colfields):
print('index=%d'%i,field)
'''
#解析标题行字段结构
def parseTitleRow(self):
tirowStart=-1
ticolStart=-1
for n in range(0,self.nrows):
ticolStart=self.checkIsTitleRow(n)
#print(n,check)
if(ticolStart!=-1):
tirowStart=n
break
if ticolStart==-1:
print('parseTitleRow--无字段标题，不作数据解析')
return -1
pass
#print("tirowstart=%d,ticolStart=%d"%(tirowStart,ticolStart)) 
#rowvalues=sheet.row_values(titilerowStart)
#cols=sheet.ncols
tirow=self.getTileRowNum(tirowStart,ticolStart)#返回（标行占用行数，最后一个字段列序号+1）
tirowNum=tirow[0]#返回标行占用行数
endFieldCol=tirow[1]#返回标行最后一个字段开序号+1
print('tirowNum=%d'%tirowNum,'endCol=%d'%endFieldCol,'ncols=%d'%self.ncols)
#{'title':(标题开始行，标题开始列，标题行数,最后一个字段列序号+1==ncols,如果最后没有数据列时)，’data‘:(数据开始行，数据开始列)))
self.posSchema={'title':(tirowStart,ticolStart,tirowNum,endFieldCol),
'colFields':{}, #最终字段列序号，{列序号1：field1,列序号2：field2,...},用于行方向有单无格合并时，最后一行的数据统计
'data':(tirowStart+tirowNum,ticolStart),
'fields':[]
}
fields=[]
colStep=0
while ticolStart+colStep<self.ncols:
col=ticolStart+colStep
fieldName=self.getFieldName(tirowStart,col)
if(len(fieldName)<2): #标题字段最少2个字符
break
#字段处理完成
#检查标题字段是否合并单元格
field=self.parseOneCellField(tirowStart,col)
fields.append(field)
#print('ticolStart=',len(fields),field)
check=self.checkmerg(tirowStart,col)
#print(tirowStart,col,check)
if(check==-1): #字段无合并单元格
colStep+=1
continue
#处理合并单元格字段,(row,row_range,col,col_range),其中[row,row_range)包括row,不包括row_range,col也是一样，即(1, 3, 4, 5)的含义是：第1到2行（不包括3）合并，(7, 8, 2, 5)的含义是：第2到4列合并。
mgcell=self.mgcells[check]
#检查是否占用整列合并单元格
colNum=mgcell[3]-mgcell[2]
colStep+=colNum
#print('end --fields=',fields)

self.posSchema['fields']=fields
def getFieldName(self,row,col):
fieldName=self.sheet.cell(row,col).value
fieldName=str(fieldName).strip()
fieldName=fieldName.replace('n','')
return fieldName
# 解析水平方向一个标题字段，可能用嵌套，返回一个 FieldSchema,可能有子字段
#下面是一个递归处理
def parseOneCellField(self,tirow,ticol):
field=FieldSchema()
field.field=self.getFieldName(tirow,ticol)
field.fieldPos=[tirow,ticol,1,1]
check=self.checkmerg(tirow,ticol)
if(check==-1): #字段无合并单元格
self.posSchema['colFields'][ticol]=field #最终字段
return field
#处理子字段情况
mgcell=self.mgcells[check]
#检查是否占用整列合并单元格
tirowStart=self.posSchema['title'][0]
ticolStart=self.posSchema['title'][1]
tirowNum=
self.posSchema['title'][2]
rowstep=tirow-tirowStart #如果无子字段时，这个值为0，如果有子字段，则不为0
rowNum=mgcell[1]-mgcell[0]
colNum=mgcell[3]-mgcell[2]
field.fieldPos[2]=rowNum
#占用行数
field.fieldPos[3]=colNum #占用列数
if rowstep+rowNum==tirowNum: #如果一个标题行占用整列所有行,或余下的所有行，则只可能是一个最小字段单元，无子字段，但这个字段可能占用多行，[学生成绩记录.xlsx,专业能力-->通用能力，核心能力]
self.posSchema['colFields'][ticol]=field #最终字段
return field
#下面求子字段，先下一个格子行，再水平
#先扫描垂直方向，后扫描水平方向,但不包括当前字段自己所在行
nextRow=tirow+rowNum #指向标题字段下一行位置，可能要跨过多行
fieldrow=nextRow
nextcolIndex=0
subFields=[] #一个字段，可能有多个并行子字段
while nextcolIndex<colNum:#水平方向,求出所有直接的子字段
fieldcol=ticol+nextcolIndex
subField=self.parseOneCellField(fieldrow,fieldcol) #递归方法求子字段
subFields.append(subField)
check2=self.checkmerg(fieldrow,fieldcol)
if(check2==-1):
nextcolIndex=nextcolIndex+1
continue
mgcell=self.mgcells[check2]
nextcolIndex+=mgcell[3]-mgcell[2]
field.subFields=subFields
return field
def checkEmptyRow(self,rowIndex,startcol=0,endcol=-1):
if endcol==-1:
endcol=self.ncols
col=startcol
while col <endcol:
cell=sheet.cell(rowIndex,col)
ctype=cell.ctype
value=cell.value
if ctype==1:
#string type
value=value.strip()
if(len(value)>0):
return True
elif ctype==2: # number type
if( float(value)%1!=0.0):
return False
elif len(str(value))>0:
#date ,true/false,error
return False
check=self.checkmerg(rowIndex,col)
if(check==-1):
col+=1
else:
mgcell=self.mgcells[check]
col+=mgcell[3]-mgcell[2]
return True
#检查某一行是否标题行，最少有两个字段不为空，并且每个字段的名称的长度要>=2
#如果是标题行，则返回标题开始列号
def checkIsTitleRow(self,rowIndex):
notEmptyRowNum=0
col=0
ticolStart=-1
while col<self.ncols:
#print(rowIndex,col,notEmptyRowNum)
cell=self.sheet.cell(rowIndex,col)
value=cell.value
ctype=cell.ctype
if ctype==1: #只有字符串类型时，才能作为标题字段
value=value.strip()
else:
value=''
pass
if(len(value)>1):
notEmptyRowNum+=1
elif notEmptyRowNum==1:
notEmptyRowNum=0
ticolStart=-1
if(notEmptyRowNum==1):
ticolStart=col
check=self.checkmerg(rowIndex,col)
if(check==-1):
col+=1
else:
mgcell=self.mgcells[check]
col+=mgcell[3]-mgcell[2]
if notEmptyRowNum<2 or rowIndex+1==self.nrows:
#无两个连续字符串字段，或标题是最后一行
return -1
#检查标题行的下一行数据不能为空
nullCellNum=0
col=ticolStart
while col<self.ncols:
value=self.sheet.cell(rowIndex+1,col).value
value=str(value).strip()
if( len(value)>0):
nullCellNum+=1
check=self.checkmerg(rowIndex+1,col)
if(check==-1):
col+=1
else:
mgcell=self.mgcells[check]
col+=mgcell[3]-mgcell[2]
if(nullCellNum==0):
return -1
return ticolStart
#返回(标题行占用行数 ,及最后一列序号+1)
def getTileRowNum(self,tirow,ticol):
titleRowNum=-1
col=ticol
endcol=-1
while col<self.ncols:
fieldName=self.getFieldName(tirow,col)
if( len(fieldName)<2): #最后一个字段
break
check=self.checkmerg(tirow,col)
endcol=col
nowrowNum=1
if(check==-1):
col+=1
else:
mgcell=self.mgcells[check]
col+=mgcell[3]-mgcell[2]
nowrowNum=mgcell[1]-mgcell[0]
titleRowNum=max(nowrowNum,titleRowNum)
#返回最后一个单元列+1
check=self.checkmerg(tirow,endcol)
if(check==-1):
endcol+=1
else:
endcol+=mgcell[3]-mgcell[2]
return (titleRowNum,endcol)
#检查某个格子是否处在合并单元格中,如果存在，则返回合并单元格索引号，不存在时，返回-1
def checkmerg(self,empr,empc):
#mgcells={()}
if len(self.mgcells)==0:
return -1
emptycell=(empr,empc)
for (index,mg) in enumerate(self.mgcells):
for row in range(mg[0],mg[1]):
for column in range(mg[2],mg[3]):
cell=(row,column)
if cell==emptycell:
return index
#print('row=%d,column=%d'%(row,column),cell)
# mgcells.add(cell)
return -1
'''
pe=ParseExcel('学生成绩记录表.xlsx','标题测试Abc')
pe=ParseExcel('月租户订单管理.xlsx','Sheet2
')
pe=ParseExcel('学生成绩记录表.xlsx','学生成绩清单')
pe=ParseExcel('学生成绩记录表.xlsx','学生成绩汇总')
pe=ParseExcel('月租户订单管理.xlsx','标题测试Abc')
'''
if __name__=='__main__':
#pe=ParseExcel('月租户订单管理.xlsx','用户表2
')

#pe=ParseExcel('月租户订单管理.xlsx','Sheet4')

#pe=ParseExcel('学生成绩记录表.xlsx','学生成绩清单')

#pe=ParseExcel('月租户订单管理.xlsx','月租用户收费规则表')

pe=ParseExcel('学生成绩记录表.xlsx','Sheet4')
pe.outFields()
#test() #字段嵌套独立测试

created by 刘明
www.isscollege.com

最后

以上就是光亮凉面为你收集整理的python解析excel文件多级标题嵌套行的全部内容，希望文章能够帮你解决python解析excel文件多级标题嵌套行所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。