Forum Discussion

PrathapR's avatar
PrathapR
Frequent Contributor
5 years ago

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.

 

 

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

 

 

 

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

 

Thanks in adavnce and appreciated.

  • Nastya_Khovrina's avatar
    Nastya_Khovrina
    SmartBear Alumni (Retired)

    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.

    • PrathapR's avatar
      PrathapR
      Frequent Contributor

      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.

       

      • HimanshuTayal's avatar
        HimanshuTayal
        Community Hero

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