java怎样返回一个表格_java利用poi来读取execl表格返回对象

利用poi来读取execl表格,返回一个对象(可能有点不完善,但是应该能满足平常的所用),用到了反射等等;

使用的jar包有:

commons-collections4-4.1.jar

poi-3.17.jar

poi-ooxml.3.17.jar

poi-ooxml-schemas.3.17.jar

xmllbeans-2.6.0.jar

附上百度网盘下载连接:

链接：https://pan.baidu.com/s/1t_jXUq3CuhZo9j_UI4URAQ 密码：r2qi

/**

* 静态属性包

* @author Administrator

*

*/

public class Common {

public static final String OFFICE_EXCEL_2003_POSTFIX = "xls";

public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";

public static final String EMPTY = "";

public static final String POINT = ".";

public static final String LIB_PATH = "lib";

/*public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX;

public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX;*/

public static final String NOT_EXCEL_FILE = " : Not the Excel file!";

public static final String PROCESSING = "Processing...";

}

import java.lang.reflect.Field;

/**

* 工具包

* @author Administrator

*

*/

public class Util {

public static String getPostfix(String path) {

if (path == null || Common.EMPTY.equals(path.trim())) {

return Common.EMPTY;

}

if (path.contains(Common.POINT)) {

return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length());

}

return Common.EMPTY;

}

// 获取属性的类型

public static String getFileType(Field field) {

// 如果类型是String

if (field.getGenericType().toString().equals("class java.lang.String")) { // 如果type是类类型，则前面包含"class

return "String";

}

// 如果类型是String

if (field.getGenericType().toString().equals("int")) { // 如果type是类类型，则前面包含"class

return "int";

}

// 如果类型是String

if (field.getGenericType().toString().equals("double")) { // 如果type是类类型，则前面包含"class

return "double";

}

// 如果类型是Integer

if (field.getGenericType().toString().equals("class java.lang.Integer")) {

return "Integer";

}

// 如果类型是Double

if (field.getGenericType().toString().equals("class java.lang.Double")) {

return "Double";

}

// 如果类型是Boolean 是封装类

if (field.getGenericType().toString().equals("class java.lang.Boolean")) {

return "Boolean";

}

// 如果类型是boolean 基本数据类型不一样 这里有点说名如果定义名是 isXXX的 那就全都是isXXX的

if (field.getGenericType().toString().equals("boolean")) {

return "boolean";

}

// 如果类型是Date

if (field.getGenericType().toString().equals("class java.util.Date")) {

return "Date";

}

// 如果类型是Short

if (field.getGenericType().toString().equals("class java.lang.Short")) {

return "Short";

}

return "null";

}

}

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.lang.reflect.Field;

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;

/**

* 由于jxl停止更新,因此使用poi来进行excel的表格的读入

*

* @author Administrator

*

*/

public class ReadExcel {

public List readExcel(String path, Object o) throws IOException {

if (path == null || Common.EMPTY.equals(path)) {

return null;

} else {

String postfix = Util.getPostfix(path);

if (!Common.EMPTY.equals(postfix)) {

if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {

return readXls(path, o);

} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {

return readXlsx(path, o);

}

} else {

System.out.println(path + Common.NOT_EXCEL_FILE);

}

}

return null;

}

@SuppressWarnings("resource")

public List readXlsx(String path, Object object) throws IOException {

System.out.println(Common.PROCESSING + path);

InputStream is = new FileInputStream(path);

XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);

List list = new ArrayList();

// 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) {

// 根据反射获取对象的属性名

String[] str = getFiledName(object);

for (int i = 0; i < str.length; i++) {

try {

// 根据对象的属性名来给对象赋值

Field f = object.getClass().getDeclaredField(str[i]);

// 设置对象属性可见

f.setAccessible(true);

XSSFCell temp = xssfRow.getCell(i);

// 赋值

String type = Util.getFileType(f);

if(type.equals("String")) {

f.set(object, temp.getStringCellValue());

} else if(type.equals("Integer")) {

f.set(object, (int)(temp.getNumericCellValue()));

} else if(type.equals("int")) {

f.set(object, (int)(temp.getNumericCellValue()));

} else if(type.equals("double")) {

f.set(object,(temp.getNumericCellValue()));

} else if(type.equals("Double")) {

f.set(object, (temp.getNumericCellValue()));

} else if(type.equals("boolean")) {

f.set(object, (temp.getBooleanCellValue()));

} else if(type.equals("Boolean")){

f.set(object, (temp.getBooleanCellValue()));

} else if(type.equals("Date")) {

f.set(object, (temp.getDateCellValue()));

} else {

f.set(object, temp.getStringCellValue());

}

} catch (Exception e) {

e.printStackTrace();

}

}

try {

// 拷贝Object对象

Class> fromClass = object.getClass();

// 获取属性名

Field[] fromFields = fromClass.getDeclaredFields();

// 创建拷贝的对象

Object ints = null;

// 赋值拷贝的对象

ints = fromClass.newInstance();

for(Field fromField : fromFields) {

fromField.setAccessible(true);

fromField.set(ints, fromField.get(object));

}

list.add(ints);

} catch (Exception e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

}

}

}

return list;

}

public List readXls(String path, Object object) throws IOException {

System.out.println(Common.PROCESSING + path);

InputStream is = new FileInputStream(path);

@SuppressWarnings("resource")

HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);

List list = new ArrayList();

// 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) {

// 根据反射获取对象的属性名

String[] str = getFiledName(object);

for (int i = 0; i < str.length; i++) {

try {

// 根据对象的属性名来给对象赋值

Field f = object.getClass().getDeclaredField(str[i]);

// 设置对象属性可见

f.setAccessible(true);

HSSFCell temp = hssfRow.getCell(i);

// 赋值

// 赋值

String type = Util.getFileType(f);

if(type.equals("String")) {

f.set(object, temp.getStringCellValue());

} else if(type.equals("Integer")) {

f.set(object, (int)(temp.getNumericCellValue()));

} else if(type.equals("int")) {

f.set(object, (int)(temp.getNumericCellValue()));

} else if(type.equals("double")) {

f.set(object,(temp.getNumericCellValue()));

} else if(type.equals("Double")) {

f.set(object, (temp.getNumericCellValue()));

} else if(type.equals("boolean")) {

f.set(object, (temp.getBooleanCellValue()));

} else if(type.equals("Boolean")){

f.set(object, (temp.getBooleanCellValue()));

} else if(type.equals("Date")) {

f.set(object, (temp.getDateCellValue()));

} else {

f.set(object, temp.getStringCellValue());

}

} catch (Exception e) {

e.printStackTrace();

}

}

try {

// 拷贝Object对象

Class> fromClass = object.getClass();

// 获取属性名

Field[] fromFields = fromClass.getDeclaredFields();

// 创建拷贝的对象

Object ints = null;

// 赋值拷贝的对象

ints = fromClass.newInstance();

for(Field fromField : fromFields) {

fromField.setAccessible(true);

fromField.set(ints, fromField.get(object));

}

list.add(ints);

} catch (Exception e1) {

// TODO Auto-generated catch block

e1.printStackTrace();

}

}

}

}

return list;

}

@SuppressWarnings("unused")

private String[] getFiledName(Object o) {

Field[] fields = o.getClass().getDeclaredFields();

String[] fieldNames = new String[fields.length];

for (int i = 0; i < fields.length; i++) {

fieldNames[i] = fields[i].getName();

}

return fieldNames;

}

}

下面利用student来示范一下

/**

* 实体类

* @author Administrator

*

*/

public class Student{

/**

* id

*/

private int id;

/**

* 姓名

*/

private String name;

/**

* 性别

*/

private String sex;

/**

* 年龄

*/

private int age;

/**

* 职位

*/

private String title;

public Student() {

super();

}

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getSex() {

return sex;

}

public void setSex(String sex) {

this.sex = sex;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public String getTitle() {

return title;

}

public void setTitle(String title) {

this.title = title;

}

@Override

public String toString() {

return "Student [id=" + id + ", name=" + name + ", sex=" + sex + ", age=" + age + ", title=" + title + "]";

}

}

import java.io.IOException;

import java.util.List;

/**

* 测试类

* @author Administrator

*

*/

public class TestDemo {

public static void main(String[] args) throws IOException {

String excel2010 = "H:\\MyTest\\Java\\test.xls";

// read the 2010 excel

Student student = new Student();

List list1 = new ReadExcel().readExcel(excel2010,student);

if (list1 != null) {

for (Object o : list1) {

Student s = (Student)o;

System.out.println(s);

}

}

}

}

大家有问题的可以私加我qq:2585700076