Java implements Excel Import database, the data in the database is imported into Excel. Reproduced

Summary of self-study materials

The functions implemented:

• Java implementation Excel Import database, update if present
• Data in the database is imported into Excel

1. Add Jxl.jar Mysql-connector-java.1.7-bin.jar package to the project's Lib directory

2. Excel file directory: D://book.xls

3. Database name: Javenforexcel

4. Table Name: Stu

5. Writing class: String method to connect MySQL, insert method, entity class

The table structure is as follows:

tool classes for connecting to a database

Package Com.javen.db;import Java.sql.connection;import Java.sql.drivermanager;import java.sql.PreparedStatement; Import Java.sql.resultset;import Java.sql.sqlexception;public class Dbhepler {/*string Driver = "Com.microsoft.sqlserv    Er.jdbc.SQLServerDriver ";    String url = "Jdbc:sqlserver://127.0.0.1;databasename=javenforexcel"; */String Driver = "Com.mysql.jdbc.Driver";            String url = "Jdbc:mysql://127.0.0.1:3306/javenforexcel";    Connection con = null;    ResultSet res = null;                public void DataBase () {try {class.forname (driver);            con = drivermanager.getconnection (URL, "root", "root"); } catch (ClassNotFoundException e) {//TODO auto-generated catch block System.err.println ("The Mount Jdbc/odbc driver failed.                  " );            E.printstacktrace (); } catch (SQLException e) {//TODO auto-generated catch block System.err.println ("Unable to connect to database")           ;      E.printstacktrace ();        }}//Query public ResultSet Search (String sql, String str[]) {DataBase ();            try {preparedstatement PST =con.preparestatement (SQL); if (str! = null) {for (int i = 0; i < str.length; i++) {pst.setstring (i + 1, str[i]                );        }} res = Pst.executequery ();        } catch (Exception e) {//TODO auto-generated catch block E.printstacktrace ();    } return res;        }//delete modify public int Addu (String sql, String str[]) {int a = 0;        DataBase ();            try {preparedstatement PST = con.preparestatement (SQL); if (str! = null) {for (int i = 0; i < str.length; i++) {pst.setstring (i + 1, str[i]                );        }} A = Pst.executeupdate ();    } catch (Exception e) {//TODO auto-generated catch block        E.printstacktrace ();    } return A; }}

The entity of the table is as follows

Package com.javen.entity;/** * @author javen * @Email [email protected] * */public class Stuentity {private int    Id    private String name;    Private String sex;                private int num;        Public stuentity () {} public stuentity (int ID, string name, string sex, int num) {this.id = ID;        THIS.name = name;        This.sex = sex;    This.num = num;                } @Override Public String toString () {return "stuentity [id=" + ID + ", name=" + name + ", sex=" + Sex    + ", num=" + num + "]";    } 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 Getsex () {return sex;    public void Setsex (String sex) {this.sex = sex;    } public int Getnum () {return num; } public void setnum (int num) {this.num =Num }                }

Java implementation Excel import data core class read all the data in Excel table, manipulate data (query, update)

Package Com.javen.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 com.javen.db.dbhepler;import com.javen.entity.stuentity;/** * @author Javen * @Email [email  Protected] * */public class Stuservice {/** * Query Stu table for all data * @return */public static List<stuenti        Ty> Getallbydb () {list<stuentity> list=new arraylist<stuentity> ();            try {dbhepler db=new dbhepler ();            String sql= "SELECT * from Stu"; ResultSet rs= db.            Search (SQL, NULL);                while (Rs.next ()) {int id=rs.getint ("id");                String name=rs.getstring ("name");                String sex=rs.getstring ("sex");                                int num=rs.getint ("num");                System.out.println (id+ "" +name+ "" +sex+ "" +num ");       List.add (new stuentity (ID, name, sex, num));     }} catch (SQLException e) {//TODO auto-generated catch block E.printstack        Trace ();    } return list; /** * Query all data in the spreadsheet in the specified directory * @param file full path * @return * */public static list<stuentity>        Getallbyexcel (String file) {list<stuentity> list=new arraylist<stuentity> ();            try {Workbook Rwb=workbook.getworkbook (new file); Sheet rs=rwb.getsheet ("Test Shee 1");//or Rwb.getsheet (0) int clos=rs.getcolumns ();//Get all columns int rows=            Rs.getrows ();//Get All Rows System.out.println (clos+ "Rows:" +rows);                    for (int i = 1; i < rows; i++) {for (int j = 0; J < Clos; J + +) {//First is the number of columns, the second is the number of rows String Id=rs.getcell (j + +, I). getcontents ();//The default leftmost number is also counted as a column so here's J + + String Name=rs.get                    Cell (j + +, I). getcontents (); String Sex=rs.getcell (j++, I). getcontents ();                                        String Num=rs.getcell (j + +, I). getcontents ();                    SYSTEM.OUT.PRINTLN ("ID:" +id+ "Name:" +name+ "Sex:" +sex+ "num:" +num ");                List.add (New stuentity (Integer.parseint (ID), name, sex, Integer.parseint (num))); }}} catch (Exception e) {//TODO auto-generated catch block E.printstacktrace ()        ;            } return list;            /** * ID To determine if there is a * @param ID * @return */public static Boolean isexist (int id) {try {            Dbhepler db=new Dbhepler (); ResultSet rs=db.            Search ("select * from Stu where id=?", New String[]{id+ ""});            if (Rs.next ()) {return true;        }} catch (SQLException e) {//TODO auto-generated catch block E.printstacktrace ();    } return false; } public static void Main (string[] args) {/*LIST&LT        Stuentity> All=getallbydb ();        for (stuentity Stuentity:all) {System.out.println (stuentity.tostring ());            }*/System.out.println (isexist (1)); }    }

Data import data into Excel table

Package Com.javen.excel;import Java.io.file;import Java.util.list;import com.javen.entity.stuentity;import Com.javen.service.stuservice;import JXL. Workbook;import Jxl.write.label;import Jxl.write.writablesheet;import Jxl.write.writableworkbook;public class                            testdbtoexcel {public static void main (string[] args) {try {writableworkbook WWB = null;               Create a writable Excel workbook String fileName = "D://book.xls";               File File=new file (fileName);               if (!file.exists ()) {file.createnewfile ();               }//Use filename to create a Workbook WWB = workbook.createworkbook (file);                              create worksheet Writablesheet ws = Wwb.createsheet ("Test Shee 1", 0);               Querying all data in the database list<stuentity> list= stuservice.getallbydb ();       The line number of the Excel table to insert into, the default starting from 0 label labelid= new label (0, 0, "number (ID)"), or//indicates the first        Label labelname= new label (1, 0, "name");               Label labelsex= new label (2, 0, "Gender (Sex)");                              Label labelnum= new label (3, 0, "salary (num)");               Ws.addcell (Labelid);               Ws.addcell (LabelName);               Ws.addcell (Labelsex);               Ws.addcell (Labelnum); for (int i = 0; i < list.size (); i++) {label labelid_i= new label (0, I+1, list.ge                   T (i). GetId () + "");                   Label labelname_i= new label (1, I+1, List.get (i). GetName ());                   Label labelsex_i= new Label (2, I+1, List.get (i). Getsex ());                   Label labelnum_i= new Label (3, I+1, List.get (i). Getnum () + "");                   Ws.addcell (labelid_i);                   Ws.addcell (labelname_i);                   Ws.addcell (labelsex_i);               Ws.addcell (labelnum_i);              }//write into document Wwb.write ();         Close an Excel Workbook object      Wwb.close ();        } catch (Exception e) {//TODO auto-generated catch block E.printstacktrace (); }     }}

importing data from Excel tables into MySQL database

Package Com.javen.excel;import Java.util.list;import Com.javen.db.dbhepler;import com.javen.entity.StuEntity;    Import com.javen.service.stuservice;/** * @author Javen * @Email [email protected] * */public class Testexceltodb { public static void Main (string[] args) {//Get all the data in the table list<stuentity> Listexcel=stuservice.getallby        Excel ("D://book.xls");                 /*//get all the data in the database table list<stuentity> listdb=stuservice.getallbydb (); */Dbhepler db=new dbhepler ();            for (stuentity stuentity:listexcel) {int Id=stuentity.getid (); if (! Stuservice.isexist (ID)) {//does not exist add String sql= "insert into Stu (Name,sex,num) VALUES (?,?,?)                ";                String[] Str=new string[]{stuentity.getname (), Stuentity.getsex (), stuentity.getnum () + ""}; Db.            Addu (SQL, str);         }else {//exists on update String sql= "Update stu set name=?,sex=?,num=?" where id=? ";       String[] Str=new string[]{stuentity.getname (), Stuentity.getsex (), stuentity.getnum () + "", id+ ""}; Db.            Addu (SQL, str); }        }    }}

Java implements Excel Import database, the data in the database is imported into Excel ... Reprint