springboot用poi以注解方式导出excel

第一步导入poi的依赖:

 <dependency>
     <groupId>org.apache.poi</groupId>
        <artifactId>poi</artifactId>
        <version>4.0.0</version>
    </dependency>

    <dependency>
        <groupId>org.apache.poi</groupId>
        <artifactId>poi-ooxml</artifactId>
        <version>4.0.0</version>
  </dependency>

第二步新建注解类:

package com.tianli.config.annotation.export;
import java.lang.annotation.*;

/**
 * @author lzy
 * @Date: 2020-04-10
 * @Time: 14:07
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Documented
public @interface ExportAnnotation {

    String value() default "";

}

这里的value就是excel的第一行 手机号 姓名之类

第二步建立一个TypeHandler注解:

package com.tianli.config.annotation.export;

import java.lang.annotation.*;

/**
 * @author lzy
 * @Date: 2020-04-14
 * @Time: 10:54
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
@Documented
public @interface ExportTypeAnnotation {

    Class<? extends ExportTypeHandler> typeHandler();
}

这个注解的作用主要是解决一些类型的问题,比如订单状态用1,2,3表示,导出的时候肯定不希望表格这样显示 所以这里借鉴了mybatis设计TypeHandler的思路,需要类型转换的属性需要实现ExportTypeHandler 接口

package com.tianli.config.annotation.export;

/**
 * @author lzy
 * @Date: 2020-04-14
 * @Time: 10:52
 */
public interface ExportTypeHandler {

    /**
     * 根据传入的type获取其对应的类型
     * @param type
     * @return
     * @throws Exception 转换类型错误 或者 找不到type对应的desc
     */
    String getValue(Object type) throws Exception;
}

以导出VipRechargeRecordVo为例:

package com.tianli.domain.vo;

import com.tianli.config.annotation.export.*;
import lombok.Data;

import java.math.BigDecimal;
import java.time.LocalDateTime;

/**
 * @author lzy
 * @Date: 2020-03-25
 * @Time: 18:17
 */
@Data
public class VipRechargeRecordVo {

    private Integer id;

    @ExportAnnotation("手机号")
    private String mobile;

    @ExportAnnotation("姓名")
    private String name;

    @ExportAnnotation("订单类型")
    @ExportTypeAnnotation(typeHandler = ProductTypeExportTypeHandler.class)
    private Integer productType;

    @ExportAnnotation("充值时长")
    @ExportTypeAnnotation(typeHandler = ProductHeavenTypeHandler.class)
    private Integer rechargeTime;

    @ExportAnnotation("充值金额")
    private BigDecimal amount;

    @ExportTypeAnnotation(typeHandler = PayStatusExportTypeHandler.class)
    @ExportAnnotation("状态")
    private Integer payStatus;

    @ExportAnnotation("交易时间")
    @ExportTypeAnnotation(typeHandler = ExportLocalDateTimeTypeHandler.class)
    private LocalDateTime addTime;
}

这里用到了4个typeHandler 我这边贴一个为例其他的实现都一样:

package com.tianli.config.annotation.export;

import com.alibaba.fastjson.util.TypeUtils;
import com.tianli.domain.enums.ProductHeavenEnum;

import java.util.Objects;

/**
 * @author lzy
 * @Date: 2020-04-14
 * @Time: 14:14
 */
public class ProductHeavenTypeHandler implements ExportTypeHandler {
    @Override
    public String getValue(Object type) throws Exception {
        if (Objects.isNull(type)) {
            return "";
        }
        return ProductHeavenEnum.findByHeaven(TypeUtils.castToInt(type)).getDesc();
    }
}

package com.tianli.domain.enums;

import lombok.Getter;


/**
 * @author lzy
 * @Date: 2020-04-14
 * @Time: 11:30
 */
@Getter
public enum ProductHeavenEnum {

    MONTH_CARD(30, "月卡"),

    SEASON_CARD(90, "季卡"),

    YEAR_CARD(365, "年卡");


    private Integer heaven;

    private String desc;

    ProductHeavenEnum(Integer heaven, String desc) {
        this.heaven = heaven;
        this.desc = desc;
    }

    public static ProductHeavenEnum findByHeaven(Integer heaven) {
        for (ProductHeavenEnum value : ProductHeavenEnum.values()) {
            if (heaven.equals(value.getHeaven())) {
                return value;
            }
        }
        throw ErrorCodeEnum.ARGUEMENT_ERROR.generalException();
    }
}

下面就是实现根据注解导出的代码:

package com.tianli.util;

import com.tianli.config.annotation.export.ExportAnnotation;
import com.tianli.config.annotation.export.ExportTypeAnnotation;
import com.tianli.config.annotation.export.ExportTypeHandler;
import com.tianli.config.exception.ErrCodeException;
import com.tianli.util.tool.excel.ExcelTool;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.util.*;

/**
 * @author tt
 * @Date: 2020-04-10
 * @Time: 14:53
 */
@Slf4j
public class ExportUtil {


    private static final Map<Class<? extends ExportTypeHandler>, ExportTypeHandler> TYPE_HANDLER_MAP = new HashMap<>();


    public static void export(List<?> list, HttpServletResponse httpServletResponse) {
        if (null == list || list.isEmpty()) {
            throw new ErrCodeException("没有可以导出的数据!");
        }
        List<Map<String, Object>> data = new ArrayList<>();
        for (Object o : list) {
            Map<String, Object> linkedMap = new LinkedHashMap<>();
            //获取传入的vo的所有属性
            Field[] fields = o.getClass().getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                //判断属性是否有导出的注解
                if (!field.isAnnotationPresent(ExportAnnotation.class)) {
                    continue;
                }
                //有导出的注解获取该注解的值
                ExportAnnotation annotation = field.getAnnotation(ExportAnnotation.class);
                String exportName = annotation.value();
                if (StringUtils.isBlank(exportName)) {
                    exportName = field.getName();
                }
                //获取属性的值
                Object value = null;
                try {
                    value = field.get(o);
                } catch (IllegalAccessException e) {
                    log.error("获取属性值失败:", e);
                }
                //判断值需要转类型
                value = getValue(field, value);
                linkedMap.put(exportName, value);
            }
            data.add(linkedMap);
        }
        ExcelTool.mapToExcel(data, httpServletResponse);
    }


    private static Object getValue(Field field, Object value) {
        if (Objects.isNull(value)) {
            value = "";
        } else if (field.isAnnotationPresent(ExportTypeAnnotation.class)) {
           //获取typeHandler的class
            Class<? extends ExportTypeHandler> aClass = field.getAnnotation(ExportTypeAnnotation.class).typeHandler();
            ExportTypeHandler typeHandler;
            try {
                if (TYPE_HANDLER_MAP.containsKey(aClass)) {
                    typeHandler = TYPE_HANDLER_MAP.get(aClass);
                } else {
                    //如果TYPE_HANDLER_MAP内没有该class的实力 则通过反射创建一个并存入
                    typeHandler = aClass.newInstance();
                    TYPE_HANDLER_MAP.put(aClass, typeHandler);
                }
                //调用方法获得类型对应的desc
                value = typeHandler.getValue(value);
            } catch (Exception e) {
                log.error("ExportTypeHandler转换失败,value:{},失败异常:", value, e);
            }
        }
        return value;
    }
}

ExcelTool.mapToExcel的代码:

 public static void mapToExcel(List<Map<String, Object>> data, HttpServletResponse httpServletResponse) {
        List<List<String>> result = new ArrayList<>();
        List<String> menu = new ArrayList<>();
        Set<String> m = new HashSet<>();
        result.add(menu);
        for (Map<String, Object> map : data) {
            List<String> list = new ArrayList<>();
            for (String aMenu : menu) {
                if (map.containsKey(aMenu)) {
                    Object o = map.get(aMenu);
                    if (o != null)
                        list.add(o.toString());
                    else
                        list.add("");
                } else
                    list.add("");
            }
            for (Map.Entry<String, Object> entry : map.entrySet()) {
                if (!m.contains(entry.getKey())) {
                    m.add(entry.getKey());
                    menu.add(entry.getKey());
                    Object o = entry.getValue();
                    if (o != null)
                        list.add(o.toString());
                    else
                        list.add("");
                }
            }
            result.add(list);
        }
        excel(result, httpServletResponse);
    }
    

    private static void excel(List<List<String>> data, HttpServletResponse httpServletResponse) {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        for (int i = 0; i < data.size(); i++) {
            XSSFRow row = sheet.createRow(i);
            List<String> list = data.get(i);
            for (int j = 0; j < list.size(); j++) {
                XSSFCell cell = row.createCell(j);
                cell.setCellValue(list.get(j));
            }
        }
        httpServletResponse.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        try {
            ServletOutputStream outputStream = httpServletResponse.getOutputStream();
            workbook.write(outputStream);
            outputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

以上就是自定义注解实现导出的全部代码,调用的话也很简单ExportUtil.export(records, response);