Java实现Excel导入数据库，数据库中的数据导入到Excel。。转载自学资料总结实现的功能： Java实现Excel导入数据库，如果存在就更新数据库中的数据导入到Excel 1、

落日映苍穹つ 2022-06-11 08:54 385阅读 0赞

## [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的字符串方法、插入的方法、实体类

表结构如下 ：

![312335090093127.png][]

![312343215567616.png][]

连接数据库的工具类

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

表的实体如下

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

Java实现Excel导入数据核心类 **读取Excel表中所有的数据、操作数据（查询、更新）**

![复制代码][copycode.gif]

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");
                    
                    //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<StuEntity> getAllByExcel(String file){
            List<StuEntity> list=new ArrayList<StuEntity>();
            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<StuEntity> all=getAllByDb();
            for (StuEntity stuEntity : all) {
                System.out.println(stuEntity.toString());
            }*/
            
            System.out.println(isExist(1));
            
        }
        
    }

![复制代码][copycode.gif]

**数据的数据导入到Excel表**

![复制代码][copycode.gif]

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<StuEntity> 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();
            } 
        }
    }

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

[Java_Excel_Excel]: http://www.cnblogs.com/tutuwowo/p/5846978.html
[312335090093127.png]: /images/20220611/dcf6750408974a9e83a3d927987c947a.png
[312343215567616.png]: /images/20220611/4d6ba41b089746228978a0d6bcdfd963.png
[copycode.gif]: /images/20220611/64079860154b405ca023c283d7f3f854.png