How to convert xls to Collection<String[]> 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 ;-)

Gagravarr · Accepted Answer · 2014-09-17 18:33:36Z

2

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

answered Sep 17, 2014 at 18:33

Gagravarr

48.5k11 gold badges114 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to convert xls to Collection<String[]> with Apache POI?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related