easyexcel工具类，导入导出带对象/不带对象的读写

weixin_40261504

已于 2023-08-16 14:01:40 修改

阅读量918

点赞数

文章标签： java 开发语言 excel

于 2023-08-16 11:17:51 首次发布

本文链接：https://blog.csdn.net/weixin_40261504/article/details/132315175

版权

springboot项目中使用easyexcel导入excel文件工具类：

easyexcel官方文档：

关于Easyexcel | Easy Excel

1.引入pom依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.0</version>
</dependency>

2.导入导出工具代码

@Slf4j
public class ExcelUtils {
        /**
     * 不创建对象的写
     * @param headList
     * @param dataList
     * @throws IOException
     */
    public static void exportExcel(HttpServletResponse response, String filename, String sheetName,
                                   List<List<String>> headList, List<List<String>> dataList) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(out).registerWriteHandler(setHorizontalCellStyleStrategy()).head(headList).build();
        WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
        excelWriter.write(dataList, sheet);
        excelWriter.finish();
        byte[] content = out.toByteArray();
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename +".xlsx", "UTF-8"));
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM.toString());
        response.setHeader("Content-Length", "" + content.length);
        OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
        outputStream.write(content);
        outputStream.close();
    }

    /**
     * 带对象的写
     * @param head
     * @param data
     * @param <T>
     * @throws IOException
     */
    public static <T> void exportExcel(HttpServletResponse response, String filename, String sheetName,
                              Class<T> head, List<T> data) throws IOException {
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        ExcelWriter excelWriter = EasyExcel.write(out, head).registerWriteHandler(setHorizontalCellStyleStrategy()).build();
        WriteSheet sheet = EasyExcel.writerSheet(sheetName).build();
        excelWriter.write(data, sheet);
        excelWriter.finish();
        byte[] content = out.toByteArray();
        response.reset();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename +".xlsx", "UTF-8"));
        response.setContentType(MediaType.APPLICATION_OCTET_STREAM.toString());
        response.setHeader("Content-Length", "" + content.length);
        OutputStream outputStream = new BufferedOutputStream(response.getOutputStream());
        outputStream.write(content);
        outputStream.close();
    }

    /**
     * excel  样式处理
     *
     * @return
     */
    private static HorizontalCellStyleStrategy setHorizontalCellStyleStrategy() {
        /*******自定义列标题和内容的样式******/
        // 头的策略
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        // 背景设置
        headWriteCellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 10);
        headWriteCellStyle.setWriteFont(headWriteFont);
        // 内容的策略
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        // 背景绿色
        WriteFont contentWriteFont = new WriteFont();
        // 字体大小
        contentWriteFont.setFontHeightInPoints((short) 10);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        //设置 自动换行
        contentWriteCellStyle.setWrapped(true);
        //设置 垂直居中
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置 水平居中
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //设置边框样式
        return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
    }

    /**
     * 读excel，不限制条数
     * @param file  文件
     * @param clazz 读取成的对象
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> List<T> readExcel(MultipartFile file, Class<T> clazz) throws IOException {
        return readExcel(file, -1, clazz);
    }

    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     *      读取成对象
     * @param file  文件
     * @param limitLines    限制条数
     * @param clazz     读取成的对象
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(MultipartFile file, int limitLines, Class<T> clazz) throws IOException {
        ExcelListener<T> excelListener = readExcelListener(file, limitLines, clazz);
        return Convert.toList(clazz, excelListener.getDataList());
    }

    /**
     * 解析导入excel数据  读取成map
     * @param file  文件
     * @param lineLimit 限制条数
     * @return
     * @throws IOException
     */
    public static List<Map<Integer, String>> readExcel(MultipartFile file, Integer lineLimit) throws IOException {
        ExcelListener<Map<Integer, String>> excelListener = readExcelListener(file, lineLimit);
        return excelListener.getDataList();
    }

    /**
     * 读取excel对象 listener
     * @param file  文件
     * @param limitLines  限制条数
     * @param clazz
     * @param <T>
     * @return
     * @throws IOException
     */
    public static <T> ExcelListener<T> readExcelListener(MultipartFile file, int limitLines, Class<T> clazz) throws IOException {
        ExcelListener<T> excelListener = new ExcelListener<T>(limitLines);
        return readExcelListener(file, clazz, excelListener);
    }

    /**
     * 读取excel map listener
     * @param file  文件
     * @param lineLimit 限制条数
     * @return
     * @throws IOException
     */
    public static ExcelListener<Map<Integer, String>> readExcelListener(MultipartFile file, Integer lineLimit) throws IOException {
        ExcelListener<Map<Integer, String>> excelListener = new ExcelListener<>(lineLimit);
        return readExcelListener(file, null, excelListener);
    }

    private static <T> ExcelListener<T> readExcelListener(MultipartFile file, Class<T> clazz, ExcelListener<T> excelListener) throws IOException {
        ExcelReader excelReader = getReader(file.getInputStream(), file.getOriginalFilename(), clazz, excelListener);
        if (excelReader == null) {
            return excelListener;
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        for (ReadSheet readSheet : readSheetList) {
            excelReader.read(readSheet);
        }
        excelReader.finish();
        return excelListener;
    }

    private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener<T> excelListener) {
        try {
            if (filename == null || !filename.toLowerCase().endsWith(".xlsx")) {
                return null;
            }
            ExcelReader excelReader = clazz == null ? EasyExcel.read(inputStream, excelListener).build() :
                    EasyExcel.read(inputStream, clazz, excelListener).build();
            inputStream.close();
            return excelReader;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

自定义数据监听器

@Slf4j
public class ExcelListener<T> extends AnalysisEventListener<T> {
    /**
     * 可以通过实例获取该值
     */
    private List<T> dataList = new ArrayList<>();

    /**
     * 表头map（单行表头），列号-->列名
     */
    private Map<Integer, String> headMap = new LinkedHashMap<>();

    /**
     * 读取行数最大值（含表头）<br>
     * limit: -1 默认 不限制 <br>
     *        0  不限制    <br>
     *        n  限制行数   <br>
     */
    private int limit = -1;

    public ExcelListener() {
    }

    public ExcelListener(int limit) {
        this.limit = limit;
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        this.headMap.putAll(headMap);
    }

    @Override
    public void invoke(T object, AnalysisContext context) {
        Integer rowNumber = context.readSheetHolder().getApproximateTotalRowNumber();

        if (this.limit > 0 && rowNumber > this.limit) {
            throw new ExcelAnalysisException("导入数据内容超出总行数["+this.limit+"]条限制" );
        }
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        log.info("解析到一条数据:{}", JSONUtil.toJsonStr(object));
        dataList.add(object);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //非必要语句，查看导入的数据
        System.out.println("导入的数据条数为: " + dataList.size());
    }

    /************************************** get && set ***********************************************************/

    public List<T> getDataList() {
        return dataList;
    }

    public void setDataList(List<T> dataList) {
        this.dataList = dataList;
    }

    public Map<Integer, String> getHeadMap() {
        return headMap;
    }

    public void setHeadMap(Map<Integer, String> headMap) {
        this.headMap = headMap;
    }
}