1、基础类描述

1. ExcelWriter（导出工具类）
2. Query（通用查询）
3. Consumer（函数参数）
4. SpringBeanUtil（获取bean）

2、代码

ExcelWriter

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.core.util.PageUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.excel.write.builder.ExcelWriterBuilder;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.handler.WriteHandler;
import com.alibaba.excel.write.handler.context.CellWriteHandlerContext;
import com.alibaba.excel.write.handler.context.SheetWriteHandlerContext;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.WriteTable;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.itxl.common.utils.SpringBeanUtil;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @authar liuWei
 * @ @date 2024/3/27
 */
public class ExcelWriter<T>{

    private Map<String,WriteSheet> sheetMap = null;
    private Map<String,WriteTable> tableMap = null;

    private List<WriteHandler> writeHandlers = null;

    private com.alibaba.excel.ExcelWriter writer = null;

    private OutputStream outputStream = null;


    private String curSheetMame = "sheet";

    private Integer sheetNo = 1;

    private Integer cueSheetDataSize = 0;
    private static final Integer sheetMaxNum = 1000000;

    private List<List<String>> heads = null;

    /**
     * 响应文件流类型
     */
    private static final String CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    /**
     * 响应头文件名编码格式
     */
    private static final String ENCODE = "UTF-8";

    private Long total;

    private boolean multiThread = false;

    public static <T> ExcelWriter<T> create(){
        return new ExcelWriter<>();
    }

    public ExcelWriter<T> setResponse(HttpServletResponse response,String fileName) throws IOException {
        response.setContentType(CONTENT_TYPE);
        response.setCharacterEncoding(ENCODE);
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        fileName = URLEncoder.encode(fileName, ENCODE).replaceAll("\\+", "%20");
        response.setHeader("fileName",fileName+".xlsx");
        return setOutputStream(response.getOutputStream());
    }

    public ExcelWriter<T> setOutputStream(OutputStream outputStream){
        this.outputStream = outputStream;
        return this;
    }

    private WriteSheet getSheet(String sheetName){
        if (CollUtil.isEmpty(sheetMap)){
            sheetMap = new HashMap<>();
        }
        if (StrUtil.isEmpty(sheetName)){
            sheetName = curSheetMame;
        }
        if (sheetNo > 1){
            sheetName = sheetName + sheetNo;
        }
        WriteSheet sheet = sheetMap.get(sheetName);
        if (sheet == null){
            sheet = new WriteSheet();
            sheet.setSheetName(sheetName);
            sheetMap.put(sheetName,sheet);
        }
        return sheet;
    }

    public ExcelWriter<T> setCurSheet(String sheetMame){
        this.curSheetMame = sheetMame;
        return this;
    }

    private WriteTable getTable(String tableName){
        if (CollUtil.isEmpty(tableMap)){
            tableMap = new HashMap<>();
        }
        if (StrUtil.isEmpty(tableName)){
            tableName = "table";
        }
        return tableMap.computeIfAbsent(tableName, k -> new WriteTable());
    }

    public ExcelWriter<T> addWriteHandlers(WriteHandler writeHandler){
        if (CollUtil.isEmpty(writeHandlers)){
            writeHandlers = new ArrayList<>();
        }
        writeHandlers.add(writeHandler);
        return this;
    }

    public ExcelWriter<T> builder(){
        ExcelWriterBuilder write = EasyExcelFactory.write(outputStream);
        if (CollUtil.isNotEmpty(writeHandlers)){
            for (WriteHandler writeHandler : writeHandlers) {
                write.registerWriteHandler(writeHandler);
            }
        }
        this.writer = write.build();
        return this;
    }

    public ExcelWriter<T> setHead(List<String> heads){
        return setHeads(parseHead(heads));
    }

    private List<List<String>> parseHead(List<String> heads) {
        List<List<String>> res = new ArrayList<>(heads.size());
        for (String head : heads) {
            res.add(Collections.singletonList(head));
        }
        return res;
    }

    private ExcelWriter<T> setHeads(List<List<String>> heads){
        getTable(null).setHead(heads);
        return this;
    }


    public ExcelWriter<T> writer(List<List<Object>> data){
        if (CollUtil.isEmpty(data)){
            return this;
        }
        while (cueSheetDataSize + data.size() > sheetMaxNum){
            List<List<Object>> left = ListUtil.sub(data, 0, sheetMaxNum - cueSheetDataSize);
            writer.write(left,getSheet(null),getTable(null));
            data = ListUtil.sub(data, sheetMaxNum - cueSheetDataSize, data.size());
            cueSheetDataSize = 0;
            sheetNo++;
        }
        cueSheetDataSize += data.size();
        writer.write(data,getSheet(null),getTable(null));
        return this;
    }

    public ExcelWriter<T> writer(Query<List<List<Object>>> query) throws InterruptedException {
        return writer(query, query.getPageSize());
    }

    public ExcelWriter<T> writer(Query<List<List<Object>>> query, Integer pageSize) throws InterruptedException {
        if (total == null){
            List<List<Object>> accept = query.accept();
            return writer(accept);
        }
        query.setPageSize(pageSize);
        int totalPage = PageUtil.totalPage(total, query.getPageSize());
        AtomicInteger pageNum = new AtomicInteger(1);
        if (!multiThread){
            writer(pageNum,totalPage, query);
            return this;
        }
        ThreadPoolTaskExecutor bean = SpringBeanUtil.getBean(ThreadPoolTaskExecutor.class);
        List<Future<?>> futures = new ArrayList<>();
        int treadPoolSize = Math.min(totalPage, 4);
        for (int i = 0; i < treadPoolSize; i++) {
            Future<?> future = bean.submit(() -> {
                writer(pageNum, totalPage, query);
            });
            futures.add(future);
        }
        for (Future<?> future : futures) {
            try {
                future.get();
            } catch (ExecutionException e) {
                throw new RuntimeException(e);
            }
        }
        return this;
    }

    private void writer(AtomicInteger pageNum, Integer totalPage, Query<List<List<Object>>> query){
        while (pageNum.get() <= totalPage) {
            query.setPageNum(pageNum.getAndIncrement());
            List<List<Object>> accept = query.accept();
            synchronized (writer){
                writer(accept);
            }
            query.removePage();
        }
    }

    public static  List<List<Object>> parseData(List<Object> datas,List<String> fields){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        List<List<Object>> res = new ArrayList<>(datas.size());
        for (Object data : datas) {
            JSONObject json = JSONUtil.parseObj(data);
            List<Object> objects = new ArrayList<>(fields.size());
            for (String field : fields) {
                Object o = json.get(field);
                if (o == null){
                    o = "";
                }else if (o instanceof Date){
                    o = format.format(o);
                }else{
                    o = o.toString();
                }
                objects.add(o);
            }
            res.add(objects);
        }
        return res;
    }

    public void close() {
        if (writer == null){
           return;
        }
        writer.close();
    }

    /**
     * 开启分页查询
     * @param total
     * @return
     */
    public ExcelWriter<T> paging(Long total){
        this.total = total;
        return this;
    }

    /**
     * 开启多线程
     * @param total
     * @return
     */
    public ExcelWriter<T> multiThread(Long total){
        this.multiThread = true;
        return paging(total);
    }

    /**
     * 合并数据
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @return
     */
    public ExcelWriter<T>  merge(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol){
        List<List<Integer>> mergeInfo = new ArrayList<>();
        mergeInfo.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
        return merge(mergeInfo);
    }


    /**
     * 合并数据
     * @param mergeInfo
     * @return
     */
    public ExcelWriter<T>  merge(List<List<Integer>> mergeInfo) {
        SheetWriteHandler mergeHandler = new SheetWriteHandler(){
            @Override
            public void afterSheetCreate(SheetWriteHandlerContext context) {
            for (List<Integer> merge : mergeInfo) {
                CellRangeAddress addressList = new CellRangeAddress(merge.get(0), merge.get(1), merge.get(2), merge.get(3));
                context.getWriteSheetHolder().getSheet().addMergedRegionUnsafe(addressList);
            }
            }
        };
        return addWriteHandlers(mergeHandler);
    }

    /**
     * 设置下拉框
     * @param axisInfo
     * @param data
     * @return
     */
    public ExcelWriter<T> setDropDownBox(List<Integer> axisInfo,List<String> data){
        SheetWriteHandler dropDownBoxHandler = new SheetWriteHandler() {
            @Override
            public void afterSheetCreate(SheetWriteHandlerContext context) {
                CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(axisInfo.get(0), axisInfo.get(1), axisInfo.get(2), axisInfo.get(3));
                DataValidationHelper helper = context.getWriteSheetHolder().getSheet().getDataValidationHelper();
                DataValidationConstraint constraint = helper.createExplicitListConstraint(data.toArray(new String[0]));
                DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
                context.getWriteSheetHolder().getSheet().addValidationData(dataValidation);
            }
        };
        return addWriteHandlers(dropDownBoxHandler);
    }

    /**
     * 设置下拉框
     * @param col
     * @param data
     * @return
     */
    public ExcelWriter<T> setDropDownBox(Integer col,List<String> data){
        //设置下拉框为col列往下1000行
        List<Integer> axisInfo = ListUtils.newArrayList(1,1000,col,col);
        return setDropDownBox(axisInfo,data);
    }

    /**
     * 设置列宽
     * @return
     */
    public ExcelWriter<T> setColumnWidth(List<List<Integer>> columnWidths){
        AbstractColumnWidthStyleStrategy strategy = new AbstractColumnWidthStyleStrategy(){
            @Override
            protected void setColumnWidth(CellWriteHandlerContext context) {
                Sheet sheet = context.getWriteSheetHolder().getSheet();
                for (List<Integer> columnWidth : columnWidths) {
                    sheet.setColumnWidth(columnWidth.get(0),columnWidth.get(1) * 256);
                }
            }
        };
        return addWriteHandlers(strategy);
    }

    /**
     * 设置列宽
     * @return
     */
    public ExcelWriter<T> setColumnWidth(Integer col,Integer width){
        List<List<Integer>> columnWidths = new ArrayList<>();
        columnWidths.add(ListUtils.newArrayList(col,width));
        return setColumnWidth(columnWidths);
    }

    /**
     * 设置行高
     * @param row
     * @param height
     * @return
     */
    public ExcelWriter<T> setRowHeight(Integer row,Integer height){
        List<List<Integer>> rowHeights = new ArrayList<>();
        rowHeights.add(ListUtils.newArrayList(row,height));
        return setRowHeight(rowHeights);
    }

    /**
     * 设置行高
     * @param rowHeights
     * @return
     */
    private ExcelWriter<T> setRowHeight(List<List<Integer>> rowHeights) {
        SheetWriteHandler strategy = new SheetWriteHandler(){
            @Override
            public void afterSheetCreate(SheetWriteHandlerContext context) {
                Sheet sheet = context.getWriteSheetHolder().getSheet();
                for (List<Integer> rowHeight : rowHeights) {
                    Row row = sheet.getRow(rowHeight.get(0));
                    row.setHeightInPoints(rowHeight.get(1));
                }
            }
        };
        return addWriteHandlers(strategy);
    }

    /**
     * 设置单元格样式
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     * @return
     */
    public ExcelWriter<T> setCellStyle(Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol,CellStyle cellStyle){
        List<List<Integer>> axisInfo = new ArrayList<>();
        axisInfo.add(ListUtils.newArrayList(firstRow,lastRow,firstCol,lastCol));
        return setCellStyle(axisInfo,cellStyle);
    }

    /**
     * 设置单元格样式
     * @param axisInfo
     * @return
     */
    public ExcelWriter<T> setCellStyle(List<List<Integer>> axisInfo,CellStyle cellStyle){
        SheetWriteHandler handler = new SheetWriteHandler(){
            @Override
            public void afterSheetCreate(SheetWriteHandlerContext context) {
                Sheet sheet = context.getWriteSheetHolder().getSheet();
                for (List<Integer> axis : axisInfo) {
                    for (int i = axis.get(0); i < axis.get(1); i++) {
                        Row row = sheet.getRow(i);
                        for (int j = axis.get(2); j < axis.get(3); j++) {
                            //设置样式
                            Cell cell = row.getCell(j);
                            cell.setCellStyle(cellStyle);
                        }
                    }
                }
                SheetWriteHandler.super.afterSheetCreate(context);
            }
        };
        return addWriteHandlers(handler);
    }


    public static void error(HttpServletResponse response,String msg) throws IOException {
        response.reset();
        response.setContentType("application/json");
        response.sendError(HttpServletResponse.SC_NOT_FOUND,msg);
    }



}

Query

import org.itxl.common.utils.Consumer;

/**
 * @authar liuWei
 * @ @date 2024/3/25
 */
public abstract class Query<T> implements Consumer<T> {

    private final ThreadLocal<Integer> local = new ThreadLocal<>();

    private Integer pageSize = 10000;

    public void setPageNum(Integer pageNum){
        local.set(pageNum);
    }

    public Integer getPageNum(){
       return local.get();
    }

    public void removePage(){
        local.remove();
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public void setPageSize(Integer pageSize) {
        if (pageSize == null){
            return;
        }
        this.pageSize = pageSize;
    }

    @Override
    public T accept() {
        return andThen();
    }
}

Consumer

/**
 * @authar  liuWei
 * 通用函数参数
 * @param <T>
 */
public interface Consumer<T>{
    /**
     * 方法实现
     * @return T
     */
    T andThen();

    /**
     * 方法执行
     * @return T
     */
    T accept();
}

SpringBeanUtil

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * @authar liuWei
 * @ @date 2024/2/2
 */
@Component
public class SpringBeanUtil  implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    public static Object getBean(String beanName) {
        return applicationContext.getBean(beanName);
    }

    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }

    public static <T> T getBean(String beanName, Class<T> clazz) {
        return applicationContext.getBean(beanName, clazz);
    }

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        if (SpringBeanUtil.applicationContext == null){
            SpringBeanUtil.applicationContext = applicationContext;
        }
    }
}

3、使用

ExcelWriter<Object> writer = ExcelWriter.create();
try {
    writer.setResponse(response,"测试")
            .setHead(ListUtils.newArrayList("测试"))
            .builder()
            .multiThread(436L)
            .writer(new Query<List<List<Object>>>() {
                @Override
                public List<List<Object>> andThen() {
                    List<Object> data=new ArrayList<>();
                    Date date = new Date();
                    data.add(date);
                    List<List<Object>> res = new ArrayList<>();
                    res.add(data);
                    return res;
                }
            });
} catch (Exception e) {
    try {
        ExcelWriter.error(response,e.getMessage());
    } catch (IOException ex) {
        throw new RuntimeException(ex);
    }
    throw new RuntimeException(e);
}finally {
    writer.close();
}