/** * */ import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.util.Iterator; import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.usermodel.WorkbookFactory; public class SpreadSheetReader { Object row = null; Cell cell = null; public static int Cell_Total = 0; public static int Cell_Actual = 0; public static void main(String args[]) { SpreadSheetReader read_Obj = new SpreadSheetReader(); try { read_Obj.readFile(); } catch (FileNotFoundException e) { e.printStackTrace(); } } /* * To read the file */ private void readFile() throws FileNotFoundException { File inputFile = new File("D:\\EPIW.xls"); FileInputStream fileIStream = new FileInputStream(inputFile); try { Workbook workbook = WorkbookFactory.create(fileIStream); for (int Sh_Count = 0; Sh_Count < workbook.getNumberOfSheets(); Sh_Count++) { Sheet sheet = workbook.getSheetAt(Sh_Count); System.out.println("Sheet Name :" + workbook.getSheetName(Sh_Count)); read_Rows(sheet); read_Columns(sheet); } } catch (IOException e) { e.printStackTrace(); } catch (InvalidFormatException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } /* * Reading column wise */ @SuppressWarnings("deprecation") private void read_Columns(Sheet sheet) { Iterator rows = sheet.rowIterator(); int i = 0; while (i <= Cell_Total) { try { while (rows.hasNext()) { try { row = rows.next(); Cell_Actual = ((org.apache.poi.ss.usermodel.Row) row).getLastCellNum(); if (Cell_Total < Cell_Actual) { Cell_Total = Cell_Actual; } cell = ((org.apache.poi.ss.usermodel.Row) row).getCell(i); getcellValue(cell); } catch (RuntimeException e) { // TODO Auto-generated catch block e.printStackTrace(); } } i++; rows = sheet.rowIterator(); } catch (RuntimeException e) { // TODO Auto-generated catch block e.printStackTrace(); } } Cell_Total = 0; } /* * Reading Row wise */ private void read_Rows(Sheet sheet) { System.out.println("Reading Rows...."); Iterator rows = sheet.rowIterator(); while (rows.hasNext()) { try { row =rows.next(); Iterator cells = ((org.apache.poi.ss.usermodel.Row) row).cellIterator(); while (cells.hasNext()) { cell = (Cell) cells.next(); getcellValue(cell); } } catch (RuntimeException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } /* * Handling various types of cells * */ private void getcellValue(Cell cell2) { try { if ((cell2.getCellType() == Cell.CELL_TYPE_STRING)) { System.out.println(cell2.getStringCellValue()); } else if (cell2.getCellType() == Cell.CELL_TYPE_NUMERIC) { } else if (cell2.getCellType() == Cell.CELL_TYPE_FORMULA) { } else if (cell2.getCellType() == Cell.CELL_TYPE_BLANK) { } else { } } catch (Exception e) { e.getMessage(); } } }