To familiarize yourself with the knowledge points in the workbook in Java, the ability to insert data from Excel into a database and export data to Excel is found.
Precautions: 1,mysql database;
2, the jar package that needs to be imported has Jxl.jar,mysql-connector-java-5.1.22-bin.jar,ojdbc6.jar
The code is as follows:
First, establish the database name Javaforexcel, set up the table Stu
DROP TABLE IF EXISTS ' Stu ';
CREATE TABLE ' Stu ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (DEFAULT NULL),
' Sex ' char (2) DEFAULT NULL,
' num ' int (one) DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodb auto_increment=19 DEFAULT Charset=utf8;
Second, build the entity class
Package Com.excel.model;
public class Stu {
private int Id;//id
Private String name;//Name
Private String sex;//Sex
private int num;//Wages
public Stu (int ID, string name, string sex, int num) {
This.id = ID;
THIS.name = name;
This.sex = sex;
This.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;
}
}
Third, to establish a database connection, here is just a simple test, it should be written in the common package, I wrote in the DAO package inside
Package Com.excel.dao;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.sql.PreparedStatement;
Import Java.sql.ResultSet;
public class DBHelper {
Connection Con=null;
ResultSet Res=null;
PreparedStatement Pre=null;
Connecting to a database
public void Dbbase () {
try {
String driver= "Com.mysql.jdbc.Driver";
String url= "Jdbc:mysql://127.0.0.1:3306/javenforexcel";
String username= "root";
String password= "";
Class.forName (driver);
Con=drivermanager.getconnection (Url,username,password);
} catch (Exception e) {
E.printstacktrace ();
}
}
Inquire
Public ResultSet Search (String sql,string args[]) {
Dbbase ();
try {
Pre=con.preparestatement (SQL);
if (args!=null) {
for (int i=0;i<args.length;i++) {
Pre.setstring (i+1, args[i]);
}
}
Res=pre.executequery ();
} catch (Exception e) {
E.printstacktrace ();
}
return res;
}
Delete and change
public int Adu (String sql,string args[]) {
int falg=0;
Dbbase ();
try {
Pre=con.preparestatement (SQL);
if (args!=null) {
for (int i=0;i<args.length;i++) {
Pre.setstring (i+1, args[i]);
}
}
Falg=pre.executeupdate ();
} catch (Exception e) {
E.printstacktrace ();
}
return falg;
}
}
Four, the transaction layer method is as follows:
Package com.excel.service;
Import Java.io.File;
Import Java.sql.ResultSet;
Import java.util.ArrayList;
Import java.util.List;
Import JXL. Sheet;
Import JXL. Workbook;
Import Com.excel.dao.DBhelper;
Import Com.excel.model.Stu;
public class Stuservice {
/*
* Query the Stu table for left and right data
*/
public static list<stu> Getallbydb () {
List<stu> list=new arraylist<stu> ();
try {
DBHelper dbhelper=new dbhelper ();
String sql= "SELECT * from Stu";
ResultSet rs=dbhelper.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");
List.add (new Stu (ID, name, sex, num));
}
} catch (Exception e) {
E.printstacktrace ();
}
return list;
}
/**
* Query all the data in the spreadsheet in the specified directory
* @param file Full path
* @return
*/
public static list<stu> Getallbyexcel (String file) {
List<stu> stus=new arraylist<stu> ();
try {
Workbook wb=workbook.getworkbook (file);
Sheet Sheet=wb.getsheet ("Test");
int cols=sheet.getcolumns ();//Gets the total number of columns
int rows=sheet.getrows ();//Gets the total number of rows
System.out.println ("Number of columns:" +cols+ "Number of rows:" +rows);
for (int i=1;i<rows;i++) {
for (int j = 0; J < cols; J + +) {
The first one is the number of columns, the second is the number of rows
String Id=sheet.getcell (j + +, I). getcontents ();//The default leftmost number is also counted as a column so here's J + +
String Name=sheet.getcell (j++,i). getcontents ();
String Sex=sheet.getcell (j++,i). getcontents ();
String Num=sheet.getcell (j++,i). getcontents ();
SYSTEM.OUT.PRINTLN ("ID:" +id+ "Name:" +name+ "Sex:" +sex+ "num:" +num ");
Stus.add (New Stu (Integer.parseint (ID), name, sex, Integer.parseint (num)));
}
}
} catch (Exception e) {
E.printstacktrace ();
}
return stus;
}
/**
* ID to determine if there is
* @param ID
* @return
*/
public static Boolean isexist (int id) {
Boolean flag=false;
try {
DBHelper db=new dbhelper ();
ResultSet rs=db.search ("select * from Stu where id=?", New String[]{id+ ""});
if (Rs.next ()) {
Flag=true;
}
} catch (Exception e) {
E.printstacktrace ();
}
return flag;
}
}
Five, the database imports data into Excel
Package Com.excel.control;
Import Java.io.File;
Import java.util.List;
Import Com.excel.model.Stu;
Import Com.excel.service.StuService;
Import JXL. Workbook;
Import Jxl.write.Label;
Import Jxl.write.WritableSheet;
Import Jxl.write.WritableWorkbook;
public class Dbinexcel {
public static void Main (string[] args) {
try {
Writableworkbook RB = null;//Create a writable workbook
Writablesheet ws = null;//Create worksheet
String FileName = "C://users//lidelin//desktop//test.xls";//Create a writable Excel workbook address and name
File File=new file (FileName);
if (!file.exists ()) {
File.createnewfile ();
}
RB = Workbook.createworkbook (file);//use filename to create a Workbook
WS = Rb.createsheet ("Test", 0);
List<stu> stus=stuservice.getallbydb ();//querying all data in the database
Both rows and columns start at 0.
Label Laid=new label (0, 0, "id"),//1 column 1 row
Label Laname=new label (1, 0, "name name"),//2 column 1 row
Label Lasex=new label (2, 0, "gender Sex");//3 column 1 rows
Label Lanum=new label (3, 0, "name num"),//4 column 1 row
Ws.addcell (LAID);
Ws.addcell (Laname);
Ws.addcell (Lasex);
Ws.addcell (Lanum);
for (int i=0;i<stus.size (); i++) {
Label labelid_i= New label (0, I+1, Stus.get (i). GetId () + "");
Label Labelname_i=new label (1,i+1,stus.get (i). GetName () + "");
Label labelsex_i= new Label (2, I+1, Stus.get (i). Getsex ());
Label labelnum_i= new Label (3, I+1, Stus.get (i). Getnum () + "");
Ws.addcell (labelid_i);
Ws.addcell (labelname_i);
Ws.addcell (labelsex_i);
Ws.addcell (labelnum_i);
}
Rb.write ();//write into document
SYSTEM.OUT.PRINTLN ("The data has been written to the specified file, please check!") ");
Rb.close ();//close Excel Workbook object
} catch (Exception e) {
E.printstacktrace ();
}
}
}
Six, Excel imports data into the database
Package Com.excel.control;
Import java.util.List;
Import Com.excel.dao.DBhelper;
Import Com.excel.model.Stu;
Import Com.excel.service.StuService;
public class Excelindb {
public static void Main (string[] args) {
List<stu> stus=stuservice.getallbyexcel ("C://users//lidelin//desktop//test.xls");//querying all data in the database
DBHelper db=new dbhelper ();
for (Stu stu:stus) {
int Id=stu.getid ();
if (! Stuservice.isexist (ID)) {//does not exist to add
String sql= "INSERT into Stu (Name,sex,num) VALUES (?,?,?)";
String[] Str={stu.getname (), Stu.getsex (), stu.getnum () + ""};
Db.adu (SQL, str);
}else {//exists on update
String sql= "Update stu set name=?,sex=?,num=? where id=? ";
String[] Str={stu.getname (), Stu.getsex (), stu.getnum () + "", id+ ""};
Db.adu (SQL, str);
}
}
}
}
The author level is limited, inevitably has the mistake, only for the reference!
Excel inserts data into the database and the database exports data to Excel