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

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/isscollege/article/details/80453641

一、 本文实现内容
用于解析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

阅读更多
换一批

没有更多推荐了,返回首页