Import data from Excel into MySQL using Java

This blog is an introduction to the POI technology in Java to read Excel data and then save it to MySQL data.

You can also read and import excel in: Java's POI technology to learn how to write Excel information

Using JXL technology, you can import excel in Java's JXL technology

Project structure:

Test data in Excel:

Database structure:

The corresponding SQL:

1 CREATE TABLE ' Student_info ' (2   ' id ' int (one) not NULL auto_increment,3   ' no ' varchar () DEFAULT null,4   ' name  ' varchar (default null,5 ' age   ' varchar ') default null,6   ' score ' float default ' 0 ', 7   PRIMARY KEY (' id ') 8 ) Engine=innodb DEFAULT Charset=utf8;

Insert Data successfully:

If you repeat the data, discard:

=============================================

SOURCE section:

=============================================

/exceltest/src/com/b510/client/client.java

1/** 2  *  3 */4 package  com.b510.client; 5  6 import java.io.IOException; 7 import java.sql.SQLException; 8  9 Import com.b510.excel.savedata2db;10/**12  * @author Hongten13  * @created 2014-5-1814  */15 public class Client {All public     static void Main (string[] args) throws IOException, SQLException {         savedata2d B savedata2db = new savedata2db ();         savedata2db.save ();         System.out.println ("End");     }22}

/exceltest/src/com/b510/common/common.java

 1/** 2 * 3 */4 package Com.b510.common;     5 6/** 7 * @author Hongten 8 * @created 2014-5-18 9 */10 public class Common {one-up//Connect the DATABASE13 public static final String DRIVER = "Com.mysql.jdbc.Driver"; n public static final String db_name = "Test"; Lic static final String USERNAME = "root"; public static final String PASSWORD = "root"; n Static final S Tring IP = "192.168.1.103"; public static final String PORT = "3306"; the public static final string URL = "Jdbc:m ysql://"+ IP +": "+ PORT +"/"+ db_name;20//common22 public static final String Excel_path =" Lib/stud Ent_info.xls "; sql25 public static final String insert_student_sql =" INSERT into Student_info (no, NAM E, age, score) VALUES (?,?,?,?) "; public static final String update_student_sql = "UPDATE student_info Set no =?, name =?, age=?, score =?" WHERE id =? "; public static final String Select_student_all_sql = "Select Id,no,name,age,score from Student_info"; n static final String select_student_sql = "SELECT * from S Tudent_info where name like "; 29}

/exceltest/src/com/b510/excel/readexcel.java

 1/** 2 * 3 */4 package com.b510.excel; 5 6 Import Java.io.FileInputStream; 7 Import java.io.IOException; 8 Import Java.io.InputStream; 9 Import java.util.arraylist;10 Import java.util.list;11 import org.apache.poi.hssf.usermodel.hssfcell;13 Import ORG.APACHE.POI.HSSF.USERMODEL.HSSFROW;14 Import org.apache.poi.hssf.usermodel.hssfsheet;15 Import ORG.APACHE.POI.HSSF.USERMODEL.HSSFWORKBOOK;16 import com.b510.common.common;18 Import com.b510.excel.vo.Student; /**21 * @author Hongten22 * @created 2014-5-1823 */24 public class Readexcel {All public list<student&gt ; Readxls () throws IOException {InputStream is = new FileInputStream (common.excel_path); Hssfworkbook h Ssfworkbook = new Hssfworkbook (IS), Student Student = null;30 list<student> List = new ARRAYLIST&L T Student> (); 31//Cycle sheet Sheet32 for (int numsheet = 0; Numsheet < hssfworkbook.getnumberofsheets (); nu msheet++) {Hssfsheet hSsfsheet = Hssfworkbook.getsheetat (Numsheet), if (Hssfsheet = = null) {continue;36                 }37//Loop line Row38 for (int rowNum = 1; rowNum <= hssfsheet.getlastrownum (); rownum++) {39 Hssfrow Hssfrow = Hssfsheet.getrow (rowNum); + if (hssfrow! = null) {s Tudent = new Student (); Hssfcell no = Hssfrow.getcell (0); Hssfcell name = HSSF Row.getcell (1); Hssfcell age = Hssfrow.getcell (2); Hssfcell score = hssfrow.ge                     Tcell (3); Student.setno (GetValue (No)); Student.setname (GetValue (name)); 48                     Student.setage (GetValue (age)), Student.setscore (Float.valueof (GetValue (score))); 50     List.add (student);}52}53}54 return list;55}56 @SuppressWarninGS ("Static-access") getValue ("Hssfcell Hssfcell"), (hssfcell.getcelltype () = Hssfcel L.cell_type_boolean) {60//returns the value of the Boolean type string.valueof return (Hssfcell.getbooleancellvalue (                 ); Hssfcell.getcelltype} else if (= = () = hssfcell.cell_type_numeric) {63//returns the value of a numeric type 64                 Return string.valueof (Hssfcell.getnumericcellvalue ()); n} else {66//return value of String type 67 Return string.valueof (Hssfcell.getstringcellvalue ()); 68}69}70}

/exceltest/src/com/b510/excel/savedata2db.java

 1/** 2 * 3 */4 package com.b510.excel; 5 6 Import Java.io.IOException; 7 Import java.sql.SQLException; 8 Import java.util.List;  9 Import com.b510.common.common;11 Import com.b510.excel.util.dbutil;12 import com.b510.excel.vo.student;13 14/**15     * @author Hongten16 * @created 2014-5-1817 */18 public class Savedata2db {@SuppressWarnings ({"Rawtypes"}) 21  public void Save () throws IOException, SQLException {readexcel xlsmain = new Readexcel (); Student Student = null;24 list<student> List = Xlsmain.readxls (); (int i = 0; i < list.size (); i++) {student = List.get (i), list L = Dbutil.selectone (common.select_student_sql + "'%" + stu Dent.getname () + "% '", student), if (!l.contains (1)) {Dbutil.insert (common.insert_student _sql, student); else {System.out.println ("the Record was exist:no. =" + STUDENT.GETN O () + ", Name = "+ student.getname () +", age = "+ student.getage () +", and have been throw away! "); 33}34}35}36}

/exceltest/src/com/b510/excel/util/dbutil.java

  1/** 2 * 3 */4 package com.b510.excel.util;  5 6 Import Java.sql.Connection;  7 Import Java.sql.DriverManager;  8 Import java.sql.PreparedStatement; 9 Import Java.sql.ResultSet; Ten import java.sql.SQLException; Import java.util.ArrayList; Import java.util.List; Import Com.b510.common.Common; Com.b510.excel.vo.Student import; /** * @author Hongten * @created * * * * * * * * * * * * * * * * * * * * * * * * * * *, 2014-5-18 SQL */public static void Insert (String sql, Student Student) throws SQLException {Connection C Onn = null; PreparedStatement PS = null; try {class.forname (common.driver); conn = Drivermanager.getconnection (Common.url , Common.username, Common.password); PS = conn.preparestatement (SQL); Ps.setstring (1, Student.getno ()); Ps.setstring (2, Student.getname ()); Ps.setstring (3, student.geTage ()); Ps.setstring (4, String.valueof (Student.getscore ())); PNS Boolean flag = Ps.execute (); if (!flag) {System.out.println ("Save data:no. =" + Student.getno () + ", Name =" + St Udent.getname () + ", age =" + student.getage () + "succeed!");             A. 44} catch (Exception e) {e.printstacktrace ();                 if (PS! = null) {ps.close (); (conn! = null) {48 Conn.close ();  ({"Unchecked", "Rawtypes"}), and the public static List SelectOne (String sql, Student Student) throws SQLException {Connection conn = null; ENT PS = null; ResultSet rs = null; List List = new ArrayList (); Class.forName (common.driver); conn = Drivermanager.getcOnnection (Common.url, Common.username, Common.password); PS = conn.preparestatement (SQL); rs = Ps.executequery (); (Rs.next ()) {rs.getstring ("no"). Equals (Student.getno ()) | | | rs.getstring ("name"). Equals (Student.getname ()) | |                     Rs.getstring ("Age"). Equals (Student.getage ())) {List.add (1);}else{68 List.add (0); (Exception e) {e.printstacktrace (); 73} Finally {if (rs! = null) {rs.close ();             {Ps.close (); (conn! = null) {Bayi conn.close (); 82 } to the list; ResultSet SelectAll (String sql) throws SQLException {Connection conn = null;        90 PreparedStatement PS = null; ResultSet rs = null; try {class.forname (common.driver), 94 conn = Drivermanager.getconnection (Common.url , Common.username, Common.password); PS = conn.preparestatement (SQL); rs = Ps.executequery (); The "Exception" catch (E) {98 e.printstacktrace ();} finally {if (rs! = nul             L) {101 rs.close (); 102}103 if (PS! = null) {104 ps.close (); 105 }106 if (conn! = null) {107 conn.close (); 108}109}110 R Eturn rs;111}112 113}

/exceltest/src/com/b510/excel/vo/student.java

 1/** 2 * 3 */4 package com.b510.excel.vo;  5 6/** 7 * Student 8 * 9 * @author Hongten10 * @created 2014-5-1811 */12 public class Student {13/**14 *      Id15 */16 Private Integer id;17/**18 * study number */20 private String no;21/**22 * Name 23 */24 private String name;25/**26 * College */28 private String age;29/**30 * Score 31 * /32 Private float score;33 public Integer getId () {id;36}37-public void SetId (Int Eger id) {this.id = id;40}41-Public String Getno () {no;44-}45, Public v  OID Setno (String no) {this.no = no;48}49 public String getName () {return name;52}53         59 public void SetName (string name) {this.name = name;56}57-Public String getage () Return age;60}61-public void Setage (String age) {this.age = age;64}65 $ public Float Getscore () {score return score;68}69-public void SetScore (float) {T His.score = score;72}73 74}

Import data from Excel into MySQL using Java