阿里开源的Easyexcel使用

Easyexcel

easyExcel是阿里巴巴开源的一个excel处理框架，以使用简单、节省内存著称。

Easyexcel的使用

1. 引入依赖

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

2. 创建实体类，添加注解设置表头

import com.alibaba.excel.annotation.ExcelProperty;

public class UserData {
    //设置表头
    @ExcelProperty("用户编号")
    private int uid;
    @ExcelProperty("用户名称")
    private String username;

    public int getUid() {
        return uid;
    }

    public void setUid(int uid) {
        this.uid = uid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }
    
    @Override
    public String toString() {
        return "UserData{" +
                "uid=" + uid +
                ", username='" + username + ''' +
                '}';
    }
}

实现写操作

import com.alibaba.excel.EasyExcel;

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

public class TestWrite {
    public static void main(String[] args) {
        //构建数据List集合
        List<UserData> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            UserData data = new UserData();
            data.setUid(i);
            data.setUsername("lucy" + i);
            list.add(data);
        }
        //设置excel文件路径和文件名称
        String fileName = "E:\test.xlsx";
        //调用方法实现写操作
        EasyExcel.write(fileName,UserData.class).sheet("用户信息").doWrite(list);
    }
}

实现读操作

1. 编写监听器

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.Map;

public class EasyListener extends AnalysisEventListener<UserData> {

    //读取表头信息
    @Override
    public void invokeHeadMap(Map<Integer,String> headMap, AnalysisContext context) {
        System.out.println("表头信息：" + headMap);
    }
    
    //一行一行读取excel内容，从第二行读取
    @Override
    public void invoke(UserData userData, AnalysisContext analysisContext) {
        System.out.println(userData);
    }

    //读取之后执行
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

2. 读取excel

import com.alibaba.excel.EasyExcel;

public class TestRead {
    public static void main(String[] args) {
        //读取文件路径
        String fileName = "E:\test.xlsx";
        //调用方法实现读取操作
        EasyExcel.read(fileName,UserData.class,new EasyListener()).sheet().doRead();
    }
}

接口导入excel

try {
        //获取文件名
        String filename = file.getOriginalFilename();
        //获取文件流
        InputStream inputStream = file.getInputStream();
        //实例化实现了AnalysisEventListener接口的类
        ExcelListener listener = new ExcelListener();
        //传入参数
        ExcelReader excelReader = new ExcelReader(inputStream, ExcelTypeEnum.XLS, null, listener);
        //读取信息
        excelReader.read(new Sheet(1, 0, Test.class));
        //获取数据
        List<Object> list = listener.getDatas();
        if (list.size() > 1) {
            for (int i = 0; i < list.size(); i++) {
                Testobj = (Test) list.get(i);
                JSONObject jo = new JSONObject();
            }
        }
    } catch (Exception e) {
        System.out.println(e.getMessage());
    }

接口导出excel

try {
    String filenames = "111111";
    String userAgent = request.getHeader("User-Agent");
    if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
        filenames = URLEncoder.encode(filenames, "UTF-8");
    } else {
        filenames = new String(filenames.getBytes("UTF-8"), "ISO-8859-1");
    }
    response.setContentType("application/vnd.ms-exce");
    response.setCharacterEncoding("utf-8");
    response.addHeader("Content-Disposition", "filename=" + filenames + ".xlsx");
    EasyExcel.write(response.getOutputStream(), Test.class).sheet("sheet").doWrite(testList);
} catch (Exception e) {
}