在 Groovy for SoapUI 中读取 Excel 并写入 XML
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22031375/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
reading Excel and writing to XML in Groovy for SoapUI
提问by 1234
I need to read some data in XL using groovy and create an XML using those. for SoapUI test case I can read XL but not possible to build XML
我需要使用 groovy 读取 XL 中的一些数据并使用这些数据创建 XML。对于 SoapUI 测试用例，我可以读取 XL 但无法构建 XML
below is what I wrote
下面是我写的
import java.io.File
import java.util.Date
import jxl.*
import groovy.xml.MarkupBuilder
import org.custommonkey.xmlunit.*
//read excelfile
wb = Workbook.getWorkbook(new File('C:/.../ChangedData.xls'))
sheet = wb.getSheet(0)
def writer = new FileWriter('C:/.../data.xml')
def xml = new MarkupBuilder(writer)
xml.mkp.xmlDeclaration(version: "1.0", encoding: "utf-8")
//=======================
//xml.users(){user(count:'i')
for (int i=1; i<sheet.getRows();i++) {
for (int j=0; j<sheet.getColumns(); j++){
p=sheet.getCell(j,i).getContents()
log.info p
}
}
}
wb.close()
problem is I can not build xml while reading inside "for" loops
问题是我无法在“for”循环中读取时构建 xml
I need XML with node for each row and child node for each cell, # of rows and columns are varies...
我需要每个单元格的每行节点和子节点的 XML，行数和列数各不相同...
tried several examples given in soapUI as well as codehaus.org without luck please help
尝试了soapUI和codehaus.org中给出的几个例子，但没有运气请帮忙
回答by Abhishek Asthana
There are multiple ways to do what you want, one of the ways is demonstrated below.
有多种方法可以做您想做的事，下面演示了其中一种方法。
In this i make use of scriptom and consider the xml as a string and perform basic string concatenation. If you provide more details on a specific way of resolving your question or teh structure of your excel i'll be able to implement a solution based on that. In the meantime here goes my generic solution.
在此，我使用 scriptom 并将 xml 视为字符串并执行基本的字符串连接。如果您提供有关解决问题的特定方法或 excel 结构的更多详细信息，我将能够基于此实施解决方案。与此同时，这是我的通用解决方案。
/*
* Script to convert excel into xml
* Related to: http://stackoverflow.com/questions/22031375/reading-excel-and-writing-to-xml-in-groovy-for-soapui
* Author: Abhishek Asthana
* Contact: http://abhishekasthana.com/about/
* License:
* * This program is free software. It comes without any warranty, to
* * the extent permitted by applicable law. You can redistribute it
* * and/or modify it under the terms of the Do What The fwor You Want
* * To Public License, Version 2, as published by Sam Hocevar. See
* * http://www.wtfpl.net/ for more details.
*
* This script will read an excel(Any version, i used a .XLSX) and generate an xml where each row will be a node and
* each column will be its child node. The resulting xml will look something like this.
*
* <excel2Xml>
* <row1>
* <Column>cellValue</Column>
* <Column>anotherCellsValue</Column>
* <row1>
* <row2>
* <Column>cellValue</Column>
* <Column>anotherCellsValue</Column>
* <row2>
* </excel2Xml>
*
* Dependency:
* 1. Scriptom Library
* - Download: http://groovy.codehaus.org/COM+Scripting
* - Instructions to configure scriptom: http://stackoverflow.com/questions/18724929/how-to-make-scriptom-work-with-soapui
*
*/
import org.codehaus.groovy.scriptom.*
import org.codehaus.groovy.scriptom.util.office.ExcelHelper;
def excelPath = "C:\soapUI\excel2XML\source.xlsx"
def dataSheetName = "Sheet1"
def oExcel = new ActiveXObject('Excel.Application')
Thread.sleep(1000)
assert oExcel != null, "Excel object not initalized"
def openWb = oExcel.Workbooks.Open(excelPath) //get access to the workbook
def dtUsedRange = openWb.Sheets(dataSheetName).UsedRange //get the usedRange of the sheet
int rCount = dtUsedRange.Rows.Count
int cCount = dtUsedRange.Columns.Count
def strXml = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><excel2Xml>"
//add property names to xlMapSheet under col d or col# 4
for(int r = 1;r<=rCount;r++){
strXml = strXml + "<row" + r.toString() + ">"
for(int c = 1;c<cCount;c++){
def cValue = openWb.Sheets(dataSheetName).Cells(r,c).Value
strXml = strXml + "<Column>" + cValue + "</Column>"
}
strXml = strXml + "</row" + r.toString() + ">"
}
strXml = strXml + "</excel2Xml>"
log.info strXml
openWb.Close(false,null,false)
oExcel.Quit()
Scriptom.releaseApartment()
This returns the below XML
这将返回以下 XML
<?xml version="1.0" encoding="UTF-8"?>
<excel2Xml>
<row1>
<Column>row1Col1</Column>
<Column>row1Col2</Column>
<Column>row1Col3</Column>
<Column>row1Col4</Column>
<Column>row1Col5</Column>
<Column>row1Col6</Column>
</row1>
<row2>
<Column>row2col1</Column>
<Column>row2col2</Column>
<Column>row2col3</Column>
<Column>row2col4</Column>
<Column>row2col5</Column>
<Column>row2col6</Column>
</row2>
<row3>
<Column>row3Col1</Column>
<Column>row3Col2</Column>
<Column>row3Col3</Column>
<Column>row3Col4</Column>
<Column>row3Col5</Column>
<Column>row3Col6</Column>
</row3>
</excel2Xml>
Do note that i have not implemented any error handling and if you were to use this code you would have to do that on your own.
请注意，我没有实现任何错误处理，如果您要使用此代码，则必须自己完成。
回答by Sandeep
import java.io.*
import jxl.*
import jxl.write.*
import java.text.SimpleDateFormat
def f=new File("D:\Testing\SOAP\GoogleMapAPI_SoapAutoFile.xls");
def wk= Workbook.getWorkbook(f)
def ws=wk.getSheet("Sheet1")
r=ws.getRows()
def estatus
def date = new Date()
FileDate = new SimpleDateFormat("ddMMMyyyy_HHmmss")
log.info FileDate.format(date)
def f1 = new File("D:\Testing\SOAP\Report\GoogleAPIReport\Report_"+FileDate.format(date)+".xls")
def wk2 = Workbook.getWorkbook(f)
def wk1 = Workbook.createWorkbook(f1, wk2)
def ws1=wk1.getSheet("Sheet1")
for(def i=1;i<r;i++)
{
log.info "--RRRRRRRRR-------------------------------------------------"
log.info r
log.info "--IIIIIIIIIIIIIII-----------------------------------------"
log.info i
Cell c1=ws.getCell(2,i)
if(c1.getContents().equalsIgnoreCase("Y"))
{
Cell c2=ws.getCell(3,i)
log.info c2.getContents()
Cell c22=ws.getCell(4,i)
log.info c22.getContents()
log.info c2.getContents()
testRunner.testCase.testSuite.setPropertyValue("testdata",c2.getContents())
testRunner.testCase.testSuite.setPropertyValue("testdata2",c22.getContents())
Cell c3=ws.getCell(1,i)
log.info c3.getContents()
testRunner.runTestStepByName(c3.getContents())
def assertionsList = testRunner.getTestCase().getTestStepByName(c3.getContents()).getAssertionList()
for( e in assertionsList){
log.info "--ASSERTION NAME---------------------------------------------"
log.info e.getName()
log.info e.getToken() //gives the value of the content to search for
log.info e.DESCRIPTION
log.info e.ID
log.info e.LABEL
log.info "--ASERTION STATUS----------------------------------------------"
log.info e.status
log.info e.toString()
estatus=e.status
}
log.info i
enter code here
log.info "--LABEL---------------------------------------------------------"
}
log.info estatus
ws1.addCell(new Label(5, i, estatus.toString()));
estatus=null
}
wk1.write()
wk1.close()
log.info "DONE"
And the Excel File have:
[enter image description here][1]
Now add the Testcase in SOUPUI
[enter image description here][2]
[1]: https://i.stack.imgur.com/dnfgt.jpg
[2]: https://i.stack.imgur.com/cr9Hw.jpg
