poi异步导出、excel异步导出-通用-仅限单组数据导出

 

支持单组数据（List<T>）异步导出，比如1000条数据，可以按100条数据一个sheet的形式，去异步写入10个sheet中，每个sheet有100条数据

createPageExcel方法是通过HttpServletResponse直接响应给前端

createPageExcelUrl是生成一个File，然后上传到OSS，获取到Url返回给前端，此方法中有redis和oss上传、自定义异常处理、根据枚举类型查询导出的excel数据的类需要自行修改

package com.yzf.costcontrol.core.excel;

import cn.hutool.core.thread.NamedThreadFactory;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.FileUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

/**
 * 创建excel
 * @author wjce
 * @date 2024/5/11 11:14
 */
@Component
@Slf4j
public class ExcelCreator<T extends ExcelBaseVO> {

    @Resource
    private FileInfoFeignClient fileInfoFeignClient;
    @Resource
    private RedisService redisService;
    @Resource
    private ExcelDataBuilder excelDataBuilder;

    /**
      * 根据单组数据异步创建分页sheet
      * Author: wjce
      * Date:   2024/5/11 11:21
      * @params [response, excelSheet, dataList, fileName] ExcelSheet实现类，单组数据，文件名
    */
    public void createPageExcel(HttpServletResponse response, Class<ExcelSheet> excelSheet, ExcelEnum excelEnum, JSONObject queryParam, String fileName, Integer batchSize){
        SXSSFWorkbook wb = new SXSSFWorkbook();
        wb.setCompressTempFiles(true);
        List<CompletableFuture<Void>> futures = new ArrayList<>();
        try{
            long start = System.currentTimeMillis();
            fileName = URLEncoder.encode(fileName, "UTF-8");
            OutputStream out = response.getOutputStream();
            addSheetData(excelSheet, excelDataBuilder.dataListBuild(excelEnum, queryParam), batchSize, wb, futures);
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-disposition", "attachment;filename="+fileName+";"+"filename*=utf-8''"+fileName);
            wb.write(out);
            out.flush();
            long end = System.currentTimeMillis();
            log.info("excel写入sheet耗时：{}", end-start);
        }catch (Exception e){
            log.error("创建excel失败", e);
        }finally {
            wb.dispose();
        }
    }

    /**
      * 根据单组数据异步创建分页sheet并返回oss文件地址
      * Author: wjce
      * Date:   2024/5/11 16:59
      * @params [excelSheet, queryParam, fileName, batchSize, key, verify, delete]
     * ExcelSheet实现类，excelEnum excel数据查询枚举, excel数据查询参数，文件名，每个sheet页行数，存入redis的key, 校验同时导出, 导出后是否删除缓存中的url->非定时任务调用设置true
    */
    public String createPageExcelUrl(Class<ExcelSheet> excelSheet, ExcelEnum excelEnum, JSONObject queryParam, String fileName, Integer batchSize, String key, boolean verify, boolean delete){
        Object urlObj = RedisUtil.getValue(key);
        if(urlObj != null){
            return urlObj.toString();
        }

        String verifyKey = key + ":verify";
        if(verify) {
            verifyExportIsBeing(verifyKey);
        }
        SXSSFWorkbook wb = new SXSSFWorkbook();
        wb.setCompressTempFiles(true);
        String url = null;
        List<CompletableFuture<Void>> futures = new ArrayList<>();
        FileOutputStream out = null;
        File file = new File(fileName);
        try{
            long start = System.currentTimeMillis();
            if (!file.exists()) {
                file.createNewFile();
            }

            out = new FileOutputStream(file);
            List<T> dataList = excelDataBuilder.dataListBuild(excelEnum, queryParam);
            addSheetData(excelSheet, dataList, batchSize, wb, futures);
            wb.write(out);
            out.close();

            long end = System.currentTimeMillis();
            log.info("excel写入sheet耗时：{}", end-start);
            url = getExcelUrl(fileName);
            redisService.setExportKey(key, url);
        }catch (Exception e){
            log.error("创建excel失败", e);
            try {
                out.close();
                FileUtils.forceDelete(file);
            }catch (Exception exception){}
        }finally {
            wb.dispose();
            RedisUtil.delKey(verifyKey);
            if(delete){
                RedisUtil.delKey(key);
            }
        }

        return url;
    }

    /**
      * excel上传oss并返回链接
      * Author: wjce
      * Date:   2024/5/11 16:29
      * @params [fileName]
    */
    public String getExcelUrl(String fileName){
        try {
            File file = new File(fileName);
            if(!file.exists()){
                log.error("writeExcel：{}文件不存在", fileName);
                return "";
            }
            InputStream inputStream = Files.newInputStream(file.toPath());
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            byte[] bytes = new byte[1024];
            int len = 0;
            while ( (len = inputStream.read(bytes)) != -1 ){
                byteArrayOutputStream.write(bytes, 0, len);
            }

            inputStream.close();
            String url = fileInfoFeignClient.uploadBytes(5, fileName, byteArrayOutputStream.toByteArray());
            try {
                FileUtils.forceDelete(file);
            }catch (Exception e){
                log.error("删除excel失败");
            }

            return url;
        }catch (Exception e){
            log.error("writeExcel：", e);
        }
        return null;
    }

    /**
      * 校验excel是否正在导出，防止并行导出
      * Author: wjce
      * Date:   2024/5/11 16:29
      * @params [verifyKey]
    */
    public void verifyExportIsBeing(String verifyKey){
        Object val = RedisUtil.getValue(verifyKey);
        if(val != null){
            throw new CostControlException(CostControlErrorCode.PROCESS_PROJECT_QUOTA_SYNC_EXPORT_REPEAT);
        }

        redisService.setExportKey(verifyKey, "1");
    }

    /**
      * 添加sheet页数据
      * Author: wjce
      * Date:   2024/5/11 16:57
      * @params [excelSheet, dataList, batchSize, wb, futures] ExcelSheet实现类，单组数据，每个sheet页行数
    */
    private void addSheetData(Class<ExcelSheet> excelSheet, List<T> dataList, Integer batchSize, SXSSFWorkbook wb, List<CompletableFuture<Void>> futures) throws InstantiationException, IllegalAccessException, java.lang.reflect.InvocationTargetException, NoSuchMethodException {
        batchSize = batchSize == null ? 10000 : batchSize;
        int totalSize = dataList.size();
        int batchCount = (totalSize + batchSize - 1) / batchSize;
        ExecutorService pool = Executors.newFixedThreadPool(batchCount+1, new NamedThreadFactory("ExcelCreator", false));
        for (int i = 0; i < batchCount; i++) {
            int startIndex = i * batchSize;
            int endIndex = Math.min(startIndex + batchSize, totalSize);
            List<T> subList = dataList.subList(startIndex, endIndex);
            SXSSFSheet dataSheet = wb.createSheet(String.format("第%s页", i + 1));
            ExcelSheet sheet = excelSheet.getDeclaredConstructor().newInstance();
            CompletableFuture<Void> future = CompletableFuture.runAsync(() -> {
                sheet.set(wb, dataSheet, subList);
            }, pool);
            futures.add(future);
        }

        CompletableFuture<Void> allOf = CompletableFuture.allOf(futures.toArray(new CompletableFuture[0]));
        allOf.join();
        pool.shutdown();
    }
}

 

ExcelSheet

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import java.util.Map;

/**
 * @author wjc
 * @date 2021/7/21 9:43
 */
public interface ExcelSheet {

    /**
     * @author wjc
     * @Description 设置excelSXSSFSheet内容
     * @Date 2021/7/21 9:43
     * @Param [workbook, SXSSFSheet]
     **/
    int set(SXSSFWorkbook workbook, SXSSFSheet sheet, Object param);

    /**
     * @author wjc
     * @Description 设置合并后的单元格边框
     * @Date 2021/7/23 10:54
     * @Param [rangeAddress, SXSSFSheet]
     **/
    default void setMergeBorder(CellRangeAddress rangeAddress, SXSSFSheet sheet, BorderStyle style){
        RegionUtil.setBorderBottom(style, rangeAddress, sheet);
        RegionUtil.setBorderLeft(style, rangeAddress, sheet);
        RegionUtil.setBorderRight(style, rangeAddress, sheet);
        RegionUtil.setBorderTop(style, rangeAddress, sheet);
    }

    default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, String[] rols) {
        return getRowNum(sheet, rowNum, xssfCellStyle, rols, null);
    }

    default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, Integer commentIndex, String comment, String[] rols) {
        return getRowNum(sheet, rowNum, xssfCellStyle, commentIndex, comment, rols, null);
    }

    default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, String[] rols, Short height) {
        return getRowNum(sheet, rowNum, xssfCellStyle, rols, height, null);
    }

    default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, Integer commentIndex, String comment, String[] rols, Short height) {
        return getRowNum(sheet, rowNum, xssfCellStyle, commentIndex, comment, rols, height, null);
    }

    default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, String[] rols, Short height, Map<Integer, XSSFCellStyle> otherStyle) {
        return getRowNum(sheet, rowNum, xssfCellStyle, null, null, rols, height, otherStyle);
    }

    default int getRowNum(SXSSFSheet sheet, int rowNum, CellStyle xssfCellStyle, Integer commentIndex, String comment, String[] rols, Short height, Map<Integer, XSSFCellStyle> otherStyle) {

        SXSSFRow r1 = sheet.createRow(rowNum++);
        if (height != null) {
            r1.setHeight(height);
        }
        for (int i = 0; i < rols.length; i++) {
            SXSSFCell tempXSSFCell = r1.createCell(i);
            tempXSSFCell.setCellStyle(xssfCellStyle);
            tempXSSFCell.setCellValue(rols[i]);
            if (StringUtils.isNotBlank(comment)) {
                addComment(tempXSSFCell, comment);
            }
        }

        if(otherStyle != null && !otherStyle.isEmpty()){
            otherStyle.forEach((k,v) -> {
                SXSSFCell XSSFCell = r1.getCell(k);
                XSSFCell.setCellStyle(v);
            });
        }

        return rowNum;
    }

    default CellStyle setHeadStyle(SXSSFWorkbook workbook){
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setLocked(true);
        headerStyle.setWrapText(true);
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setBorderRight(BorderStyle.THIN);

        Font headerFont = workbook.createFont();
        headerFont.setFontName("宋体");
        headerFont.setFontHeightInPoints((short) 12);
        headerFont.setBold(true);
        headerFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        headerStyle.setFont(headerFont);

        //填充单元格
//        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//        short color = HSSFColor.HSSFColorPredefined.PALE_BLUE.getIndex();
        //设置单元格背景色
//        headerStyle.setFillForegroundColor(color);
        return headerStyle;
    }

    /**
     * 设置某列单元格的自定义格式
     *
     * @param sheet
     * @param startRowIndex 开始行
     * @param endRowIndex   结束行
     * @param columnIndex   列数
     */
    default void setCellDefinedFormat(SXSSFWorkbook workbook, SXSSFSheet sheet, int startRowIndex, int endRowIndex, int columnIndex, Short color)
    {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setLocked(true);
        style.setWrapText(false);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        if(color != null) {
            //填充单元格
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            //设置单元格背景色
            style.setFillForegroundColor(color);
        }
        for(int i = startRowIndex; i <= endRowIndex; i++) {
            Row row = sheet.getRow(i);
            if(row == null){
                break;
            }
            Cell cell = row.getCell(columnIndex);
            cell.setCellStyle(style);
        }
    }

    /**
     * 给Cell添加批注
     *
     * @param cell 单元格
     * @param value 批注内容
     */
    default void addComment(Cell cell, String value) {
        Sheet sheet = cell.getSheet();
        cell.removeCellComment();
        ClientAnchor anchor = new XSSFClientAnchor();
        // 关键修改
        anchor.setDx1(0);
        anchor.setDx2(0);
        anchor.setDy1(0);
        anchor.setDy2(0);
        anchor.setCol1(cell.getColumnIndex());
        anchor.setRow1(cell.getRowIndex());
        anchor.setCol2(cell.getColumnIndex()+5);
        anchor.setRow2(cell.getRowIndex()+5);
        // 结束
        Drawing drawing = sheet.createDrawingPatriarch();
        Comment comment = drawing.createCellComment(anchor);
        // 输入批注信息
        comment.setString(new XSSFRichTextString(value));
        // 将批注添加到单元格对象中
        cell.setCellComment(comment);
    }
}