Java reads Excel file data into MySQL database

This is the second week of my company to a small learning task, the following is the implementation process:

1. Build Maven project (easy to manage jar packages)

Import jxl,mysql-connector Dependencies in Pom.xml

Can search in Maven warehouse

2. Establish database connection class, database corresponding entity class

2. Write the entity classes corresponding to the database tables, get, set methods, etc.

3. Here is the class to write the Excel file to read, and the main class to run

Package Service;import Java.io.file;import java.sql.resultset;import java.sql.sqlexception;import Java.util.arraylist;import Java.util.list;import JXL. Sheet;import JXL. Workbook;import Excel. Db;import Excel. Student; Public classStudentservice {/** * Query all the data in the student table * @return*/     Public StaticList<student>Getallbydb () {List<Student> list=NewArraylist<student>(); Try{db db=NewDB (); String SQL="SELECT * FROM Student"; ResultSet RS= db. Search (SQL,NULL);  while(Rs.next ()) {intId=rs.getint ("ID"); String S_name=rs.getstring ("S_name"); String Age=rs.getstring (" Age"); String Address=rs.getstring ("Address"); List.add (NewStudent (ID, s_name, age,address)); }                    } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }        returnlist; }        /** * Query all the data in the spreadsheet in the specified directory * @param the full path of file files * @return*/     Public StaticList<student>getallbyexcel (String file) {List<Student> list=NewArraylist<student>(); Try{Workbook RWB=workbook.getworkbook (NewFile ("F:\\student.xls")); Sheet RS=rwb.getsheet (0);//Table            intClos=rs.getcolumns ();//get all the columns            intRows=rs.getrows ();//get all the rowsSystem. out. println ("number of columns in the table:"+clos+"number of rows in the table:"+rows);  for(inti =1; i < rows; i++) {                 for(intj =0; J < Clos; J + +) {                    //The first one is the number of columns, the second is the number of rowsString Id=rs.getcell (j + +, I). getcontents ();//The default leftmost number counts as a column, so here's J + +.String S_name=rs.getcell (j + +, i). getcontents (); String Age=rs.getcell (j + +, i). getcontents (); String Address=rs.getcell (j + +, i). getcontents (); System. out. println ("ID:"+id+"Name:"+s_name+"Sex:"+age+"Address:"+address); List.add (NewStudent (Integer.parseint (ID), s_name,age,address)); }            }        } Catch(Exception e) {//TODO auto-generated Catch blockE.printstacktrace (); }         returnlist; }        /** * ID to determine if there is * @param ID * @return*/     Public StaticBoolean isexist (intID) {        Try{db db=NewDB (); ResultSet RS=db. Search ("SELECT * FROM student where id=?",NewString[]{id+""}); if(Rs.next ()) {return true; }        } Catch(SQLException e) {//TODO auto-generated Catch blockE.printstacktrace (); }        return false; }         Public Static voidMain (string[] args) {System. out. println (Isexist (1)); }    }

To run the main class:

Package Service;import java.util.list;import Excel. Db;import Excel. Student; Public classTestexceltodb { Public Static voidMain (string[] args) {//get all the data in the tableList<student> Listexcel=studentservice.getallbyexcel ("F:\\student.xls"); DB DB=NewDB ();  for(Student student:listexcel) {intId=Student.getid (); System. out. println (ID); if(!studentservice.isexist (ID)) {                //add if not presentString sql="INSERT into student (id,s_name,age,address) VALUES (?,?,?,?)"; string[] Str=NewString[]{id+"", Student.gets_name (), Student.getage (), student.getaddress () +""}; Db.            Addu (SQL, str); }Else {                //exists on updateString sql="Update student set s_name=?,age=?,address=? where id=?"; string[] Str=NewString[]{student.gets_name (), Student.getage (), student.getaddress () +"", id+""}; Db.            Addu (SQL, str); }        }    }}

Database: [Excel data header to correspond to database field]

Summary: The above is the use of JXL implementation of reading the contents of the Excel file, and the data to MySQL, the flaw is: JXL only support EXCEL2003.

To improve compatibility 2003 and 2007 need to use Pol, to import Pol-related JAR packages

There are two classes in Pol that deal with Excel2003 and Excel2007.

HSSF-----2003,XSSF-----2007

The following is a class method that determines whether the file type is 03 or 07 by reading the filename. Because 03 and 07 obviously differ in file suffix names

/** * Provide external methods to read Excel **/     Public StaticList<string>readexcel (File file) throws IOException {String FileName=File.getname (); List<String> list =NewArraylist<string>(); //get the suffix by its nameString Extension = Filename.lastindexof (".") == -1?"": fileName. Substring (Filename.lastindexof (".") +1); String[][] Result=NULL; if("xls". Equals (extension)) {Result=read2003excel (file); } Else if("xlsx". Equals (extension)) {Result=read2007excel (file); } Else {            Throw NewIOException ("Unsupported file Types"); }        intRowlength =result.length;  for(inti =0; i < rowlength; i++) {StringBuffer sb=NewStringBuffer ();  for(intj =0; J < Result[i].length; J + +) {                if(!"". Equals (Result[i][j]) && Result[i][j].trim (). Length () >0) {sb.append (Result[i][j]). Append ("##"); }Else{sb.append ("@@"). Append ("##"); }            }            if(Sb.tostring (). EndsWith ("##") {sb.delete (sb.tostring (). Length ()-2, Sb.tostring (). Length ()); } System. out. println (Sb.tostring ());        List.add (Sb.tostring ()); }        returnlist; }

Then according to the above in writing a read Excel2003 and a Excel2007 class on the line.

The above is the individual in the test after writing a little record, there is no place to look and forgive.

Java reads Excel file data into MySQL database