When using TestNG DataProvider is there a way to read and write parameters from the SAME Excel sheet?

Question

I'm still relatively new to both Java and TestNG, so forgive me if I use incorrect technical terms. I'll keep an eye out for responses throughout the day in case I need to correct myself or clarify.

In the automated test that I am writing, I am passing my parameters through an Excel sheet via Data Provider. That whole part is fine. However, I would now like to perform a getText() on an element and copy that String BACK to the excel sheet that Data Provider was reading from.

Here is an example of my intentions:

//Grabs Parameters to be used from Excel Sheet
@DataProvider
public Object[][] getData(){
suiteXls = new Xls_Reader(System.getProperty("user.dir")+"//src//testData.xlsx")
List<String> sheetNames = new ArrayList<String>();
sheetNames.add("TestStrings");
return TestUtilFinal.getData(suiteXls,sheetNames);
}

//Test Begins
@Test(dataProvider="getData")
public void Test1(String Value_1, String Operator, String Value_2, String Output){
// do some work here
Output.saveToCell();

An Issue that I potentially see is that the cell that the value would be saved in would be dynamic.

Here is what the excel sheet would look like before the test is run

Here is what the same sheet would like AFTER the test is run. Any help or insight would be greatly appreciated! Thanks

Answer 1

1. I recommend treating your input as immutable. I find that when you modify your input to a process that when you repeat that process (as is often done in testing) that it then quickly becomes unclear as to where the current results came from. It is generally much better to create a unique output file that contains some/all of the input data along with the output data. I often name such output files with a build revision and/or a time-stamp so that each one is unique and referable.
2. If you really want to modify your input then you'll need to have a reference to the Excel "Row" object for the test. e.g. You could add such to your data provided to the test.
3. It isn't clear from your getData method but I suspect that you are using Apache POI (either directly or indirectly) to read data from the Excel Worksheet. There are plenty of examples on StackOverflow and elsewhere for reading and writing rows of data using Apache POI. You simply need to maintain a reference to your test case's Row object and then know which column (cellnum) to write your output to.