Using Java reflection mechanism to read data in Excel tables

If you import data from an Excel table directly into a database, you should first read the data in Excel.

In order to achieve code reuse, the object is used, and the end result is to get a List such as list<user>, list<book>, etc., so it needs to be implemented using a generic mechanism. The following code may be slightly more complicated, but the annotations are clear and I hope you will be patient.

Just a little bit of thought before the code:

A 1.excel table must have a header, and the values of each column in the header are the same as the attributes of the entity class;

2. Read the header information first, then get the number of header columns, then determine the need to use the name of the set method, coexist in the array;

3. Using the reflection mechanism, get the properties of object objects, by comparing with Excel table header, the types of each attribute are determined and stored in the array (in order of the attributes in the Excel table);

4. Traversing the data in addition to the header row, using reflection mechanism to instantiate the object, call the corresponding set method, the method parameter is obtained by 3;

Code on: (Need to use POI package, please download it yourself)

Entity class User:

public class User {
    private int id;
    private String name;
    private String password;
	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 GetPassword () {return
		password;
	}
	public void SetPassword (String password) {
		this.password = password;
	}
      
}

Processing class:

Package Module.system.common;
Import Java.io.File;
Import Java.io.FileInputStream;
Import java.io.FileNotFoundException;
Import java.io.IOException;
Import Java.lang.reflect.Field;
Import Java.lang.reflect.Method;
Import java.util.ArrayList;

Import java.util.List;
Import Org.apache.poi.hssf.usermodel.HSSFRow;
Import Org.apache.poi.hssf.usermodel.HSSFSheet;

Import Org.apache.poi.hssf.usermodel.HSSFWorkbook; /** * reading data from Excel/writing Excel to Excel with table headers, contents of header per column corresponding to entity class properties * * @author NAGSH */public class Excelmanage {private HSSF

	Workbook workbook;
		Public Excelmanage (String filedir) {File File = new file (Filedir);
		try {workbook = new Hssfworkbook (new FileInputStream (file));
		catch (FileNotFoundException e) {e.printstacktrace ();
		catch (IOException e) {e.printstacktrace ();
	 }/** * Reads the data in the Excel table. * * @param sheetname * Table index (EXCEL is a multiple-table document, so you need to enter a table index number, such as Sheet1)/public list readfromexcel (String Sheetna Me, object) {List result =New ArrayList ();
		Gets the class object class Class_ = Object.getclass () of the object;

		Get all the properties of the class field[] fields = Class_.getdeclaredfields ();
		Read Excel data//Get the specified Excel table Hssfsheet sheet = workbook.getsheet (sheetname); Gets the total number of rows in the table int rowcount = Sheet.getlastrownum () + 1;
		Need to add one if (RowCount < 1) {return result;
		//Gets the number of columns for the header int columnCount = Sheet.getrow (0). Getlastcellnum (); Reads the header information, determines the method name to be used---set method//is used to store the method name string[] Methodnames = new String[columncount];
		Number of header columns is the number of Set methods required//For storing property types string[] fieldtypes = new String[columncount];
		Gets the header row object Hssfrow titlerow = Sheet.getrow (0); Traversal for (int columnindex = 0; columnindex < ColumnCount; columnindex++) {//Traverse header column String data = Titlerow.getcel L (columnindex). toString (); The contents of a column Udata = character.touppercase (Data.charat (0)) + data.substring (1, Data.length ());
			Capitalize the initials METHODNAMES[COLUMNINDEX] = "Set" + Udata;
				for (int i = 0; i < fields.length; i++) {//Traverse property Arrayif (Fields[i].getname ()) {//property is equal to the table header Fieldtypes[columnindex] = Fields[i].gettype (). GetName ();//Data.equals attribute type To array}}//row-by-line read data from 1 to start ignoring header for (int rowIndex = 1; rowIndex < rowcount; rowindex++) {//Get Line Object HS
			Sfrow row = Sheet.getrow (RowIndex);
				if (row!= null) {Object obj = null;
				Instantiate the object of the generic class an object try {obj = class_.newinstance ();
				catch (Exception E1) {e1.printstacktrace (); //Get the data for (int columnindex = 0; columnindex < ColumnCount columnindex++) {String data = ro in each cell of the bank
					W.getcell (columnindex). toString ();
					Gets the method name of the method to invoke String methodname = Methodnames[columnindex];
					Method method = null; try {//This part can be extended by itself if (Fieldtypes[columnindex].equals ("java.lang.String")) {method = Class_.getdeclared Method (methodname, String.class); Sets the method to execute--set method parameter is string Method.invoke (obj, data); Execute the method} else if (fieldtypes[columnindex].equals ("int")) {method = Class_.getdeclaredmethod (methodname, Int.class);//Set methods to execute--set method parameter is int double data_d
							ouble = double.parsedouble (data);
							int data_int = (int) data_double; Method.invoke (obj, data_int);
					Executes the method} catch (Exception e) {e.printstacktrace ();
			} result.add (obj);
	} return result;
		public static void Main (string[] args) {excelmanage em = new Excelmanage ("E:/test.xls");
		User user = new user ();
		List List = Em.readfromexcel ("Sheet1", user);
			for (int i = 0; i < list.size (); i++) {User NewUser = (user) list.get (i);
		System.out.println (Newuser.getid () + "" + newuser.getname () + "" + Newuser.getpassword ());
 }

	}

}

Excel table:

Run Result:

1 aa qqqq
2 bb wwwww
3 cc eeee
4 dd rrrr
5 ee tttt