通用型导出Excel（采用反射+注解开发）

通用型导出Excel（采用反射+注解开发）

注解

@Retention(RetentionPolicy.RUNTIME)
public @interface CustomTag {
    //表示字段描述注解
    String desc();
}

@Retention(RetentionPolicy.RUNTIME)
public @interface CustomTagClass {
    String name();
}

导出报表实体类

@Data
@Accessors(chain = true)
@CustomTagClass(name = "报表数据")
public class ReportFormDto {

    @CustomTag(desc = "查询开始时间")
    private String startTime;
    @CustomTag(desc = "查询截止时间")
    private String endTime;
    @CustomTag(desc = "租户")
    private String tenantName;
    @CustomTag(desc="项目")
    private String projectName;
    @CustomTag(desc="集群")
    private String clusterAliasName;
    @CustomTag(desc="分区")
    private String namespace;
    @CustomTag(desc="节点资源池")
    private String nodePoolAliasName;
    //表示部署创建的时间 YYYY-MM-DDTHH:MM:SSZ
    @CustomTag(desc="服务创建日期")
    private String deployCreateDate;
    @CustomTag(desc="服务")
    private String deployName;
    //CPU 分配量，使用量的单位是核
    @CustomTag(desc="CPU请求量(单位M)")
    private Double cpuRequest;
    @CustomTag(desc="CPU使用量(单位M)")
    private Double cpuUsage;
    //CPU 使用率
    @CustomTag(desc="CPU使用率")
    private Double cpuRate;
    //内存 分配量，使用量的单位是M
    @CustomTag(desc="内存请求量(单位M)")
    private Double memoryRequest;
    @CustomTag(desc="内存使用量(单位M)")
    private Double memoryUsage;
    //内存 使用率
    @CustomTag(desc="内存使用率")
    private Double memoryRate;

    //存储使用量 key表示存储服务类型，value表示存储分配量，单位是M
    @CustomTag(desc="存储卷申明请求量(单位M)")
    private String storageRequest;
    //存储使用量 key表示存储服务类型，value表示存储使用量，单位是M
    @CustomTag(desc="存储卷申明使用量(单位M)")
    private String storageUsage;
    //存储使用量 key表示存储服务类型，value表示存储分配率
    @CustomTag(desc="存储卷申明使用率")
    private String storageRate;
}

报表导出工具类ExportExcelUtil

/**
 * @author lyy
 * @description: TODO
 * @date 2021/12/1 11:43 上午
 */
public class ExportExcel {
    private static Logger logger = LoggerFactory.getLogger(ExportExcel.class);

    /**
     * 数据转换
     *
     * @param list       数据
     * @param fileName   文件地址
     * @param type       是否需要排序：1需要排序，2不需要排序
     * @param headersNew 排序字段
     */
    public static void exportExcelUtil(List<?> list, String fileName, String type, String[] headersNew, Object obj, HttpServletRequest request, HttpServletResponse response) {
        try {
            //List<Model>转List<Map>
            List<Map> dataMapList = new ArrayList<>();

            //获取排序数组下标
            Map<String, String> mapPx = new HashMap<>();
            for (int i = 0; i < headersNew.length; i++) {
                mapPx.put(headersNew[i], i + "");
            }

            for (int i = 0; i < list.size(); i++) {
                if (type.equals("1")) {
                    Map map = transBean2Map(list.get(i));
                    Map mapNew = new TreeMap();
                    for (Object key : map.keySet()) {
                        mapNew.put(Integer.parseInt(mapPx.get(key.toString())), map.get(key));
                    }
                    dataMapList.add(mapNew);
                } else {
                    //按照key记录值
                    dataMapList.add(transBean2Map(list.get(i)));
                }
            }

            //获取表头
            Map<String, String> map = ClassUtil.getAllDesc(obj);

            //设置excel表头
            String[] headers = null;

            //从实体类中取字段
            Object[] keys = map.keySet().toArray();
            String[] headersZd = new String[keys.length];
            for (int i = 0; i < keys.length; i++) {
                headersZd[i] = keys[i].toString();
            }
            if (type.equals("1")) {
                headers = headersNew;
            } else {
                headers = headersZd;
            }

            String[] headersTitle = null;

            //从实体类中取字段注释
            Object[] keysTitleDescript = new Object[headersNew.length];
            for(int i = 0 ;i<headersNew.length;i++){
                keysTitleDescript[i] = map.get(headersNew[i]);
            }
            String[] headersTitleDescript = new String[keysTitleDescript.length];
            for (int i = 0; i < keysTitleDescript.length; i++) {
                headersTitleDescript[i] = keysTitleDescript[i].toString();
            }

            if (type.equals("1")) {
                headersTitle = headersTitleDescript;
            } else {
                Map<String, String> mapZs = new HashMap<>();
                for (int i = 0; i < headersZd.length; i++) {
                    mapZs.put(headersZd[i], headersTitleDescript[i]);
                }
                headersTitle = new String[headersTitleDescript.length];
                for (int i = 0; i < headers.length; i++) {
                    headersTitle[i] = mapZs.get(headers[i]);
                }
            }

            //获取类上的注解值
            String title = ClassUtil.getClassName(obj.getClass());
            if (StringUtils.isBlank(title)) {
                title = "Sheet1";
            }

            exportExcel(fileName, title, headers, headersTitle, dataMapList, "2", request, response);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String[] setExcelHeaders(String clazz) {
        //自定义excel表格表头展示字段顺序
        Map<String, String> map = ClassUtil.getAllDesc(clazz);
        //从实体类中取字段
        Object[] keys = map.keySet().toArray();
        String[] headers = new String[keys.length];
        StringBuffer str = new StringBuffer("{");
        for (int i = 0; i < keys.length; i++) {
            headers[i] = keys[i].toString();
            str.append("\"" + keys[i].toString() + "\"");
            if (i < (keys.length - 1)) {
                str.append(",");
            }
        }
        str.append("}");
        System.out.println(str.toString());
        return headers;
    }

    /**
     * 把内容写入excel表格中
     *
     * @param fileName     文件地址
     * @param title        excel表格工作空间名称
     * @param headers      表格列字段
     * @param headersTitle 表格表头中文字段
     * @param dataset      数据
     * @param type         表格表头展示方式：1实体类字段名，2实体类字段名注释
     * @return
     */
    public static void exportExcel(String fileName, String title, String[] headers, String[] headersTitle, List<Map> dataset, String type, HttpServletRequest request, HttpServletResponse response) throws IOException {
        Workbook workbook = null;
        if (fileName.endsWith("xlsx")) {
            workbook = new XSSFWorkbook();
        } else if (fileName.endsWith("xls")) {
            workbook = new HSSFWorkbook();
        } else {
            try {
                throw new Exception("invalid file name, should be xls or xlsx");
            } catch (Exception e) {
                logger.info("必须是xls或者xlsx结尾的文件.");
                e.printStackTrace();
            }

        }

        Sheet sheet = workbook.createSheet(title);
        CellStyle style = workbook.createCellStyle();

        // 列名
        Row row = sheet.createRow(0);
        switch (type) {
            case "1":
                for (int i = 0; i < headers.length; i++) {
                    Cell cell = row.createCell(i);
                    sheet.setColumnWidth(i, 5000);
                    style.setAlignment(CellStyle.ALIGN_CENTER);
                    cell.setCellValue(headers[i]);
                }
                break;
            case "2":
                for (int i = 0; i < headersTitle.length; i++) {
                    Cell cell = row.createCell(i);
                    sheet.setColumnWidth(i, 5000);
                    style.setAlignment(CellStyle.ALIGN_CENTER);
                    cell.setCellValue(headersTitle[i]);
                }
                break;
            default:
                break;
        }


        Iterator<Map> it = dataset.iterator();
        int index = 0;
        while (it.hasNext()) {
            index++;
            row = sheet.createRow(index);

            Map map = it.next();
            Map testMap = new TreeMap<>(map);
            Set<Integer> mapKey = (Set<Integer>) testMap.keySet();
            Iterator<Integer> iterator = mapKey.iterator();
            int num = 0;
            while (iterator.hasNext()) {
                Cell cell = row.createCell(num);
                num++;
                Integer key = iterator.next();
                Object obj = testMap.get(key);
                if (obj instanceof Integer) {
                    cell.setCellValue((Integer) obj);
                } else if (obj instanceof Double) {
                    cell.setCellValue((Double) obj);
                } else if (obj instanceof Map) {
                    cell.setCellValue(obj.toString());
                } else {
                    cell.setCellValue((String) obj);
                }
            }
        }

        response.reset();
        response.setContentType("application/octet-stream");

        response.addHeader("Content-disposition", "attachment;filename=" + new String((title + ".xlsx").getBytes("UTF-8"), "ISO-8859-1"));
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Access-Control-Allow-Origin", request.getHeader("Origin"));
        workbook.write(response.getOutputStream());
        response.getOutputStream().flush();
        response.getOutputStream().close();
    }

    /**
     * 实体类转map
     *
     * @param obj
     * @return
     */
    public static Map<String, Object> transBean2Map(Object obj) {
        if (obj == null) {
            return null;
        }
        Map<String, Object> map = new HashMap<>();
        try {
            BeanInfo beanInfo = Introspector.getBeanInfo(obj.getClass());
            PropertyDescriptor[] propertyDescriptors = beanInfo.getPropertyDescriptors();
            for (PropertyDescriptor property : propertyDescriptors) {
                String key = property.getName();
                // 过滤class属性
                if (!key.equals("class")) {
                    // 得到property对应的getter方法
                    Method getter = property.getReadMethod();
                    Object value = getter.invoke(obj);

                    map.put(key, value);
                }

            }
        } catch (Exception e) {
            logger.error("transBean2Map Error {}", e);
        }
        return map;

    }

}

通用型类的反射工具类ClassUtil

package cn.harmonycloud.k8s.oam.biz.util.excel;

import cn.harmonycloud.k8s.oam.dal.annotation.CustomTag;
import cn.harmonycloud.k8s.oam.dal.annotation.CustomTagClass;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.annotation.Annotation;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.HashMap;
import java.util.Map;

/**
 * @author lyy
 * @description: TODO
 * @date 2021/12/1 1:50 下午
 */
public class ClassUtil {

    private static Logger logger = LoggerFactory.getLogger(ClassUtil.class);
    /**
     * 通过属性取得属性的描述注解
     *
     * @param field
     * @return
     */
    public static String getDesc(Field field) {
        String result = null;
        try {
            field.setAccessible(true);
            Annotation[] annotation = field.getAnnotations();
            for (Annotation tag : annotation) {
                if (tag instanceof CustomTag) {
                    result = ((CustomTag) tag).desc();
                    break;
                }
            }
        } catch (SecurityException e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return result;
        // return getAnnotation(DESC, field);
        // return getAnnotation(field);
    }

    /**
     * 通过对象和属性名称取得属性的描述注解
     *
     * @param obj
     * @param propertyName
     * @return
     */
    public static String getDesc(Object obj, String propertyName) {
        String result = null;
        try {
            Field[] fields = obj.getClass().getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                if (field.getName().equals(propertyName)) {
                    String desc = getDesc(field);
                    if (desc != null && !desc.isEmpty()) {
                        result = desc;
                        break;
                    }
                }
            }
        } catch (SecurityException e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return result;
    }



    /**
     * 取得obj所有属性的描述注解，返回值为key为obj的属性名称,value为此属性的描述注解
     *
     * @param obj
     * @return
     */
    public static Map<String, String> getAllDesc(Object obj) {
        try {
            Field[] fields = obj.getClass().getDeclaredFields();
            return getResult(fields);
        } catch (SecurityException e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 取得obj所有属性的描述注解，返回值为key为obj的属性名称,value为此属性的描述注解
     *
     * @param obj
     * @return
     */
    public static Map<String, String> getAllDesc(String clzName) {
        try {
            Field[] fields = Class.forName(clzName).getDeclaredFields();
            return getResult(fields);
        } catch (SecurityException e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage());
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 将field[]里的字段名称做为key和字段描述做value放在map中
     *
     * @param fields
     * @param map
     */
    private static Map<String, String> getResult(Field[] fields) {
        Map<String, String> result = new HashMap<String, String>();
        for (Field field : fields) {
            field.setAccessible(true);
            if (field.getName().equals("id")) {
                continue;
            }
            String desc = getDesc(field);
            if (desc != null && !desc.isEmpty()) {
                result.put(field.getName(), getDesc(field));
            }
        }
        return result;
    }
    public static String getClassName(Class<?> class1){
        //获取类上的注解值
        CustomTagClass anno = class1.getAnnotation(CustomTagClass.class);
        if(anno != null){
            Method[] met = anno.annotationType().getDeclaredMethods();
            for(Method me : met ){
                if(!me.isAccessible()){
                    me.setAccessible(true);
                }
                try {
                    return (String) me.invoke(anno, null);
                } catch (IllegalAccessException | IllegalArgumentException | InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
        }
        return "";
    }
}

设置报表导出的headers

public String[] setExcelHeaders(String clazz) {
        //自定义excel表格表头展示字段顺序
        Map<String, String> map = ClassUtil.getAllDesc(clazz);
        //从实体类中取字段
        Object[] keys = map.keySet().toArray();
        String[] headers = new String[keys.length];
        StringBuffer str = new StringBuffer("{");
        for (int i = 0; i < keys.length; i++) {
            headers[i] = keys[i].toString();
            str.append("\"" + keys[i].toString() + "\"");
            if (i < (keys.length - 1)) {
                str.append(",");
            }
        }
        str.append("}");
        System.out.println(str.toString());
        return headers;
    }

报表导出使用的地方

		List<ReportForm> reportForms = monitorCenterService.getReportForm(subNamespacesList, queryMap);
        List<ReportFormDto> reportFormDtoList = new ArrayList<>();
        convertToDto(reportForms,reportFormDtoList);
        if (Objects.nonNull(reportForms) && reportForms.size() > 0) {
            ExportExcel.exportExcelUtil(reportFormDtoList, reportFormFileName,"2", setExcelHeaders("cn.harmonycloud.k8s.oam.biz.model.monitor.ReportFormDto"),new ReportFormDto(),httpRequest,httpResponse);
            return ActionReturnUtil.returnSuccessWithData(reportForms);
        } else {
            return ActionReturnUtil.returnError();
        }

本次开发借鉴参考了这篇博客