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 </span>= "com.mysql.jdbc.Driver"<span style="color: #000000;">;
String url </span>= "jdbc:mysql://127.0.0.1:3306/javenforexcel"<span style="color: #000000;">;
Connection con </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
ResultSet res </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> DataBase() {
</span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
Class.forName(driver);
con </span>= DriverManager.getConnection(url, "root", "root"<span style="color: #000000;">);
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (ClassNotFoundException e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
System.err.println("装载 JDBC/ODBC 驱动程序失败。"<span style="color: #000000;"> );
e.printStackTrace();
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (SQLException e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
System.err.println("无法连接数据库"<span style="color: #000000;"> );
e.printStackTrace();
}
}
</span><span style="color: #008000;">//</span><span style="color: #008000;"> 查询</span>
<span style="color: #0000ff;">public</span><span style="color: #000000;"> ResultSet Search(String sql, String str[]) {
DataBase();
</span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
PreparedStatement pst </span>=<span style="color: #000000;">con.prepareStatement(sql);
</span><span style="color: #0000ff;">if</span> (str != <span style="color: #0000ff;">null</span><span style="color: #000000;">) {
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i < str.length; i++<span style="color: #000000;">) {
pst.setString(i </span>+ 1<span style="color: #000000;">, str[i]);
}
}
res </span>=<span style="color: #000000;"> pst.executeQuery();
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
e.printStackTrace();
}
return res;
}
</span><span style="color: #008000;">//</span><span style="color: #008000;"> 增删修改</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> AddU(String sql, String str[]) {
</span><span style="color: #0000ff;">int</span> a = 0<span style="color: #000000;">;
DataBase();
</span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
PreparedStatement pst </span>=<span style="color: #000000;"> con.prepareStatement(sql);
</span><span style="color: #0000ff;">if</span> (str != <span style="color: #0000ff;">null</span><span style="color: #000000;">) {
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i < str.length; i++<span style="color: #000000;">) {
pst.setString(i </span>+ 1<span style="color: #000000;">, str[i]);
}
}
a </span>=<span style="color: #000000;"> pst.executeUpdate();
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
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;
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> StuEntity() {
}
</span><span style="color: #0000ff;">public</span> StuEntity(<span style="color: #0000ff;">int</span> id, String name, String sex, <span style="color: #0000ff;">int</span><span style="color: #000000;"> num) {
</span><span style="color: #0000ff;">this</span>.id =<span style="color: #000000;"> id;
</span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> name;
</span><span style="color: #0000ff;">this</span>.sex =<span style="color: #000000;"> sex;
</span><span style="color: #0000ff;">this</span>.num =<span style="color: #000000;"> num;
}
@Override
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String toString() {
</span><span style="color: #0000ff;">return</span> "StuEntity [id=" + id + ", name=" + name + ", sex=" +<span style="color: #000000;"> sex
</span>+ ", num=" + num + "]"<span style="color: #000000;">;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> getId() {
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> id;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> setId(<span style="color: #0000ff;">int</span><span style="color: #000000;"> id) {
</span><span style="color: #0000ff;">this</span>.id =<span style="color: #000000;"> id;
}
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getName() {
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> name;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setName(String name) {
</span><span style="color: #0000ff;">this</span>.name =<span style="color: #000000;"> name;
}
</span><span style="color: #0000ff;">public</span><span style="color: #000000;"> String getSex() {
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> sex;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> setSex(String sex) {
</span><span style="color: #0000ff;">this</span>.sex =<span style="color: #000000;"> sex;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">int</span><span style="color: #000000;"> getNum() {
</span><span style="color: #0000ff;">return</span><span style="color: #000000;"> num;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">void</span> setNum(<span style="color: #0000ff;">int</span><span style="color: #000000;"> num) {
</span><span style="color: #0000ff;">this</span>.num =<span style="color: #000000;"> 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<StuEntity> 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”);
</span><span style="color: #008000;">//</span><span style="color: #008000;">System.out.println(id+" "+name+" "+sex+ " "+num);</span>
list.add(<span style="color: #0000ff;">new</span><span style="color: #000000;"> StuEntity(id, name, sex, num));
}
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (SQLException e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
e.printStackTrace();
}
return list;
}
</span><span style="color: #008000;">/**</span><span style="color: #008000;">
* 查询指定目录中电子表格中所有的数据
* </span><span style="color: #808080;">@param</span><span style="color: #008000;"> file 文件完整路径
* </span><span style="color: #808080;">@return</span>
<span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> List<StuEntity><span style="color: #000000;"> getAllByExcel(String file){
List</span><StuEntity> list=<span style="color: #0000ff;">new</span> ArrayList<StuEntity><span style="color: #000000;">();
</span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
Workbook rwb</span>=Workbook.getWorkbook(<span style="color: #0000ff;">new</span><span style="color: #000000;"> File(file));
Sheet rs</span>=rwb.getSheet("Test Shee 1");<span style="color: #008000;">//</span><span style="color: #008000;">或者rwb.getSheet(0)</span>
<span style="color: #0000ff;">int</span> clos=rs.getColumns();<span style="color: #008000;">//</span><span style="color: #008000;">得到所有的列</span>
<span style="color: #0000ff;">int</span> rows=rs.getRows();<span style="color: #008000;">//</span><span style="color: #008000;">得到所有的行</span>
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(</span>"id:"+id+" name:"+name+" sex:"+sex+" num:"+<span style="color: #000000;">num);
list.add(</span><span style="color: #0000ff;">new</span><span style="color: #000000;"> StuEntity(Integer.parseInt(id), name, sex, Integer.parseInt(num)));
}
}
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
e.printStackTrace();
}
return list;
}
</span><span style="color: #008000;">/**</span><span style="color: #008000;">
* 通过Id判断是否存在
* </span><span style="color: #808080;">@param</span><span style="color: #008000;"> id
* </span><span style="color: #808080;">@return</span>
<span style="color: #008000;">*/</span>
<span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">boolean</span> isExist(<span style="color: #0000ff;">int</span><span style="color: #000000;"> id){
</span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
DBhepler db</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> DBhepler();
ResultSet rs</span>=db.Search("select * from stu where id=?", <span style="color: #0000ff;">new</span> String[]{id+""<span style="color: #000000;">});
</span><span style="color: #0000ff;">if</span><span style="color: #000000;"> (rs.next()) {
</span><span style="color: #0000ff;">return</span> <span style="color: #0000ff;">true</span><span style="color: #000000;">;
}
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (SQLException e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
e.printStackTrace();
}
return false;
}
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> main(String[] args) {
</span><span style="color: #008000;">/*</span><span style="color: #008000;">List<StuEntity> all=getAllByDb();
for (StuEntity stuEntity : all) {
System.out.println(stuEntity.toString());
}</span><span style="color: #008000;">*/</span><span style="color: #000000;">
System.out.println(isExist(</span>1<span style="color: #000000;">));
}
}
数据的数据导入到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 {
</span><span style="color: #0000ff;">public</span> <span style="color: #0000ff;">static</span> <span style="color: #0000ff;">void</span><span style="color: #000000;"> main(String[] args) {
</span><span style="color: #0000ff;">try</span><span style="color: #000000;"> {
WritableWorkbook wwb </span>= <span style="color: #0000ff;">null</span><span style="color: #000000;">;
</span><span style="color: #008000;">//</span><span style="color: #008000;"> 创建可写入的Excel工作簿</span>
String fileName = "D://book.xls"<span style="color: #000000;">;
File file</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> File(fileName);
</span><span style="color: #0000ff;">if</span> (!<span style="color: #000000;">file.exists()) {
file.createNewFile();
}
</span><span style="color: #008000;">//</span><span style="color: #008000;">以fileName为文件名来创建一个Workbook</span>
wwb =<span style="color: #000000;"> Workbook.createWorkbook(file);
</span><span style="color: #008000;">//</span><span style="color: #008000;"> 创建工作表</span>
WritableSheet ws = wwb.createSheet("Test Shee 1", 0<span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">查询数据库中所有的数据</span>
List<StuEntity> list=<span style="color: #000000;"> StuService.getAllByDb();
</span><span style="color: #008000;">//</span><span style="color: #008000;">要插入到的Excel表格的行号,默认从0开始</span>
Label labelId= <span style="color: #0000ff;">new</span> Label(0, 0, "编号(id)");<span style="color: #008000;">//</span><span style="color: #008000;">表示第</span>
Label labelName= <span style="color: #0000ff;">new</span> Label(1, 0, "姓名(name)"<span style="color: #000000;">);
Label labelSex</span>= <span style="color: #0000ff;">new</span> Label(2, 0, "性别(sex)"<span style="color: #000000;">);
Label labelNum</span>= <span style="color: #0000ff;">new</span> Label(3, 0, "薪水(num)"<span style="color: #000000;">);
ws.addCell(labelId);
ws.addCell(labelName);
ws.addCell(labelSex);
ws.addCell(labelNum);
</span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = 0; i < list.size(); i++<span style="color: #000000;">) {
Label labelId_i</span>= <span style="color: #0000ff;">new</span> Label(0, i+1, list.get(i).getId()+""<span style="color: #000000;">);
Label labelName_i</span>= <span style="color: #0000ff;">new</span> Label(1, i+1<span style="color: #000000;">, list.get(i).getName());
Label labelSex_i</span>= <span style="color: #0000ff;">new</span> Label(2, i+1<span style="color: #000000;">, list.get(i).getSex());
Label labelNum_i</span>= <span style="color: #0000ff;">new</span> Label(3, i+1, list.get(i).getNum()+""<span style="color: #000000;">);
ws.addCell(labelId_i);
ws.addCell(labelName_i);
ws.addCell(labelSex_i);
ws.addCell(labelNum_i);
}
</span><span style="color: #008000;">//</span><span style="color: #008000;">写进文档</span>
wwb.write();
// 关闭Excel工作簿对象
wwb.close();
} </span><span style="color: #0000ff;">catch</span><span style="color: #000000;"> (Exception e) {
</span><span style="color: #008000;">//</span><span style="color: #008000;"> TODO Auto-generated catch block</span>
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<StuEntity> listExcel=StuService.getAllByExcel(“d://book.xls”);
///得到数据库表中所有的数据
List<StuEntity> listDb=StuService.getAllByDb();*/
DBhepler db</span>=<span style="color: #0000ff;">new</span><span style="color: #000000;"> DBhepler();
</span><span style="color: #0000ff;">for</span><span style="color: #000000;"> (StuEntity stuEntity : listExcel) {
</span><span style="color: #0000ff;">int</span> id=<span style="color: #000000;">stuEntity.getId();
</span><span style="color: #0000ff;">if</span> (!<span style="color: #000000;">StuService.isExist(id)) {
</span><span style="color: #008000;">//</span><span style="color: #008000;">不存在就添加</span>
String sql="insert into stu (name,sex,num) values(?,?,?)"<span style="color: #000000;">;
String[] str</span>=<span style="color: #0000ff;">new</span> String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+""<span style="color: #000000;">};
db.AddU(sql, str);
}</span><span style="color: #0000ff;">else</span><span style="color: #000000;"> {
</span><span style="color: #008000;">//</span><span style="color: #008000;">存在就更新</span>
String sql="update stu set name=?,sex=?,num=? where id=?"<span style="color: #000000;">;
String[] str</span>=<span style="color: #0000ff;">new</span> String[]{stuEntity.getName(),stuEntity.getSex(),stuEntity.getNum()+"",id+""<span style="color: #000000;">};
db.AddU(sql, str);
}
}
}
}
如果以上代码对你有帮助请留下脚印 支持一下 转载请标明地址 欢迎吐槽
源代码下载地址 http://download.youkuaiyun.com/detail/zyw_java/7430807
**更多java好资源请关注公众号哦**