how to iterate first columns then rows to read excel sheet in java

Question

i want to read an excel sheet so i want to read my 1st column data and iterate all rows then 2nd column so how to do it.

i have tried using row iteration first then followed by coloumn

  Iterator < Row > rowIterator = sheetName.iterator();

  while (rowIterator.hasNext()) {
   Row row = rowIterator.next();
   Iterator < Cell > cellIterator = row.cellIterator();

   while (cellIterator.hasNext()) {
    Cell cell = cellIterator.next();
   }
  }

[this](http://stackoverflow.com/questions/1516144/how-to-read-and-write-excel-file-in-java) might help — andreim, May 04 '17 at 05:26
Check [this](http://stackoverflow.com/questions/27499147/reading-specific-column-of-excel-into-java-program) and [this](http://stackoverflow.com/questions/5551815/read-a-single-column-of-excel-sheet-using-java) too — Sajjad, May 04 '17 at 05:27

score 0 · Accepted Answer · answered May 04 '17 at 06:18

0

If i understand you question properly then you are trying to iterate over column first then row . This is what you might be trying to do. Hope it helps :

Sheet sheet = workbook.getSheetAt(0);
    for(int i=0; i<sheet.getRow(sheet.getTopRow()).getLastCellNum(); i++){
        Iterator<Row> rowIterator=sheet.rowIterator();
        while(rowIterator.hasNext()){
            System.out.println(rowIterator.next().getCell(i)); //do what you want

        }

    }

answered May 04 '17 at 06:18

Zeeshan Adnan

769
6
10

Thank you for Your Answer i will try it – santosh s May 04 '17 at 07:57
yeah its comming ascolumn first then row . But i am unable to skip the rows and can't fetch the required rows data. – santosh s May 04 '17 at 08:14

score 0 · Answer 2 · edited Jul 05 '19 at 07:00

If I understand the question, you want to read the data from excel column-wise. i.e. read the entire Column1 and then move to Column2 and so on.

If that is your requirement, Please find for the code snippet in java.

// Getting List of Column Headers in the sheet and saving the same to a list
int masterSheetColumnIndex = sheet.getColumns();
List<String> ExpectedColumns = new ArrayList<String>();
for (int x = 0; x < masterSheetColumnIndex; x++) {
    Cell celll = sheet.getCell(x, 0);
    String d = celll.getContents();
    System.out.println("d : "+d);
    ExpectedColumns.add(d);
}

columnDataValues = new LinkedHashMap<String, List<String>>();
List<String> column1 = new ArrayList<String>();
// read values from excel sheet for each column
for (int j = 0; j < masterSheetColumnIndex; j++) {
    column1 = new ArrayList<String>();
    for (int i = 1; i < sheet.getRows()-7; i++) {
        Cell cell = sheet.getCell(j, i);
        column1.add(cell.getContents());
        System.out.println("cell.getContents() : "+cell.getContents());
    }
    columnDataValues.put(ExpectedColumns.get(j), column1);
}

Please revert in case of any queries in above code.

how to iterate first columns then rows to read excel sheet in java

2 Answers2