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

[nimishbhuta]

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

[Lucian]

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!

[mbheeke]

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.   

[nimishbhuta]

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

[Lucian]

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.

[nimishbhuta]

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 

[Lucian]

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.

[nimishbhuta]

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

[nimishbhuta]

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

[Lucian]

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