Read values from an Excel File using Apache POI API

Question

I am using a program to read values from an excel file and then return the read values. I have tried using an iterator as well as a for loop but the program does not return all the values in the worksheet. Please suggest.

 import java.io.FileInputStream;
 import java.util.ArrayList;
 import java.util.Iterator;
 import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellValue;
    import java.io.File;

    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    public class ExcelReading {

public static STring ReadExcel(String Path){
    String FakeReturn = null;
try
    {
        FileInputStream file = new FileInputStream(new File(Path));

        XSSFWorkbook workbook = new XSSFWorkbook(file);

        XSSFSheet sheet = workbook.getSheetAt(0);

        for(Row row:sheet)
       {
            Cell cell=row.getCell(0);
            Cell testcell = row.getCell(1);
           if(cell.getStringCellValue()!=null)
             return testcell.getStringCellValue();
           else
              break;

        }
       file.close();

    } 
    catch (Exception e) 
    {
        System.out.println("The code carries an exception");
        e.printStackTrace();
        return FakeReturn;
    }

 return FakeReturn;
}

}

Do you really think this code will work.

Vinayak Pingale
– Vinayak Pingale

2014-01-26 07:44:13 +00:00
Commented Jan 26, 2014 at 7:44 — Vinayak Pingale
– Vinayak Pingale, Commented Jan 26, 2014 at 7:44

StoopidDonut · Accepted Answer · 2014-01-27 09:49:18Z

3

You exit as soon as you encounter a single specific cell value hence you would always get a single same result all the time.

To exhaustively traverse the sheet, while iterating over the rows, you would have to get a handle on the Iterator of the cells within that row. Once you get a handle on a single Cell instance, store it's value to a Java Collection rather than just 1 single value.

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

 while (rowIterator.hasNext()) {
    Row row = rowIterator.next();

    Iterator<Cell> cellIterator = row.cellIterator();
    while (cellIterator.hasNext()) {
        Cell cell = cellIterator.next();
            cell.getStringCellValue(); //Do something useful with me
...

EDIT: To get a particular column, use CellReference, below:

XSSFSheet ws = wb.getSheet("Sheet1");
CellReference cellReference = new CellReference("A11");
XSSFRow row = sheet.getRow(cellReference.getRow());
if (row != null) {
    XSSFCell cell = row.getCell(cellReference.getCol());
}

edited Jan 27, 2014 at 9:49

answered Jan 26, 2014 at 7:47

StoopidDonut

8,6272 gold badges37 silver badges53 bronze badges

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

2 Comments

IntrepidBlue Over a year ago

Is there a way through which I can read the values of a particular column in the spreadsheet?

StoopidDonut Over a year ago

@user2911856 take a look at the edit, you can user CellReference to get a handle on a particular column

Collectives™ on Stack Overflow

Read values from an Excel File using Apache POI API

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related