easyexcel1.0导出excel测试案例

本案例为测试easyexcel读取，写入excel

开发工具：idea+maven3+springmvc4+spring4+mybatis3+mysql5.7

easyexcel版本：

 <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>easyexcel</artifactId>
      <version>1.1.2-beta5</version>
</dependency>

使用的工具类：easyexcel工具类

入参Dto：

@Getter
@Setter
@Accessors(chain = true)
public class ExportDto implements Serializable {

    private static final long serialVersionUID = 545300882882790732L;

    /**
     * 人群洞察类型:1AIPL人群洞察2流转人群洞察3人群包人群洞察
     */
    private Integer insightType;

    /**
     * 品牌编码
     */
    private String brandCode;

    /**
     * 品类编码
     */
    private String categoryCode;

    /**
     * 人群类型:0全部人群 1认知2兴趣3购买4忠诚 5流失 6未知
     */
    private String startCrowdType;

    /**
     * 人群类型:0全部人群 1认知2兴趣3购买4忠诚 5流失 6未知
     */
    private String endCrowdType;

    /**
     *流转人群洞察:1未流转2流转
     */
    private String flowType;

    /**
     * 人群包类型
     */
    private String audienceCode;

    /**
     * 大盘类型:1行业大盘2苏宁大盘，默认为行业大盘
     */
    @Min(1)
    private Integer grailType = 1;

}

出参Vo:

@Getter
@Setter
public class ExportVo extends BaseRowModel {

    /**
     * 纬度
     */
    @ExcelProperty(value = {"纬度"}, index = 0)
    private String dimension;

    /**
     * 特征
     */
    @ExcelProperty(value = {"特征"}, index = 1)
    private String feature;

    /**
     * 占比
     */
    @ExcelProperty(value = {"占比"}, index = 2)
    private String insightPercent;

    /**
     * 大盘
     */
    @ExcelProperty(value = {"大盘"}, index = 3)
    private String grailPercent;

    /**
     * 对比大盘
     */
    @ExcelProperty(value = {"对比大盘"}, index = 4)
    private String comparePercent;

}

导出一个sheet：

控制层：(数据量控制在20万以内)

@CrossOrigin
@RestController
@RequestMapping("/mip/export")
public class ExportController extends BaseController {
    /**
     * 基础属性导出
     * @param insightType
     * @param brandCode
     * @param categoryCode
     * @param startCrowdType
     * @param endCrowdType
     * @param flowType
     * @param audienceCode
     * @param request
     * @return
     */
    @GetMapping("/basicAttributeExport")
    public void basicAttributeExport(
            @RequestParam(value = "insightType")
                    Integer insightType,
            @RequestParam(value = "brandCode")
                    String brandCode,
            @RequestParam(value = "categoryCode")
                    String categoryCode, String startCrowdType, String endCrowdType, String flowType,
            String audienceCode, HttpServletRequest request, HttpServletResponse response) {
        ExportDto dto = new ExportDto().setInsightType(insightType).setBrandCode(brandCode)
                .setCategoryCode(categoryCode).setStartCrowdType(startCrowdType).setEndCrowdType(endCrowdType)
                .setFlowType(flowType).setAudienceCode(audienceCode);
        exportService.basicAttributeExport(dto, response);
    }
}

业务层：

@Service
public class ExportServiceImpl implements ExportService {

    //log日志
    private static final Logger LOGGER = LoggerFactory.getLogger(ExportServiceImpl.class);

    @Autowired
    private DalClient dalClient;

    @Autowired
    private IExportAssertionService exportAssertionService;

    /**
     * 基础属性
     * @param dto
     * @param response
     */
    public void basicAttributeExport(ExportDto dto, HttpServletResponse response) {
        LOGGER.info("基础属性导出入参为:{}", new Gson().toJson(dto));
        //查询结果
        List<ExportVo> list = queryBasicAttribute(dto);
        String fileName = SNConstant.BASICATTRIBUTE + DateUtils.getCurrentDateStr("yyyyMMddHHmmss");
        String sheetName = SNConstant.BASICATTRIBUTE;
        //方法一：poi导出
        //ExcelUtil.downLoadExcel(response, list, fileName, sheetName, ExportVo.class);
        //方法二：easyexcel导出
        EasyExcelUtil.writeExcelWithModel(response, list, fileName, sheetName, ExportVo.class, ExcelTypeEnum.XLSX);
    }
}

postman测试：

导出效果：

导出多个sheet：

控制层：(数据量控制在20万以内)

/**
     * 用户洞察中心导出
     * @param insightType
     * @param brandCode
     * @param categoryCode
     * @param startCrowdType
     * @param endCrowdType
     * @param flowType
     * @param audienceCode
     * @param request
     * @param response
     * @return
     */
    @GetMapping("/downLoadInsight")
    public void downLoadInsight(
            @Validated
            @NotNull(message = "%custom%人群洞察类型不可为空")
            @RequestParam(value = "insightType")
                    Integer insightType,
            @Validated
            @NotNull(message = "%custom%品牌编码不可为空")
            @RequestParam(value = "brandCode")
                    String brandCode,
            @RequestParam(value = "categoryCode")
                    String categoryCode, String startCrowdType, String endCrowdType, String flowType,
            String audienceCode, HttpServletRequest request, HttpServletResponse response) {
        ExportDto dto = new ExportDto().setInsightType(insightType).setBrandCode(brandCode)
                .setCategoryCode(categoryCode).setStartCrowdType(startCrowdType).setEndCrowdType(endCrowdType)
                .setFlowType(flowType).setAudienceCode(audienceCode);
        exportService.downLoadInsight(dto, response);
    }

业务层：

/**
     * 用户洞察中心导出
     * @param dto
     * @param response
     */
    public void downLoadInsight(ExportDto dto, HttpServletResponse response) {
        LOGGER.info("用户洞察中心导出入参为:{}", new Gson().toJson(dto));
        long l1 = System.currentTimeMillis() / 1000;
        String fileName = SNConstant.USERINSIGHT + DateUtils.getCurrentDateStr("yyyyMMddHHmmss");
        String sheetName1 = SNConstant.BASICATTRIBUTE;
        List<ExportVo> list1 = queryBasicAttribute(dto);
        String sheetName2 = SNConstant.LIVINGCONDITION;
        List<ExportVo> list2 = queryLivingCondition(dto);
        String sheetName3 = SNConstant.PRODUCTPREFERENCE;
        List<ExportVo> list3 = queryProductPreference(dto);
        String sheetName4 = SNConstant.MEDIAPREFERENCE;
        List<ExportVo> list4 = queryMediaPreference(dto);
        Map<String, List<? extends BaseRowModel>> dataMap = new HashMap<>();
        dataMap.put(sheetName1, list1);
        dataMap.put(sheetName2, list2);
        dataMap.put(sheetName3, list3);
        dataMap.put(sheetName4, list4);
        //方法一：Map存储数据
        //EasyExcelUtil.writeExcelWithModelSheet(response, dataMap, fileName, ExcelTypeEnum.XLSX);
        //方法二：工厂方法
        EasyExcelUtil.writeExcelWithSheets(response, list1, fileName, sheetName1, ExportVo.class, ExcelTypeEnum.XLSX)
                .write(list2, sheetName2, ExportVo.class).write(list3, sheetName3, ExportVo.class)
                .write(list4, sheetName4, ExportVo.class).finish();
        long l2 = System.currentTimeMillis() / 1000;
        System.out.println("导出耗时：" + (l2 - l1));
    }

导出效果：

easyexcel读取excel（一个sheet）

控制层：(数据量控制在20万以内)

@PostMapping("/readExcelWithModel")
    public ResponseEntity<BaseResponse> readExcelWithModel(
            @RequestParam(value = "file", required = true)
                    MultipartFile file) {
        try {
            String fileName = file.getOriginalFilename();
            if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase()
                    .endsWith(".xlsx"))) {
                throw new FieldInvalidException("readExcelWithModel", "文件格式错误！");
            }
            InputStream inputStream = new BufferedInputStream(file.getInputStream());
            List<ExportVo> data=EasyExcelUtil.readExcelWithModel(inputStream, ExportVo.class, ExcelTypeEnum.XLSX);
            return ResponseEntity.ok(BaseResponse.success().setResult(data));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return ResponseEntity.ok(new BaseResponse("读取文件失败").setResult(null));
    }

postman测试效果：

easyexcel读取excel（多个sheet）

控制层：(数据量控制在20万以内)

@PostMapping("/readExcelWithModelSheet")
    public ResponseEntity<BaseResponse> readExcelWithModelSheet(
            @RequestParam(value = "file", required = true)
                    MultipartFile file) {
        try {
            String fileName = file.getOriginalFilename();
            if (fileName == null || (!fileName.toLowerCase().endsWith(".xls") && !fileName.toLowerCase()
                    .endsWith(".xlsx"))) {
                throw new FieldInvalidException("readExcelWithModel", "文件格式错误！");
            }
            InputStream inputStream = new BufferedInputStream(file.getInputStream());
            List<ExportVo> data=EasyExcelUtil.readExcelWithModelSheet(inputStream, ExportVo.class, ExcelTypeEnum.XLSX);
            return ResponseEntity.ok(BaseResponse.success().setResult(data));
        } catch (IOException e) {
            e.printStackTrace();
        }
        return ResponseEntity.ok(new BaseResponse("读取文件失败").setResult(null));
    }

postman测试效果：

参考：

easyExcel导出excel_asyexcel.write response_傲宇苍穹的博客-CSDN博客

使用EasyExcel导入导出Excel - 简书

阿里开源（EasyExcel）---导出EXCEL_阿里easyexcel_请叫我猿叔叔的博客-CSDN博客