java读取excel并使用JDBC方式写入数据库

一，读取excel文件工具类

废话不多说直接上代码：

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class ReadExcel {
    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public List readExcel(File file) {
        try {
            // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            for (int index = 0; index < sheet_size; index++) {
                List<List> outerList=new ArrayList<List>();
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    List innerList=new ArrayList();
                    // sheet.getColumns()返回该页的总列数
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        String cellinfo = sheet.getCell(j, i).getContents();
                        if(cellinfo.isEmpty()){
                            continue;
                        }
                        innerList.add(cellinfo);
                    }
                    outerList.add(i, innerList);
                }
                return outerList;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }
}

二：JDBC工具类

import java.sql.*;

public final class JDBCUtils {
    private static String driver="com.mysql.jdbc.Driver";
    private static String url="mysqldizhi";
    private static String user="root";
    private static String password="root";

    private JDBCUtils(){}

    static {
        /**
         * 驱动注册
         */
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new ExceptionInInitializerError(e);
        }

    }

    /**
     * 获取 Connetion
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException{
        return DriverManager.getConnection(url, user, password);
    }

    /**
     * 释放资源
     * @param conn
     * @param st
     * @param rs
     */
    public static void colseResource(Connection conn,Statement st,ResultSet rs) {
        closeResultSet(rs);
        closeStatement(st);
        closeConnection(conn);

    }

    /**
     * 释放连接 Connection
     * @param conn
     */
    public static void closeConnection(Connection conn) {
        if(conn !=null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        conn = null;
    }

    /**
     * 释放语句执行者 Statement
     * @param st
     */
    public static void closeStatement(Statement st) {
        if(st !=null) {
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        st = null;
    }

    /**
     * 释放结果集 ResultSet
     * @param rs
     */
    public static void closeResultSet(ResultSet rs) {
        if(rs !=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        //等待垃圾回收
        rs = null;
    }
}

三，读取数据写入数据库

下面是我自己的代码，请各位根据自身情况修改

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class EasyTable {
        public static void main(String[] args) {
            EasyTable easyTable=new EasyTable();
            ReadExcel obj = new ReadExcel();
            File file = new File("D:/re_user_evaluate.xls");
            List excelList = obj.readExcel(file);
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            //插入re_user_evaluate数据
            try {
                // 获取连接
                conn = JDBCUtils.getConnection();
                // 编写sql
                String sql = "insert into re_user_evaluate(uid,res_id,status,last_evaluate_time) values (?,?,?,?)";
                // 创建语句执行者
                st = conn.prepareStatement(sql);
                //设置参数
                for (int i = 1; i < excelList.size(); i++) {
                    List list = (List) excelList.get(i);
                    System.out.println(i);
                    for (int j = 1; j < list.size(); j++) {
                        st.setString(j, list.get(j).toString());
                    }
                    st.executeUpdate();
                    System.out.println("re_user_evaluate插入成功！"+list);
                    // 若成功执行完所有的插入操作，则正常结束
                }
                easyTable.addUserDmission();
                conn.commit();//2,进行手动提交（commit）
            } catch (SQLException e) {
                try {
                    // 若出现异常，对数据库中所有已完成的操作全部撤销，则回滚到事务开始状态
                    if (!conn.isClosed()) {
                        conn.rollback();//4,当异常发生执行catch中SQLException时，记得要rollback(回滚)；
                        conn.setAutoCommit(true);
                    }
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            } finally {
                JDBCUtils.colseResource(conn, st, rs);
            }
        }
        //插入re_user_dimension数据
        public void addUserDmission(){
            ReadExcel obj = new ReadExcel();
            File file = new File("D:/re_user_dimension.xls");
            List excelList = obj.readExcel(file);
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            try {
                // 获取连接
                conn = JDBCUtils.getConnection();
                // 编写sql
                String sql = "insert into re_user_dimension(uid,dimension_id,question_number,right_number,`type`) values (?,?,?,?,?)";
                // 创建语句执行者
                st = conn.prepareStatement(sql);
                //设置参数
                for (int i = 1; i < excelList.size(); i++) {
                    List list = (List) excelList.get(i);
                    for (int j = 1; j < list.size(); j++) {
                        st.setString(j, list.get(j).toString());
                    }
                    st.executeUpdate();
                    System.out.println("re_user_dimension插入成功！"+list);
                    // 若成功执行完所有的插入操作，则正常结束
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }
}

我用的是maven下面是maven引入资源

<dependency>
   <groupId>net.sourceforge.jexcelapi</groupId>
   <artifactId>jxl</artifactId>
   <version>2.6.12</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.16</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.16</version>
</dependency>