使用easy-poi实现excel导入导出功能

DTO内容

DTO中内容：
import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.handler.inter.IExcelDataModel;
import cn.afterturn.easypoi.handler.inter.IExcelModel;
import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.fasterxml.jackson.annotation.JsonFormat;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import org.springframework.format.annotation.DateTimeFormat;

@Data
@ExcelIgnoreUnannotated // 忽略其他未使用注解的字段
public class DTO implements IExcelDataModel, IExcelModel {
	@Excel(name = "名称") // 导入时使用该注解
	@ExcelProperty("名称") // 导出时使用该注解
	@ApiModelProperty(name = "aa", value = "名称")
	private String aa;
	
	// 主要使用该注解对日期类型进行转换
	@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+08")
	@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
	@ApiModelProperty(name = "aa", value = "时间")
	private Date bb;

	@JsonSerialize(using = UnBigDecimalSerialize.class)
	private Bigdecimal cc;
}

// 金钱格式化方法
import com.fasterxml.jackson.core.JsonGenerator;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.JsonSerializer;
import com.fasterxml.jackson.databind.SerializerProvider;

import java.io.IOException;
import java.math.BigDecimal;
import java.text.DecimalFormat;

public class UnBigDecimalSerialize extends JsonSerializer<BigDecimal> {
    private DecimalFormat df = new DecimalFormat("##0.00");

    public UnBigDecimalSerialize() {
    }

    public void serialize(BigDecimal value, JsonGenerator gen, SerializerProvider serializers) throws IOException, JsonProcessingException {
        gen.writeString(this.df.format(value));
    }
}

导出

public R export(Searchable searchable) {
        try {
            String fileName = "导出.xlsx";
            ServletRequestAttributes servletRequestAttributes =
                (ServletRequestAttributes)RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = servletRequestAttributes.getResponse();
            OutputStream outputStream = response.getOutputStream();
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);
            final List<DTO> DTOS = service.queryDetail(searchable);

            EasyExcel.write(outputStream, DTO.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()).sheet("sheet1")
                .doWrite(DTOS);
            return R.ok(null);
        } catch (Exception e) {
                return R.error(400, "导出失败", null);
        }
    }

导入

    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public R<List<DTO>> importExcel(@RequestParam("file") MultipartFile multipartFile) {
        try {
            ImportParams params = new ImportParams();
            params.setHeadRows(1);
            // 开启Excel校验
            params.setNeedVerify(true);
            ExcelImportResult<DTO> result = ExcelImportUtil.importExcelMore(multipartFile.getInputStream(), DTO.class, params);
            StringBuilder errorMsg = new StringBuilder();
            for (DTO entity : result.getFailList()) {
                String msg = "第" + entity.getRowNum() + "行的错误是：" + entity.getErrorMsg() + ";";
                errorMsg.append(msg);
            }
            if (result.isVerfiyFail()) {
                return R.error(400, "导入失败:" + errorMsg, null);
            }
            if (result.getList().size() > 20000) {
                throw new Exception(400, "数据应少于20000条");
            }
            // 执行保存操作
            service.save(result.getList());
            return R.success(result.getList());
        } catch (Exception e) {
            return R.error(400, "导入失败", null);
        }
    }