How to convert xls to Collection with Apache POI?

Question

I want to make a paramterized junit test using @RunWith(Parameterized.class) and

@Parameterized.Parameters
public static Collection<String[]> testdata() {
    return Arrays.asList(new String[][] {
            { "inParam1", "inPAram2", "expectedOut1", "expectedOut2" } 
        });
}

The actual testdata shall be created by business people via Excel.

Is there an easy / generic way to get an Apache POI XSSFSheet to the prescribed Collection of String arrays?

If yes: can someone provide an example please ?

I found this question: Datadriven Testing in TestNG using Apache POI --- but I'd expect a kind of a 3-liner ;-)

score 2 · Accepted Answer · answered Sep 17 '14 at 18:33

It isn't quite a 3 liner, but assuming I've correctly understood your needs, you can do something like:

 Sheet sheet = workbook.getSheetAt(0);
 DataFormatter fmt = new DataFormatter();
 List<List<String>> cellData = new ArrayList<List<String>>();
 for (Row r : sheet) {
    List<String> rd = new ArrayList<String>();
    for (Cell c : r) {
       rd.add(fmt.formatCellValue(c));
    }
    cellData.add(rd);
 }

That will, for all defined rows and cells, of any time, generate you a list of list of strings, one list per row, one string per cell in that. You can switch from lists to arrays fairly easily

If you need more control over what cells/rows are/aren't included, including for empty ones, see the Iterating guide in the documentation

How to convert xls to Collection with Apache POI?

1 Answers1