Using the Java reflection mechanism to read data from Excel tables

Source: Internet
Author: User
Tags rowcount

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

To implement code reuse, object is used, and the end result is to get a List such as list<user>, list<book>, etc., so you need to use a generic mechanism to implement it. The code below will probably be a little more complicated, but the comments are clear and I hope you will read it patiently.

Before the code, simply say the following:

The 1.excel table must have a table header, and the values of the columns in the header should be the same as the attributes of the entity class;

2. First read the header information, then get the number of table header columns, and then determine the name of the set method that needs to be used, and coexist into the array;

3. Use the reflection mechanism to get the properties of object objects, by comparing them to Excel table headers, to determine the types of each property, and to store them in the array (in the order of the attributes in the Excel table);

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

On the code: (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 has a header, the contents of each column of the header correspond to the properties of the entity class * * @author NAGSH * */PU Blic class Excelmanage {private Hssfworkbook workbook;public excelmanage (String filedir) {File File = new file (Filedir); tr Y {workbook = new Hssfworkbook (new FileInputStream (file)),} catch (FileNotFoundException e) {e.printstacktrace ();} catch (IOException e) {E.printstacktrace ();}} /** * Reads data from an Excel table. * * @param sheetname * Table index (EXCEL is a multi-table document, so you need to enter the table index number, such as Sheet1) */public list readfromexcel (String sheetname, Obje CT object) {List result = new ArrayList ();//Gets the object's class object class Class_ = Object.getclass ();//Get the classhas attribute 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 an if (RowCount < 1) {return result;} Gets the number of columns of the header int columnCount = Sheet.getrow (0). Getlastcellnum ();//Read header information, determine the method name to use---set method//For storing method name string[] Methodnames = new String[columncount]; The number of table header columns is the required set method number//For storing the property type string[] fieldtypes = new string[columncount];//Get header Row object Hssfrow titlerow = Sheet.getrow (0) ;//traverse for (int columnindex = 0; columnindex < ColumnCount; columnindex++) {//Traverse header column String data = Titlerow.getcell (Colu Mnindex). toString (); The contents of a column string Udata = Character.touppercase (Data.charat (0)) + data.substring (1, Data.length ()); Capitalize the first letter Methodnames[columnindex] = "Set" + udata;for (int i = 0; i < fields.length; i++) {//Traversal property array if (Data.equals (f Ields[i].getname ())) {//The attribute is equal to the table header Fieldtypes[columnindex] = Fields[i].gettype (). GetName ();//Put the property type in the array}}}//read data line by row Starting from 1 ignores header for (int rowIndex = 1; rowindex < RowCount; rowindex++) {//Gets the Row object Hssfrow row = Sheet.getrow (RowIndex); if (row! = null) {Object obj = null;//Instantiates the object of the generic class an object try {obj = Class_.newinstance ();} catch (Exception E1) {e1.printstacktrace ();} Obtain data for each cell in the bank for (int columnindex = 0; columnindex < ColumnCount; columnindex++) {String data = Row.getcell (columnin DEX). toString ();//Gets the method name of the method to invoke string methodName = Methodnames[columnindex]; Method = Null;try {//This section can expand itself if (fieldtypes[columnindex].equals ("java.lang.String")) {method = Class_. Getdeclaredmethod (Methodname,string.class); Set the method to execute--set method parameter is Stringmethod.invoke (obj, data); Execute the method} else if (fieldtypes[columnindex].equals ("int")) {method = Class_.getdeclaredmethod (Methodname,int.class); Set the method to execute--set method parameter is intdouble data_double = double.parsedouble (data), int data_int = (int) data_double;method.invoke (obj , Data_int); Execute 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); S Ystem.out.println (Newuser.getid () + "" + newuser.getname () + "" + Newuser.getpassword ());}}
Excel table:



Operation Result:

1 AA qqqq2 BB wwwww3 cc eeee4 DD rrrr5 ee tttt




Using the Java reflection mechanism to read data from Excel tables

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.