@ApiOperation(value = "數(shù)據(jù)-excel導(dǎo)出",notes = "首次調(diào)用會(huì)返回一個(gè)processId標(biāo)識(shí),查詢進(jìn)度攜帶標(biāo)識(shí)")
    @GetMapping("/export")
    public ResultData exportHtMeta(String processId){
        HtMetaExcelProcessVo htMetaExcelProcessVo;
        if (!StringUtils.hasLength(processId)){
            try {
                htMetaExcelProcessVo=htMetaInfoService.exportHtMetaCopyModule(processId);
            } catch (Exception e) {
                throw new ExcelHandlerException("導(dǎo)入失敗:"+e.getMessage());
            }
        }else {
            Cache cache = cacheManager.getCache(HtMetaConstants.EXPORT_PREFIX);
            htMetaExcelProcessVo=cache.get(processId,HtMetaExcelProcessVo.class);
            if (htMetaExcelProcessVo==null){
                return new ResultData(ErrorCodeEnum.NOT_FOUND_DATA.getCode(),"該導(dǎo)入uid,沒有對應(yīng)數(shù)據(jù)");
            }
            if (htMetaExcelProcessVo.getCurProcess().equals(htMetaExcelProcessVo.getTotalProcess())){
                htMetaExcelProcessVo.setImportStatus(HtMetaConstants.EXCEL_PROCESS_SUCCESS);
                htMetaExcelProcessVo.setMsg("導(dǎo)出成功");
            }
        }
        return new ResultData(htMetaExcelProcessVo);
    }

/**
     * 導(dǎo)出批次大小，每個(gè)sheet導(dǎo)出模塊大小
     */
    private static final Integer SPLIT_SIZE=100;
    @Override
    public HtMetaExcelProcessVo exportHtMetaCopyModule(String exportKey) throws Exception{
        HtMetaExcelProcessVo excelProcessVo;
        Cache cache = cacheManager.getCache(HtMetaConstants.EXPORT_PREFIX);
        if (cache==null){
            throw new ExcelHandlerException("ehcahe 緩存配置異常");
        }
        if (StringUtils.hasLength(exportKey)){
            //檢查是否存在已導(dǎo)出
            if (cache.get(exportKey)!=null){
                return cache.get(exportKey,HtMetaExcelProcessVo.class);
            }
        }else {
            exportKey = UUID.randomUUID().toString().replace("-", "");
        }
        ClassPathResource resource = new ClassPathResource("excel-template/導(dǎo)入模板.xlsx");
        String exportPath = new File("").getAbsolutePath() + File.separator + "ht-meta-export";
        if (!new File(exportPath).exists()){
            boolean mkdir = new File(exportPath).mkdir();
            log.info("導(dǎo)出目錄創(chuàng)建：{}",mkdir);
        }
        File exportFile = new File(exportPath+File.separator+exportKey+".xlsx");
        log.info("華泰-元數(shù)據(jù),導(dǎo)出文件：{}",exportFile.getAbsolutePath());
        //按數(shù)據(jù)生成-臨時(shí)導(dǎo)入模板
        File tmpExportTemplate = null;
        ExcelWriter excelWriter =null;
        try {
            tmpExportTemplate = File.createTempFile("temp", ".xlsx");
            List<HtMetaClusterInfoVo> list = htMetaClusterInfoMapper.clusterList(new HtMetaClusterQo());
            log.info("導(dǎo)出數(shù)據(jù)條數(shù)：{}",list.size());
            int sheetSize = (list.size() / SPLIT_SIZE);
            if (sheetSize==1){
                excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_ING, 0, 4, "正在導(dǎo)出");
            }else {
                excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_ING, 0, 4+sheetSize, "正在導(dǎo)出");
            }
            excelProcessVo.setProcessId(exportKey);
            cache.put(exportKey,excelProcessVo);
            //階段1
            refreshProcess(cache,exportKey);
            //單條導(dǎo)出
            if (list.size()==1){
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                excelWriter = EasyExcel.write(exportFile).withTemplate(resource.getInputStream()).build();
                WriteSheet writeSheet = EasyExcel.writerSheet(0).build();
                HtMetaClusterInfoVo e = list.get(0);
                excelWriter.fill(new FillWrapper("data0", Collections.singletonList(e)), fillConfig, writeSheet);
                List<HtMetaNodeInfoVo> nodeInfoVos = e.getNodeInfoVos();
                List<HtMetaBsInfoVo> bsInfoVos = e.getBsInfoVos();
                excelWriter.fill(new FillWrapper("data1", nodeInfoVos), fillConfig, writeSheet);
                excelWriter.fill(new FillWrapper("data2", bsInfoVos), fillConfig, writeSheet);
                excelWriter.finish();
                excelProcessVo = new HtMetaExcelProcessVo(HtMetaConstants.EXCEL_PROCESS_SUCCESS, 4, 4, "導(dǎo)出成功");
                excelProcessVo.setProcessId(exportKey);
                cache.put(exportKey,excelProcessVo);
                return excelProcessVo;
            }
            int overSize;
            if (sheetSize>1){
                //剩余數(shù)量
                overSize = list.size() - (sheetSize * SPLIT_SIZE);
                log.info("剩余數(shù)據(jù)條數(shù)：{}",overSize);
            } else {
                overSize = 0;
            }
            log.info("開始生成數(shù)據(jù)導(dǎo)出模板");
            List<List<String>> fillKeys = HtMetaExcelUtil.copyMultiRow(6, 17,
                    12,list.size() - 1,
                    resource.getInputStream(), tmpExportTemplate);
            log.info("生成結(jié)束");
            //階段2
            refreshProcess(cache,exportKey);
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter = EasyExcel.write(exportFile).withTemplate(tmpExportTemplate).build();
            //階段3
            refreshProcess(cache,exportKey);
            String finalExportKey = exportKey;
            ExcelWriter finalExcelWriter = excelWriter;
            File finalTmpExportTemplate = tmpExportTemplate;
            CompletableFuture.runAsync(()->{
                try {
                    fiilTemplateExcel(finalExportKey, cache, finalExcelWriter, list, sheetSize, overSize, fillKeys, fillConfig);
                    log.info("填充結(jié)束");
                }finally {
                    if (finalExcelWriter!=null){
                        finalExcelWriter.finish();
                    }
                    boolean delete = finalTmpExportTemplate.delete();
                    log.info("臨時(shí)導(dǎo)入模板刪除: {}",delete);
                }
            });
        } catch (IOException e) {
            log.info("導(dǎo)出失敗");
            throw e;
        }
        return excelProcessVo;
    }

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.tomcat.util.http.fileupload.IOUtils;
import org.springframework.util.StringUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.util.Arrays;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
/**
 * @author xiaoshu
 * @description
 * @date 2023年09月01日 23:34
 */
@Slf4j
public class HtMetaExcelUtil {
    /**
     * 導(dǎo)出批次大小，每個(gè)sheet導(dǎo)出模塊大小
     */
    private static final Integer SPLIT_SIZE=100;
    /**
     * poi對excel進(jìn)行 多行模塊復(fù)制,并替換 Fill填充前綴
     *
     * @param startRowIndex    模塊-起始行index，excel標(biāo)記行號(hào)-1
     * @param endRowIndex      模塊-結(jié)束行
     * @param moduleRowSize    模塊行數(shù)
     * @param copyCount        復(fù)制次數(shù)
     * @param sourceFileStream 源文件流
     * @param outFile          輸出文件
     * @return List<List<String>> 填充fillKey列表
     */
    public static List<List<String>> copyMultiRow(int startRowIndex, int endRowIndex,
                                             int moduleRowSize, int copyCount,
                                             InputStream sourceFileStream,
                                             File outFile
    ) {
        File tempFile =null;
        int sheetSize=0;
        if (copyCount>SPLIT_SIZE){
            sheetSize= (copyCount + 1) / SPLIT_SIZE;
            log.info("復(fù)制sheet數(shù)量：{}",sheetSize);
            copyCount=SPLIT_SIZE-1;
        }
        //填充key列表
        List<List<String>> fillKeys = new LinkedList<>();
        //添加填充模板，默認(rèn)key
        fillKeys.add(Arrays.asList("data0","data0","data1","data2"));
        //復(fù)制起始行
        int startRow = startRowIndex;
        //復(fù)制結(jié)束行
        int endRow = endRowIndex;
        //目標(biāo)起始行
        int targetRow = endRow + 1;
        List<Integer> startRows = new LinkedList<>();
        startRows.add(startRow);
        List<Integer> endRows = new LinkedList<>();
        endRows.add(endRow);
        List<Integer> copyStartRows = new LinkedList<>();
        copyStartRows.add(targetRow);
        XSSFWorkbook workbook = null;
        XSSFWorkbook xssfWorkbook = null;
        try {
            workbook = new XSSFWorkbook(sourceFileStream);
            for (int i = 1; i < copyCount; i++) {
                startRow = startRow + moduleRowSize;
                startRows.add(startRow);
                endRow = endRow + moduleRowSize;
                endRows.add(endRow);
                targetRow = endRow + 1;
                copyStartRows.add(targetRow);
            }
            XSSFSheet sheet = workbook.getSheetAt(0);
            CellCopyPolicy policy = new CellCopyPolicy();
            policy.setCopyCellFormula(false);
            policy.setMergeHyperlink(false);
            policy.setMergeHyperlink(false);
            for (int i = 0; i < copyCount; i++) {
                sheet.copyRows(startRows.get(i), endRows.get(i), copyStartRows.get(i), policy);
                setRowsBorder(workbook,sheet,copyStartRows.get(i)+5,copyStartRows.get(i)+7);
                setRowsBorder(workbook,sheet,copyStartRows.get(i)+9,copyStartRows.get(i)+12);
            }
            //生成臨時(shí)模板文件
            tempFile = File.createTempFile("temp", ".xlsx");
            //寫入復(fù)制模塊后的文件
            workbook.write(Files.newOutputStream(tempFile.toPath()));
            //移除模板本身索引
            startRows.remove(0);
            //添加最后一列索引
            if (copyCount!=1){
                Integer lastRow = startRows.get(startRows.size() - 1);
                startRows.add(lastRow + moduleRowSize);
            }
            //替換填充前綴
            xssfWorkbook = new XSSFWorkbook(tempFile);
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            int initIndex = 3;
            if (copyCount!=1){
                for (Integer row : startRows) {
                    //每行對應(yīng)填充key
                    List<String> fillKey = new LinkedList<>();
                    XSSFRow row1 = xssfSheet.getRow(row);
                    replaceRowValue(row1, "data0", "data" + initIndex);
                    fillKey.add("data" + initIndex);
                    XSSFRow row2 = xssfSheet.getRow(row + 2);
                    replaceRowValue(row2, "data0", "data" + initIndex);
                    fillKey.add("data" + initIndex);
                    XSSFRow row3 = xssfSheet.getRow(row + 4);
                    replaceRowValue(row3, "data1", "data" + (initIndex + 1));
                    fillKey.add("data" + (initIndex + 1));
                    XSSFRow row4 = xssfSheet.getRow(row + 8);
                    replaceRowValue(row4, "data2", "data" + (initIndex + 2));
                    fillKey.add("data" + (initIndex + 2));
                    initIndex = initIndex + 3;
                    fillKeys.add(fillKey);
                }
            }else {
                //每行對應(yīng)填充key
                List<String> fillKey = new LinkedList<>();
                int row=endRowIndex+1;
                XSSFRow row1 = xssfSheet.getRow(row);
                replaceRowValue(row1, "data0", "data" + initIndex);
                fillKey.add("data" + initIndex);
                XSSFRow row2 = xssfSheet.getRow(row + 2);
                replaceRowValue(row2, "data0", "data" + initIndex);
                fillKey.add("data" + initIndex);
                XSSFRow row3 = xssfSheet.getRow(row + 4);
                replaceRowValue(row3, "data1", "data" + (initIndex + 1));
                fillKey.add("data" + (initIndex + 1));
                XSSFRow row4 = xssfSheet.getRow(row + 8);
                replaceRowValue(row4, "data2", "data" + (initIndex + 2));
                fillKey.add("data" + (initIndex + 2));
                fillKeys.add(fillKey);
            }
            if (sheetSize>=1){
                for (int i = 0; i < sheetSize; i++) {
                    xssfWorkbook.cloneSheet(0,"sheet" + (i+1));
                }
            }
            //替換填充前綴->輸出文件
            xssfWorkbook.write(Files.newOutputStream(outFile.toPath()));
            return fillKeys;
        } catch (IOException | InvalidFormatException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (xssfWorkbook != null) {
                    xssfWorkbook.close();
                }
                if (workbook != null) {
                    workbook.close();
                }
                if (sourceFileStream != null) {
                    sourceFileStream.close();
                }
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            if (tempFile!=null){
                boolean delete = tempFile.delete();
                log.info("臨時(shí)模板刪除: {}",delete);
            }
        }
    }
    //添加邊框
    public static void setRowsBorder(XSSFWorkbook xssfWorkbook,XSSFSheet sheet
            ,int startRow,int endRow){
        // 創(chuàng)建單元格樣式
        XSSFCellStyle style = xssfWorkbook.createCellStyle();
        //上下左右邊框
        // 設(shè)置邊框樣式為實(shí)線
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        // 設(shè)置邊框顏色為黑色
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        for (int i = startRow; i < endRow; i++) {
            XSSFRow row = sheet.getRow(i);
            row.setRowStyle(style);
        }
    }
    /**
     * 行值替換
     * @param row      替換行
     * @param oldValue 過去值
     * @param newValue 替換值
     */
    public static void replaceRowValue(XSSFRow row, String oldValue, String newValue) {
        Iterator<Cell> cellIterator = row.cellIterator();
        cellIterator.forEachRemaining(e -> {
            if (StringUtils.hasLength(e.getStringCellValue())) {
                String cellValue = e.getStringCellValue();
                cellValue = cellValue.replace(oldValue, newValue);
                e.setCellValue(cellValue);
            }
        });
    }
    /**
     * 獲取導(dǎo)出文件
     * @param processId 進(jìn)度id
     * @return String - 文件路徑
     */
    public static String getHtExportFile(String processId) {
        File file = new File("");
        return  file.getAbsolutePath() + File.separator + "ht-meta-export" + File.separator + processId + ".xlsx";
    }
    /**
     * 瀏覽器文件下載
     * @param targetFile  目標(biāo)文件
     * @param response    response
     */
    public static void browserDownLoad(File targetFile, String downLoadName, HttpServletResponse response){
        OutputStream out = null;
        InputStream in = null;
        try {
            response.reset();
            response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(downLoadName, "UTF-8"));
            response.addHeader("Content-Length", "" + targetFile.length());
            response.setContentType("application/vnd.ms-excel");
            out = new BufferedOutputStream(response.getOutputStream());
            in = new BufferedInputStream(new FileInputStream(targetFile));
            IOUtils.copy(in, out);
            out.flush();
        } catch (Exception e) {
        } finally {
            IOUtils.closeQuietly(in);
            IOUtils.closeQuietly(out);
        }
    }
}