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; }
Above
snippet gives you sheet data as list of rows.
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; } List<List<Cell>> getSheetData(int sheetIndex){ Sheet sheet = getSheet(sheetIndex); return getSheetData(sheet); } List<List<Cell>> getSheetData(String sheetName){ Sheet sheet = getSheet(sheetName); return getSheetData(sheet); } 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; } }
import jxl.*; import java.util.List; public class TestUtils { public static void main(String[] args) { XLSUtils util = new XLSUtils("D:\\data.xls"); List<List<Cell>> data = util.getSheetData(0); for(List<Cell> list : data){ for(Cell c1 : list){ System.out.print(c1.getContents() + " "); } System.out.println(); } util.closeWorkbook(); } }
Output
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
No comments:
Post a Comment