jxl : Get all sheets data

Below Application provides three methods, which returns xls data in three different formats.

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

Prevoius                                                 Next                                                 Home