cancel
Showing results for 
Search instead for 
Did you mean: 

Ready API - Writing the output to datasink in the same excel file

SOLVED
Highlighted
Contributor

Ready API - Writing the output to datasink in the same excel file

Hello, 

 

I have scenario in which I need to write the response in the excel sheet. I get two responses and both should be written in the same excel file. 

 

Test steps are as follows :

 

 1) DataSource - This is an unique identifier required in my request and I am using DataGenerator for it. 

 2) Request1    - This is the request which has an product item number and quantity as well as unique identifier. It gives me the response as OrderNo 

3) Request2 :This is the request which has an another product item number and quantity as well as unique identifier. It gives me the response as another OrderNo 

4) Property Transfer :- I have created 2 properties for this First Order and Second Order and transfer it to the data sink 

4) FisrtDataSink :- I am using the excel file and getting the FirstOrder response in the excel with the cell start at A1 

5)SecondDataSink I am using the excel file and getting the FirstOrder response in the excel with the cell start at A2

 

But the thing it only prints second order response in A2 and A1 is blank. It seems that it is writing the last data sink value. 

 

I need to write both the values one in A1 and another in A2. Is there any way to do it ?

 

Regards,

 

Nimish

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Hero

Re: Ready API - Writing the output to datasink in the same excel file

Hey, thought I answered to this on Friday evening but the answer was somehow lost... Smiley Sad I also attached then the demo SoapUI project but I don't have it anymore. I only found a screenshot I took and the code (which was luckily saved in my clipboard manager)...

 

So the point of my solution is to name all your requests from where you want to extract the customerPo/documentNumber in a certain way. Note that all mine where named 'GetOrder_*'

 

2018-04-13_23h47_43.png

 

Then in the groovy script I would filter for those requests, get all the required data and then finally write it to an existing excel:

 

import java.io.File;
import java.io.IOException;
import jxl.*;
import jxl.write.*;

// Define the customerPo and documentNumber variables
def customerPo = ''
def documentNumber = ''
def requestCounter = 0

// Get a list of all test steps in the current test case
def testSteps = context.testCase.getTestStepList()
// Loop through all the test steps in the current test case
// If a test step respects a certain naming then extract the response from it
//   and register the customerPo and documentNumber in an excel
testSteps.each{
	if (it.name.startsWith("GetOrder_")) {

		/**
		 * Extract the customerPo and documentNumber from current request
		 */
		def rawResponse = testRunner.testCase.testSteps[ it.name ].testRequest.response.contentAsString
		// Extract customerPo
		def customerPoStartIndex = rawResponse.indexOf("<customerPO>") + "<customerPO>".length()
		customerPo = rawResponse.substring( customerPoStartIndex, rawResponse.indexOf( "</", customerPoStartIndex ) )
		// Extract documentNumber
		def documentNumberStartIndex = rawResponse.indexOf("<documentNumber>") + "<documentNumber>".length()
		documentNumber = rawResponse.substring( documentNumberStartIndex, rawResponse.indexOf( "</", documentNumberStartIndex ) )

		/**
		 * Write to file
		 */
		Workbook workbook = Workbook.getWorkbook(new File("D:\\test3.xls"));
		WritableWorkbook writableWorkbook = Workbook.createWorkbook(new File("D:\\test3.xls"), workbook);
		WritableSheet writableSheet = writableWorkbook.getSheet(0); 
			
		// Write the first colummn
		Label label = new Label( 0, requestCounter, customerPo );
		writableSheet.addCell(label);

		// Write the second colummn
		Label label2 = new Label( 1, requestCounter, documentNumber );
		writableSheet.addCell(label2);
		
		//Write and close the workbook
		writableWorkbook.write();
		writableWorkbook.close();

		requestCounter++
	}
}

One final thing though, the excel file needs to be saved as xls (that is as an excel 2003 file). 

 

Cheers!



If my answer helped please click on the 'Accept as Solution' button.
Community Hero

Re: Ready API - Writing the output to datasink in the same excel file

Smiley LOL

 

Like this... ?

 

<customerPO>${DataSource#customerPO}-${=testRunner.testCase.testSteps['DataSource'].currentRow}</customerPO>


If my answer helped please click on the 'Accept as Solution' button.
21 REPLIES 21
Community Hero

Re: Ready API - Writing the output to datasink in the same excel file

Hi,

 

I don't know much about datasink but instead of the steps 4, 5 and 6 you can use a single groovy step:

 

 

import java.io.File;
import java.io.IOException;
import java.util.Date;
 
import jxl.*;
import jxl.write.*;
import jxl.write.Boolean;
import jxl.write.Number;
import jxl.write.biff.RowsExceededException;

def firstOrder = testRunner.testCase.getTestStepByName( "Properties" ).getPropertyValue( "firstOrderProperty" )
def secondOrder = testRunner.testCase.getTestStepByName( "Properties" ).getPropertyValue( "secondOrderProperty" )

try {
	// Create the excel file
	File exlFile = new File("D:/target_file.xls");
	final WritableWorkbook writableWorkbook = Workbook.createWorkbook( exlFile );	
	// Create a sheet in the excel file
	final WritableSheet writableSheet = writableWorkbook.createSheet("Sheet1", 0);
	
	// Write the first row
	Label label = new Label(0, 0, firstOrder);
	writableSheet.addCell(label);

	// Write the second row
	label = new Label(0, 1, secondOrder);
	writableSheet.addCell(label);

	//Write and close the workbook
	writableWorkbook.write();
	writableWorkbook.close();

} catch (IOException e) {
	e.printStackTrace();
} catch (RowsExceededException e) {
	e.printStackTrace();
} catch (WriteException e) {
	e.printStackTrace();
}

 

Cheers!

 



If my answer helped please click on the 'Accept as Solution' button.
Contributor

Re: Ready API - Writing the output to datasink in the same excel file

Maybe this will help?   Not A1  / A2 ... but  A1 / B1 will work. 

 

Use only a single Data Sink test step and include both properties in that data sink to Excel.   The first property will be written to cell A1, and the second to cell B1.   

Contributor

Re: Ready API - Writing the output to datasink in the same excel file

Thanks Lucian. As I understand, I need to to first create firstOrderProperty and secondOrderProperty in SOAPUI Pro

 

But I already have existing target file,so how can I open the target excel file rather than creating it?

 

Regards,

 

Nimish

Community Hero

Re: Ready API - Writing the output to datasink in the same excel file

Quickly googled and found:

 

Workbook workbook1 = Workbook.getWorkbook(new File("D:\\test.xls"));
WritableWorkbook copy = Workbook.createWorkbook(new File("D:\\test.xls"), workbook1);
WritableSheet sheet2 = copy.getSheet(0); 
	
// Write the first row Label label = new Label(0, 0, firstOrder); sheet2.addCell(label);
// Write the second row label = new Label(0, 1, secondOrder); sheet2.addCell(label); //Write and close the workbook copy.write(); copy.close();

As for the other part... you don't necessarily need to create properties for the first/second order. You can get those values directly in the script if you want. So you can replace:

 

def firstOrder = ...
def secondOrder = ...

with anything you need (for instance you can extract the values from the response directly here)

 

Let me know if you need more help. Smiley Very Happy



If my answer helped please click on the 'Accept as Solution' button.
Contributor

Re: Ready API - Writing the output to datasink in the same excel file

Hello, 

 

Thanks of your reply. 

 

I have some questions on this which are as follows :- 

 

1) def firstOrder ...  how can I get the value from the response xml 

    

2) If there are 10 different request : Request1,Request2....Request 10 then I need to add different rows. Is there any way we can dynamically populate the excel rows based on the no of request?

 

 

Regards,

 

Nimish 

Community Hero

Re: Ready API - Writing the output to datasink in the same excel file

Hi Nimish,

 

 

1) Can you post a sample response so I can help you with the value extraction?

2) Yes, please refer to https://community.smartbear.com/t5/SoapUI-Open-Source/Iterate-loop-through-soapui-response-data-item...

 

Lucian.



If my answer helped please click on the 'Accept as Solution' button.
Contributor

Re: Ready API - Writing the output to datasink in the same excel file

Thanks for your reply. 

 

I am attaching the sample response xml file. I require the value from the <customerPO>  <customerPO>  and <document> <document>  node 

 

customer PO value and document value should go in First row for the firts request and for the second request it should go in the 2nd row and so forth. 

 

As I am new groovy scripting, it will create if you can provide with required code 

 

Regards,

 

Nimish

Contributor

Re: Ready API - Writing the output to datasink in the same excel file

Thanks for your reply. 

 

I am attaching the sample response xml file. I require the value from the <customerPO>  <customerPO>  and <document> <document>  node 

 

customer PO value and document value should go in First row for the firts request and for the second request it should go in the 2nd row and so forth. 

 

As I am new groovy scripting, it will create if you can provide with required code 

 

Regards,

 

Nimish

Community Hero

Re: Ready API - Writing the output to datasink in the same excel file

I will try to help you but only later today. I am quite busy now. Smiley Happy



If my answer helped please click on the 'Accept as Solution' button.
February News
Top Kudoed Authors