第一步导入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);