java excel read one column

Question

I have a column (B) that I need to take all the values between B3 and B20

this is my code

try {
            OPCPackage fs;

            fs =  OPCPackage.open(new File(getFilePath()));

            XSSFWorkbook wb = new XSSFWorkbook(fs);
            XSSFSheet sheet = wb.getSheet("Master column name - Used Car");
            XSSFRow row;
            CellReference cr = new CellReference("B3");

            row = sheet.getRow(4); 
            System.out.println(row);

but as you see, i am getting one value, i didn't know how to get the values for cells B3 until B20

could you help please

micsthepick · Answer 1 · 2015-08-06T22:24:23.633

1

have you tried replacing this line:

CellReference cr = new CellReference("B3");

with:

AreaReference ar = new AreaReference("B3:B20");

i.e.

AreaReference ar = new AreaReference("B3:B20");

for (cr : ar.getAllReferencedCells()) {
    System.out.print(cr.formatAsString());
    System.out.print(" - ");
}
System.out.println();

edited Aug 06 '15 at 22:24

answered Aug 06 '15 at 21:53

micsthepick

562
7
23

i got `Java.lang.NumberFormatException: For input string: "3:B20"` – Marco Dinatsoli Aug 06 '15 at 21:57
use `AreaReference(CellReference topLeft, CellReference botRight)` – micsthepick Aug 06 '15 at 22:01
could you give me a working code? this code doesn't explain anything – Marco Dinatsoli Aug 06 '15 at 22:03
the answer provides the code you can replace, if you need each individual reference, you can use the method: `getAllReferencedCells()` – micsthepick Aug 06 '15 at 22:05
1

i.e. `System.out.println(cr.getAllReferencedCells());` – micsthepick Aug 06 '15 at 22:06

Jessie.Y · Answer 2 · 2015-12-08T02:56:17.913

To read values of certain column or cell from excel you might try this

public static void readFromExcel2(){
    try{
        FileInputStream file = new FileInputStream(new File("java_excel.xlsx"));//place path of your excel file
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        XSSFSheet sheet = workbook.getSheetAt(1);//which sheet you want to read

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

                Cell cell = cellIterator.next();
                if(cell.getColumnIndex()<2&&(cell.getRowIndex()>=3&&cell.getRowIndex()<=20)) {
                     {

                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                System.out.print((int) cell.getNumericCellValue()+" \t" );
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                System.out.print(" ");
                                break;
                            case Cell.CELL_TYPE_STRING: {
                                System.out.print(cell.getStringCellValue());
                            }

                        }
                    }
                }
            }
            System.out.println(" ");
        }
        file.close();
    }catch (Exception e){
        e.printStackTrace();
    }
}

}

java excel read one column

2 Answers2