Java Application to process xls sheets

jxl package provide APIs to read and write excel sheets. “XLSUtils.java” class uses jxl package and provides below functionality.

public int getNoOfSheets()

Returns number of xls sheets in given xls file.

public Sheet getSheet(int index)

Returns sheet with given index.

public Sheet getSheet(String name)

Return sheet with given name

public String[] getSheetNames()

Returns all sheet names in given xls file

public int getNumberOfColumns(Sheet sheet)

public int getNumberOfRows(int sheetIndex)

public int getNumberOfRows(String sheetName)

Above three methods return number of rows in the sheet of given xls file. First method takes sheet as argument, second takes sheet index in xls file and third takes sheet name in xls file.

public int getNumberOfColumns(Sheet sheet)

public int getNumberOfColumns(int sheetIndex)

public int getNumberOfColumns(String sheetName)

Above three methods return number of columns in the sheet of given xls file. First method takes sheet as argument, second takes sheet index in xls file and third takes sheet name in xls file.

public Cell getCell(int column,int row)

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

public Cell getCell(String loc)

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

List<Cell> getEntireRow(int sheetIndex, int row)

List<Cell> getEntireRow(String sheetName, int row)

List<Cell> getEntireRow(Sheet sheet, int row)

Above three methods return a row in xls sheet. These methods takes sheet index (or) sheet name (or) sheet as parameter in combination with row number and return all the fields in row as list of cells.

List<Cell> getEntireColumn(int sheetIndex, int column)

List<Cell> getEntireColumn(String sheetName, int column)

List<Cell> getEntireColumn(Sheet sheet, int column)

Above three methods return entire column data in xls sheet. These methods takes sheet index (or) sheet name (or) sheet as parameter in combination with column number and return all the cells in column as list of cells.

List<List<Cell>> getSheetData(Sheet sheet)

Return sheet data as list of rows.

Map<Integer, List<List<Cell>>> getAllSheetsDataIndexAsKey()

Returns sheets data as a map, where key is sheet index, and value is list of rows in the sheet.

       

Map<String, List<List<Cell>>> getAllSheetsDataSheetNameAsKey()

Returns sheets data as a map, where key is sheet name, and value is list of rows in the sheet.

       

Map<Sheet, List<List<Cell>>> getAllSheetsDataSheetAsKey()

Returns sheets data as a map, where key is sheet, and value is list of rows in the sheet.

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 totallColumns, int askingForColumn){
        super("Total columns are : " + totallColumns + " 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 totalRows, int askingForRow){
        super("Total rows are : " + totalRows + " 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;
import java.util.List;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

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

    public List<Cell> getEntireRow(int sheetIndex, int row){
        Sheet sheet = getSheet(sheetIndex);
        return getEntireRow(sheet, row);
    }

    public List<Cell> getEntireRow(String sheetName, int row){
        Sheet sheet = getSheet(sheetName);
        return getEntireRow(sheet, row);
    }

    public List<Cell> getEntireRow(Sheet sheet, int row){
        int totalRows = sheet.getRows()-1;
        if(row > totalRows)
            throw new SheetRowExceedException(totalRows, row);
        List<Cell> list = new ArrayList<Cell> ();

        for(int column=0; column< sheet.getColumns(); column++){
            list.add(sheet.getCell(column, row));
        }
        return list;
    }

    public List<Cell> getEntireColumn(int sheetIndex, int column){
        Sheet sheet = getSheet(sheetIndex);
        return getEntireColumn(sheet, column);
    }

    public List<Cell> getEntireColumn(String sheetName, int column){
        Sheet sheet = getSheet(sheetName);
        return getEntireColumn(sheet, column);
    }

    public List<Cell> getEntireColumn(Sheet sheet, int column){
        int totalColumns = sheet.getColumns()-1;
        if(column > totalColumns)
            throw new SheetColumnExceedException(totalColumns, column);
        
        List<Cell> list = new ArrayList<Cell> ();

        for(int row=0; row<sheet.getRows(); row++){
            list.add(sheet.getCell(column, row));
        }
        return list;
    }

    public List<List<Cell>> getSheetData(int sheetIndex){
        Sheet sheet = getSheet(sheetIndex);
        return getSheetData(sheet);
    }

    public List<List<Cell>> getSheetData(String sheetName){
        Sheet sheet = getSheet(sheetName);
        return getSheetData(sheet);
    }

    public List<List<Cell>> getSheetData(Sheet sheet){
        List<List<Cell>> data = new ArrayList<List<Cell>> ();
        int columns = sheet.getColumns();
        int rows =  sheet.getRows();

        for(int row=0; row<rows; row++){
            List<Cell> list = new ArrayList<Cell> ();
            for(int column=0; column<columns; column++){
                list.add(sheet.getCell(column, row));
            }
            data.add(list);
        }
        return data;
    }

    public Map<Integer, List<List<Cell>>> getAllSheetsDataIndexAsKey(){
        checkWorkbookInitialized();
        Map<Integer, List<List<Cell>>> map = new HashMap<Integer, List<List<Cell>>> ();

        for(int i=0; i<workbook.getNumberOfSheets(); i++){
            List<List<Cell>> list = getSheetData(i);
            map.put(i, list);
        }

        return map;
    }

    public Map<String, List<List<Cell>>> getAllSheetsDataSheetNameAsKey(){
        checkWorkbookInitialized();
        Map<String, List<List<Cell>>> map = new HashMap<String, List<List<Cell>>> ();
        String[] indexNames = workbook.getSheetNames();

        for(String sheetName : indexNames){
            map.put(sheetName, getSheetData(sheetName));
        }
        return map;
    }

    Map<Sheet, List<List<Cell>>> getAllSheetsDataSheetAsKey(){
         checkWorkbookInitialized();
         Map<Sheet, List<List<Cell>>> map = new HashMap<Sheet, List<List<Cell>>> ();

         for(int i=0; i<workbook.getNumberOfSheets(); i++){
            Sheet sheet = workbook.getSheet(i);
            List<List<Cell>> list = getSheetData(i);
            map.put(sheet, list);
        }

         return map;
    }
}

import jxl.*;
import java.util.List;
import java.util.Map;
import java.util.Set;

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

        Map<String, List<List<Cell>>> map = util.getAllSheetsDataSheetNameAsKey();
        Set<String> keys = map.keySet();

        for(String sheetName : keys){
            System.out.println(sheetName);
            System.out.println("******************");
            List<List<Cell>> sheetData = map.get(sheetName);

            for(List<Cell> list : sheetData){
                for(Cell cell : list) {
                    System.out.print(cell.getContents() + " ");
                }
                System.out.println();
            }
        }
        util.closeWorkbook();
    }
}

Output

books
******************
1 Sex Matters: From Sex to Superconsciousness 
2 Above All, Don’t Wobble 
3  Ancient Music in the Pines 
4 Bodhidharma: The Greatest Zen Master 
5 The Secret of Secrets 
employee
******************
1 PTR Nayan 
2 Rama Krishna Gurram 
3 Gopi Battu 
4 Jaideep Gera 
5 Shanmugham Chinnayappayan 
6 Janaki Sriram 
7 Reshmi Jeorge 
8 Hari Nalasani 
products
******************
1 Laptop 
2 Computer 
3 mobile 
4 bike 
5 car

Related links

      Get number of sheets in xls file

      Get particular sheet in xls file

      Get all sheet names in xls file

      Get Number of rows and column in the sheet

      Get particular cell in xls sheet

      Get a row from xls sheet

      Get entire column data in xls sheet

      Get total sheet data

      Get all sheets data

 

Prevoius                                                 Next                                                 Home