cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
Community Hero

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

Just a thing... I saw no <document> node in your response.



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

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

Hello, 

 

It is documentNumber

 

Regards,

 

Nimish

Community Hero

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

I think there are multiple  ways to do this. This is how I did it:

 

I  gave all the requests where I wanted to extract the customerPO and documentNumber a similar name (notice GetOrder_*):

 

2018-04-13_23h47_43.png

 

Then in the groovy script I simply looped over all the test steps with that similar name and extracted the required elements:

 

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++
	}
}

 

Hope it helps!



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

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.

View solution in original post

Frequent Contributor

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

Thank you very very much. It is working fine.

 

One more thing, I have another scenario where I have only 1 Request and have the data source like a data generation for the customerPO for unique identification. 

 

Now I have the counter in the data generation to run 1 Request say for 10 times, so it will create 10 different orders., so there are 2 things to achieve. 

 

1) I need to have the customerPO and orderNo in different row based on the counter, so if it runs for the first time, the customerPO and orderNo will go to the first row and then second time it run then goes to the second row. 

 

2) In the request, we have customerPO which I am generting through data generation for example : for the first counter request it will show PO-177777 and added a suffix as 1,so it will be come PO-177777-1 , then after the second counter it will run the same request, it will have PO-177778 but added a suffix as 2 , so it will become  PO-177778-2 and like wise till 10 different customerPO with the suffix. 

 

          I wanted to know how can we add the suffix in the customerPO request header based on the current counter

 

Regards,

 

Nimish 

Community Hero

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

Hi Nimish,

 

I want to help you but I couldn't quite understand your last scenario. Could you give a little bit more explanations? An example would also help.



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

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

Hello Lucian, 

 

The second scenario is that I have 1 request that  I will iterate for 10 times to create 10 different orders . For each order, customerPo is the unique identifier,so I have setup data generator - custom string but the for the customer PO, it generates the unique number such as PO1111, PO11112 .. 10 times. I need to add a suffix in the PO generated such as PO1111-1,PO1112-2  like this till 10th order such as  PO1110-10, the suffix to identify the 10th order. 

 

I need  have a logic that in the request, I should have the customerPO such as PO1111-i  i like variable which will populated with the iteration number from the data generator. For example, if it runs for the 1st time it will have i as 1 and then for the 2nd time it will have 2 like that till 10

 

Regard,

 

Nimish

 

Community Hero

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

Hi,

 

Sorry for the delayed response. I am little caught up with work now...

 

Not sure if I understood correctly, so you want to 'stamp' your customerPo values with an index? This can be done quite simply. All you need to do is add a new line after the customerPo extraction:

 

customerPo = customerPo + "-" + String.valueOf( requestCounter )


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

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

Hello Lucian, 

 

Actually, I want the current row of the DataSource,so instead of the requestCounter, I want to fetch the current row of the datasource. 

 

How can I get the current row of the datasource counter?

 

Regards,

 

Nimish

Community Hero

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

The current iteration can be extracted like:

 

def currentRow = testRunner.testCase.testSteps['DataSourceStepName'].currentRow


If my answer helped please click on the 'Accept as Solution' button.
New Here?
Join us and watch the welcome video:
Watch the new Interview
Top Kudoed Authors