使用python元素树从xml在Excel工作表中创建子表 [英] Create sub tables in Excel sheet from an xml using python element tree
本文介绍了使用python元素树从xml在Excel工作表中创建子表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
<?xml version="1.0" encoding="UTF-8"?>
-<Metrics>
-<File name="N:/Users/Source/Callbacks.c">
-<Entity name="N:/Users/Source/CAN_Callbacks.c" type="file">
<Metric name="STTDE" value="2.205"/>
<Metric name="STM28" value="27"/>
<Metric name="STTDO" value="1.999"/>
</Entity>
-<Entity name="ApplNwmBusoff" type="function">
<Metric name="STM07" value="1"/>
<Metric name="STUNV" value="1"/>
<Metric name="STSUB" value="1"/>
</Entity>
</File>
</Metrics>
这是xml代码.我想在Excel工作表中创建子表,如下所示:
This is the xml code. I want to create subtables in Excel sheet as follows:
这是我的代码:
import os
import xml.etree.ElementTree as ET
import pandas as pd
dir = r"C:\Users\HIL Tester\projt\prqa\reports\projt_MDR_13062017_112431.xml"
file_name = []
metric_name = []
metric_value = []
table_end_indices = []
metric_name_lists = []
metric_value_lists = []
main_list = []
tree = ET.parse(dir)
root = tree.getroot()
for child in root[0].iter('Entity'):
file_name.append(child.get('name'))
for table_ind in range(len(root[0])):
for c in root[0][table_ind].iter('Metric'):
metric_name.append(c.get('name'))#all the headers are appended to metric name list
metric_value.append(c.get('value'))#all the values are appended to metric value list
data_frame1 = pd.DataFrame(file_name)
data_frame2 = pd.DataFrame(metric_name)
data_frame3 = pd.DataFrame(metric_value)
frames = [data_frame1, data_frame2, data_frame3]
result = pd.concat(frames)
result=result.transpose()
writer = pd.ExcelWriter(r'N:\Users\xml\report.xlsx')
result.to_excel(writer, 'xml_report')
writer.save()
但是此代码以这种格式生成表.我想以上述格式创建表.有人可以帮我吗?
But this code generates table in this format.I want to create tables in the above mentioned format. Can someone help me please?
推荐答案
使用openpyxl
的解决方案,例如:
Solution using openpyxl
, for instance:
from openpyxl import Workbook
wb = Workbook()
ws = wb.worksheets[0]
ws.title = 'xml_report'
tree = ET.parse(xml)
root = tree.getroot()
for entity in root[0].iter('Entity'):
subtable_header = []
subtable_header.append(entity.get('name'))
subtable_values = ['']
for metric in entity.iter('Metric'):
subtable_header.append(metric.get('name'))
subtable_values.append(metric.get('value'))
ws.append(subtable_header)
ws.append(subtable_values)
wb.save('N:\Users\xml\report.xlsx')
输出:
使用Python测试:3.4.2-openpyxl:2.4.1-LibreOffice:4.3.3.2
Tested with Python: 3.4.2 - openpyxl: 2.4.1 - LibreOffice: 4.3.3.2
这篇关于使用python元素树从xml在Excel工作表中创建子表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文