StudentBean.Java类,用来存放学生信息
package com.demoexcel.util;
public class StudentBean {
private String no; //学号
private String name; //姓名
private String age; //年龄
private float score; //成绩
public StudentBean() {
super();
}
public StudentBean(String no, String name, String age, float score) {
super();
this.no = no;
this.name = name;
this.age = age;
this.score = score;
}
public String getNo() {
return no;
}
public void setNo(String no) {
this.no = no;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getAge() {
return age;
}
public void setAge(String age) {
this.age = age;
}
public float getScore() {
return score;
}
public void setScore(float score) {
this.score = score;
}
}
ReadExcel.java类,用来解析并读取excel文件
package com.demoexcel.util;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ReadExcel {
/**
* 读取excel中的数据
* @param path
* @return List<StudentBean>
* @author zhang 2015-08-18 00:08
*/
public List<StudentBean> readExcel(String path) {
if (path != null && !path.equals("")) {
String ext = getExt(path);
if (ext!=null && !ext.equals("")) {
if (ext.equals("xls")) {
return readXls(path);
} else if (ext.equals("xlsx")) {
return readXlsx(path);
}
}
}
return new ArrayList<StudentBean>();
}
/**
* 读取后缀为xls的excel文件的数据
* @param path
* @return List<StudentBean>
* @author zhang 2015-08-18 00:10
*/
private List<StudentBean> readXls(String path) {
HSSFWorkbook hssfWorkbook = null;
try {
InputStream is = new FileInputStream(path);
hssfWorkbook = new HSSFWorkbook(is);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
StudentBean studentBean = null;
List<StudentBean> list = new ArrayList<StudentBean>();
if (hssfWorkbook != null) {
// Read the Sheet
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum);
if (hssfRow != null) {
studentBean = new StudentBean();
HSSFCell no = hssfRow.getCell(0);
HSSFCell name = hssfRow.getCell(1);
HSSFCell age = hssfRow.getCell(2);
HSSFCell score = hssfRow.getCell(3);
studentBean.setNo(getValue(no));
studentBean.setName(getValue(name));
studentBean.setAge(getValue(age));
studentBean.setScore(Float.valueOf(getValue(score)));
list.add(studentBean);
}
}
}
}
return list;
}
/**
* 读取后缀为xlsx的excel文件的数据
* @param path
* @return List<StudentBean>
* @author zhang 2015-08-18 00:08
*/
private List<StudentBean> readXlsx(String path) {
XSSFWorkbook xssfWorkbook = null;
try {
InputStream is = new FileInputStream(path);
xssfWorkbook = new XSSFWorkbook(is);
} catch (IOException e) {
e.printStackTrace();
}
StudentBean studentBean = null;
List<StudentBean> list = new ArrayList<StudentBean>();
if(xssfWorkbook!=null){
// Read the Sheet
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
// Read the Row
for (int rowNum = 1; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum);
if (xssfRow != null) {
studentBean = new StudentBean();
XSSFCell no = xssfRow.getCell(0);
XSSFCell name = xssfRow.getCell(1);
XSSFCell age = xssfRow.getCell(2);
XSSFCell score = xssfRow.getCell(3);
studentBean.setNo(getValue(no));
studentBean.setName(getValue(name));
studentBean.setAge(getValue(age));
studentBean.setScore(Float.valueOf(getValue(score)));
list.add(studentBean);
}
}
}
}
return list;
}
/**
* 获取文件扩展名
* @param path
* @return String
* @author zhang 2015-08-17 23:26
*/
private String getExt(String path) {
if (path == null || path.equals("") || !path.contains(".")) {
return null;
} else {
return path.substring(path.lastIndexOf(".") + 1, path.length());
}
}
/**
* 判断后缀为xlsx的excel文件的数据类型
* @param xssfRow
* @return String
* @author zhang 2015-08-18 00:12
*/
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue());
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue());
} else {
return String.valueOf(xssfRow.getStringCellValue());
}
}
/**
* 判断后缀为xls的excel文件的数据类型
* @param hssfCell
* @return String
* @author zhang 2015-08-18 00:12
*/
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue());
} else {
return String.valueOf(hssfCell.getStringCellValue());
}
}
public static void main(String[] args) {
System.out.println(new ReadExcel().readExcel("D://student_info.xlsx").size());
}
}