基于easyexcel基于模板实现复杂的导出

easyexcel可以很容易实现基于模板的一些常见导出

对于一些常见的列表、对象可以快速实现，但是做项目中遇到了一个需求，easyexcel无法完美满足，但是不用easyexcel的基于模板导出，只能用poi原始的一个单元格一个单元格的方式又显得特别繁琐，最终经过实践，想到了一套特殊的方案进行解决。

1、效果

页面选择多个订单进行下载

订单按照商家进行分组

每一个商家对应多个订单形成一个sheet页（上述样例）

每一个sheet页有商家信息、订单汇总信息、以及每个订单的详细信息

2、解决方案

1、通过复制sheet的方式 ，如果有多个供应商，就将商家汇总页面复制多少个，

2、有多少个订单，就讲订单详情复制成多个sheet，

3、再利用EasyExcel将订单详情和商家汇总的所有sheet数据填充，

4、最后利用poi框架提供的复制功能，将供应商对应的订单详情一个一个拷贝到相应的商家汇总sheet，

5、最后删除所有的订单详情sheet，只保留商家汇总

核心思想就是拆分、填充、再复制、最后删除

代码

package com.taoge.easyexcel.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.taoge.easyexcel.bean.ExportResult;
import com.taoge.easyexcel.bean.OrderDetailExportModel;
import com.taoge.easyexcel.bean.OrderExportModel;
import com.taoge.easyexcel.mapper.OrderDetailMapper;
import com.taoge.easyexcel.mapper.OrderMapper;
import com.taoge.easyexcel.model.OrderDetailModel;
import com.taoge.easyexcel.model.OrderModel;
import com.taoge.easyexcel.service.IOrderDownloadService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.CellCopyPolicy;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * <p>
 * 服务实现类
 * </p>
 *
 * @author txf
 * @since 2022-10-15
 */
@Service
@Slf4j
public class OrderDownloadServiceImpl implements IOrderDownloadService {

    @Resource
    private OrderMapper orderMapper;

    @Resource
    private OrderDetailMapper orderDetailMapper;

    @Override
    public ExportResult downloadOrderInfo(String startTime, String endTime) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        QueryWrapper<OrderModel> orderQuery = new QueryWrapper<>();
        try {
            Date time1 = format.parse(startTime);
            Date time2 = format.parse(endTime);
            orderQuery.gt("date", time1);
            orderQuery.lt("date", time2);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        List<OrderModel> orderModels = orderMapper.selectList(orderQuery);
        if (CollectionUtils.isNotEmpty(orderModels)) {
            List<Long> collect = orderModels.stream().map(OrderModel::getId).collect(Collectors.toList());
            LambdaQueryWrapper<OrderDetailModel> detailQuery = new LambdaQueryWrapper<>();
            detailQuery.in(OrderDetailModel::getOrderId, collect);
            List<OrderDetailModel> detailModels = orderDetailMapper.selectList(detailQuery);
            if (CollectionUtils.isNotEmpty(detailModels)) {
                String export = export(orderModels, detailModels);
                return ExportResult.builder().downloadUrl(export).build();
            }
        }
        return ExportResult.builder().downloadUrl("").build();
    }

    public String export(List<OrderModel> orderModels, List<OrderDetailModel> detailModels) {
        String format = new Date().getTime() + "";
        String fileName = "订单下载" + format + ".xlsx";
        String dirPath = "D:\\Downloads\\";
        //下载文件地址
        File file = new File(dirPath + fileName);
        //模板地址
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("订单.xlsx");
        OutputStream os = null;
        try {
            os = new FileOutputStream(file);
            List<OrderExportModel> orderExportModels = convertOrder(orderModels);
            List<OrderDetailExportModel> orderDetailExportModels = convertDetailOrder(detailModels);
            exportExcelWithTemplate(orderExportModels, orderDetailExportModels, os, inputStream);
            mergeSheet(file.getAbsolutePath(), orderExportModels, orderDetailExportModels);
        } catch (Exception e) {
            log.error("download failed", e);
        }
        return file.getAbsolutePath();
    }

    private boolean exportExcelWithTemplate(List<OrderExportModel> orderExportModelList, List<OrderDetailExportModel> detailExportModelList,
                                            OutputStream outputStream, InputStream templateInputStream) {
        try {
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            XSSFWorkbook workbook = new XSSFWorkbook(templateInputStream);
            List<String> sellerNameList = orderExportModelList.stream().map(OrderExportModel::getSellerName).distinct().collect(Collectors.toList());
            workbook.setSheetName(0, sellerNameList.get(0));
            for (int i = 1; i < sellerNameList.size(); i++) {
                workbook.cloneSheet(0, sellerNameList.get(i));
            }
            workbook.setSheetName(1, orderExportModelList.get(0).getId());
            //原模板只有一个订单sheet，通过poi复制出需要的sheet个数的模板
            for (int i = 1; i < orderExportModelList.size(); i++) {
                workbook.cloneSheet(1, orderExportModelList.get(i).getId());
            }

            Map<String, Integer> indexMap = new HashMap<>();
            int numberOfSheets = workbook.getNumberOfSheets();
            for (int i = 0; i < numberOfSheets; i++) {
                indexMap.put(workbook.getSheetAt(i).getSheetName(), i);
            }

            //写到流里
            workbook.write(bos);
            byte[] bArray = bos.toByteArray();
            InputStream is = new ByteArrayInputStream(bArray);

            ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(is).build();
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.VERTICAL).forceNewRow(Boolean.TRUE).build();

            Map<String, List<OrderExportModel>> sellerOrderMap = orderExportModelList.stream().collect(Collectors.groupingBy(OrderExportModel::getSellerCode));
            Map<String, OrderExportModel> collect = orderExportModelList.stream().collect(Collectors.toMap(OrderExportModel::getId, Function.identity(), (a, b) -> a));
            Map<String, List<OrderDetailExportModel>> detailMap = detailExportModelList.stream().collect(Collectors.groupingBy(OrderDetailExportModel::getOrderId));

            for (Map.Entry<String, List<OrderExportModel>> entry : sellerOrderMap.entrySet()) {
                List<OrderExportModel> exportModels = entry.getValue();
                OrderExportModel model = exportModels.get(0);
                WriteSheet writeSheet = EasyExcel.writerSheet(indexMap.get(model.getSellerCode())).build();
                excelWriter.fill(exportModels, fillConfig, writeSheet);
                excelWriter.fill(BeanUtils.describe(model), fillConfig, writeSheet);
            }

            for (int i = 0; i < orderExportModelList.size(); i++) {
                OrderExportModel orderExportModel = orderExportModelList.get(i);
                WriteSheet writeSheet = EasyExcel.writerSheet(indexMap.get(orderExportModel.getId())).build();
                List<OrderDetailExportModel> orderDetailExportModels = detailMap.get(orderExportModel.getId());
                excelWriter.fill(orderDetailExportModels, fillConfig, writeSheet);
                excelWriter.fill(orderExportModel, fillConfig, writeSheet);
            }
            excelWriter.finish();
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            log.error("导出失败！", e);
        }
        return false;
    }

    private void mergeSheet(String filePath, List<OrderExportModel> orderExportModelList, List<OrderDetailExportModel> detailExportModelList) {

        try {
            FileInputStream fis = new FileInputStream(filePath);
            XSSFWorkbook source = new XSSFWorkbook(fis);

            Map<String, List<OrderExportModel>> sellerOrderMap = orderExportModelList.stream().collect(Collectors.groupingBy(OrderExportModel::getSellerName));

            int numberOfSheets = source.getNumberOfSheets();

            for (int i = 0; i < numberOfSheets; i++) {
                XSSFSheet to = source.getSheetAt(i);
                String sheetName = to.getSheetName();
                if (sellerOrderMap.containsKey(sheetName)) {
                    List<OrderExportModel> orderExportModels = sellerOrderMap.get(sheetName);
                    for (OrderExportModel model : orderExportModels) {
                        XSSFSheet from = source.getSheet(model.getId());
                        merge(from, to);
                    }
                }
            }

            while (true) {
                int num = source.getNumberOfSheets();
                boolean hasDel = false;
                for (int i = 0; i < num; i++) {
                    XSSFSheet sheetAt = source.getSheetAt(i);
                    if (!sellerOrderMap.containsKey(sheetAt.getSheetName())) {
                        source.removeSheetAt(i);
                        hasDel = true;
                        break;
                    }
                }
                if (!hasDel) {
                    break;
                }
            }

            FileOutputStream fos = new FileOutputStream(filePath);
            source.write(fos);
            fis.close();
        } catch (Exception e) {
            log.error("merge sheet error", e);
        }
    }

    private void merge(XSSFSheet from, XSSFSheet to) {
        int len = to.getPhysicalNumberOfRows() + 3;
        int cnt = from.getPhysicalNumberOfRows();
        for (int i = 0; i < cnt; i++) {
            XSSFRow row = to.createRow(len + i);
            row.copyRowFrom(from.getRow(i), new CellCopyPolicy());
        }
    }

    private List<OrderExportModel> convertOrder(List<OrderModel> orderModels) {
        return orderModels.stream().map(order -> {
            return OrderExportModel.builder()
                    .id(order.getId() + "")
                    .title(order.getTitle())
                    .sellerCode(order.getSellerCode())
                    .sellerName(order.getSellerName())
                    .date(order.getDate().format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")))
                    .address(order.getAddress())
                    .userName(order.getUserName())
                    .price(order.getPrice().toString())
                    .build();
        }).collect(Collectors.toList());
    }

    private List<OrderDetailExportModel> convertDetailOrder(List<OrderDetailModel> orderDetailModels) {
        return orderDetailModels.stream().map(order -> {
            return OrderDetailExportModel.builder()
                    .skuCode(order.getSkuCode())
                    .skuName(order.getSkuName())
                    .orderId(order.getOrderId() + "")
                    .num(order.getNum() + "")
                    .unitPrice(order.getUnitPrice().toString())
                    .price(order.getPrice().toString())
                    .build();
        }).collect(Collectors.toList());
    }

}