Mysql根据Excel表格生成DDL

package com.yieldchain.reg;

import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import org.junit.Test;
import org.springframework.util.ObjectUtils;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class MysqlDDLUtilTest {
    private static final String fileName = "template/PeriodicReportQuarter.xlsx";
    @Test
    public void test(){
        ExcelReader reader = ExcelUtil.getReader(fileName);
        StringBuilder sql = new StringBuilder();
        //以下生成建表Sql
        sql.append("create table ").append("reg_" + "periodic_report_quarter").append("(");
        sql.append("\n");
        sql.append("    `id` bigint NOT NULL COMMENT 'id',");
        sql.append("\n");
        Set<String> fields = new HashSet<String>();
        List<String> longColumn = new ArrayList<>();

        List<List<Object>> readAll = reader.read();
        for(List<Object> list : readAll)    {
            String columnDesc = (String) list.get(0);
            String column = (String) list.get(1);
            if(ObjectUtils.isEmpty(column)){
                continue;
            }
            if(fields.contains(column.trim())){
                continue;
            }
            String length = String.valueOf(list.get(2));
            if(ObjectUtils.isEmpty(length)){
                continue;
            }
            String desc = String.valueOf(list.get(3));

            //处理超长字段，处理方式截取，首先截取后60个字符，如果有重复，则截取前60个字符，再重复则报错
            if(column.length() > 64){
                String columnTem = column.substring(column.length() - 60);
                if(longColumn.contains(columnTem)){
                    columnTem = column.substring(0, 59);
                }
                if(longColumn.contains(columnTem)){
                    throw new IllegalArgumentException(column + "is not a valid");
                }
                column= columnTem;
                longColumn.add(columnTem);
            }

            sql.append("    ").append(column).append(" ");
            if(length.contains(",") || length.contains("，")){
                length = length.replace("，",",");
                sql.append("decimal("+length+") NOT NULL COMMENT '" +columnDesc + "'");
            }else if(desc.contains("整数")){
                if("20".equals(length)){
                    sql.append("bigint NOT NULL COMMENT '"+columnDesc+"'");
                }else{
                    sql.append("int NOT NULL COMMENT '"+columnDesc+"'");
                }

            }
            else{
                if(desc.contains("必填") && !desc.contains("时必填")){
                    sql.append("VARCHAR("+length+") NOT NULL COMMENT '" + columnDesc + "'");
                }else {
                    sql.append("VARCHAR("+length+") DEFAULT NULL COMMENT '" + columnDesc + "'");
                }
            }
            sql.append(",");
            sql.append("\n");
            fields.add(column);


        }
        sql.append("    `create_time` datetime NULL DEFAULT NULL,");
        sql.append("\n");
        sql.append("    `create_user` bigint NULL DEFAULT NULL,");
        sql.append("\n");
        sql.append("    `update_time` datetime NULL DEFAULT NULL,");
        sql.append("\n");
        sql.append("    `update_user` bigint NULL DEFAULT NULL,");
        sql.append("\n");
        sql.append("    PRIMARY KEY (`id`) USING BTREE");
        //sql.delete(sql.lastIndexOf(","), sql.length());
        sql.append("\n)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
        System.out.println(sql);


    }

}