excel导入数据库java_Java实现Excel导入数据库，数据库中的数据导入到Excel

实现的功能：

Java实现Excel导入数据库，如果存在就更新

数据库中的数据导入到Excel

1、添加jxl.jar mysql-connector-java.1.7-bin.jar包到项目的lib目录下­

2、Excel文件目录:D://book.xls

3、数据库名：javenforexcel

4、表名:stu

5、编写类：连接mysql的字符串方法、插入的方法、实体类­­

表结构如下 ：

连接数据库的工具类

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.sqlserver.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("装载 JDBC/ODBC 驱动程序失败。" );

e.printStackTrace();

} catch (SQLException e) {

// TODO Auto-generated catch block

System.err.println("无法连接数据库" );

e.printStackTrace();

}

}

// 查询

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;

}

// 增删修改

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;

}

}

表的实体如下

package com.javen.entity;

/**

* @author Javen

* @Email zyw205@gmail.com

*

*/

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实现Excel导入数据核心类 读取Excel表中所有的数据、操作数据(查询、更新)

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 zyw205@gmail.com

*

*/

public class StuService {

/**

* 查询stu表中所有的数据

* @return

*/

public static List getAllByDb(){

List list=new ArrayList();

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.printStackTrace();

}

return list;

}

/**

* 查询指定目录中电子表格中所有的数据

* @param file 文件完整路径

* @return

*/

public static List getAllByExcel(String file){

List list=new ArrayList();

try {

Workbook rwb=Workbook.getWorkbook(new File(file));

Sheet rs=rwb.getSheet("Test Shee 1");//或者rwb.getSheet(0)

int clos=rs.getColumns();//得到所有的列

int rows=rs.getRows();//得到所有的行

System.out.println(clos+" rows:"+rows);

for (int i = 1; i < rows; i++) {

for (int j = 0; j < clos; j++) {

//第一个是列数，第二个是行数

String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++

String name=rs.getCell(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判断是否存在

* @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 all=getAllByDb();

for (StuEntity stuEntity : all) {

System.out.println(stuEntity.toString());

}*/

System.out.println(isExist(1));

}

}

数据的数据导入到Excel表

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;

// 创建可写入的Excel工作簿

String fileName = "D://book.xls";

File file=new File(fileName);

if (!file.exists()) {

file.createNewFile();

}

//以fileName为文件名来创建一个Workbook

wwb = Workbook.createWorkbook(file);

// 创建工作表

WritableSheet ws = wwb.createSheet("Test Shee 1", 0);

//查询数据库中所有的数据

List list= StuService.getAllByDb();

//要插入到的Excel表格的行号，默认从0开始

Label labelId= new Label(0, 0, "编号(id)");//表示第

Label labelName= new Label(1, 0, "姓名(name)");

Label labelSex= new Label(2, 0, "性别(sex)");

Label labelNum= new Label(3, 0, "薪水(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.get(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);

}

//写进文档

wwb.write();

// 关闭Excel工作簿对象

wwb.close();

} catch (Exception e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

Excel表中的数据导入到MySql数据库

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 zyw205@gmail.com

*

*/

public class TestExcelToDb {

public static void main(String[] args) {

//得到表格中所有的数据

List listExcel=StuService.getAllByExcel("d://book.xls");

/*//得到数据库表中所有的数据

List listDb=StuService.getAllByDb();*/

DBhepler db=new DBhepler();

for (StuEntity stuEntity : listExcel) {

int id=stuEntity.getId();

if (!StuService.isExist(id)) {

//不存在就添加

String sql="insert into stu (name,sex,num) values(?,?,?)";

String[] str=new String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""};

db.AddU(sql, str);

}else {

//存在就更新

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);

}

}

}

}