how to read a single cell from excel sheet into java program,

Question

I need to read a single cell, (example A1) into my java program. I am able to read out whole content into my program but how can i read only a specific cell?

import java.io.*;
import java.util.Iterator;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

class JavaApplication2
{
static void readXlsx(File inputFile) 
{
try 
{
    // Get the workbook instance for XLSX file
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(inputFile));

    // Get first sheet from the workbook
    XSSFSheet sheet = wb.getSheetAt(0);

    Row row;
    Cell cell;

    // Iterate through each rows from first sheet
    Iterator<Row> rowIterator = sheet.iterator();

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

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

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

            switch (cell.getCellType()) 
            {

            case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;

            case Cell.CELL_TYPE_NUMERIC:
                    System.out.println(cell.getNumericCellValue());
                    break;

            case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getStringCellValue());
                    break;

            case Cell.CELL_TYPE_BLANK:
                    System.out.println(" ");
                    break;

            default:
                    System.out.println(cell);

            }
            }
    }
}
catch (Exception e) 
{
    System.err.println("Exception :" + e.getMessage());
}
}

static void readXls(File inputFile) 
{
try 
{
    // Get the workbook instance for XLS file
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
    // Get first sheet from the workbook
    HSSFSheet sheet = workbook.getSheetAt(0);
    Cell cell;
    Row row;

    // Iterate through each rows from first sheet
    Iterator<Row> rowIterator = sheet.iterator();

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

            // For each row, iterate through each columns
            Iterator<Cell> cellIterator = row.cellIterator();

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

            switch (cell.getCellType()) 
            {

            case Cell.CELL_TYPE_BOOLEAN:
                    System.out.println(cell.getBooleanCellValue());
                    break;

            case Cell.CELL_TYPE_NUMERIC:
                    System.out.println(cell.getNumericCellValue());
                    break;

            case Cell.CELL_TYPE_STRING:
                    System.out.println(cell.getStringCellValue());
                    break;

            case Cell.CELL_TYPE_BLANK:
                    System.out.println(" ");
                    break;

            default:
                    System.out.println(cell);
            }
            }
    }

} 

catch (FileNotFoundException e) 
{
    System.err.println("Exception" + e.getMessage());
}
catch (IOException e) 
{
    System.err.println("Exception" + e.getMessage());
}
}

public static void main(String[] args) 
{
    File inputFile = new File("C://users/admin/Desktop/Content.xls");

    readXls(inputFile);

}
}

this is what I have done, I am reading the whole content but I need a specific cell

With the POI streaming API you can stream the workbook until you find what you need. But because of the way Excel files are structured, you obviously cannot locate the data an `A1` without reading a lot of other information. Further, what if `A1` contains a formula? — Boris the Spider, Dec 17 '14 at 08:34
possible duplicate of [Get Cell Value from Excel Sheet with Apache Poi](http://stackoverflow.com/questions/5578535/get-cell-value-from-excel-sheet-with-apache-poi) — MihaiC, Dec 17 '14 at 08:39
possible duplicate of your own question from yesterday, [How to read column a1 from an Excel sheet and perform string matching to define variables in a Java program](http://stackoverflow.com/questions/27505196/how-to-read-column-a1-from-an-excel-sheet-and-perform-string-matching-to-define) — Jean-François Corbett, Dec 17 '14 at 08:42

Ye Win · Accepted Answer · 2014-12-17T08:46:32.453

4

You can use row.getCell() method.
Try this:

Workbook workbook = WorkbookFactory.create(yourFile.getInputstream());

 Sheet sheet = workbook.getSheet(0);//1,2,3

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

        while (rowIterator.hasNext()) {

            Row row = rowIterator.next();

            row.getCell(0);
            row.getCell(1);
}

edited Dec 17 '14 at 08:46

answered Dec 17 '14 at 08:41

Ye Win

2,020
14
21

Shouldn't you break after the first run of the while loop since you only need to retrieve 1 value no need for it to run any more? – baarkerlounger Dec 17 '14 at 08:45
1

I just point out for getCell method.Well, we can modify the code as program need. – Ye Win Dec 17 '14 at 08:55

score 0 · Answer 2 · edited Nov 04 '22 at 11:20

try {
    FileInputStream inputStream = new FileInputStream(new File("C:\\Users\\HP\\Desktop\\test.xlsx"));
    XSSFWorkbook wb = new XSSFWorkbook(inputStream);
    XSSFSheet sheet = wb.getSheetAt(0);
    Row row;
    Cell cell;
    Iterator<Row> rowIterator = sheet.iterator();
    while (rowIterator.hasNext()) {
        row = (Row) rowIterator.next();
        cell = row.getCell(0);
        int id = (int)cell.getNumericCellValue();
        System.out.println(id);
    }
    
    inputStream.close(); 

} catch (Exception e) {
    System.out.println(e.getStackTrace());
    
}

how to read a single cell from excel sheet into java program,

2 Answers2