Excel inserts data into the database and the database exports data to Excel

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