需求

业务给到一份excel表格，需要导入到对应的数据表，由于字段不同以及对应到多张表，数据也不完整，
打算先临时导入到数据库临时表中，再用sql语句处理数据后再使用sql导入到对应的表。

pom依赖

    <dependencies>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.10</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.32</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.12</version>
        </dependency>
    </dependencies>

导入代码

    public static void main(String[] args) {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName(JDBC_DRIVER);
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            String fileName = "F:\\test\\data.xlsx";
            List<Map<String, String>> list = EasyExcel.read(fileName).sheet().doReadSync();
            for (Map<String, String> row : list) {
                Set<Map.Entry<String, String>> entries = row.entrySet();
                StringBuilder values = new StringBuilder("(");
                for (Map.Entry<String, String> entry : entries) {
                    String v = entry.getValue();
                    if (v == null) {
                        values.append("null,");
                        continue;
                    }
                    v = v.replaceAll("'", "\\\\'");
                    values.append("'").append(v).append("',");
                }
                values = new StringBuilder(values.substring(0, values.length() - 1));
                values.append(")");
                stmt = conn.createStatement();
                String sql = "INSERT INTO ex(a1,a2,a3,a4,a5,a6,a7,a8,a9,a10,a11,a12,a13,a14,a15,a16,a17,a18,a19,a20," +
                        "a21,a22)" +
                        " VALUES" + values;
                System.out.println(sql);
                stmt = conn.createStatement();
                stmt.executeUpdate(sql);
                stmt.close();
                // conn.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
    }