【工具】将excel实体表格生成为JavaBean对象

实例

Excel实体样例

最终生成的JavaBean样例

package org.example.po;

/**
 * 按如下方式导入依赖
 */
import java.util.Date;
import lombok.Builder;
import lombok.Data;
import org.example.OwnerAnno;

/**
 * 自定义内容
 */
@Data
@Builder
public class User {
        @OwnerAnno(orgFieldName="name",name="name" ,type="DB.STRING",desc="资源名称")
        private String name;
        @OwnerAnno(orgFieldName="city",name="city" ,type="DB.STRING",desc="所属地市")
        private String city;
        @OwnerAnno(orgFieldName="start_time",name="startTime" ,type="DB.DATETIME",desc="投运日期")
        private Date startTime;
        @OwnerAnno(orgFieldName="stop_time",name="stopTime" ,type="DB.DATETIME",desc="退运日期")
        private Date stopTime;
        @OwnerAnno(orgFieldName="ctime",name="ctime" ,type="DB.DATETIME",desc="创建时间")
        private Date ctime;
        @OwnerAnno(orgFieldName="grid_code",name="gridCode" ,type="DB.STRING",desc="所属网格")
        private String gridCode;
}

 介绍

实现思路很简单，通过工具类读取文件并解析，获取到实体类的属性名称、类型、字段备注等基础信息。这里需要初步加工的操作有，对excel字段进行驼峰命名转换、对类型进行转换（excel标注的字段转成Java字段、生成实体对象存储数据库时，对应的数据库字段类型）。

如果有自定义注解，或者其他在生成实体类需求的时候要进行处理的操作，都可以在最终通过模板生成文件的时候，提前处理好。

pom.xml

<dependencies>
    <dependency>
      <groupId>org.projectlombok</groupId>
      <artifactId>lombok</artifactId>
      <version>1.18.24</version>
      <optional>true</optional>
    </dependency>
    <!-- EasyExcel是一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具 -->
    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>3.1.1</version>
    </dependency>
    <!-- freemarker -->
    <dependency>
      <groupId>org.freemarker</groupId>
      <artifactId>freemarker</artifactId>
      <version>2.3.23</version>
    </dependency>
  </dependencies>

package org.example;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

@Getter
@Setter
//@Builder
@EqualsAndHashCode
public class PropertyEntity {
    /** 属性字段名 字段命名最好不要采用下划线 需要转换成驼峰命名 */
    @ExcelIgnore
    private String propertyName;
    /** 属性字段中文描述 */
    @ExcelProperty(value = "属性说明")
    private String propertyDesc;
    /** 属性字段类型 原始 */
    @ExcelProperty(value = "数据类型")
    private String propertyType;
    /** 属性原始字段名称 */
    @ExcelProperty(value = "属性名称")
    private String orgFieldName;
    /** 属性字段类型 图数据库 */
    @ExcelIgnore
    private String propertyDbType;
    /** 属性字段类型 Java */
    @ExcelIgnore
    private String propertyJavaType;
}

package org.example;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import freemarker.template.Configuration;
import freemarker.template.Template;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ParseModelToJavaBean {

    private static final String CLASS_PATH = "src/main/java/org/example/po";
    private static Map<String,String> toJavaType ;
    // 对应数据库类型
    private static Map<String,String> toDbType;
    static{
        //初始化类型转换
        toJavaType = new HashMap<>();
        toJavaType.put("STRING","String");
        toJavaType.put("DATETIME","Date");


        toDbType = new HashMap<>();
        // 自行定义类型转换
        toDbType.put("STRING","DB.STRING");
        toDbType.put("DATETIME","DB.DATETIME");
    }

    public static void main(String[] args) {
        Configuration configuration = new Configuration();
        Writer out = null;
        try {
            configuration.setDirectoryForTemplateLoading(new File(new File("").getAbsolutePath()+File.separator+"file"));
            Map<String, Object> dataMap = new HashMap<String, Object>();
            dataMap.put("classPath", "org.example.po");
            dataMap.put("className", "User");
            List<PropertyEntity> properties  = parseExcel("test.xlsx");
            transportPropertyType(properties);
            dataMap.put("properties", properties);
            Template template = configuration.getTemplate("test.ftl");
            File docFile = new File(CLASS_PATH + "\\" + "User.java");
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(docFile)));
            template.process(dataMap, out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (null != out) {
                    out.flush();
                }
            } catch (Exception e2) {
                e2.printStackTrace();
            }
        }
    }
    public static List<PropertyEntity> parseExcel(String entityFileName){
        List<PropertyEntity> propertyEntities = new ArrayList<>();
        String fileName = new File("").getAbsolutePath()+File.separator+"file"+File.separator+"entity"+File.separator+entityFileName;
        System.out.println(fileName);
        EasyExcel.read(fileName, PropertyEntity.class, new PageReadListener<PropertyEntity>(dataList -> {
            for (PropertyEntity entity : dataList) {
                propertyEntities.add(entity);
            }
        })).sheet().doRead();
        return propertyEntities;
    }

    /**
     * 转换属性
     * @param properties
     */
    public static void transportPropertyType(List<PropertyEntity> properties){
        properties.forEach(propertyEntity -> {
            propertyEntity.setPropertyJavaType(toJavaType.get(propertyEntity.getPropertyType()));
            propertyEntity.setPropertyDbType(toDbType.get(propertyEntity.getPropertyType()));
            String fieldName = propertyEntity.getOrgFieldName();
            propertyEntity.setPropertyName(toHump(fieldName));

        });
    }
    public static String toHump(String fieldName){
        // 驼峰命名
        String[] keyArray = fieldName.toLowerCase().split("_");
        if (keyArray.length > 1) {
            StringBuilder sb = new StringBuilder();
            for (int i = 0; i < keyArray.length; i++) {
                String ks = keyArray[i];
                if (!"".equals(ks)) {
                    if (i == 0) {
                        sb.append(ks);
                    } else {
                        int c = ks.charAt(0);
                        if (c >= 97 && c <= 122) {
                            int v = c - 32;
                            sb.append((char) v);
                            if (ks.length() > 1) {
                                sb.append(ks.substring(1));
                            }
                        } else {
                            sb.append(ks);
                        }
                    }
                }
            }
            return sb.toString();
        }
        return fieldName;
    }
}

 

参考代码下载地址：

(57条消息) 将excel实体表格生成为JavaBean对象-Java文档类资源-CSDN文库