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

HI PrathapR,

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-Excel-DataSink/idi-p/148609

2. 

---

PrathapR 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.