Java实现Excel导入数据库,数据库中的数据导入到Excel

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 &lt; 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 &lt; 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;

/**

*/
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;

/**

/
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&lt;StuEntity&gt;<span style="color: #000000;"> getAllByExcel(String file){
    List</span>&lt;StuEntity&gt; list=<span style="color: #0000ff;">new</span> ArrayList&lt;StuEntity&gt;<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&lt;StuEntity&gt; 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&lt;StuEntity&gt; 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 &lt; 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;

/**

/
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好资源请关注公众号哦**

在这里插入图片描述

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值