reading Excel and writing to XML in Groovy for SoapUI

Question

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

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

I need XML with node for each row and child node for each cell, # of rows and columns are varies...

tried several examples given in soapUI as well as codehaus.org without luck please help

Answer 1

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.

/*
 * 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 Fuck 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 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.

Answer 2

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