Step 1: Get the sheet
reference.
Step 2: Get each cell in
that column and add it to list and return.
for(int
row=0; row<sheet.getRows(); row++){
list.add(sheet.getCell(column, row));
}
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; 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); } List<Cell> getEntireRow(int sheetIndex, int row){ Sheet sheet = getSheet(sheetIndex); return getEntireRow(sheet, row); } List<Cell> getEntireRow(String sheetName, int row){ Sheet sheet = getSheet(sheetName); return getEntireRow(sheet, row); } 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; } List<Cell> getEntireColumn(int sheetIndex, int column){ Sheet sheet = getSheet(sheetIndex); return getEntireColumn(sheet, column); } List<Cell> getEntireColumn(String sheetName, int column){ Sheet sheet = getSheet(sheetName); return getEntireColumn(sheet, column); } 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; } }
import jxl.*; import java.util.List; public class TestUtils { public static void main(String[] args) { XLSUtils util = new XLSUtils("D:\\data.xls"); System.out.println("Last names of employees are"); System.out.println("----------------------------"); List<Cell> list = util.getEntireColumn("employee", 2); for(Cell cell : list){ System.out.println(cell.getContents() + " "); } util.closeWorkbook(); } }
Output
Last names of employees are
----------------------------
Nayan
Gurram
Battu
Gera
Chinnayappayan
Sriram
Jeorge
Nalasani
No comments:
Post a Comment