本文实例讲述了Python实现提取XML内容并保存到Excel中的方法。分享给大家供大家参考,具体如下:
最近做一个项目是解析XML文件,提取其中的chatid和lt、timestamp等信息,存到excel里。
1.解析xml,提取数据
使用python自带的xml.dom中的minidom(也可以用lxml)
xml文件如下:
minidom.parse()#解析文件,返回DOM对象
_get_documentElement()DOM是树形结构,获得了树形结构的根节点
getElementsByTagName()根据name查找根目录下的子节点
getAttribute()获取DOM节点的属性的值
提取的代码如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
class get_xml(): #加载获取xml的文档对象 def __init__( self ,address): #解析address文件,返回DOM对象,address为文件地址 self .doc = minidom.parse(address) #DOM是树形结构,_get_documentElement()获得了树形结构的根节点 self .root = self .doc._get_documentElement() #.getElementsByTagName(),根据name查找根目录下的子节点 self .httpSample_nodes = self .root.getElementsByTagName( 'httpSample' ) def getxmldata( self ): data_list = [] j = - 1 responseData_node = self .root.getElementsByTagName( "responseData" ) for i in self .httpSample_nodes: j = j + 1 #getAttribute(),获取DOM节点的属性的值 if i.getAttribute( "lb" ) = = "发送信息" : a = 'chatId":"(.*?)"' elif i.getAttribute( "lb" ) = = "接收信息" : # a = "chatId%3A%22(.*?)%22" a = "info%3A%22(.*?)%22" if (i.getAttribute( "lb" ) = = "发送信息" or i.getAttribute( "lb" ) = = "接收信息" ) and i.getAttribute( "rc" ) = = "200" : try : #使用re包里面的方法,通过正则表达式提取数据 b = re.search(a, responseData_node[j].firstChild.data) if b is not None : d = b.group( 1 ) print ( "d:" ,d) data_list.append((d, i.getAttribute( "ts" ), i.getAttribute( "lt" ),i.getAttribute( "lb" ))) except : pass return data_list |
2.存储为Excel,导出数据到Excel
用到的包openpyxl,openpyxl.workbook下的Workbook()
用来在内存里创建文件,最后写进磁盘的
wb = load_workbook(filename = XXXX.xlsx):读取Excel文件,文件地址为XXXX.xlsx
wb = Workbook():创建一个Workbook对象
ew = ExcelWriter(workbook = wb):新建一个excelWriter,最后用来保存
wb.create_sheet(0, 'XXX'):新建一个sheet,位置是0,sheet名字是XXX
ws = wb.worksheets[0]:打开一个sheet,sheet位置是0,即第1个sheet
ws.cell(row=1,column=1).value = XXX:在1行1列的位置加入数据XXX
ew.save(filename = XXXX.xlsx):将数据导出到本地,本地文件地址为XXXX.xlsx
一个导出Excel的例子如下:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
import openpyxl from openpyxl import writer,load_workbook # Workbook用来在内存里创建文件最后写进磁盘的 from openpyxl.workbook import workbook, Workbook from openpyxl.writer.excel import ExcelWriter from openpyxl.cell import get_column_letter # if __name__ == "__main__": def importexcel(match,dest_filename): if (os.path.exists(dest_filename)): wb = load_workbook(filename = dest_filename) else : wb = Workbook() ew = ExcelWriter(workbook = wb) #创建一个新sheet wb.create_sheet( 0 , '聊聊发送接收请求' ) # 打开已存在的第一个sheet,也可以用get_sheet_names获得所有的sheet的名字 ws = wb.worksheets[ 0 ] ws.title = "聊聊发送接收请求" ws.cell( 'A1' ).value = "chartid" ws.cell( 'B1' ).value = "接收时间戳" ws.cell( 'C1' ).value = "发送时间戳" ws.cell( 'D1' ).value = "时间戳差" ws.cell( 'E1' ).value = "接收lt" ws.cell( 'F1' ).value = "发送到接收的响应时间" l = 2 for i in match: ws.cell(row = l,column = 1 ).value = i[ 'chatId' ] ws.cell(row = l,column = 2 ).value = i[ 'accept_timestamp' ] ws.cell(row = l,column = 3 ).value = i[ 'send_timestamp' ] ws.cell(row = l,column = 4 ).value = i[ 'timestamp_gap' ] ws.cell(row = l,column = 5 ).value = i[ 'accept_lt' ] ws.cell(row = l,column = 6 ).value = i[ 'response_time' ] print (i,l) l = l + 1 ew.save(filename = dest_filename) |
希望本文所述对大家Python程序设计有所帮助。
原文链接:https://www.cnblogs.com/meitian/p/4584237.html