Java poi 用excel生成sql文件建表

效果展示

在编写文档的时候想着直接把文档变成表结构，可以减少工作量，于是写了这个工具类，excel结构如下

字段属性： 0为必填，1为主键
字段类型： varchar默认255，innodb引擎varchar变长存储，255基本够用了；float默认float(17,6)

生成的sql效果

代码

package com.wtbl.iot.util;

import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.poi.excel.WorkbookUtil;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.util.*;

/**
 * excel 转换成 sql工具类
 * @author wuhao
 * @date 2023-03-08
 */
public class GenerateSqlUtil {

    @Data
    public static class ExcelEntity {
        /**
         * 字段名
         */
        private String filedName;
        /**
         * 字段类型
         */
        private String filedType;
        /**
         * 字段注释
         */
        private String notes;
        /**
         * 默认值
         */
        private String defaultValue;
        /**
         * 字段属性
         */
        private String attribute;
        /**
         * 字段索引
         */
        private String index;
    }


    public static void main(String[] args) throws IOException {
        generateSql();
    }

    /**
     * 生成sql文件
     */
    private static void generateSql() throws IOException{
        String filePath = "F:/excel.xlsx";
        String  outputPath = "F:/sql.sql";
        List<Map<String, Object>> result = readExcel(filePath);
        System.out.println("================================开始生成SQL，共"+result.size()+"张表================================");
        StringBuilder sb = new StringBuilder();
        StringBuilder indexSb = null;
        for (Map<String, Object> map : result) {
            JSONObject json = new JSONObject(true);// 有序
            List<ExcelEntity> list = (List<ExcelEntity>) map.get("list");
            String tableName = (String) map.get("tableName"); //表名
            String tableNotes = (String) map.get("tableNotes"); //注释
            sb.append(String.format("-- %s：%s\n", tableName, tableNotes));
            sb.append(String.format("DROP TABLE IF EXISTS %s;\n", tableName));
            sb.append(String.format("CREATE TABLE %s (\n", tableName));
            int size = list.size();
            indexSb = new StringBuilder();
            for (int i = 0; i < size; i++) {
                ExcelEntity entity = list.get(i);
                if(entity.getFiledName() == null){
                    char c = sb.charAt(sb.length() - 2);
                    if(c == ','){
                        sb.deleteCharAt(sb.length() - 2);
                    }
                    continue;
                }
                sb.append(String.format("\t`%s` %s", entity.getFiledName(), entity.getFiledType()));
                String type = entity.getFiledType();
                String attribute = entity.getAttribute();
                attribute = attribute == null ? "" : attribute;
                if (attribute.contains("1")){
                    sb.append(" PRIMARY KEY AUTO_INCREMENT"); //设置主键
                }else if (type.contains("char") || type.contains("text")){
                    sb.append(" CHARACTER SET utf8 COLLATE utf8_general_ci"); //字符串、文本设置编码
                }
                if (attribute.contains("0")){
                    sb.append(" NOT NULL");
                }else{
                    sb.append(" NULL");
                }
                // 设置默认值
                if (StrUtil.isNotBlank(entity.getDefaultValue())) {
                    sb.append(" DEFAULT ");
                    if (type.contains("char") || type.contains("text")){
                        sb.append(String.format("'%s'", entity.getDefaultValue()));
                    }else{
                        sb.append(entity.getDefaultValue());
                    }
                }
                sb.append(String.format(" COMMENT  '%s'", entity.getNotes()));//设置注释
                if (StrUtil.isNotBlank(entity.getIndex())) { //设置索引
                    //INDEX `index_name`(`field1`, `field2`) USING BTREE
                    indexSb.append("\tINDEX `");
                    String[] index = entity.getIndex().split(":");
                    indexSb.append(index[0]);
                    indexSb.append("`(`");
                    indexSb.append(index[1]);
                    indexSb.append("`)");
                    indexSb.append(" USING BTREE,\n");
                }
                if (i < size - 1 || indexSb.length() > 0) sb.append(",");
                sb.append("\n");
                json.putOpt(entity.getFiledName(),entity.getNotes());
            }
            if (indexSb.length() > 0) {
                sb.append(indexSb.substring(0, indexSb.lastIndexOf(",")));
                sb.append("\n");
            }
            sb.append(String.format(") ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '%s' ROW_FORMAT = Dynamic;", tableNotes));
            sb.append("\n\n");
            System.out.println(tableNotes+"："+json.toString());
        }
        File file = new File(outputPath);
        FileOutputStream fos1 = new FileOutputStream(file);
        OutputStreamWriter dos1 = new OutputStreamWriter(fos1);
        dos1.write(sb.toString());
        dos1.close();
        System.out.println("=========================SQL生成完成=========================");
        System.exit(0);
    }

    /**
     * 根据文件路径解析文件
     * @param filePath   文件路径
     */
    private static List<Map<String, Object>> readExcel(String filePath) {
        List<Map<String, Object>> result = new ArrayList<>();
        File file = new File(filePath);
        Workbook book = WorkbookUtil.createBook(file);
        int sheetNum = book.getNumberOfSheets();
        for (int i = 0; i < sheetNum; i++) {
            Map<String, Object> stringObjectMap = readExcel(book.getSheetAt(i));
            if(stringObjectMap.size() > 0){
                result.add(stringObjectMap);
            }
        }
        return result;
    }

    /**
     * 读取指定sheet的数据，获取表名，字段名、字段类型、字段注释
     */
    private static Map<String, Object> readExcel(Sheet sheet) {
        Map<String, Object> map = new HashMap<>();
        List<ExcelEntity> list = new ArrayList<>();
        //第一行是标题，默认是注释:表名
        Row titleRow = sheet.getRow(0);
        if(titleRow != null){
            String title = titleRow.getCell(0).toString();
            map.put("tableName", title.split(":")[1]);
            map.put("tableNotes", title.split(":")[0]);
            //第二行是表头，不读取。第三行开始才是数据
            int lastRowNum = sheet.getLastRowNum();
            for (int i = 2; i <= lastRowNum; i++) {
                list.add(getEntity(sheet.getRow(i)));
            }
            map.put("list", list);
        }
        return map;
    }

    private static ExcelEntity getEntity(Row row) {
        ExcelEntity entity = new ExcelEntity();
        //列数：5列，分别是字段名、字段类型、字段注释、字段属性、字段索引
        for (int j = 0; j < 5; j++) {
            Cell cell = row.getCell(j);
            if(cell == null){
                continue;
            }
            cell.setCellType(CellType.STRING);
            if (!Objects.isNull(cell)) {
                String str = cell.toString();
                if (StrUtil.isNotBlank(str)) {
                    switch (j) {
                        case 0: entity.setFiledName(str); break;
                        case 1:
                            if (str.contains("char")) {
                                str = str + "(255)";
                            }
                            if(str.equals("float")){
                                str = "float(17,6)";
                            }
                            entity.setFiledType(str);
                            break;
                        case 2: entity.setNotes(str); break;
                        case 3: entity.setAttribute(str); break;
                        case 4: entity.setDefaultValue(str); break;
                        case 5: entity.setIndex(str); break;
                    }
                }
            }
        }
        return entity;
    }


}