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
Solved! Go to Solution.
Hey, thought I answered to this on Friday evening but the answer was somehow lost... 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_*'
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!
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!
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.
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
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.
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
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.
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
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
I will try to help you but only later today. I am quite busy now.