/**
*
*/
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();
}
}
}