If you are using MAVEN you can add these dependencies into your POM.XML file :
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.10-FINAL</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.10-FINAL</version>
</dependency>
Below is the excel file :
package com.io.mnb;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadingExcelFile {
public static void main(String[] args) throws IOException {
readExcelFile();
}
public static void readExcelFile() throws IOException {
List<Employee> empList = new ArrayList<Employee>();
File file = new File("EmpData.xls");
Workbook workBook = null;
if (file.getName().contains("xlsx")) {
workBook = new XSSFWorkbook(new FileInputStream(file));
} else {
workBook = new HSSFWorkbook(new FileInputStream(file));
}
Sheet sheet = workBook.getSheet("Emp_Details");
Iterator<Row> rows = sheet.rowIterator();
while (rows.hasNext()) {
Row row = rows.next();
Employee e = new Employee();
Iterator<Cell> cells = row.cellIterator();
while (cells.hasNext()) {
Cell cell = cells.next();
switch (cell.getCellType()) {
case Cell.CELL_TYPE_NUMERIC:
// System.out.print(cell.getNumericCellValue() + "\t");
if (cell.getColumnIndex() == 0)
e.setEmpId((int) cell.getNumericCellValue());
else
e.setEmpSal((int) cell.getNumericCellValue());
break;
case Cell.CELL_TYPE_STRING:
// System.out.print(cell.getStringCellValue() + "\t");
if (cell.getColumnIndex() == 1)
e.setEmpName(cell.getStringCellValue());
else if (cell.getColumnIndex() == 2)
e.setEmpDesig(cell.getStringCellValue());
else
e.setEmpDept(cell.getStringCellValue());
break;
}
}
// System.out.println("");
empList.add(e);
}
System.out.println("A) Read All employee details form the excel sheet and print it in console");
for (Employee e : empList) {
System.out.println(e);
}
System.out.println("B) Find the employee details who are having salary greater than 10000");
for (Employee e : empList) {
if (e.getEmpSal() > 10000)
System.out.println(e);
}
System.out.println("C) Find the employee details who are having salary between 10000 to 20000");
for (Employee e : empList) {
if (e.getEmpSal() < 20000 && e.getEmpSal() > 10000)
System.out.println(e);
}
Collections.sort(empList, new Employee());
System.out.println("D) Sort the employee names by alphabetical order");
for (Employee e : empList) {
System.out.println(e);
}
}
static class Employee implements Comparator<Employee> {
private int empId;
private String empName;
private String empDesig;
private String empDept;
private int empSal;
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getEmpName() {
return empName;
}
public void setEmpName(String empName) {
this.empName = empName;
}
public String getEmpDesig() {
return empDesig;
}
public void setEmpDesig(String empDesig) {
this.empDesig = empDesig;
}
public String getEmpDept() {
return empDept;
}
public void setEmpDept(String empDept) {
this.empDept = empDept;
}
public int getEmpSal() {
return empSal;
}
public void setEmpSal(int empSal) {
this.empSal = empSal;
}
@Override
public String toString() {
return empId + " : " + empName + " : " + empDesig + " : " +
empDept+ " : " + empSal;
}
@Override
public int compare(Employee o1, Employee o2) {
return o1.empName.compareTo(o2.empName);
}
}
}
Output:
A) Read All employee details form the excel sheet and print it in console
1 : Sachin : SE : Java : 20000
2 : Dhoni : SE : Java : 15000
3 : Rohit : SE : Java : 12000
4 : Kohli : SE : Java : 18000
B) Find the employee details who are having salary greater than 10000
1 : Sachin : SE : Java : 20000
2 : Dhoni : SE : Java : 15000
3 : Rohit : SE : Java : 12000
4 : Kohli : SE : Java : 18000
C) Find the employee details who are having salary between 10000 to 20000
2 : Dhoni : SE : Java : 15000
3 : Rohit : SE : Java : 12000
4 : Kohli : SE : Java : 18000
D) Sort the employee names by alphabetical order
2 : Dhoni : SE : Java : 15000
4 : Kohli : SE : Java : 18000
3 : Rohit : SE : Java : 12000
1 : Sachin : SE : Java : 20000
No comments:
Post a Comment