python工具xml格式文件转excle

1. xml文件

2. <?xml version="1.0" encoding="UTF-8"?>
   <RECORDS xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <ZZZSYBQ>
       <RQ>R1</RQ>
       <YBZMC>Y1</YBZMC>
       <DCQMC>D1</DCQMC>
       <LTCSZQDM>L1</LTCSZQDM>
     </ZZZSYBQ>
     <ZZZSYBQ>
       <RQ>R2</RQ>
       <YBZMC>Y2</YBZMC>
       <DCQMC>D2</DCQMC>
       <LTCSZQDM>L2</LTCSZQDM>
     </ZZZSYBQ>
     <ZZZSYBQ>
       <RQ>R3</RQ>
       <YBZMC>Y3</YBZMC>
       <DCQMC>D3</DCQMC>
       <LTCSZQDM>L3</LTCSZQDM>
     </ZZZSYBQ>
     <ZZZSYBQ>
       <RQ>R4</RQ>
       <YBZMC>Y4</YBZMC>
       <DCQMC>D4</DCQMC>
       <LTCSZQDM>L4</LTCSZQDM>
     </ZZZSYBQ>
   </RECORDS>

    

3. python脚本

4. import xml.etree.ElementTree as ET
   
   import xlsxwriter
   
   tree = ET.parse('20190607.xml')
   root = tree.getroot()
   #print(root.tag)
   # 一个节点有tag、attrib、text三个值
   # tag是标签的名字
   # text是标签的内容
   # attrib是标签属性的字典，通过字典的get('key')来获取对应的属性的值
   
   workbook = xlsxwriter.Workbook('excle20190607.xlsx')  # 创建一个Excel文件
   
   title = ['RQ','YBZMC','DCQMC','LTCSZQDM']     #表格title
   
   row = 1
   # 直接for chile in parent 来遍历节点下的子节点
   print('总条数：',len(root))
   worksheet = 0
   for index, child in enumerate(root):
       #print(child.tag, child.attrib)
       if index % 60000 == 0:  #每60000条数据分一个sheet
           worksheet = workbook.add_worksheet()
           worksheet.write_row('A1', title)
           row = 1
   
       for elem in child:
           #print(elem.tag, elem.text)
           worksheet.write(row, title.index(elem.tag), elem.text)
   
       row += 1
   
   workbook.close()