一、Java 通过hutool工具类ExcelWriter 导出

运用到多线程分页查询

这个采用的是Java的utool工具类ExcelWriter 导出
踩过一些坑，尽量用一条sql 将所有数据查询出来，否则再循环时查询会随着表数据的增大查询速度会成倍增加，所以
建议用一条sql把查询出结果。实测21列1.1w 多条数据查询 4067ms左右。
还可以进一步优化。
1.大量数据导出，先调整一下前端请求的response的超时时间 timeout: 1000 * 60 * 10 // 调整个10分钟
2.直接上Java代码 
注：page 和Ipage 主要是用到分页，当前页数和大小，自定义分页查询;
	每个sheet的导入数据list容易数据过大，请自行优化，后期有时间再做这个优化

controller 代码：

  /**
     * 导出用户信息
     */
    @GetMapping("/exportUser")
    public void exportUser(UserManagerReqParam user, HttpServletResponse response){
        // 开始时间
        long start = System.currentTimeMillis();
        PageParam<User> userPageParam = new PageParam<>();
        userPageParam.setCurrent(1);
        userPageParam.setSize(10L);
        IPage<UserManagerParam> userPage =  userService.getUserInfoPage(userPageParam,user);
        // 总共有多少条数据
        Long total = userPage.getTotal();
        // 用户有很多，考虑2000条以上数据的导出 一个之多104w 行数据
        Long rowMaxCount = 500000L;
        // 每一次查询条数
        Long eachCount = 1000L;
        // 这里不用PageParam<User> ，为了方便自由调整查询条数
        Page<User> pages = new Page<>();
        pages.setCurrent(1);

        // 控制list 大小
        Long listSize = 2000L;
        // list 分片的数量
        int listNums = getPageSize(total,listSize);
        List<UserManagerParam> list = new ArrayList<>();

        String filepath = "";
        // 查询记录数
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getBigWriter();
        String fileName = "用户信息表.xls";
        if (total<=rowMaxCount){
            if (total<= eachCount) {
                pages.setSize(total);
                userPage = userService.getUserInfoPage(pages, user);
                exportExcel(userPage.getRecords(), 1, 1, response,writer);
                // 导出
                PoiExcelUtil.writeExcel(fileName,writer,response);
            } else {
                // 开启多线程查询，每eachCount(100)行数据为一个线程开始
                int pageSize = getPageSize(total, eachCount);
//                ExecutorService execservice = new ThreadPoolExecutor(4,10,200L, TimeUnit.MILLISECONDS, new ArrayBlockingQueue(10));
                ExecutorService execservice =Executors.newFixedThreadPool(15);
                try {
                    List<Callable<List<UserManagerParam>>> tasks = new ArrayList<Callable<List<UserManagerParam>>>();
                    for (int i = 1; i <= pageSize; i++) {
                        Page<User> pagesIndex = new Page<>();
                        pagesIndex.setCurrent(i);
                        pagesIndex.setSize(eachCount);
                        Callable<List<UserManagerParam>> task = new AnalysisSalseTask(userService, user, pagesIndex);
                        tasks.add(task);
                    }
                    List<Future<List<UserManagerParam>>> futures = execservice.invokeAll(tasks);
                    if (futures != null && futures.size() > 0) {
                        for (Future<List<UserManagerParam>> future : futures) {
                            list.addAll(future.get());
                        }
                    }
                    execservice.shutdown();
                    tasks.clear();
                    long end = System.currentTimeMillis();
                    System.out.println("线程查询数据用时:"+(end-start)+"ms");
                } catch (Exception e) {
                    System.out.println("多线程查询异常");
                }
                exportExcel(list, 1, 0, response, writer);
                list.clear();
                // 导出
                PoiExcelUtil.writeExcel(fileName,writer,response);
            }
        } else {
            // 分多少个 sheet
            int pageSize = getPageSize(total,rowMaxCount);
            eachCount = 1000L;
            for (int i = 1; i <= pageSize; i++) {
                list.clear();
                int size = getPageSize(rowMaxCount, eachCount);
                try {
                    ExecutorService execservice = new ThreadPoolExecutor(4, 10, 200L, TimeUnit.MILLISECONDS, new ArrayBlockingQueue(10));
                    List<Callable<List<UserManagerParam>>> tasks = new ArrayList<Callable<List<UserManagerParam>>>();
                    for (int j = 1; j <= size; j++) {
                        Page<User> pagesIndex = new Page<>();
                        pagesIndex.setSize(eachCount);
                        pagesIndex.setCurrent((i-1) * size + j);
                        Callable<List<UserManagerParam>> task = new AnalysisSalseTask(userService, user, pagesIndex);
                        tasks.add(task);
                    }
                    List<Future<List<UserManagerParam>>> futures = execservice.invokeAll(tasks);
                    if (Objects.nonNull(futures) && futures.size() > 0) {
                        for (Future<List<UserManagerParam>> future : futures) {
                            list.addAll(future.get());
                        }
                    }
                    tasks.clear();
                    execservice.shutdown();
                    long end = System.currentTimeMillis();
                    System.out.println("线程查询数据用时:"+(end-start)+"ms");
                } catch (Exception e) {
                    System.out.println("多线程查询异常");
                }
                // 序号 (i-1) * rowMaxCount + 1
                int rowStart = new BigDecimal(i - 1).multiply(new BigDecimal(rowMaxCount)).add(new BigDecimal(1)).intValue();
                //方法1： 导出到一个临时文件，
                // 然后合并小于50W行的Excel到100W行为一个Excel文件,此处跳过，直接是100W行为一个文件
                // 然后将100W的每一个Excel文件进行压缩

                // 方法2：分多个sheet 导出
                // 此时达到 rowMaxCount 行数据 list 导出到excel
                exportExcel(list, rowStart, i-1, response, writer);
                list.clear();
            }
            // 导出
            PoiExcelUtil.writeExcel(fileName,writer,response);
        }
    }
    private int getPageSize(Long total, Long eachCount) {
        int pageSize = new BigDecimal(total).divide(new BigDecimal(eachCount),1).intValue();
        int mod = new BigDecimal(total).divideAndRemainder(new BigDecimal(eachCount))[1].intValue();
        if (mod > 0) {
            pageSize = pageSize + 1;
//            pageSize = new BigDecimal(pageSize).add(new BigDecimal(1)).intValue();
        }
        return pageSize;
    }

 /**
     * @param list 导出的数据
     * @param rowStart 开始的行数
     * @param pages 一共有多少页
     */
    private void exportExcel(List<UserManagerParam> list,int rowStart,int pages, HttpServletResponse response,ExcelWriter writer) {

        // 商品导出or模板
        List<String> headerList;
        String[] header = {
                "序号", "用户昵称", "用户名称", "联系电话", "会员等级",
                "会员类型", "用户积分", "状态","消费金额", "实付金额",
                "消费次数", "平均折扣", "充值金额","充值次数", "退款金额",
                "退款次数",  "累计积分","当前余额", "累计余额",
                "注册时间", "最近消费时间"
        };
        headerList = Arrays.asList(header);
        writer.setSheet(pages);
        Sheet sheet = writer.getSheet();
        writer.merge(headerList.size() - 1, "用户信息表");
        writer.writeRow(headerList);
        for (int i = 0; i < headerList.size(); i++) {
            if (i==19 || i==20) {
                sheet.setColumnWidth(i, 30 * 256);
            } else {
                sheet.setColumnWidth(i, 20 * 256);
            }
        }
        // 如果要导出的数据为空，导出一个模板
        if (CollectionUtils.isEmpty(list)) {
            PoiExcelUtil.writeExcel(response, writer);
            return;
        }
        int row = rowStart;
        int size = list.size();
        for (UserManagerParam param : list) {
            int firstRow = row + 1;
            int lastRow = row + 1;
            int col = -1;
            // 序号
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,rowStart++);
            // 用户昵称
            String nickName = Objects.isNull(param.getNickName())?"":param.getNickName();
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,nickName);
            // 用户名称
            String realName = Objects.isNull(param.getRealName())?"":param.getRealName();
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,realName);
            // 联系电话
            String userMobile = Objects.isNull(param.getUserMobile())?"":param.getUserMobile();
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,userMobile);
            // 会员等级
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getLevelName());
            // 会员类型
            String levelType = param.getLevelType() == 0 ? "普通会员": "付费会员";
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,levelType);
            // 用户积分
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getScore());
            // 状态
            String status = param.getStatus() == 0 ? "禁用": "正常";
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,status);
            // 消费金额
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getConsAmount());
            // 实付金额
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getActualAmount());
            // 消费次数
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getConsTimes());
            // 平均折扣
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getAverDiscount());

            // 充值金额
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getRechargeAmount());
            // 充值次数
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getRechargeTimes());

            // 退款金额
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getAfterSaleAmount());
            // 退款次数
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getAfterSaleTimes());
            // 当前积分
//            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getCurrentScore());
            // 累计积分
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getSumScore());
            // 当前余额
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getCurrentBalance());
            // 累计余额
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,param.getSumBalance());
            // 注册时间
            String regTime = "";
            if (Objects.nonNull(param.getUserRegtime())){
                regTime = DateUtil.format(param.getUserRegtime(),"yyyy-MM-dd HH:mm:ss");
            }
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,regTime);
            // 最近消费时间
            String recTime = "";
            if (Objects.nonNull(param.getUserRegtime())){
                recTime = DateUtil.format(param.getReConsTime(),"yyyy-MM-dd HH:mm:ss");
            }
            PoiExcelUtil.mergeIfNeed(writer, firstRow, lastRow, ++col, col,recTime);
            row++;
        }
    }

多线程代码：
AnalysisSalseTask.java

@Slf4j
public class AnalysisSalseTask implements Callable<List<UserManagerParam>> {

    private UserService userService;
    private UserManagerReqParam user;
    private Page<User> pages;

    public AnalysisSalseTask(UserService userService, UserManagerReqParam user, Page<User> pages) {
        this.userService = userService;
        this.user = user;
        this.pages = pages;
    }

    @Override
    public List<UserManagerParam> call() throws Exception {
        IPage<UserManagerParam> userPage =  userService.getUserInfoPage(pages,user);
        return userPage.getRecords();
    }
}

针对 ExcelWriter 的excel操作工具类：
PoiExcelUtil.java

package com.XXX.util;

import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

import cn.hutool.core.io.IORuntimeException;
import cn.hutool.poi.excel.BigExcelWriter;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

/**
 * 功能: poi导出excel工具类
 */
public class PoiExcelUtil {

    /**
     * 合并单元格处理,获取合并行
     *
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public static List<CellRangeAddress> getCombineCell(Sheet sheet) {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        // 获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        // 遍历所有的合并单元格
        for (int i = 0; i < sheetmergerCount; i++) {
            // 获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    public static int getRowNum(List<CellRangeAddress> listCombineCell, Cell cell, Sheet sheet) {
        int xr = 0;
        int firstC = 0;
        int lastC = 0;
        int firstR = 0;
        int lastR = 0;
        for (CellRangeAddress ca : listCombineCell) {
            // 获得合并单元格的起始行, 结束行, 起始列, 结束列
            firstC = ca.getFirstColumn();
            lastC = ca.getLastColumn();
            firstR = ca.getFirstRow();
            lastR = ca.getLastRow();
            if (cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR) {
                if (cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC) {
                    xr = lastR;
                }
            }

        }
        return xr;

    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param sheet
     * @param row
     *            行下标
     * @param column
     *            列下标
     * @return
     */
    public static boolean isMergedRegion(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

    /**
     * 如果需要合并的话，就合并
     */
    public static void mergeIfNeed(ExcelWriter writer, int firstRow, int lastRow, int firstColumn, int lastColumn, Object content) {
        if (lastRow - firstRow > 0 || lastColumn - firstColumn > 0) {
            writer.merge(firstRow, lastRow, firstColumn, lastColumn, content, false);
        } else {
            writer.writeCellValue(firstColumn, firstRow, content);
        }

    }
    public static void writeExcel(HttpServletResponse response, ExcelWriter writer) {
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition", "attachment;filename=1.xls");

        ServletOutputStream servletOutputStream = null;
        try {
            servletOutputStream = response.getOutputStream();
            writer.flush(servletOutputStream);
            servletOutputStream.flush();
        } catch (IORuntimeException | IOException e) {
            e.printStackTrace();
        } finally {
            writer.close();
            try {
                if (servletOutputStream != null) {
                    servletOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    public static void writeExcel(String filename, ExcelWriter writer, HttpServletResponse response){
        ServletOutputStream ouputStream = null;
        try {
            filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Content-disposition", "attachment;filename=" + filename);
            ouputStream = response.getOutputStream();
            writer.flush(ouputStream);
            ouputStream.flush();
            Runtime.getRuntime().gc();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            writer.close();
            if (null != ouputStream) {
                try {
                    ouputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}