将Excel表格数据转换成XML格式文件
读取Excel文件,将其表中的列名作为属性名,单元格值作为属性值,转换成xml格式文件。代码如下:
import xml.etree.ElementTree as ETimport xlrd#read data from a xlsx'file and assign them to a listExcelFileName = 'F:\\sh\\m8\\data\\t1.xlsx'workbook = xlrd.open_workbook(ExcelFileName)worksheet = workbook.sheet_by_name("Sheet1")num_rows = worksheet.nrowsnum_cols = worksheet.ncolsresult_data = []for curr_row in range(0, num_rows, 1): row_data = []for curr_col in range(0, num_cols, 1):data = worksheet.cell_value(curr_row, curr_col)row_data.append(data)result_data.append(row_data)print(result_data)# create the file structuredata = ET.Element('data')length = len(result_data)-1for i in range(length):rows = ET.SubElement(data, f'row{i+1}')for x in result_data[0]:items = ET.SubElement(rows, x)items.set('name', x)items.text = str(result_data[i+1][result_data[0].index(x)])# create a new XML file with the resultsmydata = ET.tostring(data)myfile = open("data/example1.xml", "wb")myfile.write(mydata)myfile.close()
输出结果片断:
<data> <row1><S.No name="S.No">1.0</S.No><Name name="Name">Name1</Name><Module name="Module">Module1</Module> <Val1 name="Val1">0.0004</Val1><Val2 name="Val2">1.874</Val2><Val3 name="Val3">0.9</Val3><Val4 name="Val4">0.0</Val4><Total name="Total">2.7744</Total> </row1> <row2>......