SpringBoot整合EasyPoi导出(带批注)、导入(参数校验导出、带图片)

添加依赖#

            <!-- easy poi -->
            <dependency>
                <groupId>cn.afterturn</groupId>
                <artifactId>easypoi-spring-boot-starter</artifactId>
                <version>4.1.0</version>
            </dependency>
            <!-- JSR 303 规范验证包 -->
            <dependency>
                <groupId>org.hibernate</groupId>
                <artifactId>hibernate-validator</artifactId>
                <version>5.2.4.Final</version>
            </dependency>

工具类#

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;
import java.lang.reflect.Field;
import java.util.*;

/**
 * Easy Poi Excel批注工具类
 *
 * @Author：chenyanbin
 */
public class EasyPoiExcelCommentUtil {
    private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator();

    /**
     * 添加批注
     *
     * @param workbook       工作簿
     * @param titleRowsIndex 标题的行索引，从0计数
     * @param commentMap     批注map，key=列索引，从0计数；value=批注值
     */
    public static void buildComment(Workbook workbook, int titleRowsIndex, Map<Integer, String> commentMap) {
        Sheet sheet = workbook.getSheetAt(0);
        //创建一个图画工具
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        Row row = sheet.getRow(titleRowsIndex);
        if (!commentMap.isEmpty()) {
            for (Map.Entry<Integer, String> entry : commentMap.entrySet()) {
                Cell cell = row.getCell(entry.getKey());
                //创建批注
                Comment comment = drawing.createCellComment(newClientAnchor(workbook));
                //输入批注信息
                comment.setString(newRichTextString(workbook, entry.getValue()));
                //将批注添加到单元格对象中
                cell.setCellComment(comment);

//                //设置单元格背景颜色
//                CellStyle cellStyle = workbook.createCellStyle();
//                //设置颜色
//                cellStyle.setFillForegroundColor(IndexedColors.BLACK1.getIndex());
//                //设置实心填充
//                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
//                cell.setCellStyle(cellStyle);
            }
        }
    }

    /**
     * 添加批注
     *
     * @param workbook      工作簿
     * @param errorInfoList 批注错误集合
     */
    public static void buildComment(Workbook workbook, List<ExcelErrorInfoVo> errorInfoList) {
        Sheet sheet = workbook.getSheetAt(0);
        //创建一个图画工具
        Drawing<?> drawing = sheet.createDrawingPatriarch();
        for (ExcelErrorInfoVo vo : errorInfoList) {
            Row row = sheet.getRow(vo.getRowIndex());
            if (StringUtils.isNotBlank(vo.getReasonText())) {
                Cell cell = row.getCell(vo.getCellIndex());
                //创建批注
                Comment comment = drawing.createCellComment(newClientAnchor(workbook));
                //输入批注信息
                comment.setString(newRichTextString(workbook, vo.getReasonText()));
                //将批注添加到单元格对象中
                cell.setCellComment(comment);

                //设置单元格背景颜色
                CellStyle cellStyle = workbook.createCellStyle();
                //设置颜色
                cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                //设置实心填充
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                cell.setCellStyle(cellStyle);
            }
        }
    }

    /**
     * 校验Excel数据
     *
     * @param obj      Excel中当前行的数据对象
     * @param clz      Excel中当前行的数据对象的类
     * @param rowIndex 该条记录对应的行索引
     * @return
     */
    public static <T> List<ExcelErrorInfoVo> checkExcelData(T obj, Class<?> clz, int rowIndex) {
        List<ExcelErrorInfoVo> errorInfoList = new ArrayList<>();
        Set<ConstraintViolation<T>> cvSet = validator.validate(obj);
        Field f = null;
        for (ConstraintViolation<T> cv : cvSet) {
            try {
                f = clz.getDeclaredField(cv.getPropertyPath().toString());
                f.setAccessible(true);
                EasyPoiCellAnnotation annotation = f.getAnnotation(EasyPoiCellAnnotation.class);
                if (annotation == null) {
                    continue;
                }
                int cellIndex = annotation.cellIndex();
                ExcelErrorInfoVo vo = new ExcelErrorInfoVo();
                vo.setRowIndex(rowIndex);
                vo.setCellIndex(cellIndex);
                vo.setReasonText(cv.getMessage());
                errorInfoList.add(vo);
            } catch (NoSuchFieldException e) {
            } finally {
                if (f != null) {
                    f.setAccessible(false);
                }
            }
        }
        return errorInfoList;
    }

    /**
     * 批注信息，默认解析：批注#列索引，比如用户名不允许重复#0。可覆盖此方法，解析自定义的批注格式
     *
     * @param commentStr 当前行的所有批注信息
     * @return key：列索引，value：对应列的所有批注信息
     */
    protected static Map<Integer, String> getCommentMap(String commentStr) {
        //每行的所有单元格的批注都在commentStr里，并用”__”分隔
        String[] split = commentStr.split("__");
        Map<Integer, String> commentMap = new HashMap<>();
        for (String msg : split) {
            String[] cellMsg = msg.split("#");
            //如果当前列没有批注，会将该列的索引作为key存到map里；已有批注，以“,“分隔继续拼接
            int cellIndex = Integer.parseInt(cellMsg[0]);
            if (commentMap.get(cellIndex) == null) {
                commentMap.put(cellIndex, cellMsg[1]);
            } else {
                commentMap.replace(cellIndex, commentMap.get(cellIndex) + "," + cellMsg[1]);
            }
        }
        return commentMap;
    }

    private static ClientAnchor newClientAnchor(Workbook workbook) {
        //xls
        if (workbook instanceof HSSFWorkbook) {
            return new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
        }
        //xlsx
        else {
            return new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6);
        }
    }

    private static RichTextString newRichTextString(Workbook workbook, String msg) {
        //xls
        if (workbook instanceof HSSFWorkbook) {
            return new HSSFRichTextString(msg);
        }
        //xlsx
        else {
            return new XSSFRichTextString(msg);
        }
    }
}

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

/**
 * Easy Poi 导出样式
 * @Author：chenyanbin
 */
public class EasyPoiExcelExportStylerUitl implements IExcelExportStyler {

    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 10;
    private static final short FONT_SIZE_ELEVEN = 11;
    private static final short FONT_SIZE_TWELVE = 12;
    /**
     * 大标题样式
     */
    private CellStyle headerStyle;
    /**
     * 每列标题样式
     */
    private CellStyle titleStyle;
    /**
     * 数据行样式
     */
    private CellStyle styles;

    public EasyPoiExcelExportStylerUitl(Workbook workbook) {
        this.init(workbook);
    }

    /**
     * 初始化样式
     *
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
        this.styles = initStyles(workbook);
    }

    /**
     * 大标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
    }

    /**
     * 每列标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
    }

    /**
     * 数据行样式
     *
     * @param parity 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
     */
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
    }

    /**
     * 获取样式方法
     *
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
     */
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
    }

    /**
     * 模板使用的样式设置
     */
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
    }

    /**
     * 初始化--大标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, (short) 14, true));
        return style;
    }

    /**
     * 初始化--每列标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        //背景色
//        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 初始化--数据行样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initStyles(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        //背景色
//        style.setFillForegroundColor(IndexedColors.RED.getIndex());
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 基础样式
     *
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("宋体");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
}

import cn.afterturn.easypoi.excel.entity.params.ExcelExportEntity;
import cn.afterturn.easypoi.excel.entity.params.ExcelForEachParams;
import cn.afterturn.easypoi.excel.export.styler.IExcelExportStyler;
import org.apache.poi.ss.usermodel.*;

/**
 * Easy Poi 导出样式
 * @Author：chenyanbin
 */
public class EasyPoiExcelExportStylerUitl implements IExcelExportStyler {

    private static final short STRING_FORMAT = (short) BuiltinFormats.getBuiltinFormat("TEXT");
    private static final short FONT_SIZE_TEN = 10;
    private static final short FONT_SIZE_ELEVEN = 11;
    private static final short FONT_SIZE_TWELVE = 12;
    /**
     * 大标题样式
     */
    private CellStyle headerStyle;
    /**
     * 每列标题样式
     */
    private CellStyle titleStyle;
    /**
     * 数据行样式
     */
    private CellStyle styles;

    public EasyPoiExcelExportStylerUitl(Workbook workbook) {
        this.init(workbook);
    }

    /**
     * 初始化样式
     *
     * @param workbook
     */
    private void init(Workbook workbook) {
        this.headerStyle = initHeaderStyle(workbook);
        this.titleStyle = initTitleStyle(workbook);
        this.styles = initStyles(workbook);
    }

    /**
     * 大标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getHeaderStyle(short color) {
        return headerStyle;
    }

    /**
     * 每列标题样式
     *
     * @param color
     * @return
     */
    @Override
    public CellStyle getTitleStyle(short color) {
        return titleStyle;
    }

    /**
     * 数据行样式
     *
     * @param parity 可以用来表示奇偶行
     * @param entity 数据内容
     * @return 样式
     */
    @Override
    public CellStyle getStyles(boolean parity, ExcelExportEntity entity) {
        return styles;
    }

    /**
     * 获取样式方法
     *
     * @param dataRow 数据行
     * @param obj     对象
     * @param data    数据
     */
    @Override
    public CellStyle getStyles(Cell cell, int dataRow, ExcelExportEntity entity, Object obj, Object data) {
        return getStyles(true, entity);
    }

    /**
     * 模板使用的样式设置
     */
    @Override
    public CellStyle getTemplateStyles(boolean isSingle, ExcelForEachParams excelForEachParams) {
        return null;
    }

    /**
     * 初始化--大标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initHeaderStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, (short) 14, true));
        return style;
    }

    /**
     * 初始化--每列标题样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initTitleStyle(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_TWELVE, true));
        //背景色
//        style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 初始化--数据行样式
     *
     * @param workbook
     * @return
     */
    private CellStyle initStyles(Workbook workbook) {
        CellStyle style = getBaseCellStyle(workbook);
        style.setFont(getFont(workbook, FONT_SIZE_ELEVEN, false));
        //背景色
//        style.setFillForegroundColor(IndexedColors.RED.getIndex());
//        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setDataFormat(STRING_FORMAT);
        return style;
    }

    /**
     * 基础样式
     *
     * @return
     */
    private CellStyle getBaseCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        //下边框
        style.setBorderBottom(BorderStyle.THIN);
        //左边框
        style.setBorderLeft(BorderStyle.THIN);
        //上边框
        style.setBorderTop(BorderStyle.THIN);
        //右边框
        style.setBorderRight(BorderStyle.THIN);
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        return style;
    }

    /**
     * 字体样式
     *
     * @param size   字体大小
     * @param isBold 是否加粗
     * @return
     */
    private Font getFont(Workbook workbook, short size, boolean isBold) {
        Font font = workbook.createFont();
        //字体样式
        font.setFontName("宋体");
        //是否加粗
        font.setBold(isBold);
        //字体大小
        font.setFontHeightInPoints(size);
        return font;
    }
}

import java.lang.annotation.*;

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface EasyPoiCellAnnotation {
    /**
     * 字段索引位置，从0开始计数
     * @return
     */
    int cellIndex();
}

import lombok.Data;

/**
 * Excel 错误批注信息vo
 * @Author：chenyanbin
 */
@Data
public class ExcelErrorInfoVo {
    /**
     * 行索引，从0开始
     */
    private int rowIndex;
    /**
     * 列索引，从0开始
     */
    private int cellIndex;

    /**
     * 错误原因
     */
    private String reasonText;
}

import lombok.Data;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Configuration;

/**
 * 阿里云oss配置文件
 * @Author：chenyanbin
 */
@Data
@Configuration
public class OssConfig {
    @Value("${aliyun.oss.endpoint}")
    private String endpoint;

    @Value("${aliyun.oss.accessKeyId}")
    private String accessKeyId;

    @Value("${aliyun.oss.accessSecret}")
    private String accessSecret;

    @Value("${aliyun.oss.bucketName}")
    private String bucketName;
}

import com.aliyun.oss.OSS;
import com.aliyun.oss.OSSClientBuilder;
import com.aliyun.oss.model.ObjectMetadata;
import com.aliyun.oss.model.PutObjectResult;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

/**
 * 公共oss，本地文件上传至阿里云
 *
 * @Author：chenyanbin
 */
@Service
@Slf4j
public class PubOssService {
    @Autowired
    OssConfig ossConfig;

    /**
     * 上传客户端本地文件
     *
     * @param file
     * @return
     */
    public String uploadClientFile(File file) {
        try {
            //获取相关配置
            String bucketName = ossConfig.getBucketName();
            String endpoint = ossConfig.getEndpoint();
            String accessKeyId = ossConfig.getAccessKeyId();
            String accessSecret = ossConfig.getAccessSecret();
            //创建OSS对象
            OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessSecret);
            //以输入流的形式上传文件
            InputStream is = new FileInputStream(file);
            //文件名
            String fileName = file.getName();
            //文件大小
            Long fileSize = file.length();
            //创建上传Object的Metadata
            ObjectMetadata metadata = new ObjectMetadata();
            //上传的文件的长度
            metadata.setContentLength(is.available());
            //指定该Object被下载时的网页的缓存行为
            metadata.setCacheControl("no-cache");
            //指定该Object下设置Header
            metadata.setHeader("Pragma", "no-cache");
            //指定该Object被下载时的内容编码格式
            metadata.setContentEncoding("utf-8");
            //文件的MIME，定义文件的类型及网页编码，决定浏览器将以什么形式、什么编码读取文件。如果用户没有指定则根据Key或文件名的扩展名生成，
            //如果没有扩展名则填默认值application/octet-stream
            metadata.setContentType(getContentType(fileName));
            //指定该Object被下载时的名称（指示MINME用户代理如何显示附加的文件，打开或下载，及文件名称）
            metadata.setContentDisposition("filename/filesize=" + fileName + "/" + fileSize + "Byte.");
            //JDK8 日期格式化
            LocalDateTime ldt = LocalDateTime.now();
            DateTimeFormatter dtf = DateTimeFormatter.ofPattern("yyyy/MM/dd");
            DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");
            //文件路径
            String folder = dtf.format(ldt);
            //扩展名
            String extension = fileName.substring(fileName.lastIndexOf("."));
            String newFileName = "scm/excel/" + folder + "/" + dateTimeFormatter.format(ldt) + CommonUtil.generateUUID().substring(0, 8) + extension;
            //上传文件   (上传文件流的形式)
            PutObjectResult putResult = ossClient.putObject(bucketName, newFileName, is, metadata);
            String imgUrl = "https://" + bucketName + "." + endpoint + "/" + newFileName + extension;
            return imgUrl;
        } catch (Exception e) {
        }
        return null;
    }

    /**
     * 通过文件名判断并获取OSS服务文件上传时文件的contentType
     *
     * @param fileName 文件名
     * @return 文件的contentType
     */
    private String getContentType(String fileName) {
        //文件的后缀名
        String fileExtension = fileName.substring(fileName.lastIndexOf("."));
        if (".bmp".equalsIgnoreCase(fileExtension)) {
            return "image/bmp";
        }
        if (".gif".equalsIgnoreCase(fileExtension)) {
            return "image/gif";
        }
        if (".jpeg".equalsIgnoreCase(fileExtension) || ".jpg".equalsIgnoreCase(fileExtension) || ".png".equalsIgnoreCase(fileExtension)) {
            return "image/jpeg";
        }
        if (".html".equalsIgnoreCase(fileExtension)) {
            return "text/html";
        }
        if (".txt".equalsIgnoreCase(fileExtension)) {
            return "text/plain";
        }
        if (".vsd".equalsIgnoreCase(fileExtension)) {
            return "application/vnd.visio";
        }
        if (".ppt".equalsIgnoreCase(fileExtension) || "pptx".equalsIgnoreCase(fileExtension)) {
            return "application/vnd.ms-powerpoint";
        }
        if (".doc".equalsIgnoreCase(fileExtension) || "docx".equalsIgnoreCase(fileExtension)) {
            return "application/msword";
        }
        if (".xml".equalsIgnoreCase(fileExtension)) {
            return "text/xml";
        }
        //默认返回类型
        return "image/jpeg";
    }
}

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.google.common.util.concurrent.ThreadFactoryBuilder;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;
import java.util.concurrent.CountDownLatch;
import java.util.concurrent.LinkedBlockingDeque;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;
import java.util.concurrent.atomic.AtomicReference;

/**
 * EasyPoi 导出工具类
 *
 * @Author：chenyanbin
 */
@Slf4j
public class EasyPoiExportUtil {
    private static final Long KEEP_ALIVE_TIME = 60L;
    private static final int APS = Runtime.getRuntime().availableProcessors();
    private static final ThreadPoolExecutor THREAD_POOL_EXECUTOR = new ThreadPoolExecutor(
            APS * 2,
            APS * 4,
            KEEP_ALIVE_TIME,
            TimeUnit.SECONDS,
            new LinkedBlockingDeque<>(512),
            new ThreadFactoryBuilder().setNameFormat("excel工具类-pool-%d").build(),
            new ThreadPoolExecutor.CallerRunsPolicy()
    );
    /**
     * 导出Excel 一对多关系
     *
     * @param list        Excel数据集合
     * @param title       Excel第一行标题，如果设置为null，默认不显示
     * @param sheetName   sheet名称
     * @param pojoClass   泛型List的对象
     * @param fileName    导出文件名
     * @param type        excel类型 HSSF || XSSF
     * @param isOneToMany 是否一对多
     * @param response    响应体
     */
    public static void exportOneToManyExcel(
            List<?> list,
            String title,
            String sheetName,
            Class<?> pojoClass,
            String fileName,
            ExcelType type,
            boolean isOneToMany,
            HttpServletResponse response
    ) {
        ExportParams exportParams = new ExportParams(title, sheetName, type);
        exportParams.setStyle(EasyPoiExcelExportStylerUitl.class);
        exportParams.setType(ExcelType.XSSF);
        AtomicReference<Workbook> workbook = new AtomicReference<>();
        workbookHandler(workbook, exportParams, pojoClass, list);
        if (workbook.get() == null) {
            return;
        }
        //判断是否是一对多
        if (isOneToMany) {
            setRowHeight(workbook.get());
        }
        downLoadExcel(fileName, response, workbook.get());
    }

    /**
     * 导出excel
     *
     * @param list         Excel数据集合
     * @param title        Excel第一行标题，如果设置为null，默认不显示
     * @param sheetName    sheet名称
     * @param pojoClass    泛型List的对象
     * @param fileName     导出文件名
     * @param setRowHeight 是否行高自适应
     * @param response     响应体
     */
    public static void exportOneExcel(
            List<?> list,
            String title,
            String sheetName,
            Class<?> pojoClass,
            String fileName,
            boolean setRowHeight,
            HttpServletResponse response
    ) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setStyle(EasyPoiExcelExportStylerUitl.class);
        exportParams.setType(ExcelType.XSSF);
        AtomicReference<Workbook> workbook = new AtomicReference<>();
        workbookHandler(workbook, exportParams, pojoClass, list);
        if (workbook.get() == null) {
            return;
        }
        //判断是否根据内容自适应行高
        if (setRowHeight) {
            Sheet sheet = workbook.get().getSheetAt(0);
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                setRowHeight(row);
            }
        }
        downLoadExcel(fileName, response, workbook.get());
    }


    /**
     * 下载excel导入模板
     *
     * @param list           Excel数据集合
     * @param title          Excel第一行标题，如果设置为null，默认不显示
     * @param sheetName      sheet名称
     * @param pojoClass      泛型List的对象
     * @param fileName       导出文件名
     * @param setRowHeight   是否行高自适应
     * @param response       响应体
     * @param needComment    是否需要标题批注
     * @param titleRowsIndex 标题的行索引，从0计数
     * @param commentMap     批注map，key=列索引，从0计数；value=批注值
     */
    public static void downLoadExcelTemplate(
            List<?> list,
            String title,
            String sheetName,
            Class<?> pojoClass,
            String fileName,
            boolean setRowHeight,
            HttpServletResponse response,
            boolean needComment,
            int titleRowsIndex,
            Map<Integer, String> commentMap
    ) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setStyle(EasyPoiExcelExportStylerUitl.class);
        exportParams.setType(ExcelType.XSSF);
        AtomicReference<Workbook> workbook = new AtomicReference<>();
        workbookHandler(workbook, exportParams, pojoClass, list);
        if (workbook.get() == null) {
            return;
        }
        //设置标题批注
        if (needComment) {
            EasyPoiExcelCommentUtil.buildComment(workbook.get(), titleRowsIndex, commentMap);
        }
        //判断是否根据内容自适应行高
        if (setRowHeight) {
            Sheet sheet = workbook.get().getSheetAt(0);
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                setRowHeight(row);
            }
        }
        downLoadExcel(fileName, response, workbook.get());
    }

    /**
     * 下载excel校验失败导入模板
     *
     * @param list           Excel数据集合
     * @param title          Excel第一行标题，如果设置为null，默认不显示
     * @param sheetName      sheet名称
     * @param pojoClass      泛型List的对象
     * @param fileName       导出文件名
     * @param setRowHeight   是否行高自适应
     * @param response       响应体
     * @param needComment    是否需要标题批注
     * @param titleRowsIndex 标题的行索引，从0计数
     * @param commentMap     批注map，key=列索引，从0计数；value=批注值
     * @param errorInfoList  校验模板参数错误集合
     */
    public static void downLoadExcelCheckFailseTemplate(
            List<?> list,
            String title,
            String sheetName,
            Class<?> pojoClass,
            String fileName,
            boolean setRowHeight,
            HttpServletResponse response,
            boolean needComment,
            int titleRowsIndex,
            Map<Integer, String> commentMap,
            List<ExcelErrorInfoVo> errorInfoList
    ) {
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setStyle(EasyPoiExcelExportStylerUitl.class);
        exportParams.setType(ExcelType.XSSF);
        AtomicReference<Workbook> workbook = new AtomicReference<>();
        workbookHandler(workbook, exportParams, pojoClass, list);
        if (workbook.get() == null) {
            return;
        }
        //设置标题批注
        if (needComment) {
            EasyPoiExcelCommentUtil.buildComment(workbook.get(), titleRowsIndex, commentMap);
        }
        //设置Excel参数校验失败批注
        EasyPoiExcelCommentUtil.buildComment(workbook.get(), errorInfoList);
        //判断是否根据内容自适应行高
        if (setRowHeight) {
            Sheet sheet = workbook.get().getSheetAt(0);
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                setRowHeight(row);
            }
        }
        downLoadExcel(fileName, response, workbook.get());
    }

    /**
     * 下载Excel
     *
     * @param fileName
     * @param response
     * @param workbook
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        ServletOutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setHeader("Pragma", "No-cache");
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            throw new RuntimeException(e.getMessage());
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error("excel导出关闭输出流异常：{}", e.getMessage());
                }
            }
        }
    }

    /**
     * 一对多，设置行高
     */
    private static void setRowHeight(Workbook workbook) {
        Sheet sheet = workbook.getSheetAt(0);
        //设置第4列的列宽为60（下标从0开始），TestExportSub2Vo 不知道为什么设置了列宽但是不起作用，只能在这里单独设置
        sheet.setColumnWidth(3, 60 * 256);
        for (int i = 0; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (i == 0) {
                //设置第一行的行高（表格标题）
                row.setHeightInPoints(35);
            } else if (i == 1) {
                //设置第二行的行高（表格表头）
                row.setHeightInPoints(25);
            } else {
                //设置其他行的行高根据内容自适应
                setRowHeight(row);
            }
        }
    }

    private static void setRowHeight(Row row) {
        //根据内容长度设置行高
        int enterCnt = 0;
        for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
            int rwsTemp = row.getCell(j).toString().length();
            //这里取每一行中的每一列字符长度最大的那一列的字符
            if (rwsTemp > enterCnt) {
                enterCnt = rwsTemp;
            }
        }
        //设置默认行高为35
        row.setHeightInPoints(35);
        //如果字符长度大于35，判断大了多少倍，根据倍数来设置相应的行高
        if (enterCnt > 35) {
            float d = enterCnt / 35;
            float f = 35 * d;
            /*if (d>2 && d<4){
                f = 35*2;
            }else if(d>=4 && d<6){
                f = 35*3;
            }else if (d>=6 && d<8){
                f = 35*4;
            }*/
            row.setHeightInPoints(f);
        }
    }

    private static void workbookHandler(AtomicReference<Workbook> workbook, ExportParams exportParams, Class<?> pojoClass, List<?> list) {
        CountDownLatch latch = new CountDownLatch(1);
        THREAD_POOL_EXECUTOR.execute(() -> {
            try {
                workbook.set(ExcelExportUtil.exportExcel(exportParams, pojoClass, list));
            } finally {
                latch.countDown();
            }
        });
        try {
            latch.await();
        } catch (InterruptedException e) {
            log.error("多线程导出等待异常：{}", e.getMessage());
        }
    }
}

 

导入导出实体类

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import org.hibernate.validator.constraints.NotBlank;

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

/**
 * @Author：chenyanbin
 */
@Data
@ApiModel(value = "ProductFilingImportExcelVo对象", description = "商品备案明细导入信息")
public class ProductFilingImportExcelVo implements Serializable {

    @Excel(name = "HS编码", width = 25)
    @NotBlank(message = "必输项，长度不超过20")
    @Size(min = 1, max = 50, message = "必输项，长度不超过20")
    @EasyPoiCellAnnotation(cellIndex = 0)
    private String hsCode;

    @Excel(name = "商品名称(不要有空格和特殊符号)", width = 40)
    @NotBlank(message = "必输项，长度不超过100")
    @Size(min = 1, max = 100, message = "必输项，长度不超过100")
    @EasyPoiCellAnnotation(cellIndex = 1)
    private String productName;

    /**
     * 品牌id
     */
    private Integer brandId;

    @Excel(name = "品牌")
    @NotBlank(message = "必输项，长度不超过100")
    @Size(min = 1, max = 100, message = "必输项，长度不超过100")
    @EasyPoiCellAnnotation(cellIndex = 2)
    private String brandName;

    @Excel(name = "规格型号", width = 20)
    @NotBlank(message = "必输项，长度不超过100")
    @Size(min = 1, max = 100, message = "必输项，长度不超过100")
    @EasyPoiCellAnnotation(cellIndex = 3)
    private String specificationModel;

    @Excel(name = "申报要素", width = 40)
    @NotBlank(message = "必输项，长度不超过100")
    @Size(min = 1, max = 100, message = "必输项，长度不超过100")
    @EasyPoiCellAnnotation(cellIndex = 4)
    private String reportElements;

    @Excel(name = "产品成分(必须和产品包装背面成分翻译一模一样，成分100%)", width = 80)
    @NotBlank(message = "必输项，长度不超过500")
    @Size(min = 1, max = 500, message = "必输项，长度不超过500")
    @EasyPoiCellAnnotation(cellIndex = 5)
    private String productComposition;

    @Excel(name = "生产企业", width = 25)
    @NotBlank(message = "必输项，长度不超过100")
    @Size(min = 1, max = 100, message = "必输项，长度不超过100")
    @EasyPoiCellAnnotation(cellIndex = 6)
    private String enterprise;

    @Excel(name = "生产国/地区", width = 20)
    @NotBlank(message = "必输项，参照国别代码表，长度不超过20")
    @Size(min = 1, max = 20, message = "必输项，参照国别代码表，长度不超过20")
    @EasyPoiCellAnnotation(cellIndex = 7)
    private String country;

    @Excel(name = "适用标准", width = 20)
    @NotBlank(message = "必输项，长度不超过100")
    @Size(min = 1, max = 100, message = "必输项，长度不超过100")
    @EasyPoiCellAnnotation(cellIndex = 8)
    private String standards;

    @Excel(name = "商品条码", width = 30)
    @NotBlank(message = "必输项，长度不超过100")
    @Size(min = 1, max = 100, message = "必输项，长度不超过100")
    @EasyPoiCellAnnotation(cellIndex = 9)
    private String productBarCode;

    @Excel(name = "功能(保健品需写，不是保健品不用写)", width = 40)
    @EasyPoiCellAnnotation(cellIndex = 10)
    private String function;

    @Excel(name = "其他说明", width = 40)
    @EasyPoiCellAnnotation(cellIndex = 11)
    private String remark;

    //mac上存在bug，必须指定savePath！！！Windows和Linux不需要指定保存路径
    @Excel(name = "商品正面", type = 2, savePath = "/Users/chenyanbin/upload")
    @NotBlank(message = "您还有未填写的内容")
    @EasyPoiCellAnnotation(cellIndex = 12)
    private String productFrontPic;

    /**
     * 临时商品正面url
     */
    private String productFrontUrl;

    //mac上存在bug，必须指定savePath！！！Windows和Linux不需要指定保存路径
    @Excel(name = "商品背面", type = 2, savePath = "/Users/chenyanbin/upload")
    @EasyPoiCellAnnotation(cellIndex = 14)
    private String productBackPic;

    /**
     * 临时商品背面url
     */
    private String productBackUrl;

    //mac上存在bug，必须指定savePath！！！Windows和Linux不需要指定保存路径
    @Excel(name = "商品其他面", width = 15, type = 2, savePath = "/Users/chenyanbin/upload")
    @EasyPoiCellAnnotation(cellIndex = 15)
    private String productOtherPic;

    /**
     * 临时商品其他面url
     */
    private String productOtherUrl;
}

import cn.afterturn.easypoi.excel.annotation.Excel;
import io.swagger.annotations.ApiModel;
import lombok.Data;

import java.io.Serializable;

/**
 * @Author：chenyanbin
 */
@Data
@ApiModel(value = "ProductFilingExportExcelVo对象", description = "商品备案明细导出信息")
public class ProductFilingExportExcelVo implements Serializable {
    /**
     * 主键
     */
    private Long id;

    @Excel(name = "商品编码", width = 20)
    private String productCode;

    @Excel(name = "HS编码", width = 25)
    private String hsCode;

    @Excel(name = "商品名称", width = 40)
    private String productName;

    @Excel(name = "品牌")
    private String brandName;

    @Excel(name = "规格型号", width = 20)
    private String specificationModel;

    @Excel(name = "申报要素", width = 40)
    private String reportElements;

    @Excel(name = "产品成分", width = 50)
    private String productComposition;

    @Excel(name = "生产企业", width = 25)
    private String enterprise;

    @Excel(name = "生产国/地区", width = 20)
    private String country;

    @Excel(name = "适用标准", width = 20)
    private String standards;

    @Excel(name = "商品条码", width = 30)
    private String productBarCode;

    @Excel(name = "功能", width = 40)
    private String function;

    @Excel(name = "其他说明", width = 40)
    private String remark;

    @Excel(name = "商品正面", type = 2, imageType = 2)
    private byte[] productFrontPic;

    /**
     * 临时商品正面url
     */
    private String tempProductFrontUrl;

    @Excel(name = "商品背面", type = 2, imageType = 2)
    private byte[] productBackPic;

    /**
     * 临时商品背面url
     */
    private String tempProductBackUrl;

    @Excel(name = "商品其他面", width = 15, type = 2, imageType = 2)
    private byte[] productOtherPic;

    /**
     * 临时商品其他url
     */
    private String tempProductOtherUrl;
}

下载Excel导入模板，下载和导出代码雷同，dto需要获取数据库中真实数据

  @ApiOperation("下载商品备案明细导入Excel模板")
    @GetMapping("down_load_excel_template")
    public void downloadExcelTemplate(
            HttpServletResponse response
    ) {
        EasyPoiExportExcelDto<ProductFilingImportExcelVo> dto = new EasyPoiExportExcelDto<>();
        dto.setSheetName("商品备案导入");
        dto.setFileName("备案商品导入.xlsx");
        dto.setTitle("进口商品备案");
        dto.setPojoClass(new ProductFilingImportExcelVo());
        dto.setExcelDataList(new ArrayList<>());
        Map<Integer, String> commentMap = new LinkedHashMap<>();
        commentMap.put(0, "必填项，长度不超过20");
        commentMap.put(1, "必填项，长度不超过100");
        commentMap.put(2, "必填项，长度不超过100");
        commentMap.put(3, "必填项，长度不超过100");
        commentMap.put(4, "必填项，长度不超过100");
        commentMap.put(5, "必填项，长度不超过500");
        commentMap.put(6, "必填项，长度不超过100");
        commentMap.put(7, "必填项，参照国别代码表");
        commentMap.put(8, "必填项，长度不超过100");
        commentMap.put(9, "必填项，长度不超过30");
        commentMap.put(12, "商品正面必填");
        EasyPoiExportUtil.downLoadExcelTemplate(
                dto.getExcelDataList(),
                dto.getTitle(),
                dto.getSheetName(),
                dto.getPojoClass().getClass(),
                dto.getFileName(),
                false,
                response,
                true,
                1,
                commentMap
        );
    }

导入Excel

 @ApiOperation("商品备案明细导入Excel模块")
    @PostMapping("import_excel_template")
    public void importExcelTemplate(
            @ApiParam(value = "文件上传", required = true) @RequestPart("file") MultipartFile file,
            HttpServletResponse response,
            @ApiParam(value = "缓存编码", required = true) @RequestParam(value = "cache_code") String cacheCode
    ) throws Exception {
        //1、导入参数配置
        ImportParams params = new ImportParams();
        params.setNeedSave(true);
        params.setTitleRows(1);
        //mac上必须指定导入Excel保存路径，Windows和Linux不需要指定！！！
        params.setSaveUrl("/Users/chenyanbin/upload");
        //2、拿到excel数据
        List<ProductFilingImportExcelVo> excelList = ExcelImportUtil.importExcel(
                file.getInputStream(),
                ProductFilingImportExcelVo.class,
                params
        );
        //3、处理上传图片问题，转字节流
        List<ExcelErrorInfoVo> errorList = new ArrayList<>();
        CountDownLatch latch = new CountDownLatch(1);
        THREAD_POOL_EXECUTOR.execute(() -> {
            for (int i = 0; i < excelList.size(); i++) {
                //正面
                if (StringUtils.isNotBlank(excelList.get(i).getProductFrontPic())) {
                    excelList.get(i).setProductFrontUrl(
                            pubOssService.uploadClientFile(
                                    new File(
                                            excelList.get(i).getProductFrontPic()
                                    )
                            )
                    );
                }
                //背面
                if (StringUtils.isNotBlank(excelList.get(i).getProductBackPic())) {
                    excelList.get(i).setProductBackUrl(
                            pubOssService.uploadClientFile(
                                    new File(
                                            excelList.get(i).getProductBackPic()
                                    )
                            )
                    );
                }
                //其他面
                if (StringUtils.isNotBlank(excelList.get(i).getProductOtherPic())) {
                    excelList.get(i).setProductOtherUrl(
                            pubOssService.uploadClientFile(
                                    new File(
                                            excelList.get(i).getProductOtherPic()
                                    )
                            )
                    );
                }
                //校验数据
                errorList.addAll(
                        EasyPoiExcelCommentUtil
                                .checkExcelData(
                                        excelList.get(i),
                                        ProductFilingImportExcelVo.class,
                                        i + 2
                                )
                );
            }
            latch.countDown();
        });
        latch.await();
        //4、校验数据通过
        if (errorList.size() == 0) {
            //保存到数据库
            System.err.println(excelList);
        } else {
            //5、将错误excel导出
            EasyPoiExportExcelDto<ProductFilingImportExcelVo> dto = new EasyPoiExportExcelDto<>();
            dto.setSheetName("商品备案导入");
            dto.setFileName("备案商品导入-错误.xlsx");
            dto.setTitle("进口商品备案");
            dto.setPojoClass(new ProductFilingImportExcelVo());
            dto.setExcelDataList(new ArrayList<>());
            Map<Integer, String> commentMap = new LinkedHashMap<>();
            commentMap.put(0, "必填项，长度不超过20");
            commentMap.put(1, "必填项，长度不超过100");
            commentMap.put(2, "必填项，长度不超过100");
            commentMap.put(3, "必填项，长度不超过100");
            commentMap.put(4, "必填项，长度不超过100");
            commentMap.put(5, "必填项，长度不超过500");
            commentMap.put(6, "必填项，长度不超过100");
            commentMap.put(7, "必填项，参照国别代码表");
            commentMap.put(8, "必填项，长度不超过100");
            commentMap.put(9, "必填项，长度不超过30");
            commentMap.put(12, "商品正面必填");
            EasyPoiExportUtil.downLoadExcelCheckFailseTemplate(
                    excelList,
                    dto.getTitle(),
                    dto.getSheetName(),
                    dto.getPojoClass().getClass(),
                    dto.getFileName(),
                    false,
                    response,
                    true,
                    1,
                    commentMap,
                    errorList
            );
        }

演示#