纯记录!!!
自定义表头
List headList = setHeadRows(dynamicHeadList, orderExcelList, list);
Workbook workbook = ExcelExportUtil.exportExcel(params,headList, list);
固定表头
Workbook workbook = ExcelExportUtil.exportExcel(params, OpenActivityOrderExcelOut.class, orderExcelList);
一、导出方法
/**
* 导出excel
*
* @return
*/
@PostMapping("exportOrderList")
public Object exportOrderList(@RequestBody OpenActivityOrderQueryIn in, HttpServletResponse response) {
List<OpenActivityOrderExcelOut> orderExcelList = new ArrayList<>();
FileOutputStream fos = null;
try {
Page<OpenActivityOrderOut> retPage = userBuyOpenActivityOrderService.getOpenCoursesOrderPage(in);
List<OpenActivityOrderOut> orderList = retPage.getRecords();
Optional.ofNullable(orderList).filter(o -> o.size() > 0).orElseThrow(() -> new BusinessException("数据为空,无法导出"));
List<String> dynamicHeadList = new ArrayList<>();
for (OpenActivityOrderOut out : orderList) {
OpenActivityOrderExcelOut orderExcelOut = new OpenActivityOrderExcelOut();
BeanUtils.copyProperties(out, orderExcelOut);
orderExcelOut.setUnitPrice((out.getUnitPrice().setScale(2)).toString());
orderExcelOut.setBuyNum(String.valueOf(out.getBuyNum()));
orderExcelOut.setOrderAmount((out.getOrderAmount().setScale(2)).toString());
/** 设置标签 */
CommunityUserBuyOpenActivityOrderTagIn tagIn = new CommunityUserBuyOpenActivityOrderTagIn();
tagIn.setActivityOperationId(out.getOpenCoursesOperationId());
tagIn.setOrderOperationId(out.getOperationId());
tagIn.setUserOperationId(out.getUserOperationId());
tagIn.setTagStyle(BDic.INPUT);
List<CommunityUserBuyOpenActivityOrderTagOut> activityInputTagList = communityUserBuyOpenActivityOrderTagService.getByOrder(tagIn);
Optional.ofNullable(activityInputTagList).filter(a -> a.size() > 0).ifPresent(a -> {
orderExcelOut.setActivityInputTagList(a);
});
tagIn.setTagStyle(BDic.CHECKBOX);
List<CommunityUserBuyOpenActivityOrderTagOut> activityCheckBoxTagList = communityUserBuyOpenActivityOrderTagService.getByOrder(tagIn);
Optional.ofNullable(activityCheckBoxTagList).filter(a -> a.size() > 0).ifPresent(a -> {
orderExcelOut.setActivityCheckBoxTagList(a);
});
orderExcelOut.setCreateTime(out.getCreateTime() != null ? DUtils.fmtYMDHMSSTR(out.getCreateTime()) : "");
orderExcelOut.setCancelTime(out.getCancelTime() != null ? DUtils.fmtYMDHMSSTR(out.getCancelTime()) : "");
orderExcelOut.setPaymentTime(out.getPaymentTime() != null ? DUtils.fmtYMDHMSSTR(out.getPaymentTime()) : "");
orderExcelOut.setOrderStatus(OpenCoursesOrderStatusEnum.getEnumByCode(out.getOrderStatus()).getName());
orderExcelList.add(orderExcelOut);
}
List<Map<String, Object>> list = new ArrayList<>();
ExportParams params = new ExportParams(null, "报名订单");
/** 自定义表头 */
List headList = setHeadRows(dynamicHeadList, orderExcelList, list);
Workbook workbook = ExcelExportUtil.exportExcel(params,headList, list);
/** 固定表头 */
Workbook workbook = ExcelExportUtil.exportExcel(params, OpenActivityOrderExcelOut.class, orderExcelList);
response.setContentType("application/octet-stream");
response.setCharacterEncoding("UTF-8");
String filename = "报名订单" + DUtils.fmtYMDHMS_Str(new Date()) + ".xls";
response.setHeader("Content-disposition", "attachment;filename=\"" + java.net.URLEncoder.encode(filename, "UTF-8") + "\"");
response.flushBuffer();
workbook.write(response.getOutputStream());
} catch (Exception e) {
logger.error(e.getMessage(), e);
return MessageBox.build(com.zy.core.model.BDic.FAIL, "导出失败,请联系管理员");
} finally {
try {
if (null != fos) {
fos.flush();
fos.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
return MessageBox.build(com.zy.core.model.BDic.SUCCESS, "Excel导出成功");
}
/**
* 自定义表头
*
* @return
*/
public static List<ExcelExportEntity> setHeadRows(List<String> dynamicHeadList, List<OpenActivityOrderExcelOut> valueList, List<Map<String, Object>> mapList) {
List<ExcelExportEntity> headList = new ArrayList<>();
ExcelExportEntity orderNo = new ExcelExportEntity("订单编号", "orderNo");
orderNo.setOrderNum(1);
orderNo.setWidth(30);
headList.add(orderNo);
ExcelExportEntity title = new ExcelExportEntity("活动名称", "title");
title.setOrderNum(1);
title.setWidth(30);
headList.add(title);
ExcelExportEntity unitPrice = new ExcelExportEntity("单价(元)", "unitPrice");
unitPrice.setOrderNum(1);
unitPrice.setWidth(30);
headList.add(unitPrice);
ExcelExportEntity buyNum = new ExcelExportEntity("下单数量", "buyNum");
buyNum.setOrderNum(1);
buyNum.setWidth(30);
headList.add(buyNum);
ExcelExportEntity orderAmount = new ExcelExportEntity("实付金额(元)", "orderAmount");
orderAmount.setOrderNum(1);
orderAmount.setWidth(30);
headList.add(orderAmount);
ExcelExportEntity userName = new ExcelExportEntity("下单用户", "userName");
userName.setOrderNum(1);
userName.setWidth(30);
headList.add(userName);
ExcelExportEntity userPhone = new ExcelExportEntity("下单用户手机号", "userPhone");
userPhone.setOrderNum(1);
userPhone.setWidth(30);
headList.add(userPhone);
ExcelExportEntity affiliatedBusinessManager = new ExcelExportEntity("所属员业务", "affiliatedBusinessManager");
affiliatedBusinessManager.setOrderNum(1);
affiliatedBusinessManager.setWidth(30);
headList.add(affiliatedBusinessManager);
List<List<CommunityUserBuyOpenActivityOrderTagOut>> inputList = valueList.stream().filter(v -> v.getActivityInputTagList() != null && v.getActivityInputTagList().size() > 0).map(OpenActivityOrderExcelOut::getActivityInputTagList).collect(Collectors.toList());
Integer maxInputSize = 0;
if (inputList != null && inputList.size() > 0) {
Optional<List<CommunityUserBuyOpenActivityOrderTagOut>> inputLst = inputList.stream().max((u1, u2) -> Integer.valueOf(u1.size()).compareTo(u2.size()));
maxInputSize = inputLst.get().size();
if (maxInputSize != null && maxInputSize > 0) {
for (int i = 0; i < maxInputSize; i++) {
ExcelExportEntity createTime = new ExcelExportEntity("输入框标签" + (i + 1), "input" + (i + 1));
createTime.setOrderNum(1);
createTime.setWidth(30);
headList.add(createTime);
}
}
}
Integer maxCheckboxSize = 0;
List<List<CommunityUserBuyOpenActivityOrderTagOut>> checkboxList = valueList.stream().filter(v -> v.getActivityCheckBoxTagList() != null && v.getActivityCheckBoxTagList().size() > 0).map(OpenActivityOrderExcelOut::getActivityCheckBoxTagList).collect(Collectors.toList());
if (checkboxList != null && checkboxList.size() > 0) {
Optional<List<CommunityUserBuyOpenActivityOrderTagOut>> checkboxLst = checkboxList.stream().max((u1, u2) -> Integer.valueOf(u1.size()).compareTo(u2.size()));
maxCheckboxSize = checkboxLst.get().size();
if (maxCheckboxSize != null && maxCheckboxSize > 0) {
for (int i = 0; i < maxCheckboxSize; i++) {
ExcelExportEntity createTime = new ExcelExportEntity("复选框标签" + (i + 1), "checkbox" + (i + 1));
createTime.setOrderNum(1);
createTime.setWidth(30);
headList.add(createTime);
}
}
}
ExcelExportEntity createTime = new ExcelExportEntity("创建时间", "createTime");
createTime.setOrderNum(1);
createTime.setWidth(30);
headList.add(createTime);
ExcelExportEntity paymentTime = new ExcelExportEntity("付款时间", "paymentTime");
paymentTime.setOrderNum(1);
paymentTime.setWidth(30);
headList.add(paymentTime);
ExcelExportEntity cancelTime = new ExcelExportEntity("取消时间", "cancelTime");
cancelTime.setOrderNum(1);
cancelTime.setWidth(30);
headList.add(cancelTime);
ExcelExportEntity status = new ExcelExportEntity("订单状态", "status");
status.setOrderNum(1);
status.setWidth(30);
headList.add(status);
Integer finalMaxInputSize = maxInputSize;
Integer finalMaxCheckboxSize = maxCheckboxSize;
valueList.forEach(v -> {
Map<String, Object> valueMap = new HashMap<>();
valueMap.put("orderNo", v.getOrderNo());
valueMap.put("title", v.getTitle());
valueMap.put("unitPrice", new BigDecimal(v.getUnitPrice()).setScale(2).toString());
valueMap.put("buyNum", v.getBuyNum());
valueMap.put("orderAmount", v.getOrderAmount());
valueMap.put("userName", v.getPaymentUserName());
valueMap.put("userPhone", v.getPaymentUserPhone());
valueMap.put("affiliatedBusinessManager", v.getAffiliatedBusinessManager());
try {
for (int i = 0; i < finalMaxInputSize; i++) {
if (v.getActivityInputTagList() != null && v.getActivityInputTagList().size() > i && v.getActivityInputTagList().get(i) != null && v.getActivityInputTagList().get(i).getTagDetailList() != null && v.getActivityInputTagList().get(i).getTagDetailList().size() > 0) {
valueMap.put("input" + (i + 1), v.getActivityInputTagList().get(i).getTagName() + ":" + v.getActivityInputTagList().get(i).getTagDetailList().get(0).getTagDetailValue());
} else {
valueMap.put("input" + (i + 1), " -");
}
}
for (int i = 0; i < finalMaxCheckboxSize; i++) {
if (v.getActivityCheckBoxTagList() != null && v.getActivityCheckBoxTagList().size() > i && v.getActivityCheckBoxTagList().get(i).getTagDetailList() != null && v.getActivityCheckBoxTagList().get(i).getTagDetailList().size() > 0) {
List<CommunityUserBuyOpenActivityOrderTagDetailOut> checkBoxDetailList = v.getActivityCheckBoxTagList().get(i).getTagDetailList();
List<String> checkBoxDetailStrList = checkBoxDetailList.stream().map(CommunityUserBuyOpenActivityOrderTagDetailOut::getTagDetailValue).collect(Collectors.toList());
StringBuffer sb = new StringBuffer();
sb.append(v.getActivityCheckBoxTagList().get(i).getTagName() + ":");
checkBoxDetailStrList.forEach(s -> {
sb.append(s);
sb.append("、");
});
sb.deleteCharAt(sb.length() - 1);
valueMap.put("checkbox" + (i + 1), sb.toString());
} else {
valueMap.put("checkbox" + (i + 1), " -");
}
}
} catch (Exception e) {
log.error("标签导出异常:", e);
}
valueMap.put("createTime", v.getCreateTime());
valueMap.put("paymentTime", v.getPaymentTime());
valueMap.put("cancelTime", v.getCancelTime());
valueMap.put("status", v.getOrderStatus());
mapList.add(valueMap);
});
return headList;
}
View Code
二、实体类
@Data
public class OpenActivityOrderExcelOut {
@Excel(name = "订单编号",width = 30)
private String orderNo;
@Excel(name = "活动名称",width = 30)
private String title;
@Excel(name = "单价(元)",width = 30)
private String unitPrice;
@Excel(name = "下单数量",width = 30)
private String buyNum;
@Excel(name = "实付金额(元)",width = 30)
private String orderAmount;
@Excel(name = "下单用户",width = 30)
private String paymentUserName;
@Excel(name = "下单用户手机号",width = 30)
private String paymentUserPhone;
@Excel(name = "所属业务员",width = 30)
private String affiliatedBusinessManager;
@Excel(name = "创建时间",width = 30)
private String createTime;
@Excel(name = "付款时间",width = 30)
private String paymentTime;
@Excel(name = "取消时间",width = 30)
private String cancelTime;
@Excel(name = "订单状态",width = 30)
private String orderStatus;
@Excel(name = "标签",width = 30)
List<CommunityUserBuyOpenActivityOrderTagOut> activityInputTagList;
@Excel(name = "标签",width = 30)
List<CommunityUserBuyOpenActivityOrderTagOut> activityCheckBoxTagList;
}
View Code