Java uses POI to read EXECL table return objects

Use POI to read the Execl table, return an object (may be a bit imperfect, but should be able to meet the usual use), the use of reflection and so on;

The jar packages used are:

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

/**
* Static Property Bag
* @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;
/**
* Tool Pack
* @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;
}

Gets the type of the property
public static String getfiletype (Field field) {

If the type is a string
if (Field.getgenerictype (). toString (). Equals ("Class java.lang.String")) {//if type is a class type, it is preceded by the "class
return "String";
}


If the type is a string
if (Field.getgenerictype (). toString (). Equals ("int")) {//if type is a class type, it is preceded by the "class
return "int";
}

If the type is a string
if (Field.getgenerictype (). toString (). Equals ("Double")) {//if type is a class type, it is preceded by the "class
Return "Double";
}

If the type is an integer
if (Field.getgenerictype (). toString (). Equals ("Class Java.lang.Integer")) {
return "Integer";
}

If the type is double
if (Field.getgenerictype (). toString (). Equals ("Class Java.lang.Double")) {
return "Double";
}

If the type is Boolean is the encapsulated class
if (Field.getgenerictype (). toString (). Equals ("Class Java.lang.Boolean")) {
return "Boolean";

}

If the type is a Boolean base data type It's a bit of a name, if the definition name is isxxx, then it's all isxxx.
if (Field.getgenerictype (). toString (). Equals ("Boolean")) {
Return "Boolean";
}
If the type is date
if (Field.getgenerictype (). toString (). Equals ("Class Java.util.Date")) {
return "Date";
}
If the type is 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;

/**
* Because JXL stops updating, use POI to read in Excel's Table
*
* @author Administrator
*
*/
public class Readexcel {

Public list<object> 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<object> readxlsx (String path, Object object) throws IOException {

SYSTEM.OUT.PRINTLN (common.processing + path);
InputStream is = new FileInputStream (path);
Xssfworkbook Xssfworkbook = new Xssfworkbook (IS);
list<object> list = new arraylist<object> ();

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) {
Gets the property name of an object based on reflection
string[] str = Getfiledname (object);
for (int i = 0; i < str.length; i++) {
try {
Assigns a value to an object based on the object's property name
Field f = Object.getclass (). Getdeclaredfield (Str[i]);
Set object Properties Visible
F.setaccessible (TRUE);
Xssfcell temp = Xssfrow.getcell (i);
Assign value
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 {
Copying Object objects
class<?> Fromclass = Object.getclass ();
Get Property name
field[] Fromfields = Fromclass.getdeclaredfields ();
Create a copied object
Object ints = null;
Assigned copy of the object
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<object> 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<object> list = new arraylist<object> ();
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) {
Gets the property name of an object based on reflection
string[] str = Getfiledname (object);
for (int i = 0; i < str.length; i++) {
try {
Assigns a value to an object based on the object's property name
Field f = Object.getclass (). Getdeclaredfield (Str[i]);
Set object Properties Visible
F.setaccessible (TRUE);
Hssfcell temp = Hssfrow.getcell (i);
Assign value
Assign value
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 {
Copying Object objects
class<?> Fromclass = Object.getclass ();
Get Property name
field[] Fromfields = Fromclass.getdeclaredfields ();
Create a copied object
Object ints = null;
Assigned copy of the object
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;
}

}

Let's use student to demonstrate

/**
* Entity class
* @author Administrator
*
*/
public class student{

/**
* ID
*/
private int id;
/**
* Name
*/
private String name;
/**
* Gender
*/
Private String sex;
/**
* Age
*/
private int age;
/**
* Position
*/
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.) {
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;
/**
* Test class
* @author Administrator
*
*/
public class Testdemo {

public static void Main (string[] args) throws IOException {

String excel2010 = "H:\\mytest\\java\\test.xls";
Read the Excel
Student Student = new Student ();
List<object> List1 = new Readexcel (). Readexcel (excel2010,student);
if (list1! = null) {
for (Object O:list1) {
Student s = (Student) o;
System.out.println (s);
}
}
}
}

If you have any questions, you can add me qq:2585700076.

Java uses POI to read EXECL table return objects