Get Number of rows and column in the sheet

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 : Get number of rows and columns using getRows, getColumns method of Sheet class.

sheet. getColumns()

sheet.getRows()

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 SheetIndexOutOfBoundsException extends RuntimeException{

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

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);
    }
    
}

import jxl.*;

public class TestUtils {
    public static void main(String[] args) {
        XLSUtils util = new XLSUtils("D:\\data.xls");
        Sheet sheet = util.getSheet(2);
        
        System.out.println("Number of columns for sheet books " + util.getNumberOfColumns("books"));
        System.out.println("Number of columns for sheet with index 0 " + util.getNumberOfColumns(0));
        System.out.println("Number of columns for sheet products " + util.getNumberOfColumns(sheet));

        System.out.println("Number of rows for sheet books " + util.getNumberOfRows("books"));
        System.out.println("Number of rows for sheet with index 0 " + util.getNumberOfRows(0));
        System.out.println("Number of rows for sheet products " + util.getNumberOfRows(sheet));

        util.closeWorkbook();
    }
}

Output

Number of columns for sheet books 2
Number of columns for sheet with index 0 3
Number of columns for sheet products 2
Number of rows for sheet books 5
Number of rows for sheet with index 0 8
Number of rows for sheet products 5

Prevoius                                                 Next                                                 Home