【EasyExcel】Java利用EasyExcel生成excel（并导入数据）

一、需求：

        下载列表(支持筛选)所有数据，并生成excel文件

二、实现思路：
        由于数据量的原因，使用到了es 通过es数据筛选完之后，将数据全部取出，然后转转easyExcel格式的List数据，写入到excel，返回成流。

三、代码：

Controller层

    @ApiOperation(value = "download", notes = "download")
    @GetMapping("/download")
    public void download(HttpServletResponse response
            ,@ApiIgnore CactusContext context,
                         @RequestParam("f") String f,@RequestParam("filterId") String filterId) throws IOException {
        //---- begin---- 构建查询方法 这里是走的es 如果走别的 在这里将数据查出即可---------------
        JSONObject criteria = null;
        if(StringUtils.isNotBlank(filterId)){
            Long aLong = Long.valueOf(filterId.trim());
            Filters filter = filtersService.getOne(Wrappers.<Filters>lambdaQuery().eq(Filters::getAccountId, context.getAccountId())
                    .eq(Filters::getCreatorId, context.getUserId()).eq(Filters::getId, aLong));
            if (Objects.nonNull(filter)){
                criteria = JSONObject.parseObject(filter.getCriteria());
            }
        }else {
            if(StringUtils.isNotBlank(f)){
                criteria =  JSONObject.parseObject(f);
            }else{
                criteria = null;
            }
        }
        // 查询的数据为List集合
        List<SolutionDocument> solutionDocuments = returnAndRefundListService.downloadAndJudgeFilter(context, criteria);
        //---- end---- 构建查询方法 这里是走的es 如果走别的 在这里将数据查出即可---------------
        DateTimeFormatter timeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        // 将查询出的数据转成EasyExcel对应的模版类 SolutionExcelData
        List<SolutionExcelData> data = BeanUtils.copyList(solutionDocuments, c -> {
            SolutionExcelData solutionExcelData = BeanUtils.copyProperties(c, SolutionExcelData.class);
            //  to String  这里是设置导出的excel里面时间格式 不需要可以删除
            solutionExcelData.setTicketCreateDate(c.getTicketCreateDate() != null ? timeFormatter.format(c.getTicketCreateDate()) : "");
            solutionExcelData.setOrderPurchaseDate(c.getOrderPurchaseDate() != null ? timeFormatter.format(c.getOrderPurchaseDate()) : "");
            solutionExcelData.setSolutionCreateDate(timeFormatter.format(c.getSolutionCreateDate()));
            return solutionExcelData;
        });

        try{
            // 使用swagger 会导致各种问题，直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 导出文件名称
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("HH:mm:ss");
            String name = "solution_List_"+ LocalDate.now()+"_"+dtf.format(LocalDateTimeUtil.now());
            String fileName = URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xls");

            EasyExcel.write(response.getOutputStream(),SolutionExcelData.class)
                    .autoCloseStream(Boolean.FALSE)
                    .sheet("sheet")
                    .doWrite(data);
        }catch (Exception e){
            log.error("-----error-----------getStackTrace------------ {}"+e.getStackTrace());
            log.error("-----error----------message------------- {}"+e.getMessage());
        }
    }

EasyExcel模版导出类（SolutionExcelData）

import com.alibaba.excel.annotation.ExcelProperty;
import groovy.transform.EqualsAndHashCode;
import lombok.Getter;
import lombok.Setter;

import java.math.BigDecimal;
/**
  * @Author: c
  * @Description:
  * @Date:  2022/4/14
  */
@Getter
@Setter
@EqualsAndHashCode
//@HeadRowHeight(value = 23)
public class SolutionExcelData {


    @ExcelProperty(value = "Ticket Number", index = 0)
    private String ticketNumber;

    @ExcelProperty(value = "Ticket Create Date", index = 1)
    private String ticketCreateDate;
    @ExcelProperty(value = "Customer Email", index = 2)
    private String customerEmail;
    @ExcelProperty(value = "Customer Name", index = 3)
    private String customerName;
    @ExcelProperty(value = "Platform", index = 4)
    private String platform;
    @ExcelProperty(value = "Market", index = 5)
    private String market;
    @ExcelProperty(value = "Seller", index = 6)
    private String seller;

    @ExcelProperty(value = "Order Purchase Date", index = 7)
    private String orderPurchaseDate;

    @ExcelProperty(value = "Order Number", index = 8)
    private String orderNumber;
    @ExcelProperty(value = "Product Category", index = 9)
    private String productCategory;
    @ExcelProperty(value = "SKU", index = 10)
    private String sku;
    @ExcelProperty(value = "Items", index = 11)
    private String items;

    @ExcelProperty(value = "SKU Quantity", index = 12)
    private Integer skuQuantity;
    @ExcelProperty(value = "SKU Amount", index = 13)
    private BigDecimal skuAmount;

    @ExcelProperty(value = "Solution Create Date", index = 14)
//    @JsonFormat(pattern="yyyy-MM-dd")
    private String solutionCreateDate;

    @ExcelProperty(value = "Solution Type", index = 15)
    private String solutionType;
    @ExcelProperty(value = "Solution Quantity", index = 16)
    private Long solutionQuantity;
    @ExcelProperty(value = "Solution Amount", index = 17)
    private BigDecimal solutionAmount;
    @ExcelProperty(value = "Currency", index = 18)
    private String currency;
    @ExcelProperty(value = "Solution Note", index = 19)
    private String solutionNote;

}

四、结果：

        页面请求直接会生成excle文件（可自定义后缀xls、xlsx）