Using Java reflection mechanism to read data in Excel tables

Source: Internet
Author: User
Tags reflection rowcount

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




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.