EasyExcel工具类实现导入导出

**

java利用EasyExcel简单快捷实现文件的导入导出功能

**

EasyExcel特点

生成Excel比较有名的框架有Apache poi,jxl等，但都存在一个严重的问题就是非常的耗内存，系统并发量不大的话可能还行，否则并发上来后一定会OOM或者JVM频繁的full gc.

EasyExcel是阿里巴巴开源的一个excel处理框架，以使用简单，节省内存著称，EasyExcel能大大减少占用内存的主要原因是在解析Excel时没有将文件数据一次性全部加载到内存中，而是从磁盘上一行行读取数据，逐个解析。

EasyExcel采用一行一行的解析模式，并将一行的解析结果以观察者的模式通知处理（AnalysisEventListener）。

所需依赖

        <!-- 阿里巴巴Excel处理-->
        <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>

        <!-- 工具类-->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.4.0</version>
        </dependency>

hutool工具类十分强大，可自行到官网查看文档。https://www.hutool.cn/docs/#/**

导入导出实体类

public class TyGoodsCategory implements Serializable {
    private static final long serialVersionUID = -41708876706950978L;

    @ExcelProperty(value = "ID",index = 0)
    private Integer id;
    /**
     * 类型 1:产品分类 2:渠道分类 3:食材库分类 4:成品库分类
     */
    @ExcelProperty(value = "类型",index = 1)
    private Byte type;

    /**
     * 商品分类名称
     */
    @ExcelProperty(value = "商品分类名称",index = 2)
    private String name;

    /**
     * 父级id
     */
    @ExcelProperty(value = "父级id",index = 3)
    private Integer pId;
    /**
     * 家族图谱
     */
    @ExcelProperty(value = "家族图谱",index = 4)
    private String path;
    /**
     * 等级
     */
    @ExcelProperty(value = "等级",index = 5)
    private Byte level;
    /**
     * 顺序排序
     */
    @ExcelProperty(value = "顺序排序",index = 6)
    private Integer sort;
    /**
     * 1显示  0不显示
     */
    @ExcelProperty(value = "是否显示",index = 7)
    private Boolean isShow;
    /**
     * 1热门 0不否
     */
    @ExcelProperty(value = "是否热门",index = 8)
    private boolean isHot;

/*    @TableField(fill = FieldFill.INSERT_UPDATE)//创建与修改时自动填充
    private Date updateTime;*/

    @ExcelProperty(value = "创建时间",index = 9)
    @TableField(fill = FieldFill.INSERT) //创建时自动填充
    //@JsonFormat(timezone = "GMT+8", pattern = "yyyy-MM-dd HH:mm:ss")
    private Date createTime;

    @ExcelProperty(value = "更新时间",index = 10)
    @TableField(fill = FieldFill.UPDATE)
    private Date updateTime;

    /**
     * 子类集合
     */
    // 不需要导出的字段
    @ExcelIgnore
    // 不属于数据库字段
    @TableField(exist = false)
    private List<TyGoodsCategory> childrenList;
    }

备注：不需要导出的字段可用注解@ExcelIgnore标识

excel导入监听类

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

    @Override
    public void invoke(Object object, AnalysisContext context) {
        //数据存储到list，供批量处理，或后续自己业务逻辑处理。
        dataList.add(object);
    }

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

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

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

导入导出工具类

备注：该工具类可直接传入结果集调用fileExport（）方法进行excel文件导出。导入调用readExcel（）方法可以直接转换为list集合

package com.ruoyi.common.core.utils;


import cn.hutool.core.convert.Convert;
import cn.hutool.core.date.DateUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.ruoyi.common.core.constant.HttpStatus;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

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



/**
 * 导入导出工具类
 */
public class ExcelSelfUtil {

    /**
     *  通用导出
     * @param list 数据集合
     * @param response
     * @param clazz 对象类型  *.class
     */
    public static void fileExport(List<?> list, HttpServletResponse response, Class<?> clazz) throws IOException {
        ServletOutputStream out = response.getOutputStream();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(System.currentTimeMillis() + ".csv", "UTF-8"));
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(out, clazz).autoCloseStream(true).registerWriteHandler(horizontalCellStyleStrategy).sheet("信息列表").doWrite(list);
        out.flush();
    }

    public static void fileExportForName(List<?> list, String fileName, HttpServletResponse response, Class<?> clazz) throws IOException {
        ServletOutputStream out = response.getOutputStream();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment;filename*=utf-8'zh_cn'" + URLEncoder.encode(fileName + ".csv", "UTF-8"));
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(out, clazz).autoCloseStream(true).registerWriteHandler(horizontalCellStyleStrategy).sheet("信息列表").doWrite(list);
        out.flush();
    }

    /**
     * 读取Excel（多个sheet可以用同一个实体类解析）
     * @param excelInputStream
     * @param fileName
     * @param clazz
     * @param <T>
     * @return
     */
    public static <T> List<T> readExcel(InputStream excelInputStream, String fileName, Class<T> clazz) {
        ExcelListener excelListener = new ExcelListener();
        ExcelReader excelReader = getReader(excelInputStream, fileName,clazz, excelListener);
        if (excelReader == null) {
            return new ArrayList<>();
        }
        List<ReadSheet> readSheetList = excelReader.excelExecutor().sheetList();
        for (ReadSheet readSheet : readSheetList) {
            excelReader.read(readSheet);
        }
        excelReader.finish();
        return Convert.toList(clazz, excelListener.getDataList());
    }



    /**
     * 导出
     *
     * @param response
     * @param data
     * @param fileName
     * @param sheetName
     * @param clazz
     * @throws Exception
     */
    public static void writeExcel(HttpServletResponse response, List<?> data, String fileName, String sheetName, Class clazz) throws Exception {
        OutputStream outputStream = getOutputStream(fileName, response);
        EasyExcel.write(outputStream, clazz).autoCloseStream(true).sheet(sheetName).doWrite(data);
        outputStream.flush();
    }

    /**
     * 流方式响应给前端
     * @param fileName
     * @param response
     * @return
     * @throws Exception
     */
    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".csv");
        response.setStatus(HttpStatus.SUCCESS);
        return response.getOutputStream();
    }

    /**
     * 返回ExcelReader
     *
     * @param clazz 实体类
     * @param excelListener
     */
    private static <T> ExcelReader getReader(InputStream inputStream, String filename, Class<T> clazz, ExcelListener excelListener) {
        try {
            if (filename == null ||
                    (!filename.toLowerCase().endsWith(".xls") && !filename.toLowerCase().endsWith(".csv"))) {
                return null;
            }
            ExcelReader excelReader = EasyExcel.read(inputStream, clazz, excelListener).build();
            inputStream.close();
            return excelReader;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}

测试 Controller

    /**
     * 导出测试
     * @param tyGoodsCategory
     * @param response
     */
    @PostMapping("test1")
    public void test1(@RequestBody TyGoodsCategory tyGoodsCategory, HttpServletResponse response){
        MyAssert.MyAssert4Operation.notNull(tyGoodsCategory);
        MyAssert.MyAssert4Operation.notNull(tyGoodsCategory.getType(), "未指定查询的分类类型！");
        tyGoodsCategoryService.test1(tyGoodsCategory, response);
    }

    /**
     * 导入测试
     * @param excel
     * @return  返回值为导入的数据集合
     * @throws IOException
     */
    @PostMapping(value = "/import")
    public List<TyGoodsCategory> read(@RequestParam("files") MultipartFile excel) throws IOException {
        return ExcelUtil.readExcel(excel.getInputStream(), excel.getOriginalFilename(), TyGoodsCategory.class);
    }

测试 service

    @Override
    public void test1(TyGoodsCategory tyGoodsCategory, HttpServletResponse response) {
        QueryWrapper<TyGoodsCategory> wrapper = new QueryWrapper<>();
        wrapper.eq("type", tyGoodsCategory.getType());
        Page<TyGoodsCategory> page = new Page<>(tyGoodsCategory.getPageNum(), tyGoodsCategory.getPageSize());
        // 自定义sql查询 可用于多表
        Page<TyGoodsCategory> tyGoodsCategoryPage = tyGoodsCategoryDao.selectMyPage(page, tyGoodsCategory);
        List<TyGoodsCategory> records = tyGoodsCategoryPage.getRecords();
        ExcelUtil.fileExport(records, response, TyGoodsCategory.class);
    }

调用导出接口![Alt]