一、需求：
????????下载列表(支持筛选)所有数据，并生成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）?
