根据实体类生成数据库表结构设计excel的util

因为公司项目中都需要给甲方提供数据表结构设计，再结合公司所用技术与框架，特写此工具类方便以后使用，希望能帮助到大家。

所用技术

1. java反射；
2. 阿里的easypoi；
3. lombok；
4. 注解；

实体类

@Data
@Entity
@Table(name = "sys_user")
@ApiModel(description = "用户信息")
public class UserInfo extends DateEntity<UserInfo> {

    private static final long serialVersionUID = -1876413578654013212L;

    @ApiModelProperty("用户Id")
    @Id
    @GenericGenerator(name = "system-uuid", strategy = "uuid")
    @GeneratedValue(generator = "system-uuid")
    @Column( length = 32)
    private String userId;//用户Id

    @ApiModelProperty("手机号")
    @Column(length = 11, unique = true, nullable = false)
    private String phone;//手机号

    @ApiModelProperty("用户名称")
    @QueryField(condition = SqlExpression.LIKE)
    @Column(length = 128, nullable = false)
    private String name;//用户名称

    @ApiModelProperty("账号")
    @Column(length = 20, unique = true, nullable = false)
    private String account;
    
    @JsonIgnore
    @Column(length = 64, nullable = false)
    private String password;//密码

    @JsonIgnore
    @Column(length = 32, nullable = false)
    private String salt;//盐
    
    @ApiModelProperty("职务")
    @Column(length = 32)
    private String position;
    
    @ApiModelProperty("性别:1男;0女")
    @Column()
    private Integer sex;

    @ApiModelProperty("民族")
    @Column(length = 30)
    private String ethnic;

    @ApiModelProperty("身份证号")
    @Column(length = 18)
    private String idNumber;

    @ApiModelProperty("出生日期")
    @JsonFormat(pattern = "yyyy-MM-dd")
    @Column
    private Date dateOfBirth;

}

在很多实体类中都会继承一些基类，根据情况来。

生成excel的util

public class EntityExcelBuildUtils {
    public static void main(String[] args) {
        Class<?> classType = UserInfo.class; //实体对象
        build(classType);
    }
    public static void build(Class<?> clazz) {
        Entity entity = clazz.getAnnotation(Entity.class);
        Table table = clazz.getAnnotation(Table.class);
        if (entity == null) {
            return;
        }
        List fieldsList = new ArrayList<Field>();
        while (clazz != null) {  // 遍历所有父类字节码对象
            Field[] declaredFields = clazz.getDeclaredFields(); //这个方法只能获取到子类的属性
            fieldsList.addAll(Arrays.asList(declaredFields));  //将`Filed[]`数组转换为`List<>`然后再将其拼接至`ArrayList`上
            clazz = clazz.getSuperclass();  // 获得父类的字节码对象
        }
        ArrayList<Map<String, Object>> list = new ArrayList<>();
        for (Object object : fieldsList) {
            Field field = (Field) object;
            Column column = field.getAnnotation(Column.class);
            if (column==null){
                continue;
            }
            Map<String, Object> map = new HashMap<>();
            String name = underscoreName(field.getName());
            map.put("name",name);//列名
            String type = "VARCHAR";
            String fileType = field.getType().toString();
            //根据不同类型取对应数据库中的数据类型
            if ("class java.lang.String".equals(fileType)){
                type="VARCHAR";
            }else if ("class java.lang.Integer".equals(fileType)){
                type="INTEGER";
            }else if ("class java.util.Date".equals(fileType)){
                type="DATETIME";
            }
            else if ("class java.lang.Double".equals(fileType)){
                type="DOUBLE";
            }
            map.put("type",type);//类型
            map.put("size",column.length());//长度
            String isNullStr="是";
            if (!column.nullable()){
                isNullStr="否";
            }
            map.put("isNull",isNullStr);//是否为空
            ApiModelProperty apiModelProperty = field.getAnnotation(ApiModelProperty.class);
            if (apiModelProperty!=null){
                map.put("description",apiModelProperty.value());//说明
            }
            list.add(map);
        }
        exportExcel(table.name(),list);

    }

    /**
     *
     * @param entityName 表名
     * @param list 表字段信息集合
     */
    public static void exportExcel(String entityName,List<Map<String, Object>> list) {
        try {
            List<ExcelExportEntity> entity = new ArrayList<ExcelExportEntity>();

            entity.add(new ExcelExportEntity("列名", "name"));
            entity.add(new ExcelExportEntity("类型", "type"));
            entity.add(new ExcelExportEntity("长度", "size"));
            entity.add(new ExcelExportEntity("是否为空", "isNull"));
            entity.add(new ExcelExportEntity("说明", "description"));
            Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(entityName,entityName), entity,
                    list);
            //导出文件
            FileOutputStream fos = new FileOutputStream("D:/excel/"+entityName+".xls");
            workbook.write(fos);
            fos.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 驼峰字段转换为大写下划线字段名称
     *
     * @param camelCaseName
     * @return
     */
    public static String underscoreName(String camelCaseName) {
        StringBuilder result = new StringBuilder();
        if (camelCaseName != null && camelCaseName.length() > 0) {
            result.append(camelCaseName.substring(0, 1).toLowerCase());
            for (int i = 1; i < camelCaseName.length(); i++) {
                char ch = camelCaseName.charAt(i);
                if (Character.isUpperCase(ch)) {
                    result.append("_");
                    result.append(Character.toLowerCase(ch));
                } else {
                    result.append(ch);
                }
            }
        }
        return result.toString().toUpperCase();
    }

}

生成的excel