excel导入数据库实例（含源码超级详细适合新手）

（案例的源码在我的百度网盘里，需要的朋友可以加我微信分享，gqljxg1514）

所有文件一览：

运行结果图：

1，首先创建实体类Student.java

package com.b510.excel.vo;


public class Student {
    /**
     * id
     */
    private Integer id;
    /**
     * 学号
     */
    private String no;
    /**
     * 姓名
     */
    private String name;
    /**
     * 学院
     */
    private String age;
    /**
     * 成绩
     */
    private float score;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getNo() {
        return no;
    }

    public void setNo(String no) {
        this.no = no;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getAge() {
        return age;
    }

    public void setAge(String age) {
        this.age = age;
    }

    public float getScore() {
        return score;
    }

    public void setScore(float score) {
        this.score = score;
    }

}

View Code

2,编写工具类DbUtil.java,工具类主要作用是为了拿来保存进数据库的

package com.b510.excel.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.b510.common.Common;
import com.b510.excel.vo.Student;


public class DbUtil {
   //保存到数据库
    public static void insert(String sql, Student student) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            Class.forName(Common.DRIVER);
            conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getNo());
            ps.setString(2, student.getName());
            ps.setString(3, student.getAge());
            ps.setString(4, String.valueOf(student.getScore()));
            boolean flag = ps.execute();
            if(!flag){
                System.out.println("Save data : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + " succeed!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
    }
    

    @SuppressWarnings({ "unchecked", "rawtypes" })
    public static List selectOne(String sql, Student student) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List list = new ArrayList();
        try {
            Class.forName(Common.DRIVER);
            conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                if(rs.getString("no").equals(student.getNo()) || rs.getString("name").equals(student.getName())|| rs.getString("age").equals(student.getAge())){
                    list.add(1);
                }else{
                    list.add(0);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return list;
    }
    
    
    public static ResultSet selectAll(String sql) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName(Common.DRIVER);
            conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (ps != null) {
                ps.close();
            }
            if (conn != null) {
                conn.close();
            }
        }
        return rs;
    }

}

View Code

3，编写Common.java 主要用来连接数据库，这里的IP一般都可以默认写127.0.0.1

package com.b510.common;

public class Common {

    // connect the database
    public static final String DRIVER = "com.mysql.jdbc.Driver";
    public static final String DB_NAME = "test";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "root";
    public static final String IP = "127.0.0.1";
    public static final String PORT = "3306";
    public static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME;
    
    // common
    public static final String EXCEL_PATH = "lib/student_info.xls";
    
    // sql
    public static final String INSERT_STUDENT_SQL = "insert into student_info(no, name, age, score) values(?, ?, ?, ?)";
    public static final String UPDATE_STUDENT_SQL = "update student_info set no = ?, name = ?, age= ?, score = ? where id = ? ";
    public static final String SELECT_STUDENT_ALL_SQL = "select id,no,name,age,score from student_info";
    public static final String SELECT_STUDENT_SQL = "select * from student_info where name like ";
}

View Code

4，编写 ReadExcel.java 主要用来读取Excel表的内容的

package com.b510.excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.b510.common.Common;
import com.b510.excel.vo.Student;

public class ReadExcel {

    public List<Student> readXls() throws IOException {
        InputStream is = new FileInputStream(Common.EXCEL_PATH);
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        Student student = null;
        List<Student> list = new ArrayList<Student>();
        // 循环工作表Sheet
        for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
            HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
            if (hssfSheet == null) {
                continue;
            }
            // 循环行Row
            for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
                HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                if (hssfRow != null) {
                    student = new Student();
                    HSSFCell no = hssfRow.getCell(0);
                    HSSFCell name = hssfRow.getCell(1);
                    HSSFCell age = hssfRow.getCell(2);
                    HSSFCell score = hssfRow.getCell(3);
                    student.setNo(getValue(no));
                    student.setName(getValue(name));
                    student.setAge(getValue(age));
                    student.setScore(Float.valueOf(getValue(score)));
                    list.add(student);
                }
            }
        }
        return list;
    }
    
     @SuppressWarnings("static-access")
    private String getValue(HSSFCell hssfCell) {
            if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
                // 返回布尔类型的值
                return String.valueOf(hssfCell.getBooleanCellValue());
            } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
                // 返回数值类型的值
                return String.valueOf(hssfCell.getNumericCellValue());
            } else {
                // 返回字符串类型的值
                return String.valueOf(hssfCell.getStringCellValue());
            }
        }
}

View Code

5，编写SaveData2DB.java 在save（）中 new一个ReadExcel（）；然后将所有数据保存到数据库中

package com.b510.excel;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import com.b510.common.Common;
import com.b510.excel.util.DbUtil;
import com.b510.excel.vo.Student;


public class SaveData2DB {

    @SuppressWarnings({ "rawtypes" })
    public void save() throws IOException, SQLException {
        ReadExcel xlsMain = new ReadExcel();
        Student student = null;
        List<Student> list = xlsMain.readXls();

        for (int i = 0; i < list.size(); i++) {
            student = list.get(i);
            List l = DbUtil.selectOne(Common.SELECT_STUDENT_SQL + "'%" + student.getName() + "%'", student);
            if (!l.contains(1)) {
                DbUtil.insert(Common.INSERT_STUDENT_SQL, student);
            } else {
                System.out.println("The Record was Exist : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + ", and has been throw away!");
            }
        }
    }
}

View Code

6,最后写测试类Client.java 直接运行main（）方法即可存入数据库中

注意：lib下的包可以在官网下载，其中

这两项别添加配置，否则容易出错

有问题的地方欢迎大家指正我的错误，互相学习，可以加我微信互相讨论gqljxg1514

发表于 2018-07-09 16:22 梨花压不压海棠阅读(2224) 评论(0) 编辑收藏举报

刷新页面返回顶部

excel导入数据库实例（含源码超级详细适合新手）

公告

导航

excel导入数据库实例（含源码 超级详细适合新手）

公告

导航

excel导入数据库实例（含源码超级详细适合新手）