cancel
Showing results for 
Search instead for 
Did you mean: 

How to Create New Sheet in Existing Excel through Data Sink Including Headers

Highlighted
Contributor

How to Create New Sheet in Existing Excel through Data Sink Including Headers

Hi All,

 I'm having 2 testcases, each of them having data sink test step.

I would like to store both of them results into seperate sheets in same excel file(The sheet names are customized not Sheet1, Sheet2). I can able to do this, without "Template File"(Which gives headers). By Checking "Append to end of file option".

 

But when we add "Template File" It's not creating 2 sheets, It's creating only latest one and creating one empty sheet same like ""Template File" Please  find below snapshot for refernce.

 

1) When added Template File

When ran TestCase1(TD_NA), it's just simply storing empty sheet with headers, always sheet name as Sheet1.

When ran TestCase 2(TD_PA), It's creating data without headers.

It acting same, if ran from project or testsuite level or Individual testcases as well.

 

WithHeaders.png

 

2) When we don't add Template file and Checked "Append to end of file option"

I would say it's working fine, as expected creating 2 sheets with custom names and proper data, but need to add headers

 

WithOutHeaders.png

 

 

Please let me know, if any one come across same issue, or any one have any idea. 

 

Thanks in adavnce and appreciated.

Tags (2)
5 REPLIES 5
Highlighted
Moderator

Re: How to Create New Sheet in Existing Excel through Data Sink Including Headers

HI @Redde,

 

Thank you for your post. Here are my comments:

1. There is a feature request to export the data to an Excel file along with the property names:

https://community.smartbear.com/t5/ReadyAPI-Feature-Requests/Capability-to-add-column-headings-to-Ex...

2. 


@Redde wrote:

1) When added Template File


There is a bug with using multiple DataSink test steps. If you have 2 test cases and run the TestSuite, ReadyAPI will write the data only for one DataSink (selected randomly).

3. 

2) When we don't add Template file and Checked "Append to end of file option"

 You can use a groovy script to add property names to a file. Note that, in this case, you will need to enable the "Append to end of file" option and specify the Worksheet name in the DataSink test step options. 

Here is the script example (feel free to optimize):

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
 
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

      String excelFilePath = "D:\\Response.xlsx";    //Provide a path to your output Excel file
      FileInputStream inputStream = new FileInputStream(new File(excelFilePath));
      Workbook workbook = WorkbookFactory.create(inputStream);
 
      Sheet sheet = workbook.getSheetAt(0);  //Specify a sheet by index (starts from zero)
 
      Row row = sheet.createRow(0);
      int columnCount = 0;
      Cell cell = row.createCell(columnCount);

      def data = context.testCase.getTestStepByName("DataSink");  //Provide your DataSink test step name

	 propToGet = data.getPropertyNames();
	 for ( int j = 0 ; j < propToGet.size(); j++ ){
		def name = propToGet[j]
	     log.info name
	     cell.setCellValue(name);
          cell = row.createCell(++columnCount);
		}
 
      inputStream.close();
 
      FileOutputStream outputStream = new FileOutputStream(excelFilePath);
      workbook.write(outputStream);
      workbook.close();
      outputStream.close();

Don't forget to change the values in bold. In particular, a sheet index (or name) should be different for different test cases (since you use different sheets for them).

Also, please note that the script works starting from ReadyAPI 3.0.0.



Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
Highlighted
Contributor

Re: How to Create New Sheet in Existing Excel through Data Sink Including Headers

Thanks for reply @Nastya_Khovrina .

 

1)Yes, I can able to do with Apache POI with Groovy script. But would like to use data sink steps, since using paid version.

 

2) Yesterday had a call with SmatBear system engineer and opened a ticket for Data Sink steps.

 

Highlighted
Community Leader

Re: How to Create New Sheet in Existing Excel through Data Sink Including Headers

@Redde  : You can use the Groovy Code to write the data into any particular Cell but i would suggest not to use same Excel sheet while reading and writing because

 

what i have observed with working on same excel workbook is :

 

Excel file got corrupted if object didn't get closed by your code(anything script failure, run time issue, etc etc) and you try to read the file.

 


Click "Accept as Solution" if my answer has helped, and remember to give "kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal
Highlighted
Community Manager

Re: How to Create New Sheet in Existing Excel through Data Sink Including Headers

Thank you Himanshu!!

@Redde Please keep us updated on the investigation results with the support team.


Sonya Mihaljova
Community and Education Specialist

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
Highlighted
Community Manager

Re: How to Create New Sheet in Existing Excel through Data Sink Including Headers

@Redde , I see that you got a temporary solution in the support ticket. Let me post it here and mark it so, as well. 

Do not hesitate to update this thread!

 

>>

The only way I see out of the box to add headers when creating a new sheet in a excel file is to use a template file. The template file will be written to the Output File in the exact format it is in so the template file should be the same file as the output file. The thing that would need to change dynamically is the "Start at cell" attribute. If you save the last row the output excel file was written to a testsuite property, increment it, and then use dynamic property expansion to retrieve it in the "Start at cell" attribute the new values will be written to the correct row each time. Below is an example of a dynamic property expansion that can be used in the "Start at cell" attribute.

${= return 'A' + context.expand( '${#TestSuite#rowNum}' )}

If the property "rowNum" value is 3 , the dynamic property expansion will return A3 as the "Start at cell" value and data will be written starting at cell A3. Below is a link on dynamic property expansion and how to save values to properties using Groovy.

https://support.smartbear.com/readyapi/docs/testing/properties/expansion.html#dynamic

https://support.smartbear.com/readyapi/docs/testing/scripts/samples/variable.html#get-and-set-proper...

<< 


Sonya Mihaljova
Community and Education Specialist

Learn SmartBear products in a fun and easy way and prove your knowledge!
>>Participate in the TechCorner Challenge today
New Here?
Join us and watch the welcome video:
Announcements
TechCorner Leaderboard
Compete with community members in the TechCorner Challenge and get into the Leaderboard!

Rank Participant Points Earned
1 msiadak 12
2 nmrao 8
3 HimanshuTayal 3
Top Kudoed Authors