jxl : Get particular cell in xls sheet

You can access the cell by specifying column, row (or) by specifying location in string format. Sheet class provides below methods to access cell.

public Cell getCell(int column,int row)

Returns the cell specified at this row and at this column.

public Cell getCell(String loc)

Returns the cell for the specified location eg. "B4".

Step 1 : Instantiate Workbook object

Workbook workbook = Workbook.getWorkbook(new File(path));

Step 2 : Get Sheet reference from workbook object

Sheet sheet = workbook.getSheet(index);

Step 3: Use sheet getCell method to get cell reference.

You can get sample xls sheet here

public class NoSheetWithGivenNameException extends RuntimeException{
    NoSheetWithGivenNameException(String name){
        super("No xls sheet with given name " + name);
    }
}

public class SheetColumnExceedException extends RuntimeException{
    SheetColumnExceedException(int actualColumns, int askingForColumn){
        super("Total columns are : " + actualColumns + " application looking for column " + askingForColumn);
    }
}

public class SheetIndexOutOfBoundsException extends RuntimeException{

    SheetIndexOutOfBoundsException(int index, int actual){
        super("looking for sheet : " + (index+1) + " but actual sheets are : " + actual);
    }
}

public class SheetRowExceedException extends RuntimeException{
    
    SheetRowExceedException(int actualRows, int askingForRow){
        super("Total rows are : " + actualRows + " application looking for row " + askingForRow);
    }

}

public class WorkBookNotInitializedException extends RuntimeException{

    WorkBookNotInitializedException(){
        super("work book is not initialized");
    }
}

import java.io.File;
import java.io.IOException;
import jxl.*;
import jxl.read.biff.BiffException;

public class XLSUtils {

    private Workbook workbook = null;

    public XLSUtils(String path){
        try {
            workbook = Workbook.getWorkbook(new File(path));
        } catch (IOException ex) {
            ex.printStackTrace();
        } catch (BiffException ex) {
            ex.printStackTrace();
        }
    }

    private void checkWorkbookInitialized(){
        if(workbook == null)
            throw new WorkBookNotInitializedException();
    }
    
    public int getNoOfSheets(){
        checkWorkbookInitialized();
        return workbook.getNumberOfSheets();
    }

    public void closeWorkbook(){
        checkWorkbookInitialized();
        workbook.close();
    }

    public Sheet getSheet(int index){
        checkWorkbookInitialized();
        if(index >= workbook.getNumberOfSheets())
            throw new SheetIndexOutOfBoundsException(index, workbook.getNumberOfSheets());
        
        return workbook.getSheet(index);
    }

    public Sheet getSheet(String name){
        checkWorkbookInitialized();
        Sheet sheet = workbook.getSheet(name);
        if(sheet == null)
            throw new NoSheetWithGivenNameException(name);
        return sheet;
    }

    public String[] getSheetNames(){
        checkWorkbookInitialized();
        return workbook.getSheetNames();
    }

    public int getNumberOfRows(Sheet sheet){
        checkWorkbookInitialized();
        return sheet.getRows();
    }

    public int getNumberOfColumns(Sheet sheet){
        checkWorkbookInitialized();
        return sheet.getColumns();
    }

    public int getNumberOfRows(int sheetIndex){
        Sheet sheet = getSheet(sheetIndex);
        return getNumberOfRows(sheet);
    }

    public int getNumberOfRows(String sheetName){
        Sheet sheet = getSheet(sheetName);
        return getNumberOfRows(sheet);
    }

    public int getNumberOfColumns(int sheetIndex){
         Sheet sheet = getSheet(sheetIndex);
         return getNumberOfColumns(sheet);
    }

    public int getNumberOfColumns(String sheetName){
         Sheet sheet = getSheet(sheetName);
         return getNumberOfColumns(sheet);
    }

    public Cell getCell(int sheetIndex, int column, int row){
        Sheet sheet = getSheet(sheetIndex);
        return getCell(sheet, column, row);
    }

    public Cell getCell(String sheetName, int column, int row){
        Sheet sheet = getSheet(sheetName);
        return getCell(sheet, column, row);
        
    }

    public Cell getCell(Sheet sheet, int column, int row){
        int actualColumns = sheet.getColumns()-1;
        int actualRows = sheet.getRows() - 1;
        
        if(column > actualColumns)
            throw new SheetColumnExceedException(actualColumns, column);

        if(row > actualRows)
            throw new SheetColumnExceedException(actualRows, row);

        return sheet.getCell(column, row);
    }

    public Cell getCell(int sheetIndex, String loc){
        Sheet sheet = getSheet(sheetIndex);
        return getCell(sheet, loc);
    }

    public Cell getCell(String sheetName, String loc){
        Sheet sheet = getSheet(sheetName);
        return getCell(sheet, loc);
    }

    public Cell getCell(Sheet sheet, String loc){
        return sheet.getCell(loc);
    }
    
}

import jxl.*;

public class TestUtils {
    public static void main(String[] args) {
        XLSUtils util = new XLSUtils("D:\\data.xls");

        Cell cell1 = util.getCell("books", 1, 3);
        Cell cell2 = util.getCell("books", "B4");

        System.out.println(cell1.getContents());
        System.out.println(cell2.getContents());
        
        util.closeWorkbook();
    }
}

Output

Bodhidharma: The Greatest Zen Master
Bodhidharma: The Greatest Zen Master

Prevoius                                                 Next                                                 Home