Java implementation Excel Import database, data in database import into Excel

Source: Internet
Author: User
Tags import database

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 = "    Er.jdbc.SQLServerDriver ";    String url = "Jdbc:sqlserver://;databasename=javenforexcel"; */String Driver = "Com.mysql.jdbc.Driver";            String url = "Jdbc:mysql://";    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) { = ID; = name; = 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) { = ID;    } public String GetName () {return name;    } public void SetName (String name) { = name;    } public String Getsex () {return sex;    public void Setsex (String 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;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 ( ()) {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 ( ()) {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;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,                   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); }        }    }}

Source Code

Java implementation Excel Import database, data in database import into Excel

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: 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.