How to modify parameters for testing using excel or other approach?

12
August 21, 2019, at 05:20 AM

I would like to separate the testing parameters from the testing script to achieve data-driven automation testing. I have to be under java 1.6 environment so I cannot utilize testNG which requires java 1.7+. Therefore, I need to use some workaround like apache-poi to read excel files and configure parameters for achieving my goal.

I cannot quite understand how to get the row of value in column A from xls which has been switched on as "1" on B column or other notion to be the testing parameter in-use. I don't know if apache-poi is supposed to be doing such job as I have done my research in this community but seem no one is using it in such way.

 |  A  | B
1| ABC | 1
2| DEF | 0
3| GHI | 0
4| JKL | 0

1 and 0 from B column could be other expression of booleans and could be multiple positive results but the idea is to list out all the parameter options and switch them on or off for use in the testing script. Just like in the sql idea of Select A from table where B = 1. But I can only get the value "1" instead of "ABC" during my use.

The script would be getStringCellValue or something with the value content of "ABC" as the parameter for running the test instead of putting "ABC" straight inside the script and change it/them every time when we need to use different parameters.

btw could there be other way of using sql syntax and get the value from a file? I'm not bonded with using xls or not. But .xml parameters using testNG is not feasible for me because I need to be in the java 1.6 environment as mentioned.

Thanks for reading.

UPDATE:

    HSSFSheet hspsheet = workbook.getSheetAt(0);
    List<HSSFRow> filteredhspRows = new ArrayList<HSSFRow>();
    Iterator<Row> hspritr = hspsheet.iterator();
    while (hspritr.hasNext()) {
    HSSFRow rows = (HSSFRow) hspritr.next();
    Iterator<Cell> hspcitr = rows.cellIterator();
    while (hspcitr.hasNext())
    {
        HSSFCell cell = (HSSFCell) hspcitr.next();
        if (cell.toString().contains("T")) 
        {
            filteredhspRows.add(rows);
            System.out.println(cell.getStringCellValue());
        }
    }
    }

By using the above codes, I am only being able to get the cells containing "T" but not only getting the value in Column A when its corresponding value (same row) in Column B is "T".

Answer 1

As far as I know there is no SQL-syntax while accessing a spreadsheet via Apache POI.

If I understood correctly you are more or less interested in the values of A, where B has value 1.

You could use the following code to find out all the relevant A-values by simply iterating over the rows of the sheet:

Sheet sheet = workbook.getSheetAt(0);
Iterator<Row> rowIterator = sheet.rowIterator();
Collection<String> relevantValuesFromA = new ArrayList<String>();
while (rowIterator.hasNext()) {
    Row row = rowIterator.next();
    Cell bCellOfRow = row.getCell(1);
    if ("1".equals(bCellOfRow.getStringCellValue())) {
        relevantValuesFromA.add(row.getCell(0).getStringCellValue());
    }
}
//now you can use relevantValuesFromA for your tests
Answer 2

This is for get cell value only in first row.

Remove the condition : if (cell.toString().contains("T")) and remove state break;

Iterator<Cell> hspcitr = rows.cellIterator();
while (hspcitr.hasNext())
{
    HSSFCell cell = (HSSFCell) hspcitr.next();
    filteredhspRows.add(rows);
    System.out.println(cell.getStringCellValue());
}

But if you want get all cell each row, use :

while (hspritr.hasNext ()){
    HSSFRow rows = (HSSFRow) hspritr.next();
    Iterator<Cell> hspcitr = rows.cellIterator();
    while (hspcitr.hasNext())
    {
        HSSFCell cell = (HSSFCell) hspcitr.next();
        filteredhspRows.add(rows);
        System.out.println(cell.getStringCellValue());
    }
}
READ ALSO
Understanding exceptions in Java with interfaces

Understanding exceptions in Java with interfaces

I have the following code which has two interfaces which have two methods of the same nameHowever each method throws a different type of Exception

17
Intellij Project some files are not updating on compile

Intellij Project some files are not updating on compile

I am trying to compile an IntelliJ project, and have been working on it for over a month with no issuesToday I tried to compile and the changes I made in certain files did not make it into the compiled jar

47
Google Script MailApp - Setting Timer

Google Script MailApp - Setting Timer

Are there any possibilities that I can control how long it takes after a form has been submitted before it will send an email out?

51
Content overwritten on top of another when formula is applied to excel cell using Apache poi

Content overwritten on top of another when formula is applied to excel cell using Apache poi

I am getting an issue of content being overwritten on top of another ie old value and new values overlap after applying formula

32