常用工具方法集合

自用小工具

word工具类

import org.apache.poi.xwpf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.util.Base64;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * word工具类
 */
public class WordBuilder {

    private static final String TEXT = "${";
    private static final String IMAGE = "${image:";
    private static final Pattern TEXT_PLACEHOLDER = Pattern.compile("\\$\\{([^}]+)}");
    private static final Pattern IMAGE_PLACEHOLDER = Pattern.compile("\\$\\{image:([^}]+)}");

    private final XWPFDocument doc;

    public WordBuilder(InputStream tpl) throws IOException {
        this.doc = new XWPFDocument(tpl);
    }

    public WordBuilder(byte[] tpl) throws IOException {
        this(new ByteArrayInputStream(tpl));
    }

    /**
     * 普通占位符
     */
    public WordBuilder replace(Map<String, Object> vars) {
        replaceAll(vars);
        return this;
    }

    /**
     * 图片
     *
     * @param images 字段名：base68字符串
     */
    public WordBuilder image(Map<String, Object> images) {
        return image(images, -1, -1);
    }

    /**
     * 图片
     *
     * @param images     字段名：base68字符串
     * @param widthEmus  图片宽度
     * @param heightEmus 图片高度
     */
    public WordBuilder image(Map<String, Object> images, int widthEmus, int heightEmus) {
        replaceAllImages(images, widthEmus * 9525, heightEmus * 9525);
        return this;
    }

    public WordBuilder image(String key,String base68Image, int widthEmus, int heightEmus) {
        Map<String, Object> map = new HashMap<>();
        map.put(key, base68Image);
        replaceAllImages(map, widthEmus * 9525, heightEmus * 9525);
        return this;
    }

    /**
     * 表格
     *
     * @param data 数据列表
     */
    public WordBuilder table(List<Map<String, Object>> data) {
        return table(data, 0, 1);
    }

    /**
     * 表格
     *
     * @param data         数据列表
     * @param tableIndex   文档中第几个表格（0 开始）
     * @param tempRowIndex 模板行号（0 开始）
     */
    public WordBuilder table(List<Map<String, Object>> data, int tableIndex, int tempRowIndex) {
        if (data == null || data.isEmpty() || tableIndex < 0 || tempRowIndex < 0) {
            return this;
        }
        List<XWPFTable> tables = doc.getTables();
        if (tableIndex >= tables.size()) {
            return this;
        }
        XWPFTable table = tables.get(tableIndex);
        if (tempRowIndex >= table.getRows().size()) {
            return this;
        }
        XWPFTableRow templateRow = table.getRow(tempRowIndex);
        for (int i = 0; i < data.size(); i++) {
            Map<String, Object> rowData = data.get(i);
            // 复制模板行
            XWPFTableRow newRow = new XWPFTableRow((CTRow) templateRow.getCtRow().copy(), table);
            for (XWPFTableCell newCell: newRow.getTableCells()) {
                for (XWPFParagraph newPara : newCell.getParagraphs()) {
                    // 替换文本占位符
                    replaceParagraph(newPara, rowData);
                }
            }
            int newRowIndex = tempRowIndex + 1 + i;
            table.addRow(newRow, newRowIndex);
        }
        // 删除模板行
        table.removeRow(tempRowIndex);
        return this;
    }

    /**
     * 设置文本颜色(全局)
     *
     * @param textColor 文本颜色
     */
    public WordBuilder textColor(String textColor) {
        return textColorParagraph(textColor, -1);
    }

    /**
     * 设置段落文本颜色
     *
     * @param textColor 文本颜色
     * @param index     文档中第几个段落（0 开始）
     */
    public WordBuilder textColorParagraph(String textColor, int index) {
        // TODO

        return this;
    }

    /**
     * 设置表格文本颜色
     *
     * @param textColor 文本颜色
     * @param index     文档中第几个表格（0 开始）
     */
    public WordBuilder textColorTable(String textColor, int index) {
        return textColorTable(textColor, index, -1, -1);
    }

    /**
     * 设置表格文本颜色
     *
     * @param textColor 文本颜色
     * @param index     文档中第几个表格（0 开始）
     * @param rowIndex  行下标
     */
    public WordBuilder textColorTableRow(String textColor, int index, int rowIndex) {
        return textColorTable(textColor, index, rowIndex, -1);
    }

    /**
     * 设置表格文本颜色
     *
     * @param textColor 文本颜色
     * @param index     文档中第几个表格（0 开始）
     * @param colIndex  列下标
     */
    public WordBuilder textColorTableCol(String textColor, int index, int colIndex) {
        return textColorTable(textColor, index, -1, colIndex);
    }

    /**
     * 设置表格文本颜色
     *
     * @param textColor 文本颜色
     * @param index     文档中第几个表格（0 开始）
     * @param rowIndex  行下标
     * @param colIndex  列下标
     */
    public WordBuilder textColorTable(String textColor, int index, int rowIndex, int colIndex) {
        // TODO

        return this;
    }

    public void write(HttpServletResponse resp) throws IOException {
        resp.setContentType("application/vnd.openxmlformats-officedocument.wordprocessingml.document");
        resp.setHeader("Content-Disposition", "attachment;filename=file.docx");
        write(resp.getOutputStream());
    }

    public void write(File file) throws IOException {
        try (FileOutputStream out = new FileOutputStream(file)) {
            write(out);
        }
    }

    public byte[] toBytes() throws IOException {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        write(bos);
        return bos.toByteArray();
    }

    public void write(OutputStream out) throws IOException {
        doc.write(out);
        doc.close();
    }

    private void replaceAll(Map<String, Object> map) {
        // 段落
        for (XWPFParagraph p : doc.getParagraphs()) {
            replaceParagraph(p, map);
        }
        // 表格
        for (XWPFTable table : doc.getTables()) {
            for (XWPFTableRow row : table.getRows()) {
                for (XWPFTableCell cell : row.getTableCells()) {
                    for (XWPFParagraph p : cell.getParagraphs()) {
                        replaceParagraph(p, map);
                    }
                }
            }
        }
        // 页眉
        for (XWPFHeader header : doc.getHeaderList()) {
            for (XWPFParagraph paragraph : header.getParagraphs()) {
                replaceParagraph(paragraph, map);
            }
        }
        // 页脚
        List<XWPFFooter> footerList = doc.getFooterList();
        for (XWPFFooter footer : footerList) {
            for (XWPFParagraph paragraph : footer.getParagraphs()) {
                replaceParagraph(paragraph, map);
            }
        }
    }

     String txt = run.text();
        if (txt.contains(TEXT) && !txt.contains(IMAGE)) {
            Matcher m = TEXT_PLACEHOLDER.matcher(txt);
            StringBuffer sb = new StringBuffer();
            while (m.find()) {
                String key = m.group(1);
                Object val = data.get(key);
                if (val != null) {
                    m.appendReplacement(sb, val.toString());
                }
            }
            m.appendTail(sb);
            txt = sb.toString();
            // 换行处理
            if (txt.contains("\n")) {
                String[] lines = txt.split("\n");
                run.setText(lines[0], 0);
                for (int i = 1; i < lines.length; i++) {
                    run.addBreak();
                    run.setText(lines[i]);
                }
            } else {
                run.setText(txt, 0);
            }
        }

    private void replaceAllImages(Map<String, Object> images, int widthEmus, int heightEmus) {
        // 段落
        for (XWPFParagraph p : doc.getParagraphs()) {
            replaceParagraphWithImage(p, images, widthEmus, heightEmus);
        }
        // 表格
        for (XWPFTable table : doc.getTables()) {
            for (XWPFTableRow row : table.getRows()) {
                for (XWPFTableCell cell : row.getTableCells()) {
                    for (XWPFParagraph p : cell.getParagraphs()) {
                        replaceParagraphWithImage(p, images, widthEmus, heightEmus);
                    }
                }
            }
        }
    }

    private void replaceParagraphWithImage(XWPFParagraph p, Map<String, Object> images, int widthEmus, int heightEmus) {
        if (!p.getText().contains(IMAGE)) {
            return;
        }
        joinRuns(p);
        for (XWPFRun run : p.getRuns()) {
            String txt = run.text();
            Matcher m = IMAGE_PLACEHOLDER.matcher(txt);
            while (m.find()) {
                String key = m.group(1);
                Object val = images.get(key);
                if (val == null) {
                    continue;
                }
                byte[] bytes = toBytes(val);
                if (bytes == null || bytes.length == 0) {
                    continue;
                }
                String ext = getFormat(bytes);
                int pictureType = getPictureType(ext);

                try {
                    int width = widthEmus > 0 ? widthEmus : 200 * 9525;
                    int height = heightEmus > 0 ? heightEmus : 200 * 9525;

                    run.addPicture(new ByteArrayInputStream(bytes), pictureType, "image." + ext, width, height);
                    run.setText("", 0);
                } catch (Exception e) {
                    throw new RuntimeException("插入图片失败: " + key, e);
                }
            }
        }
    }

    /**
     * 合并 run，防止占位符被拆开
     */
    private void joinRuns(XWPFParagraph p) {
        if (p.getRuns().size() <= 1) {
            return;
        }
        StringBuilder sb = new StringBuilder();
        for (XWPFRun r : p.getRuns()) {
            sb.append(r.text());
        }
        p.getRuns().get(0).setText(sb.toString(), 0);
        while (p.getRuns().size() > 1) {
            p.removeRun(1);
        }
    }

    private byte[] toBytes(Object val) {
        try {
            if (val instanceof byte[]) {
                return (byte[]) val;
            }
            if (val instanceof String) {
                String s = (String) val;
                // 去掉 data URI 头（如果有）
                if (s.contains(",")) {
                    s = s.split(",")[1];
                }
                return Base64.getDecoder().decode(s.trim());
            }
            if (val instanceof File) {
                return org.apache.poi.util.IOUtils.toByteArray(Files.newInputStream(((File) val).toPath()));
            }
            if (val instanceof InputStream) {
                return org.apache.poi.util.IOUtils.toByteArray((InputStream) val);
            }
        } catch (IOException e) {
            throw new RuntimeException("读取图片失败", e);
        }
        return null;
    }

    private String getFormat(byte[] bs) {
        // 简单魔数
        if (bs[0] == (byte) 0x89 && bs[1] == (byte) 0x50) {
            return "png";
        }
        if (bs[0] == (byte) 0xFF && bs[1] == (byte) 0xD8) {
            return "jpg";
        }
        if (bs[0] == (byte) 0x47 && bs[1] == (byte) 0x49) {
            return "gif";
        }
        return "png";
    }

    private int getPictureType(String ext) {
        switch (ext.toLowerCase()) {
            case "jpg":
            case "jpeg":
                return Document.PICTURE_TYPE_JPEG;
            case "gif":
                return Document.PICTURE_TYPE_GIF;
            case "png":
            default:
                return Document.PICTURE_TYPE_PNG;
        }
    }

}

PDF工具类

import com.spire.doc.Document;
import com.spire.doc.FileFormat;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;

/**
 * PDF工具类
 */
public class PdfUtil {

    public static byte[] convertWordToPdf(byte[] docBytes) {
        Document doc = new Document();
        doc.loadFromStream(new ByteArrayInputStream(docBytes), FileFormat.Docx);
        ByteArrayOutputStream pdfOut = new ByteArrayOutputStream();
        doc.saveToStream(pdfOut, FileFormat.PDF);
        doc.dispose();
        return pdfOut.toByteArray();
    }

}

集合工具

import cn.hutool.core.collection.CollectionUtil;

import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 集合工具
 */
public class ListUtil {

    /**
     * 求差集
     *
     * @param objectComparator 比较器
     * @return list1 与 list2 的差集
     */
    public static <T> List<T> difference(List<T> list1, List<T> list2, Comparator<T> objectComparator) {
        if (CollectionUtil.isEmpty(list1)) {
            return new ArrayList<>();
        }
        if (CollectionUtil.isEmpty(list2)) {
            return list1;
        }
        return list1.stream()
                .filter(objA -> list2.stream()
                        .noneMatch(objB -> objectComparator.compare(objA, objB) == 0))
                .collect(Collectors.toList());
    }

    /**
     * 求差集
     *
     * @param keyExtractors 比较规则
     * @return list1 与 list2 的差集
     */
    @SafeVarargs
    public static <T> List<T> difference(List<T> list1, List<T> list2, Function<T, ?>... keyExtractors) {
        Comparator<T> comparator = createComparator(keyExtractors);
        return difference(list1, list2, comparator);
    }

    /**
     * 去重
     *
     * @param comparator 比较器
     */
    public static <T> List<T> distinct(List<T> list, Comparator<T> comparator) {
        if (CollectionUtil.isEmpty(list)) {
            return new ArrayList<>();
        }
        Set<T> set = new LinkedHashSet<>();
        for (T element : list) {
            boolean isUnique = set.stream().noneMatch(e -> comparator.compare(e, element) == 0);
            if (isUnique) {
                set.add(element);
            }
        }
        return new ArrayList<>(set);
    }

    /**
     * 去重
     *
     * @param keyExtractors 比较字段
     */
    @SafeVarargs
    public static <T> List<T> distinct(List<T> list, Function<T, ?>... keyExtractors) {
        Comparator<T> comparator = createComparator(keyExtractors);
        return distinct(list, comparator);
    }

    /**
     * 比较器
     *
     * @return 0:相等，-1:不相等
     */
    @SafeVarargs
    public static <T> Comparator<T> createComparator(Function<T, ?>... keyExtractors) {
        return (o1, o2) -> {
            for (Function<T, ?> keyExtractor : keyExtractors) {
                Object key1 = keyExtractor.apply(o1);
                Object key2 = keyExtractor.apply(o2);
                if (!Objects.equals(key1, key2)) {
                    return -1;
                }
            }
            return 0;
        };
    }

}

map工具类

import io.seata.common.util.CollectionUtils;

import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * map工具类
 */
public class MapUtil {

    /**
     * List 转成 Map
     *
     * @param list      list数据
     * @param keyMapper 取主键的 Lambda
     * @param <T>       原始元素类型
     * @param <K>       主键类型
     * @return Map<主键, VO>
     */
    public static <T, K> Map<K, T> listToMap(List<T> list, Function<T, K> keyMapper) {
        if (CollectionUtils.isEmpty(list)) {
            return Collections.emptyMap();
        }
        return list.stream().collect(Collectors.toMap(
                keyMapper,
                Function.identity(),
                (oldVal, newVal) -> oldVal));
    }

    /**
     * List 转成 Map
     *
     * @param list        list数据
     * @param keyMapper   取主键的 Lambda
     * @param valueMapper 取值的 Lambda
     * @param <T>         原始元素类型shi
     * @param <K>         主键类型
     * @param <V>         值类型
     * @return Map<主键, 值>
     */
    public static <T, K, V> Map<K, V> listToMap(List<T> list, Function<T, K> keyMapper, Function<T, V> valueMapper) {
        if (CollectionUtils.isEmpty(list)) {
            return Collections.emptyMap();
        }
        return list.stream().collect(Collectors.toMap(
                keyMapper,
                valueMapper,
                (oldVal, newVal) -> oldVal));
    }

}

文件工具类

import com.sinopec.sicii.wzcggl.config.MyWzcgglException;
import lombok.extern.slf4j.Slf4j;
import org.springframework.http.MediaType;
import org.springframework.util.MimeTypeUtils;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.Locale;

/**
 * 文件工具类
 */
@Slf4j
public class FileUtil {

    public static void download(HttpServletResponse response, byte[] bytes, String fileName) {
        if (bytes == null || bytes.length == 0) {
            throw new MyWzcgglException("文件为空!");
        }

        try {
            MediaType mediaType = resolveMediaType(fileName);

            response.reset();
            response.setContentType(mediaType.toString());
            response.setContentLength(bytes.length);
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            try (ServletOutputStream outputStream = response.getOutputStream()) {
                outputStream.write(bytes);
                outputStream.flush();
            }
        } catch (IOException e) {
            log.error("文件下载失败，fileName：{}，错误:{}", fileName, e.getMessage());
            throw new MyWzcgglException("文件下载失败!");
        }
    }

    private static MediaType resolveMediaType(String fileName) {
        if (fileName == null) {
            return MediaType.APPLICATION_OCTET_STREAM;
        }
        String ext = fileName
                .substring(fileName.lastIndexOf('.') + 1)
                .toLowerCase(Locale.ENGLISH);
        switch (ext) {
            case "pdf":
                return MediaType.APPLICATION_PDF;
            case "xls":
                return MediaType.valueOf("application/vnd.ms-excel");
            case "xlsx":
                return MediaType.valueOf(
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            case "doc":
                return MediaType.valueOf("application/msword");
            case "docx":
                return MediaType.valueOf(
                        "application/vnd.openxmlformats-officedocument.wordprocessingml.document");
            case "ppt":
                return MediaType.valueOf("application/vnd.ms-powerpoint");
            case "pptx":
                return MediaType.valueOf(
                        "application/vnd.openxmlformats-officedocument.presentationml.presentation");
            case "txt":
                return MediaType.TEXT_PLAIN;
            case "png":
                return MediaType.IMAGE_PNG;
            case "jpg":
            case "jpeg":
                return MediaType.IMAGE_JPEG;
            case "gif":
                return MediaType.IMAGE_GIF;
            case "zip":
                return MediaType.valueOf("application/zip");
            default:
                String mime = MimeTypeUtils.parseMimeType(ext).toString();
                return MediaType.parseMediaType(mime);
        }
    }

}

反射工具

import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.Map;

/**
 * 反射工具
 */
public class FieldUtil {

    /**
     * 获取包括父类在内的所有字段
     */
    public static Map<String, Object> getFieldMap(Object obj) {
        Map<String, Object> fieldMap = new HashMap<>();
        if (obj == null) {
            return fieldMap;
        }

        try {
            Class<?> clazz = obj.getClass();
            while (clazz != null && clazz != Object.class) {
                Field[] fields = clazz.getDeclaredFields();
                for (Field field : fields) {
                    field.setAccessible(true);
                    String fieldName = field.getName();
                    Object fieldValue = field.get(obj);
                    fieldMap.put(fieldName, fieldValue);
                }
                clazz = clazz.getSuperclass();
            }
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }
        return fieldMap;
    }


}

Validator 校验框架工具

import javax.validation.ConstraintViolation;
import javax.validation.ConstraintViolationException;
import javax.validation.Validator;

import cn.hutool.extra.spring.SpringUtil;
import lombok.AccessLevel;
import lombok.NoArgsConstructor;

import java.util.Set;

/**
 * Validator 校验框架工具
 */
@NoArgsConstructor(access = AccessLevel.PRIVATE)
public class ValidatorUtil {

    private static final Validator VALID = SpringUtil.getBean(Validator.class);

    /**
     * 对给定对象进行参数校验，并根据指定的校验组进行校验
     *
     * @param object 要进行校验的对象
     * @param groups 校验组
     * @throws ConstraintViolationException 如果校验不通过，则抛出参数校验异常
     */
    public static <T> void validate(T object, Class<?>... groups) {
        Set<ConstraintViolation<T>> validate = VALID.validate(object, groups);
        if (!validate.isEmpty()) {
            throw new ConstraintViolationException("参数校验异常", validate);
        }
    }

}

阿里excel工具类

import com.alibaba.excel.EasyExcel;
import com.esse.projects.inm.common.excel.DefaultExcelListener;
import com.esse.projects.inm.common.excel.ExcelListener;
import com.esse.projects.inm.common.excel.ExcelResult;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 * 阿里excel工具类
 */
public class EasyExcelUtil {

    /**
     * 同步导入(适用于小数据量)
     *
     * @param is 输入流
     * @return 转换后集合
     */
    public static <T> List<T> importExcel(InputStream is, Class<T> clazz) {
        return EasyExcel.read(is).head(clazz).autoCloseStream(false).sheet().doReadSync();
    }

    /**
     * 使用校验监听器 异步导入 同步返回
     *
     * @param is         输入流
     * @param clazz      对象类型
     * @param isValidate 是否 Validator 检验 默认为是
     * @return 转换后集合
     */
    public static <T> ExcelResult<T> importExcel(InputStream is, Class<T> clazz, boolean isValidate) {
        DefaultExcelListener<T> listener = new DefaultExcelListener<>(isValidate);
        EasyExcel.read(is, clazz, listener).sheet().doRead();
        return listener.getExcelResult();
    }

    /**
     * 使用自定义监听器 异步导入 自定义返回
     *
     * @param is       输入流
     * @param clazz    对象类型
     * @param listener 自定义监听器
     * @return 转换后集合
     */
    public static <T> ExcelResult<T> importExcel(InputStream is, Class<T> clazz, ExcelListener<T> listener) {
        EasyExcel.read(is, clazz, listener).sheet().doRead();
        return listener.getExcelResult();
    }

    /**
     * 导出excel
     *
     * @param list      导出数据集合
     * @param sheetName 工作表的名称
     * @param clazz     实体类
     */
    public static <T> void exportExcel(List<T> list, String sheetName, Class<T> clazz, HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode(sheetName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), clazz)
                .sheet(sheetName)
                .doWrite(list);
    }

}

import java.util.List;

/**
 * excel返回对象
 */
public interface ExcelResult<T> {

    /**
     * 对象列表
     */
    List<T> getList();

    /**
     * 错误列表
     */
    List<String> getErrorList();

    /**
     * 导入回执
     */
    String getAnalysis();
}

import com.alibaba.excel.read.listener.ReadListener;

/**
 * Excel 导入监听
 */
public interface ExcelListener<T> extends ReadListener<T> {

    ExcelResult<T> getExcelResult();

    void addError(String error);

}

import lombok.Setter;

import java.util.ArrayList;
import java.util.List;

/**
 * 默认excel返回对象
 */
public class DefaultExcelResult<T> implements ExcelResult<T> {

    /**
     * 数据对象list
     */
    @Setter
    private List<T> list;

    /**
     * 错误信息列表
     */
    @Setter
    private List<String> errorList;

    public DefaultExcelResult() {
        this.list = new ArrayList<>();
        this.errorList = new ArrayList<>();
    }

    public DefaultExcelResult(List<T> list, List<String> errorList) {
        this.list = list;
        this.errorList = errorList;
    }

    public DefaultExcelResult(ExcelResult<T> excelResult) {
        this.list = excelResult.getList();
        this.errorList = excelResult.getErrorList();
    }

    @Override
    public List<T> getList() {
        return list;
    }

    @Override
    public List<String> getErrorList() {
        return errorList;
    }

    /**
     * 获取导入回执
     *
     * @return 导入回执
     */
    @Override
    public String getAnalysis() {
        int successCount = list.size();
        int errorCount = errorList.size();
        if (errorCount == 0) {
            return "全部导入成功!";
        } else {
            if (successCount == 0) {
                return "全部导入失败<br/>" + String.join("<br/>", errorList);
            } else {
                return "部分导入失败<br/>" + String.join("<br/>", errorList);
            }
        }
    }
}

import cn.hutool.core.util.StrUtil;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.fastjson.JSON;
import com.esse.projects.inm.common.util.ValidatorUtil;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import javax.validation.ConstraintViolation;
import javax.validation.ConstraintViolationException;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * Excel 默认导入监听
 */
@Slf4j
@NoArgsConstructor
public class DefaultExcelListener<T> extends AnalysisEventListener<T> implements ExcelListener<T> {

    /**
     * 是否Validator检验，默认为是
     */
    private Boolean isValidate = Boolean.TRUE;

    /**
     * excel 表头数据
     */
    private Map<Integer, String> headMap;

    /**
     * 导入回执
     */
    private ExcelResult<T> excelResult;

    private static final String FORMAT = "第{}行数据校验异常: {}";
    private static final String BUSINESS = "第{}行业务逻辑异常: {}";

    public DefaultExcelListener(boolean isValidate) {
        this.excelResult = new DefaultExcelResult<>();
        this.isValidate = isValidate;
    }

    /**
     * 处理异常
     *
     * @param exception ExcelDataConvertException
     * @param context   Excel 上下文
     */
    @Override
    public void onException(Exception exception, AnalysisContext context) {
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            // 如果是某一个单元格的转换异常 能获取到具体行号
            Integer rowIndex = excelDataConvertException.getRowIndex();
            Integer columnIndex = excelDataConvertException.getColumnIndex();
            addError(StrUtil.format("第{}行-第{}列-表头{}: 解析异常<br/>",
                    rowIndex + 1, columnIndex + 1, headMap.get(columnIndex)));
        } else if (exception instanceof ConstraintViolationException) {
            ConstraintViolationException constraintViolationException = (ConstraintViolationException) exception;
            Set<ConstraintViolation<?>> constraintViolations = constraintViolationException.getConstraintViolations();
            String constraintViolationsMsg = constraintViolations.stream().map(ConstraintViolation::getMessage)
                    .collect(Collectors.joining(", "));
            addFormatError(context.readRowHolder().getRowIndex() + 1, constraintViolationsMsg);
        }
    }

    /**
     * 处理表头
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        this.headMap = headMap;
        log.debug("解析到一条表头数据: {}", JSON.toJSONString(headMap));
    }

    /**
     * 处理数据
     */
    @Override
    public void invoke(T data, AnalysisContext context) {
        if (isValidate) {
            ValidatorUtil.validate(data);
        }
        excelResult.getList().add(data);
    }

    /**
     * 所有数据分析完成后的回调
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        log.debug("所有数据解析完成！");
    }

    @Override
    public ExcelResult<T> getExcelResult() {
        return excelResult;
    }

    @Override
    public void addError(String error) {
        excelResult.getErrorList().add(error);
    }

    public void addFormatError(Integer rowIndex, String error) {
        addError(StrUtil.format(FORMAT, rowIndex, error));
    }

    public void addBusinessError(Integer rowIndex, String error) {
        addError(StrUtil.format(BUSINESS, rowIndex, error));
    }

}

使用示例

import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.EqualsAndHashCode;

import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;
import javax.validation.constraints.Size;
import java.io.Serializable;


@EqualsAndHashCode(callSuper = true)
@Data
@ExcelTarget("***")
public class StableUnitExcel extends BaseExcel {

    @ExcelProperty("类型*")
    @ColumnWidth(20)
    @NotBlank(message = "类型不能为空")
    private String typeName;

    @ExcelProperty("社会统一信用代码*")
    @ColumnWidth(30)
    @NotBlank(message = "社会统一信用代码不能为空")
    private String unitIntegrityCode;

    @ExcelProperty("单位名称")
    @ColumnWidth(25)
    private String unitName;

    @ExcelProperty("项目编号*")
    @ColumnWidth(25)
    @NotBlank(message = "项目编号不能为空")
    private String projectCode;

    @ExcelProperty("项目名称")
    @ColumnWidth(30)
    private String projectName;

    @ExcelProperty("禁用到期时间（不填为永久）")
    @ColumnWidth(25)
    @Pattern(message = "禁用到期时间格式错误，正确示例：2025-10-01", regexp = "^[0-9]{4}-(0[1-9]|1[0-2])-(0[1-9]|[12][0-9]|3[01])$")
    private String forbiddenEndTime;

    @ExcelProperty("事由*")
    @ColumnWidth(50)
    @NotBlank(message = "事由不能为空")
    @Size(message = "事由长度不能大于2000", max = 2000)
    private String remark;

}

import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import com.alibaba.excel.context.AnalysisContext;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.BeanUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * 监听
 */
@Slf4j
public class StableGreyExcelListener extends DefaultExcelListener<StableGreyExcel> {

    private final IStableGreyService stableGreyService;
    private final RemoteEmployeeServer remoteEmployeeServer;
    private final CmService cmService;
    private final ProjectInfoService projectInfoService;
    private final ProjectSystemDetailService projectSystemDetailService;

    public StableGreyExcelListener() {
        super(false);
        this.remoteEmployeeServer = SpringUtil.getBean(RemoteEmployeeServer.class);
        this.stableGreyService = SpringUtil.getBean(IStableGreyService.class);
        this.cmService = SpringUtil.getBean(CmService.class);
        this.projectInfoService = SpringUtil.getBean(ProjectInfoService.class);
        this.projectSystemDetailService = SpringUtil.getBean(ProjectSystemDetailService.class);
    }

    @Override
    public void invoke(StableGreyExcel data, AnalysisContext context) {
        // 保存行号信息
        data.setRowIndex(context.readRowHolder().getRowIndex() + 1);
        // 格式校验
        ValidatorUtil.validate(data);
        getExcelResult().getList().add(data);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        List<StableGreyExcel> list = getExcelResult().getList();

        // 批量查询单位信息、项目信息
        Set<String> unitIntegrityCodeSet = list.stream().map(StableGreyExcel::getUnitIntegrityCode).collect(Collectors.toSet());
        Set<String> projectCodeSet = list.stream().map(StableGreyExcel::getProjectCode).collect(Collectors.toSet());
        Map<String, ContractorUnitVO> unitMap = MapUtil.listToMap(
                cmService.getUnitListByIntegrityCodeList(new ArrayList<>(unitIntegrityCodeSet)).getData(),
                ContractorUnitVO::getIntegrityCode
        );
        Map<String, ProjectInfoVO> projectMap = MapUtil.listToMap(
                projectInfoService.getProjectByCodes2(String.join(",", projectCodeSet)),
                ProjectInfoVO::getProjectInfoCode
        );

        Long tenantId = SecurityUtils.getUser().getSysTenantUserVO().getTenantId();

        List<StableGreyEntity> insertList = new ArrayList<>();
        for (StableGreyExcel stableGreyExcel : list) {
            Integer rowIndex = stableGreyExcel.getRowIndex();

            StableGreyEntity entity = new StableGreyEntity();
            BeanUtils.copyProperties(stableGreyExcel, entity);

            // 禁用到期时间
            if (StrUtil.isNotBlank(stableGreyExcel.getForbiddenEndTime())) {
                entity.setForbiddenEndTime(DateUtil.stringToDate(stableGreyExcel.getForbiddenEndTime()));
            }

            // 人员信息
            List<IdNameOrgVO> userList = remoteEmployeeServer.listByidNo(stableGreyExcel.getIdentityCard(), tenantId).getData();
            if (CollectionUtils.isEmpty(userList)) {
                addBusinessError(rowIndex, "未找到相关人员信息");
                continue;
            }
            IdNameOrgVO user = userList.get(0);
            if (!stableGreyExcel.getGreyUserName().equals(user.getName())) {
                addBusinessError(rowIndex, "人员姓名与身份证号不匹配");
                continue;
            }
            entity.setGreyUserId(Long.parseLong(user.getId()));

            // 单位信息
            ContractorUnitVO unit = unitMap.get(stableGreyExcel.getUnitIntegrityCode());
            if (unit == null) {
                addBusinessError(rowIndex, "未找到该统一信用社会代码的单位");
                continue;
            }
            if (StrUtil.isNotEmpty(stableGreyExcel.getUnitName())
                    && !stableGreyExcel.getUnitName().equals(unit.getName())) {
                addBusinessError(rowIndex, "统一信用社会代码与单位名称不匹配");
                continue;
            }
            entity.setUnitId(unit.getId());
            entity.setUnitName(unit.getName());

            // 项目信息
            if (StrUtil.isNotBlank(stableGreyExcel.getProjectCode())) {
                ProjectInfoVO projectInfoVO = projectMap.get(stableGreyExcel.getProjectCode());
                if (projectInfoVO == null) {
                    addBusinessError(rowIndex, "未找到该项目编码的项目信息");
                    continue;
                }
                if (StrUtil.isNotBlank(stableGreyExcel.getProjectName())
                        && !stableGreyExcel.getProjectName().equals(projectInfoVO.getProjectInfoName())) {
                    addBusinessError(rowIndex, "项目编码和项目名称不匹配");
                    continue;
                }
                entity.setProjectId(projectInfoVO.getProjectInfoId());
                entity.setProjectName(projectInfoVO.getProjectInfoName());
            }

            // 职务信息 如果未匹配到则不处理
            if (StrUtil.isNotBlank(stableGreyExcel.getPostName()) && entity.getUnitId() != null) {
                List<SimpleBeanVO> simpleBeanVOList = projectSystemDetailService.personListOfPerson(Long.parseLong(user.getId()), entity.getUnitId()).getData();
                for (SimpleBeanVO simpleBeanVO : simpleBeanVOList) {
                    if (stableGreyExcel.getPostName().equals(simpleBeanVO.getName())) {
                        entity.setPostId(simpleBeanVO.getId());
                    }
                }
            }

            insertList.add(entity);
        }

        if (CollectionUtils.isNotEmpty(insertList)) {
            stableGreyService.saveBatch(insertList);
        }

        log.info("所有数据解析完成！");
    }

}