productivity-platform/biz-base/src/test/java/com/dayou/BaseApplicationTests.java

package com.dayou;

import cn.hutool.core.lang.Console;
import cn.hutool.http.HttpRequest;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.HashMap;
import java.util.Map;

@SpringBootTest
class BaseApplicationTests {

    @Test
    void contextLoads() {

    }

    // @Test
    void hutoolPostTest(){
        //链式构建请求
        String result = HttpRequest.post("localhost:8088/api/auth/checkOaToken?token=eyJhbGciOiJIUzUxMiJ9.eyJzdWIiOiIxIiwiZXhwIjoxNzI5MzE3MzUyLCJpYXQiOjE3MjkyMzA5NTJ9.6zLI3TXlNGD2XCEMwl7vyYRDKFMRAfxhr8TaC1GEnug4eTnJZTJQRUeLBH7pymniKlQDVtrZD_ZGd359vZid7Q")
                .timeout(20000)//超时，毫秒
                .execute().body();
        Console.log(result);
    }

    /**
     * 测试使用EasyExcel填充值到带公式的excel模板
     */
    // @Test
    void testEasyExcel(){
        // 模板文件路径
        String templateFilePath = "E:\\test\\input.xlsx";
        // 输出文件路径
        String outputPath = "E:\\test\\output.xlsx";

        // 创建一个Map来存储要填充的数据
        Map<String, Object> dataMap = new HashMap<>();
        dataMap.put("a", 13.7);
        dataMap.put("b", 46.9);

        // 使用EasyExcel填充数据
        ExcelWriter excelWriter = EasyExcel.write(outputPath)
                .withTemplate(templateFilePath)
                .inMemory(true) // 启用内存模式
                .build();
        WriteSheet writeSheet = EasyExcel.writerSheet().build();
        excelWriter.fill(dataMap, writeSheet); // 填充数据

        // 由于填充后不会自动更新公式值,所以此处需要手动更新,大文件慎用(可能会导致内存溢出)
        Workbook workbook = excelWriter.writeContext().writeWorkbookHolder().getWorkbook();
        workbook.getCreationHelper().createFormulaEvaluator().evaluateAll(); // 强制计算公式

        excelWriter.finish();
    }

    /**
     * 测试使用poi合并Excel文件
     * 大概就是读取模板文件的行信息(包括数据,样式等等),再写入到目标文件中
     */
    // @Test
    void testExcelMerge(){
        String sourceExcelPath = "E:\\test\\source.xlsx"; // 源Excel文件路径
        String targetExcelPath = "E:\\test\\target.xlsx"; // 目标Excel文件路径
        int targetSheetIndex = 0; // 目标sheet索引
        int targetRowNum = 4; // 目标行索引

        // 加载源Excel工作簿
        try (InputStream inputStream = Files.newInputStream(Paths.get(sourceExcelPath))) {
            Workbook sourceWorkbook = new XSSFWorkbook(inputStream);
            Sheet sourceSheet = sourceWorkbook.getSheetAt(0); // 读取第一个sheet

            // 打开已存在的Excel工作簿
            Workbook targetWorkbook = WorkbookFactory.create(Files.newInputStream(Paths.get(targetExcelPath)));
            Sheet targetSheet = targetWorkbook.getSheetAt(targetSheetIndex); // 获取目标sheet

            // 复制样式映射
            Map<Short, Short> styleMap = copyCellStyle(sourceWorkbook, targetWorkbook);

            // 复制行和单元格
            for (Row sourceRow : sourceSheet) {
                Row targetRow = targetSheet.createRow(targetRowNum + sourceRow.getRowNum());
                for (Cell sourceCell : sourceRow) {
                    Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
                    copyCell(sourceCell, targetCell, targetWorkbook, styleMap);
                }
            }

            // 复制数据验证
            copyDataValidations(sourceSheet, targetSheet);

            // 保存目标Excel工作簿
            try (FileOutputStream outputStream = new FileOutputStream(targetExcelPath)) {
                targetWorkbook.write(outputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 复制单元格样式
     * @param srcBook
     * @param desBook
     * @return
     */
    private static Map<Short, Short> copyCellStyle(Workbook srcBook, Workbook desBook) {
        Map<Short, Short> styleMap = new HashMap<>();
        for (short i = 0; i < srcBook.getNumCellStyles(); i++) {
            CellStyle srcStyle = srcBook.getCellStyleAt(i);
            CellStyle desStyle = desBook.createCellStyle();
            desStyle.cloneStyleFrom(srcStyle);
            styleMap.put(srcStyle.getIndex(), desStyle.getIndex());
        }
        return styleMap;
    }

    /**
     * 复制单元格
     * @param srcCell
     * @param desCell
     * @param targetWorkbook
     * @param styleMap
     */
    private static void copyCell(Cell srcCell, Cell desCell, Workbook targetWorkbook, Map<Short, Short> styleMap) {
        // 判断单元格值类型
        if (srcCell.getCellType() == CellType.NUMERIC) {
            desCell.setCellValue(srcCell.getNumericCellValue());
        } else if (srcCell.getCellType() == CellType.STRING) {
            desCell.setCellValue(srcCell.getStringCellValue());
        } else if (srcCell.getCellType() == CellType.BOOLEAN) {
            desCell.setCellValue(srcCell.getBooleanCellValue());
        } else if (srcCell.getCellType() == CellType.BLANK) {
            desCell.setBlank();
        } else if (srcCell.getCellType() == CellType.FORMULA) {
            desCell.setCellFormula(srcCell.getCellFormula());
        }

        // 复制样式
        desCell.setCellStyle(targetWorkbook.getCellStyleAt(styleMap.get(srcCell.getCellStyle().getIndex())));
    }

    /**
     * 复制数据验证
     * @param sourceSheet
     * @param targetSheet
     */
    private static void copyDataValidations(Sheet sourceSheet, Sheet targetSheet) {
        for (DataValidation validation : sourceSheet.getDataValidations()) {
            // 设置要设置数据验证的单元格坐标
            CellRangeAddressList regions = new CellRangeAddressList(validation.getRegions().getCellRangeAddress(0).getFirstRow() + 4,
                    validation.getRegions().getCellRangeAddress(0).getLastRow() + 4,
                    validation.getRegions().getCellRangeAddress(0).getFirstColumn(),
                    validation.getRegions().getCellRangeAddress(0).getLastColumn());
            // 获取数据源的数据验证信息
            DataValidationHelper validationHelper = targetSheet.getDataValidationHelper();
            DataValidationConstraint constraint = validation.getValidationConstraint();
            // 新建数据验证
            DataValidation newValidation = validationHelper.createValidation(constraint, regions);
            // 在目标excel中设置数据验证
            targetSheet.addValidationData(newValidation);
        }
    }

}