EasyExcel工具类

功能列表

• 导出Excel返回文件的File
• 导出到浏览器
• 导出到浏览器(动态字段)
• 导出数据到多Sheet表 -> 浏览器
• 读取表格标题和数据

内置已实现动态列宽和行高

public class EasyExcelUtils {

    private static final Logger log = LoggerFactory.getLogger(EasyExcelUtils.class);


    /**
     * 导出Excel返回文件的File
     *
     * @param data
     * @param clazz
     * @param fileName
     */
    public static <T> File exportReturnToFile(List<T> data, Class<T> clazz, String fileName) {
        File file = null;
        //名称拆分前后缀
        //1.判断文件名是否有扩展
        try {
            if (StringUtils.isBlank(fileName)) {
                file = File.createTempFile(fileName, "");
            } else {
                String[] parts = fileName.split("\\.");
                String extension = "." + parts[parts.length - 1];
                String name = fileName.substring(0, fileName.length() - extension.length() - 1);
                file = File.createTempFile(name, extension);
            }
        } catch (IOException e) {
            log.error("文件生成失败,文件名异常.异常信息:{}", e.getMessage());
            throw new RuntimeException("文件生成失败,文件名异常");
        }


        //导出
        EasyExcel.write(file, clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(defaultStylePolicyPolicy())
                .sheet()
                .doWrite(data);
        return file;
    }

    /**
     * EasyExce导出到浏览器(类模板方式)
     *
     * @param fileName 文件名
     * @param response 响应对象
     * @param data     数据集
     * @param clazz    实体类模板
     * @param <T>      泛型
     */
    public static <T> void export(String fileName, HttpServletResponse response, List<T> data, Class<T> clazz) throws IOException {
        exportPublic(fileName, response, data, clazz, null);
    }

    /**
     * EasyExce导出到浏览器(类模板方式)
     *
     * @param fileName                文件名
     * @param response                响应对象
     * @param data                    数据集
     * @param clazz                   实体类模板
     * @param excludeColumnFieldNames 忽略指定列导出根据字段名
     * @param <T>                     泛型
     */
    public static <T> void export(String fileName, HttpServletResponse response, List<T> data, Class<T> clazz, List<String> excludeColumnFieldNames) throws IOException {
        exportPublic(fileName, response, data, clazz, excludeColumnFieldNames);
    }

    /**
     * export公共
     *
     * @param fileName                文件名
     * @param response                响应对象
     * @param data                    数据集
     * @param clazz                   实体类模板
     * @param excludeColumnFieldNames 忽略指定列导出根据字段名
     * @param <T>                     泛型
     * @throws IOException
     */
    private static <T> void exportPublic(String fileName, HttpServletResponse response, List<T> data, Class<T> clazz, List<String> excludeColumnFieldNames) throws IOException {

        setResponse(response, fileName);

        //导出
        EasyExcel.write(response.getOutputStream(), clazz)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(defaultStylePolicyPolicy())
                .registerWriteHandler(new CustomCellWriteWidthConfig())
//                .registerWriteHandler(new CustomCellWriteHeightConfig())
                .sheet()
                .excludeColumnFieldNames(excludeColumnFieldNames)
                .doWrite(data);

    }

    /**
     * EasyExce导出到浏览器(动态标题(字段)方式)
     *
     * @param fileName 文件名
     * @param response 响应对象
     * @param heads    标题(字段)
     * @param data     数据
     * @throws IOException
     */
    public static void export(String fileName, HttpServletResponse response, List<List<String>> heads, List<List<Object>> data) throws IOException {

        setResponse(response, fileName);

        //导出
        EasyExcel.write(response.getOutputStream())
                .head(heads)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerWriteHandler(defaultStylePolicyPolicy())
                .sheet()
                .doWrite(data);
    }

    /**
     * 导出失败返回错误模板
     *
     * @param response 响应对象
     * @param pathName 模板路径
     * @param msg      异常消息
     * @throws IOException
     */
    public static void exportError(HttpServletResponse response, String pathName, String msg) throws IOException {

        setResponse(response, "导出失败");

        InputStream resourceAsStream = EasyExcelUtils.class.getResourceAsStream(pathName);

        Map<String, String> map = new HashMap<>();
        map.put("msg", msg);

        //导出
        EasyExcel.write(response.getOutputStream()).withTemplate(resourceAsStream).sheet().doFill(map);


    }

    /**
     * EasyExce导出Excel到多Sheet表 -> 浏览器
     *
     * @param fileName                文件名
     * @param response                响应对象
     * @param sheetNameList           已经排序过的sheet名称列表
     * @param data                    数据集 key为sheet名称,value为数据集合
     * @param clazz                   实体类模板
     * @param excludeColumnFieldNames 忽略指定列导出根据字段名
     * @throws IOException
     */
    public static <T> void exportToMultipleSheets(String fileName, HttpServletResponse response, List<String> sheetNameList, Map<String, List<T>> data, Class<T> clazz, List<String> excludeColumnFieldNames) throws IOException {

        setResponse(response, fileName);

        // 创建ExcelWriter对象
        ExcelWriter writer = EasyExcel
                .write(response.getOutputStream())
                .registerWriteHandler(new CustomCellWriteWidthConfig()) /*自适应列宽*/
                .registerWriteHandler(new CustomCellWriteHeightConfig()) /*自适应行高（根据自己情况选择使用，我这里没用到）*/
                //自定义样式
                .registerWriteHandler(EasyExcelUtils.defaultStylePolicyPolicy())
                .build();

        //创建工作表
        sheetNameList.forEach(sheetName -> {
            WriteSheet sheet = EasyExcel.writerSheet(sheetName).excludeColumnFieldNames(excludeColumnFieldNames).head(clazz).build();
            List<T> valueList = data.get(sheetName);
            if (CollUtil.isEmpty(valueList)) throw new RuntimeException("查询不到对应sheet数据,请检查数据!");
            writer.write(valueList, sheet);

        });

        //导出
        writer.finish();
    }

    /**
     * 设置响应对象
     *
     * @param response
     * @param fileName
     */
    private static void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20") + ".xlsx");
    }

    /**
     * 获取标题和数据
     * 标题  listTitle
     * 数据  listBody
     *
     * @param file 文件
     * @return 返回包含标题和数据的Map
     * @throws IOException 当发生I/O错误时抛出
     */
    public static Map<String, Object> readExcelTitleAndBody(MultipartFile file) throws IOException {
        InputStream inputStream = file.getInputStream();
        List<String> listTitle = new ArrayList<>(4);
        List<Map<String, String>> listBody = new ArrayList<>();

        Integer count = 0;
        AnalysisEventListener<Map<Integer, String>> listener = new AnalysisEventListener<Map<Integer, String>>() {
            @Override
            public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
                // 在这里处理标题行的数据
                // headMap是一个Map，键为列的索引，值为列的标题
                // 你可以在这里获取到标题行的数据
                if (listTitle.size() == 0) {
                    headMap.forEach((k, v) -> {
                        listTitle.add(v);
                    });
                }
            }

            @Override
            public void invoke(Map<Integer, String> data, AnalysisContext context) {
                // 这里是读取到每一行数据时的处理逻辑
                Map<String, String> map = new HashMap<>();
                data.forEach((k, v) -> {
                    map.put(listTitle.get(k), v);
                });
                listBody.add(map);
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                // 读取完成后的处理逻辑
            }
        };

        EasyExcel.read(inputStream, listener).sheet().doRead();
        inputStream.close();
        Map<String, Object> resMap = new HashMap<>();
        resMap.put("listTitle", listTitle);
        resMap.put("listBody", listBody);
        // 返回标题行的数据
        return resMap;
    }

    /**
     * 默认样式策略策略
     *
     * @return
     */
    public static HorizontalCellStyleStrategy defaultStylePolicyPolicy() {
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置列宽
        // 背景设置为浅蓝色
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        //设置水平对齐方式
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        headWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置字体为微软雅黑
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontName("宋体");
        headWriteCellStyle.setWriteFont(headWriteFont);

        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置字体为微软雅黑
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontName("宋体");
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 这个策略是 头是头的样式 内容是内容的样式
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }


    /**
     * 通用xlsx模板下载
     *
     * @param response
     * @param fileName
     */
    public static void templateDownload(HttpServletResponse response, String fileName) {
        response.setCharacterEncoding("utf-8");
        response.setContentType("text/html;charset=utf-8");
        response.setContentType("multipart/form-data");
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        try {
            InputStream inputStream = EasyExcelUtils.class.getResourceAsStream("/static/template/" + fileName);
            if (inputStream == null) {
                return;
            }
            OutputStream os = response.getOutputStream();
            byte[] b = new byte[2048];
            int length;
            while ((length = inputStream.read(b)) > 0) {
                os.write(b, 0, length);
            }
            os.close();
            inputStream.close();
        } catch (IOException e) {
            log.error(e.getMessage());
        }
    }

}

/**
 * 自适应列宽
 */
class CustomCellWriteWidthConfig extends AbstractColumnWidthStyleStrategy {

    private final Map<String, Map<Integer, Integer>> CACHE = new HashMap<>();

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer integer, Boolean isHead) {
        boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
        if (needSetWidth) {
            Map<Integer, Integer> maxColumnWidthMap = CACHE.computeIfAbsent(writeSheetHolder.getSheetName() + writeSheetHolder.getSheetNo(), k -> new HashMap<>());

            Integer columnWidth = this.dataLength(cellDataList, cell, isHead);
            // 单元格文本长度大于60换行
            if (columnWidth >= 0) {
                if (columnWidth > 60) {
                    columnWidth = 60;
                }
                Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
                if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
                    maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                    Sheet sheet = writeSheetHolder.getSheet();
                    sheet.setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
                }
            }
        }
    }

    /**
     * 计算长度
     *
     * @param cellDataList
     * @param cell
     * @param isHead
     * @return
     */
    private Integer dataLength(List<WriteCellData<?>> cellDataList, Cell cell, Boolean isHead) {
        if (isHead) {
            return cell.getStringCellValue().getBytes().length;
        } else {
            CellData<?> cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
                return -1;
            } else {
                switch (type) {
                    case STRING:
                        // 换行符（数据需要提前解析好）
                        int index = cellData.getStringValue().indexOf("\n");
                        return index != -1 ?
                                cellData.getStringValue().substring(0, index).getBytes().length + 1 : cellData.getStringValue().getBytes().length + 1;
                    case BOOLEAN:
                        return cellData.getBooleanValue().toString().getBytes().length;
                    case NUMBER:
                        return cellData.getNumberValue().toString().getBytes().length;
                    default:
                        return -1;
                }
            }
        }
    }
}

/**
 * 自适应行高
 */
class CustomCellWriteHeightConfig extends AbstractRowHeightStyleStrategy {
    /**
     * 默认高度
     */
    private static final Integer DEFAULT_HEIGHT = 300;

    @Override
    protected void setHeadColumnHeight(Row row, int relativeRowIndex) {
    }

    @Override
    protected void setContentColumnHeight(Row row, int relativeRowIndex) {
        Iterator<Cell> cellIterator = row.cellIterator();
        if (!cellIterator.hasNext()) {
            return;
        }
        // 默认为 1行高度
        int maxHeight = 1;
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            if (cell.getCellTypeEnum() == CellType.STRING) {
                String value = cell.getStringCellValue();
                int len = value.length();
                int num = 0;
                if (len > 50) {
                    num = len % 50 > 0 ? len / 50 : len / 2 - 1;
                }
                if (num > 0) {
                    for (int i = 0; i < num; i++) {
                        value = value.substring(0, (i + 1) * 50 + i) + "\n" + value.substring((i + 1) * 50 + i, len + i);
                    }
                }
                if (value.contains("\n")) {
                    int length = value.split("\n").length;
                    maxHeight = Math.max(maxHeight, length) + 1;
                }
            }
        }
        row.setHeight((short) ((maxHeight) * DEFAULT_HEIGHT));
    }

}

/**
 * 合并行策略
 */
class MergeRowStrategy implements RowWriteHandler {

    /**
     * 需要合并的依据的列下表数组（从 0 开始）,例如传{1,2}则判断第二列和第三列是否相同,相同则合并mergeColumnIndexList对应列
     */
    private final int[] mergeByColumn;
    /**
     * 需合并的列下标数组（从 0 开始）
     */
    private final int[] mergeColumnIndexList;

    /**
     * 是否清空冗余内容
     */
    private boolean isCleanSurplusContent = true;

    /**
     * 合并 Key
     */
    private String mergeKey = null;
    /**
     * 合并起始行下标
     */
    private int mergeStartRowIndex;

    /**
     * @param mergeByColumn        需要合并的依据的列下表数组
     * @param mergeColumnIndexList 需合并的列下标数组
     */
    public MergeRowStrategy(int[] mergeByColumn, int[] mergeColumnIndexList) {

        this.mergeByColumn = mergeByColumn;
        this.mergeColumnIndexList = mergeColumnIndexList;
    }

    @Override
    public void afterRowDispose(RowWriteHandlerContext context) {
        if (context.getHead() || context.getRelativeRowIndex() == null) {
            return;
        }

        //需要合并的依据组合
        StringJoiner sj = new StringJoiner("&-&");
        for (int index : mergeByColumn) {
            sj.add(context.getRow().getCell(index).getStringCellValue());
        }

        if (context.getRelativeRowIndex() == 0) {
            this.mergeKey = sj.toString();
            this.mergeStartRowIndex = context.getRowIndex();
            return;
        }

        String currentKey = sj.toString();
        if (!Objects.equals(currentKey, this.mergeKey)) {
            this.mergeKey = currentKey;
            this.mergeStartRowIndex = context.getRowIndex();
            return;
        } else {
            //清空当前要合并单元格的内容
            for (Integer index : mergeColumnIndexList) {
                context.getWriteSheetHolder().getSheet().getRow(context.getRowIndex()).getCell(index).setCellValue("");
            }

        }

        for (Integer columnIndex : mergeColumnIndexList) {
            CellRangeAddress cellRangeAddress = new CellRangeAddress(
                    mergeStartRowIndex,
                    context.getRowIndex(),
                    columnIndex,
                    columnIndex);
            context.getWriteSheetHolder().getSheet().addMergedRegionUnsafe(cellRangeAddress);
        }
    }
}