Java读取excel模板生成对应数据库表的ddl建表语句

怼烎@ 2024-03-25 09:48 72阅读 0赞

## 业务功能、应该场景 ##

一般会通过excle里，写入表的字段信息，如果新建的表、字段信息较多，自己手动写成SQL非常不方便，因此推出两个excle模板 放入对应的内容信息即可快速生成建表语句。  
我这里推出两个模板，代码实现也比较简单，你也可以自定义自己工作中的模板，改写下代码即可。为了方便我这里把代码跟文件模板放在这里，[需要的点我下载即可][Link 1]

![在这里插入图片描述][13d382580ecb488da98d42e83501f6ef.png]  
![在这里插入图片描述][17792dc79a414e7ea4bc1102a0645903.png]

![在这里插入图片描述][925a2ed5160e4e7688cf1af591289561.png]  
代码部分  
Maven 依赖

<dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>4.0.1</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>4.0.1</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-lang3</artifactId>
                <version>3.7</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.commons</groupId>
                <artifactId>commons-collections4</artifactId>
                <version>4.1</version>
            </dependency>

模板1的代码

package org.example.demo.onetest.excletable;
    
    import org.apache.commons.lang3.StringUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.*;
    import java.util.Iterator;
    
    /**
     * @author Jack Li
     * @description 读取excel文件内容生成数据库表ddl
     * @date 2022/3/27 19:54
     */
    
    public class ExcelSheet1Utils {
        
    
    
        /**
         * 读取excel文件内容生成数据库表ddl
         *
         * @param filePath excel文件的绝对路径
         */
        public static void getDataFromExcel(String filePath) {
        
            if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
        
                System.out.println("文件不是excel类型");
            }
            InputStream fis = null;
            Workbook wookbook = null;
            try {
        
                fis = new FileInputStream(filePath);
                if (filePath.endsWith(".xls")) {
        
                    try {
        
                        //2003版本的excel，用.xls结尾
                        wookbook = new HSSFWorkbook(fis);
                    } catch (IOException e) {
        
                        e.printStackTrace();
                    }
                }
                if (filePath.endsWith(".xlsx")) {
        
                    try {
        
                        //2007版本的excel，用.xlsx结尾
                        wookbook = new XSSFWorkbook(fis);
                    } catch (IOException e) {
        
                        e.printStackTrace();
                    }
                }
                Iterator<Sheet> sheets = wookbook.sheetIterator();
    //            while (sheets.hasNext() ) {
        
                    StringBuilder ddl = new StringBuilder();
                    // 是否自增
                    boolean autoIncrement = false;
                    Sheet sheet = sheets.next();
                    System.out.println("-- ------------------------当前读取的sheet页：" + sheet.getSheetName() + "--------------------------");
                    // 当前读取行的行号
                    int rowId = 1;
                    Iterator<Row> rows = sheet.rowIterator();
                    String tableEnglishName = "";
                    String tableChineseName = "";
                    while (rows.hasNext()) {
        
                        Row row = rows.next();
                        Cell modelRow = row.getCell(0);
                        //获取表英文名
                        if (rowId == 1) {
        
                            Cell cell1 = row.getCell(0);
                            if (!"表英文名".equals(cell1.getStringCellValue())) {
        
                                System.out.println("第一行第一格应该为“表英文名”!");
                                return;
                            }
                            Cell cell2 = row.getCell(1);
                            tableEnglishName = cell2.getStringCellValue();
                            ddl.append("CREATE TABLE " + "`" + tableEnglishName + "` (" + "\r\n");
                            rowId++;
                            continue;
                        }
                        //获取表中文名
                        if (rowId == 2) {
        
                            Cell cell1 = row.getCell(0);
                            if (!"表中文名".equals(cell1.getStringCellValue())) {
        
                                System.out.println("第2行第一格应该为“表中文名”!");
                                return;
                            }
                            Cell cell2 = row.getCell(1);
                            tableChineseName = cell2.getStringCellValue();
                            rowId++;
                            continue;
                        }
                        //校验属性列名称和顺序
                        if (rowId == 3) {
        
                            if (row.getPhysicalNumberOfCells() != 7) {
        
                                System.out.println("第2行应该只有7个单元格!");
                                return;
                            }
                            Iterator<Cell> cells = row.cellIterator();
                            StringBuilder tableField = new StringBuilder();
                            while (cells.hasNext()) {
        
                                tableField.append(cells.next().getStringCellValue().trim());
                            }
                            if (!"字段名类型长度,小数点是否为主键是否自增是否为空注释".equals(tableField.toString())) {
        
                                System.out.println("第3行应该为 字段名 类型 长度,小数点 是否为主键 是否自增 是否为空 注释 !");
                                return;
                            }
                            rowId++;
                            continue;
                        }
                        //过滤掉模板备注的东西
                        if (modelRow.getStringCellValue().contains("模板备注")) {
        
                            break;
                        }
                        if (!row.cellIterator().hasNext()) {
        
                            break;
                        }
                        // 字段名
                        String fieldName = row.getCell(0).getStringCellValue();
                        if (fieldName == null | "".equals(fieldName)){
        
                            break;
                        }
                        // 字段类型
                        String fieldType = row.getCell(1).getStringCellValue();
                        // 字段长度
                        Cell cell3 = row.getCell(2);
                        cell3.setCellType(CellType.STRING);
                        String fieldLength = cell3.getStringCellValue();
                        if (StringUtils.isBlank(fieldLength)) {
        
                            fieldLength = "0";
                        }
                        // 是否为主键
                        Cell cell4 = row.getCell(3);
                        // 是否自增
                        Cell cell5 = row.getCell(4);
                        //字段是否为空
                        //  String ifnullValue = row.getCell(5).getStringCellValue();
                        Cell cell6 = row.getCell(5);
                        // 字段注释
                        String fieldComment = row.getCell(6).getStringCellValue();
    
                        ddl.append(
                                "`" + fieldName + "` "
                                        + fieldType
                                        + (!"0".equals(fieldLength) ? "(" + fieldLength + ")" : "")
                                        + (cell4 != null && "Y".equals(cell4.getStringCellValue()) ? " PRIMARY KEY " : "")
                                        + (cell5 != null && "Y".equals(cell5.getStringCellValue()) ? " AUTO_INCREMENT " : "")
                                        + (cell6 != null && "N".equals(cell6.getStringCellValue()) ? " NOT NULL " : " DEFAULT NULL")
                                        + " COMMENT '" + fieldComment + "'"
                                        + (rows.hasNext() ? ",\r\n" : "\r\n")
                        );
                        if (cell4 != null && "Y".equals(cell5.getStringCellValue())) {
        
                            autoIncrement = true;
                        }
    
                        rowId++;
                    }
                    if (ddl.toString().endsWith(",\r\n")){
        
                        ddl = ddl.deleteCharAt(ddl.length()-3);
    //                    ddl.append("\r\n");
                    }
                    ddl.append(") ENGINE=InnoDB " + (autoIncrement ? "AUTO_INCREMENT=1" : "") + " DEFAULT CHARSET=utf8 "
                            + (!"".equals(tableChineseName) ? "COMMENT = '" + tableChineseName + "'" : "") + ";\r\n");
                    ddl.append("-- --------------------------------------------------------------------------------\r\n");
                    System.out.println(ddl.toString());
                    //writeMessageToFile(ddl.toString());
    //            }
                System.out.println("-- 运行成功");
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                try {
        
                    fis.close();
                } catch (IOException e) {
        
                    e.printStackTrace();
                }
            }
        }
    
        public static void writeMessageToFile(String message) {
        
            try {
        
                File file = new File("C:\\Users\\91845\\Desktop\\ddl.txt");
    
                if (!file.exists()) {
        
                    file.createNewFile();
                }
                FileWriter fileWriter = new FileWriter(file.getName(), true);
                fileWriter.write(message);
                fileWriter.close();
    
            } catch (IOException e) {
        
                e.printStackTrace();
            }
    
        }
    
    
        public static void main(String[] args) {
        
            getDataFromExcel("C:\\Users\\91845\\Desktop\\ExcelSheet1.xlsx");
        }
    
    }

模板二的代码

package org.example.demo.onetest.excletable;
    
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.*;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    import java.io.*;
    import java.util.Iterator;
    
    /**
     * @author Jack Li
     * @description 读取excel文件内容生成数据库表ddl
     * @date 2022/3/27 19:54
     */
    
    public class ExcelSheet2Utils {
        
    
    
        /**
         * 读取excel文件内容生成数据库表ddl
         *
         * @param filePath excel文件的绝对路径
         */
        public static void getDataFromExcel(String filePath) {
        
            if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
        
                System.out.println("文件不是excel类型");
            }
            InputStream fis = null;
            Workbook wookbook = null;
            try {
        
                fis = new FileInputStream(filePath);
                if (filePath.endsWith(".xls")) {
        
                    try {
        
                        //2003版本的excel，用.xls结尾
                        wookbook = new HSSFWorkbook(fis);
                    } catch (IOException e) {
        
                        e.printStackTrace();
                    }
                }
                if (filePath.endsWith(".xlsx")) {
        
                    try {
        
                        //2007版本的excel，用.xlsx结尾
                        wookbook = new XSSFWorkbook(fis);
                    } catch (IOException e) {
        
                        e.printStackTrace();
                    }
                }
                Iterator<Sheet> sheets = wookbook.sheetIterator();
                StringBuilder ddl = new StringBuilder();
                // 是否自增
                boolean autoIncrement = false;
                Sheet sheet = sheets.next();
                System.out.println("-- ------------------------当前读取的sheet页：" + sheet.getSheetName() + "--------------------------");
                // 当前读取行的行号
                int rowId = 1;
                Iterator<Row> rows = sheet.rowIterator();
                String tableEnglishName = "";
                String tableChineseName = "";
                while (rows.hasNext()) {
        
                    Row row = rows.next();
                    Cell modelRow = row.getCell(0);
                    //获取表英文名
                    if (rowId == 1) {
        
                        Cell cell1 = row.getCell(0);
                        if (!"表英文名".equals(cell1.getStringCellValue())) {
        
                            System.out.println("第一行第一格应该为“表英文名”!");
                            return;
                        }
                        Cell cell2 = row.getCell(1);
                        tableEnglishName = cell2.getStringCellValue();
                        ddl.append("CREATE TABLE " + "`" + tableEnglishName + "` (" + "\r\n");
                        rowId++;
                        continue;
                    }
                    //获取表中文名
                    if (rowId == 2) {
        
                        Cell cell1 = row.getCell(0);
                        if (!"表中文名".equals(cell1.getStringCellValue())) {
        
                            System.out.println("第2行第一格应该为“表中文名”!");
                            return;
                        }
                        Cell cell2 = row.getCell(1);
                        tableChineseName = cell2.getStringCellValue();
                        rowId++;
                        continue;
                    }
                    //校验属性列名称和顺序
                    if (rowId == 3) {
        
                        if (row.getPhysicalNumberOfCells() != 6) {
        
                            System.out.println("第2行应该只有6个单元格!");
                            return;
                        }
                        Iterator<Cell> cells = row.cellIterator();
                        StringBuilder tableField = new StringBuilder();
                        while (cells.hasNext()) {
        
                            tableField.append(cells.next().getStringCellValue().trim());
                        }
                        if (!"字段名类型是否为主键是否自增是否为空注释".equals(tableField.toString())) {
        
                            System.out.println("第3行应该为 字段名 类型  是否为主键 是否自增 是否为空 注释 !");
                            return;
                        }
                        rowId++;
                        continue;
                    }
                    //过滤掉模板备注的东西
                    if (modelRow.getStringCellValue().contains("模板备注")) {
        
                        break;
                    }
                    if (!row.cellIterator().hasNext()) {
        
                        break;
                    }
                    // 字段名
                    String fieldName = row.getCell(0).getStringCellValue();
                    if (fieldName == null | "".equals(fieldName)) {
        
                        break;
                    }
                    // 字段类型
                    String fieldType = row.getCell(1).getStringCellValue();
                    // 是否为主键
                    Cell cell4 = row.getCell(2);
                    // 是否自增
                    Cell cell5 = row.getCell(3);
                    //字段是否为空
                    //  String ifnullValue = row.getCell(5).getStringCellValue();
                    Cell cell6 = row.getCell(4);
                    // 字段注释
                    String fieldComment = row.getCell(5).getStringCellValue();
    
                    ddl.append(
                            "`" + fieldName + "` "
                                    + fieldType
                                    // + (!"0".equals(fieldLength) ? "(" + fieldLength + ")" : "")
                                    + (cell4 != null && "Y".equals(cell4.getStringCellValue()) ? " PRIMARY KEY " : "")
                                    + (cell5 != null && "Y".equals(cell5.getStringCellValue()) ? " AUTO_INCREMENT " : "")
                                    + (cell6 != null && "N".equals(cell6.getStringCellValue()) ? " NOT NULL " : " DEFAULT NULL")
                                    + " COMMENT '" + fieldComment + "'"
                                    + (rows.hasNext() ? ",\r\n" : "\r\n")
                    );
                    if (cell4 != null && "Y".equals(cell5.getStringCellValue())) {
        
                        autoIncrement = true;
                    }
    
                    rowId++;
                }
                if (ddl.toString().endsWith(",\r\n")) {
        
                    ddl = ddl.deleteCharAt(ddl.length() - 3);
    //                ddl.append("\r\n");
                }
                ddl.append(") ENGINE=InnoDB " + (autoIncrement ? "AUTO_INCREMENT=1" : "") + " DEFAULT CHARSET=utf8 "
                        + (!"".equals(tableChineseName) ? "COMMENT = '" + tableChineseName + "'" : "") + ";\r\n");
                ddl.append("-- --------------------------------------------------------------------------------\r\n");
                System.out.println(ddl.toString());
                //writeMessageToFile(ddl.toString());
                System.out.println("-- 运行成功");
            } catch (Exception e) {
        
                e.printStackTrace();
            } finally {
        
                try {
        
                    fis.close();
                } catch (IOException e) {
        
                    e.printStackTrace();
                }
            }
        }
    
        public static void writeMessageToFile(String message) {
        
            try {
        
                File file = new File("C:\\Users\\91845\\Desktop\\ddl.txt");
    
                if (!file.exists()) {
        
                    file.createNewFile();
                }
                FileWriter fileWriter = new FileWriter(file.getName(), true);
                fileWriter.write(message);
                fileWriter.close();
    
            } catch (IOException e) {
        
                e.printStackTrace();
            }
    
        }
    
    
        public static void main(String[] args) {
        
            getDataFromExcel("C:\\Users\\91845\\Desktop\\ExcelSheet2.xlsx");
        }
    
    }

[Link 1]: https://download.csdn.net/download/liu918458630/87560503?spm=1001.2014.3001.5503
[13d382580ecb488da98d42e83501f6ef.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/8e6e94dafe26400a89f07f9a9d1aadf6.png
[17792dc79a414e7ea4bc1102a0645903.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/beb77c43551b4102832f4a1e74933f59.png
[925a2ed5160e4e7688cf1af591289561.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/25/25503b1b417a42f596351367140720f7.png