EXCEL转换为XML如何用Python实现？ - 51Testing软件测试论坛

C:\Users\Administrator>pip install pywin32
Fatal error in launcher: Unable to create process using '"c:\python36\python3.exe""F:\Python36\Scripts\pip.exe" install pywin32': ???????????

复制代码

F:\Python36\Scripts>pip install pywin32
Fatal error in launcher: Unable to create process using '"c:\python36\python3.exe""F:\Python36\Scripts\pip.exe" install pywin32': ???????????

复制代码

C:\Users\Administrator>python3 F:\case\operate.py
File "F:\case\operate.py", line 46
print 'str=', str
^
SyntaxError: Missing parentheses in call to 'print'

复制代码

C:\Users\Administrator>python3 C:\Python36\Tools\scripts\2to3.py -w D:\python2to3\operate.py
RefactoringTool: Skipping optional fixer: buffer
RefactoringTool: Skipping optional fixer: idioms
RefactoringTool: Skipping optional fixer: set_literal
RefactoringTool: Skipping optional fixer: ws_comma
RefactoringTool: Refactored D:\python2to3\operate.py
--- D:\python2to3\operate.py (original)
+++ D:\python2to3\operate.py (refactored)
[url=home.php?mod=space&uid=40658]@@[/url] -1,7 +1,8 @@
# coding:utf-8
import os,re,xlrd
import sys
-reload(sys)
+import imp
+imp.reload(sys)
sys.setdefaultencoding("utf-8")
from easy_excel import easy_excel
@@ -43,7 +44,7 @@
def actions_add_div(str):
if str:
new_action_str=""
- print 'str=', str
+ print('str=', str)
for str_index in str.split('\n'):
newline=str_index + "" + "</div>" + "<div>" + "" + "</p>" + "<p>"
new_action_str+=newline
@@ -122,16 +123,16 @@
if __name__ == "__main__":
- print os.path.abspath('.')
+ print(os.path.abspath('.'))
excellist=[]
#列出当前目录下的所有.xml文件
for fullname in iterbrowse(os.path.abspath('.')):
- print fullname
+ print(fullname)
obj1=re.compile(r'([\W\w]*)(\.xlsx)
[/color][/align]
[/backcolor]
[backcolor=rgb(247, 247, 247)]
[/backcolor]
)
for m in obj1.finditer(fullname):
- print m.group()
+ print(m.group())
excellist.append(m.group())
- print excellist
+ print(excellist)
for fileName in excellist:
fileName=fileName.split('\\')[-1]
file_data = xlrd.open_workbook(fileName)
@@ -139,12 +140,12 @@
sheetList=[]
for index in range(sheetnum):
sheet_name=file_data.sheets()[index].name
- print sheet_name
+ print(sheet_name)
sheetList.append(sheet_name)
- print sheetList
+ print(sheetList)
for sheetName in sheetList:
test = operate(fileName, sheetName)
test.xlsx_to_dic(sheetName)
test.dic_to_xml(fileName, sheetName)
- print "Convert success!"
+ print("Convert success!")
os.system('pause')
RefactoringTool: Files that were modified:
RefactoringTool: D:\python2to3\operate.py

复制代码

C:\Users\Administrator>python3 C:\Python36\Tools\scripts\2to3.py -w D:\python2to3\operate.py
RefactoringTool: Skipping optional fixer: buffer
RefactoringTool: Skipping optional fixer: idioms
RefactoringTool: Skipping optional fixer: set_literal
RefactoringTool: Skipping optional fixer: ws_comma
RefactoringTool: No changes to D:\python2to3\operate.py
RefactoringTool: Files that need to be modified:
RefactoringTool: D:\python2to3\operate.py

复制代码

D:\python2to3>python3 operate.py
D:\python2to3
D:\python2to3\case.xlsx
D:\python2to3\case.xlsx
D:\python2to3\easy_excel.py
D:\python2to3\easy_excel.py.bak
D:\python2to3\operate.py
D:\python2to3\operate.py.bak
D:\python2to3\__pycache__\easy_excel.cpython-36.pyc
['D:\\python2to3\\case.xlsx']
XX功能模块1
XX功能模块2
['XX功能模块1', 'XX功能模块2']
('str=', '1.步骤一\n2.步骤二\n3.步骤三')
('str=', '1.步骤一\n2.步骤二\n3.步骤三')
Convert success!
请按任意键继续. . .

复制代码

# coding=utf-8
from xml.etree import ElementTree
from win32com.client import Dispatch
import win32com.client
import os
import sys
import imp
imp.reload(sys)
class easy_excel:
def __init__(self, filename=None):
self.xlApp = win32com.client.Dispatch('Excel.Application')
if filename:
self.filename = os.getcwd() + "\\" + filename
# self.xlApp.Visible=True
self.xlBook = self.xlApp.Workbooks.Open(self.filename)
else:
# self.xlApp.Visible=True
self.xlBook = self.xlApp.Workbooks.Add()
self.filename = ''
def save(self, newfilename=None):
if newfilename:
self.filename = os.getcwd() + "\\" + newfilename
# if os.path.exists(self.filename):
# os.remove(self.filename)
self.xlBook.SaveAs(self.filename)
else:
self.xlBook.Save()
def close(self):
self.xlBook.Close(SaveChanges=0)
self.xlApp.Quit()
def getCell(self, sheet, row, col):
sht = self.xlBook.Worksheets(sheet)
return sht.Cells(row, col).Value
def setCell(self, sheet, row, col, value):
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).Value = value
# 设置居中
sht.Cells(row, col).HorizontalAlignment = 3
sht.Rows(row).WrapText = True
def mergeCells(self, sheet, row1, col1, row2, col2):
start_coloum = int(dic_config["start_coloum"])
# 如果这列不存在就不合并单元格
if col2 != start_coloum - 1:
sht = self.xlBook.Worksheets(sheet)
sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Merge()
# else:
# print 'Merge cells coloum %s failed!' %col2
def setBorder(self, sheet, row, col):
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).Borders.LineStyle = 1
def set_col_width(self, sheet, start, end, length):
start += 96
end += 96
msg = chr(start) + ":" + chr(end)
# print msg
sht = self.xlBook.Worksheets(sheet)
sht.Columns(msg.upper()).ColumnWidth = length

复制代码

# coding:utf-8
import os,re,xlrd
import sys
import imp
imp.reload(sys)
from easy_excel import easy_excel
class operate():
def __init__(self, ExcelFileName, SheetName):
self.excelFile = ExcelFileName
self.excelSheet = SheetName
self.temp = easy_excel(self.excelFile)
self.dic_testlink = {}
self.row_flag = 2
self.testsuite = self.temp.getCell(self.excelSheet, 2, 2)
#print 'self.testsuite=',self.testsuite
self.dic_testlink[self.testsuite] = {"node_order": "13", "details": "", "testcase": []}
self.content = ""
self.content_list = []
def xlsx_to_dic(self, SheetName):
while True:
testcase = {"name": "", "node_order": "100", "externalid": "", "version": "1", "summary": "",
"preconditions": "", "execution_type": "1", "importance": "3", "steps": [], "keywords": "P1", "author":""}
testcase["name"] = self.temp.getCell(self.excelSheet, self.row_flag, 5)
testcase["summary"] = self.temp.getCell(self.excelSheet, self.row_flag, 3)
testcase["preconditions"] = self.temp.getCell(self.excelSheet, self.row_flag, 6)
execution_type = self.temp.getCell(self.excelSheet, self.row_flag, 9)
testcase["keywords"] = self.temp.getCell(self.excelSheet, self.row_flag, 10)
testcase["author"] = self.temp.getCell(self.excelSheet, self.row_flag, 11)
if execution_type == "自动":
testcase["execution_type"] = 2
step_number = 1
importance = self.temp.getCell(self.excelSheet, self.row_flag, 4)
if importance == "基本功能" or importance == "P1":
testcase["importance"]="2"
elif importance == "拓展" or importance == "P2":
testcase["importance"] = "1"
while True:
step = {"step_number": "", "actions": "", "expectedresults": "", "execution_type": ""}
step["step_number"] = step_number
step["actions"] = self.temp.getCell(self.excelSheet, self.row_flag, 7)
def actions_add_div(str):
if str:
new_action_str=""
print(('str=', str))
for str_index in str.split('\n'):
newline=str_index + "" + "</div>" + "<div>" + "" + "</p>" + "<p>"
new_action_str+=newline
return new_action_str[:-21]
step["actions"]=actions_add_div(step["actions"])
step["expectedresults"] = self.temp.getCell(self.excelSheet, self.row_flag, 8)
testcase["steps"].append(step)
step_number += 1
self.row_flag += 1
if self.temp.getCell(self.excelSheet, self.row_flag, 1) is not None or self.temp.getCell(self.excelSheet, self.row_flag, 5) is None:
break
# print testcase
self.dic_testlink[self.testsuite]["testcase"].append(testcase)
# print self.row_flag
if self.temp.getCell(self.excelSheet, self.row_flag, 5) is None and self.temp.getCell(self.excelSheet, self.row_flag + 1, 5) is None:
break
self.temp.close()
# print self.dic_testlink
def content_to_xml(self, key, value=None):
if key == 'step_number' or key == 'execution_type' or key == 'node_order' or key == 'externalid' or key == 'version' or key == 'importance':
return "<" + str(key) + "><![CDATA[" + str(value) + "]]></" + str(key) + ">"
elif key == 'actions' or key == 'expectedresults' or key == 'summary' or key == 'preconditions':
return "<" + str(key) + "><![CDATA[<div>" + str(value) + "</div> ]]></" + str(key) + ">"
elif key == 'keywords':
return '<keywords><keyword name="' + str(value) + '"><notes><![CDATA[ aaaa ]]></notes></keyword></keywords>'
elif key == 'name':
return '<testcase name="' + str(value) + '">'
elif key == 'author':
return '<custom_fields><custom_field><name><![CDATA[设计人员]]></name><value><![CDATA[%s]]></value></custom_field></custom_fields>' % str(value)
else:
return '##########'
def dic_to_xml(self, ExcelFileName, SheetName):
testcase_list = self.dic_testlink[self.testsuite]["testcase"]
for testcase in testcase_list:
for step in testcase["steps"]:
self.content += "<step>"
self.content += self.content_to_xml("step_number", step["step_number"])
self.content += self.content_to_xml("actions", step["actions"])
self.content += self.content_to_xml("expectedresults", step["expectedresults"])
self.content += self.content_to_xml("execution_type", step["execution_type"])
self.content += "</step>"
self.content = "<steps>" + self.content + "</steps>"
self.content = self.content_to_xml("importance", testcase["importance"]) + self.content
self.content = self.content_to_xml("execution_type", testcase["execution_type"]) + self.content
self.content = self.content_to_xml("preconditions", testcase["preconditions"]) + self.content
self.content = self.content_to_xml("summary", testcase["summary"]) + self.content
self.content = self.content_to_xml("version", testcase["version"]) + self.content
self.content = self.content_to_xml("externalid", testcase["externalid"]) + self.content
self.content = self.content_to_xml("node_order", testcase["node_order"]) + self.content
self.content = self.content + self.content_to_xml("keywords", testcase["keywords"])
self.content = self.content_to_xml("name", testcase["name"]) + self.content
self.content = self.content + self.content_to_xml("author", testcase["author"])
self.content = self.content + "</testcase>"
self.content_list.append(self.content)
self.content = ""
self.content = "".join(self.content_list)
self.content = '<testsuite name="' + self.testsuite + '">' + self.content + "</testsuite>"
self.content = '<?xml version="1.0" encoding="UTF-8"?>' + self.content
self.write_to_file(ExcelFileName, SheetName)
def write_to_file(self, ExcelFileName, SheetName):
xmlFileName = ExcelFileName + '_' + SheetName + '.xml'
cp = open(xmlFileName, "w")
cp.write(self.content)
cp.close()
#遍历某个文件目录下的所有文件名称
def iterbrowse(path):
for home, dirs, files in os.walk(path):
for filename in files:
yield os.path.join(home, filename)
if __name__ == "__main__":
print((os.path.abspath('.')))
excellist=[]
#列出当前目录下的所有.xml文件
for fullname in iterbrowse(os.path.abspath('.')):
print(fullname)
obj1=re.compile(r'([\W\w]*)(\.xlsx)
)
for m in obj1.finditer(fullname):
print((m.group()))
excellist.append(m.group())
print(excellist)
for fileName in excellist:
fileName=fileName.split('\\')[-1]
file_data = xlrd.open_workbook(fileName)
sheetnum=len(file_data.sheets())
sheetList=[]
for index in range(sheetnum):
sheet_name=file_data.sheets()[index].name
print(sheet_name)
sheetList.append(sheet_name)
print(sheetList)
for sheetName in sheetList:
test = operate(fileName, sheetName)
test.xlsx_to_dic(sheetName)
test.dic_to_xml(fileName, sheetName)
print("Convert success!")
os.system('pause')

复制代码