将excel表格数据转为数据库数据

将excel表格数据转为数据库数据

使用的是POI,思路是:将excel的数据全部取出封装在实体类中,然后使用jdbc将数据写入数据库.
将数据库数据转为excel:https://blog.csdn.net/sqlgao22/article/details/100736325

(一)Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office格式档案读和写的功能。

(二)POI框架的类库：
HSSF － 提供读写Microsoft Excel格式档案的功能。
XSSF － 提供读写Microsoft Excel OOXML格式档案的功能。
HWPF － 提供读写Microsoft Word格式档案的功能。
HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
HDGF － 提供读写Microsoft Visio格式档案的功能。

使用的依赖

		<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>27.0.1-jre</version>
        </dependency>

封装的实体类

单元格类

public class XCell {
    private int cellIndex;
    private String value;
    }

excel表行类

public class XRow {
    private int rowIndex;
    private List<XCell> rowValue;
    }

工具类

读取excel的

public class ReadExcel {
    /**
     * 获取第一行的数据,作为数据库字段的注释
     * @param file
     * @return
     * @throws Exception
     */
    public static XRow getFirstRowData(File file) throws Exception {

        if (!file.exists()) {
            return null;
        }

        //获取 work
        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(fis);
        //只取第一个sheet
        Sheet sheet = workbook.getSheetAt(0);
        //第一行的数据封装
        XRow firstRow = new XRow();
        //取第一行
        Row row = sheet.getRow(sheet.getFirstRowNum());
        firstRow.setRowIndex(sheet.getFirstRowNum()+1);
        //取出头和尾
        short firstCellNum = row.getFirstCellNum();
        short lastCellNum = row.getLastCellNum();
        List<XCell> cells = new ArrayList<>();
        //取出所有的cell
        for (int i = firstCellNum; i < lastCellNum; i++) {
            Cell cell = row.getCell(i);
            //单元格的数据封装
            XCell xCell = new XCell();
            xCell.setCellIndex(i+1);
            //暂时使用string类型测试,可以详细的进行value分类
            xCell.setValue(cell.getStringCellValue());
            cells.add(xCell);
        }
        firstRow.setRowValue(cells);

        //遍历查看
        System.out.println("=====rowIndex===="+firstRow.getRowIndex());
        List<XCell> rowValue = firstRow.getRowValue();
        for (XCell xCell : rowValue) {
            System.out.println(xCell);
        }

        return firstRow;
    }

    /**
     * 以第一行为宽度获取其余的数据
     * @param file
     * @param firstRow
     * @return
     * @throws Exception
     */
    public static List<XRow> getOtherData(File file,XRow firstRow) throws Exception {
        //所有的行数据
        List<XRow> rows = new ArrayList<>();

        //获取 work
        FileInputStream fis = new FileInputStream(file);
        Workbook workbook = new HSSFWorkbook(fis);
        Sheet sheet = workbook.getSheetAt(0);
        int firstRowNum = sheet.getFirstRowNum();
        int lastRowNum = sheet.getLastRowNum();
        //第一行的列数
        int columnSize = firstRow.getRowValue().size();
        int firstCellIndex = firstRow.getRowValue().get(0).getCellIndex();
        int lastCellIndex = firstRow.getRowValue().get(columnSize-1).getCellIndex();
        //去掉第一行
        for (int i = firstRowNum+1; i < lastRowNum+1; i++) {
            //遍历所有的行
            XRow xRow = new XRow();
            xRow.setRowIndex(i+1);
            Row row = sheet.getRow(i);
            List<XCell> cells = new ArrayList<>();
            //以第一行为宽度,遍历所有的cell
            for (int j = firstCellIndex-1; j < lastCellIndex; j++) {
                Cell cell = row.getCell(j);
                XCell xCell = new XCell();
                xCell.setCellIndex(j+1);
                //暂时使用string类型测试,可以详细的进行value分类
                xCell.setValue(cell.getStringCellValue());
                cells.add(xCell);
            }
            xRow.setRowValue(cells);
            rows.add(xRow);
        }

        //遍历查看
        for (XRow row : rows) {
            int rowIndex = row.getRowIndex();
            List<XCell> rowValue = row.getRowValue();
            System.out.println("======rowIndex==="+rowIndex);
            for (XCell xCell : rowValue) {
                System.out.println(xCell);
            }
        }
        return rows;
    }
}

jdbc的工具类

public class DBUtil {

    private static String driver = "com.mysql.cj.jdbc.Driver";
    private static String ip = "127.0.0.1";
    private static String port = "3306";
    private static String db = "dev";
    private static String name = "root";
    private static String password = "112233";
    private static String tableName = "tableName";

    private static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(generateDBUrl(ip, port, db), name, password);
        } catch (ClassNotFoundException e) {
            System.out.println("can not load jdbc driver" + e);
        } catch (SQLException e) {
            System.out.println("get connection failure" + e);
        }
        return conn;
    }

    /**
     * 生成一个连接url
     * @param ip     地址
     * @param port   端口
     * @param dbname 数据库名
     * @return
     */
    private static String generateDBUrl(String ip, String port, String dbname) {
        StringBuilder sb = new StringBuilder();
        sb.append("jdbc:mysql://").append(ip);
        sb.append(":").append(port);
        sb.append("/").append(dbname);
        //sb.append("?useUnicode=true&characterEncoding=utf-8&autoreconnect=true&useSSL=false");
        sb.append("?useUnicode=true&characterEncoding=UTF-8&autoreconnect=true&useSSL=false&rewriteBatchedStatements=true&serverTimezone=GMT%2B8");
        return sb.toString();
    }

    /**
     * 关闭数据库连接
     * @param conn
     */
    private static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 1.创建数据库表
     * @param xRow 表格的列名作为数据库的列名的注释
     * @return 返回一个数据库的列名 column_x
     */
    public static List<String> createTable(XRow xRow) {
        Connection conn = getConnection();
        List<XCell> columnList = xRow.getRowValue();
        Statement stmt;
        List<String> dbColumnList = new ArrayList<>();
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("CREATE TABLE ").append(tableName).append("(");
            sb.append("id BIGINT AUTO_INCREMENT COMMENT '主键' PRIMARY KEY, ");
            int index = 0;
            for (XCell colimn : columnList) {
                index++;
                dbColumnList.add("column" + index);
                sb.append("column" + index + " ");
                sb.append("varchar(200) null ");
                if (index < columnList.size()) {
                    sb.append("COMMENT '" + colimn.getValue() + "', ");
                } else {
                    sb.append("COMMENT '" + colimn.getValue() + "'");
                }
            }
            sb.append(") ENGINE = InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;");
            stmt = conn.createStatement();
            if (0 == stmt.executeLargeUpdate(sb.toString())) {
                System.out.println("成功创建表！");
            } else {
                System.out.println("创建表失败！");
            }
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
        //返回的是当前的列.
        return dbColumnList;
    }

    /**
     * 2.生成一条插入数据的SQL语句
     * @param columnList 数据库的列名,从createTable方法中获取
     * @return 返回一个SQL语句
     */
    private static String generateInsertSQL(List<String> columnList) {
        List<String> columnDataList = new ArrayList<>();
        for (int i = 0; i < columnList.size(); i++) {
            columnDataList.add("?");
        }
        String columnNameStr = Joiner.on(",").join(columnList);
        String columnDataStr = Joiner.on(",").join(columnDataList);
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ");
        sb.append(tableName);
        sb.append(" (");
        sb.append(columnNameStr);
        sb.append(") values(");
        sb.append(columnDataStr);
        sb.append(")");
        return sb.toString();
    }

    /**
     * 插入一个SQL
     *
     * @param columnList 列名
     * @param row        行数据封装类
     * @return
     */
    public static int insertRow(List<String> columnList, XRow row) {
        String insertSQL = generateInsertSQL(columnList);
        int a = 0;
        Connection conn = getConnection();
        try {
            PreparedStatement pst = conn.prepareStatement(insertSQL);
            if (row != null) {
                List<XCell> rowValue = row.getRowValue();
                for (int i = 0; i < rowValue.size(); i++) {
                    XCell xCell = rowValue.get(i);
                    pst.setString(i + 1, xCell.getValue());
                }
            }
            a = pst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
        return a;
    }

    /**
     * 3.批量插入SQL语句
     * @param columnList 列名
     * @param otherData  批量行数据
     * @return
     */
    public static int[] insertRowBatch(List<String> columnList, List<XRow> otherData) {
        String insertSQL = generateInsertSQL(columnList);
        int[] a = null;
        Connection conn = getConnection();

        try {
            PreparedStatement pst = conn.prepareStatement(insertSQL);
            for (XRow rows : otherData) {
                List<XCell> rowValue = rows.getRowValue();
                for (int i = 0; i < rowValue.size(); i++) {
                    XCell xCell = rowValue.get(i);
                    pst.setString(i + 1, xCell.getValue());
                }
                pst.addBatch();
            }
            long startTime = System.currentTimeMillis();
            a = pst.executeBatch();
            long endTime = System.currentTimeMillis();
            System.out.println("插入用时" + (endTime - startTime));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeConnection(conn);
        }
        //返回-2是执行成功
        return a;
    }

    /**
     * 4.获取数据库下的所有表名
     */
    public static List<String> getDbAllTables() {
        /*t_roles  t_user  tablename  tablename1  uf_1122  sys_config*/
        List<String> tableNames = new ArrayList<>();
        Connection conn = getConnection();
        ResultSet rs = null;
        try {
            //获取数据库的元数据
            DatabaseMetaData db = conn.getMetaData();
            //从元数据中获取到所有的表名
            rs = db.getTables(null, null, null, new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                closeConnection(conn);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableNames;
    }
}

注释

1.先将excel中的第一行表头读取出来,作为数据库的字段的注释,因为最好不要使用中文的字段,使用column_x作为字段名.实际代表类型是注释中的内容.
2.使用表头数据创建数据库的表,表创建成功后方法返回表的字段.用于数据的插入和更新.
3.批量生成insert SQL将数据写入新创建的表中.

代码需要改进地方

1.读取excel的时候需要根据文件类型判断是xls还是用xlxs类型,这两种类型使用的POI实现类不通.
2.读取表头和读取数据可以合并到一起,看情况.
3.读取单元格的时候,需要判断cell的具体类型,我为了测试,只是使用了string,实际可以进行类型的判断.

测试

表格内容:

测试用例

        //获取第一行作为表的注释
        XRow firstRowData = ReadExcel.getFirstRowData(new File("d:\\test.xls"));

        //获取数据作为表的内容
        List<XRow> otherData = ReadExcel.getOtherData(new File("d:\\test.xls"), firstRowData);

        //创建数据库表,返回表的字段
        List<String> columnList = DBUtil.createTable(firstRowData);

        //插入数据
        int[] ints = DBUtil.insertRowBatch(columnList, otherData);

效果

只取出了与表头相同宽度的数据.