EasyExcel导入、导出数据

EasyExcel

官方解析

Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。
easyexcel重写了poi对07版Excel的解析，一个3M的excel用POI sax解析依然需要100M左右内存，改用easyexcel可以降低到几M，并且再大的excel也不会出现内存溢出；03版依赖POI的sax模式，在上层做了模型转换的封装，让使用者更加简单方便

网站

• 官方网站：https://easyexcel.opensource.alibaba.com/
• github地址：https://github.com/alibaba/easyexcel
• gitee地址：https://gitee.com/easyexcel/easyexcel

写Excel

最简单写

示例

模板对象

@Data
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private String dateTime;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

插入值

private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDateTime(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

开始写

/**
     * 最简单的写
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入
        // 写法1 JDK8+
        // since: 3.0.0-beta1
        String path = "D:\\excel\\";
        String fileName = path + "simpleWrite.xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
        System.out.println("excel导出成功");
    }

根据参数只导出指定列

示例：排除导出日期时间列

模板对象和插入值和以上一致

开始写

@Test
    public void excludeOrIncludeWrite() {
        String path = "D:\\excel\\";
        String fileName = path + "excludeOrIncludeWrite" + ".xlsx";
        // 这里需要注意 在使用ExcelProperty注解的使用，如果想不空列则需要加入order字段，
        // 而不是index,order会忽略空列，然后继续往后，而index，不会忽略空列，在第几列就是第几列。
        // 根据用户传入字段 假设我们要忽略 date
        Set<String> excludeColumnFiledNames = new HashSet<String>();
        //将不想导出的列放到一个集合中
        excludeColumnFiledNames.add("dateTime");
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).excludeColumnFieldNames(excludeColumnFiledNames).sheet("模板")
                .doWrite(data());
        System.out.println("excel导出成功");
    }

指定写入的列

①、使用index格式

示例：使用index会出现空列情况

模板对象

//index，不会忽略空列，在第几列就是第几列。
@Data
@EqualsAndHashCode
public class IndexData {
    @ExcelProperty(value = "字符串标题", index = 0)
    private String string;
    @ExcelProperty(value = "日期标题", index = 1)
    private String dateTime;
    /**
     * 这里设置3 会导致第二列空的
     */
    @ExcelProperty(value = "数字标题", index = 3)
    private Double doubleData;
}

插入值

private List<IndexData> data() {
        List<IndexData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            IndexData data = new IndexData();
            data.setString("字符串" + i);
            data.setDateTime(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

开始写

/**
     * 指定写入的列
     * <p>1. 创建excel对应的实体对象 参照{@link IndexData}
     * <p>2.
     * <p>3. 直接写即可
     */
    @Test
    public void indexWrite() {
        String path = "D:\\excel\\";
        String fileName = path + "indexWrite" + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, IndexData.class).sheet("模板").doWrite(data());
    }

②、使用order格式

示例：使用order格式不会出现空列情况

模板对象

//order，不会出现空列情况
@Data
@EqualsAndHashCode
public class OrderData {
    @ExcelProperty(value = "字符串标题", order = 0)
    private String string;
    @ExcelProperty(value = "日期标题", order = 1)
    private String dateTime;
    /**
     * 这里设置3 不会出现空列情况
     */
    @ExcelProperty(value = "数字标题", order = 3)
    private Double doubleData;
}

插入值

private List<OrderData> data() {
        List<OrderData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            OrderData data = new OrderData();
            data.setString("字符串" + i);
            data.setDateTime(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

开始写

/**
     * 指定写入的列
     * <p>1. 创建excel对应的实体对象 参照{@link OrderData}
     * <p>2.
     * <p>3. 直接写即可
     */
    @Test
    public void OrderWrite() {
        String path = "D:\\excel\\";
        String fileName = path + "OrderWrite" + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, OrderData.class).sheet("模板").doWrite(data());
    }

复杂头写入

类似类似插入单元格

示例

模板对象

@Getter
@Setter
@EqualsAndHashCode
public class ComplexHeadData {
    @ExcelProperty({"主标题", "字符串标题"})
    private String string;
    @ExcelProperty({"主标题", "日期标题"})
    private Date date;
    @ExcelProperty({"主标题", "数字标题"})
    private Double doubleData;
}

插入值

private List<ComplexHeadData> data() {
        List<ComplexHeadData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            ComplexHeadData data = new ComplexHeadData();
            data.setString("字符串" + i);
            data.setDateTime(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

开始写

/**
     * 复杂头写入
     * <p>1. 创建excel对应的实体对象 参照{@link ComplexHeadData}
     * <p>2.
     * <p>3. 直接写即可
     */
    @Test
    public void complexHeadWrite() {
        String path = "D:\\excel\\";
        String fileName = path + "complexHeadWrite" + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, ComplexHeadData.class).sheet("模板").doWrite(data());
    }

重复多次写入(写到单个或者多个Sheet)

①、写到同一个sheet

示例：同一个数据重复写了五次

模板对象

@Data
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private String dateTime;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

插入值

private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDateTime(new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

写到同一个sheet

/**
     * 重复多次写入
     * <p>
     * 1. 创建excel对应的实体对象
     * 3. 直接调用二次写入即可
     */
    @Test
    public void repeatedWrite() {
        // 方法1: 如果写到同一个sheet
        String path = "D:\\excel\\";
        String fileName = path + "repeatedWrite" + ".xlsx";
        // 这里 需要指定写用哪个class去写
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
            // 这里注意 如果同一个sheet只要创建一次
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
            for (int i = 0; i < 5; i++) {
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                List<DemoData> data = data();
                excelWriter.write(data, writeSheet);
            }
        }
    }

②、写到不同的sheet 同一个对象

示例

开始写入

// 方法2: 如果写到不同的sheet 同一个对象
        // 这里 指定文件
        try (ExcelWriter excelWriter = EasyExcel.write(fileName, DemoData.class).build()) {
            // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
            for (int i = 0; i < 5; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).build();
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                List<DemoData> data = data();
                excelWriter.write(data, writeSheet);
            }
        }

③、写到不同的sheet 不同的对象

示例

开始写入

// 方法3 如果写到不同的sheet 不同的对象
        // 这里 指定文件
        try (ExcelWriter excelWriter = EasyExcel.write(fileName).build()) {
            // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
            for (int i = 0; i < 5; i++) {
                // 每次都要创建writeSheet 这里注意必须指定sheetNo 而且sheetName必须不一样。这里注意DemoData.class 可以每次都变，我这里为了方便 所以用的同一个class
                // 实际上可以一直变
                WriteSheet writeSheet = EasyExcel.writerSheet(i, "模板" + i).head(DemoData.class).build();
                // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
                List<DemoData> data = data();
                excelWriter.write(data, writeSheet);
            }
        }

日期、数字或者自定义格式转换

示例

模板对象

@Data
@EqualsAndHashCode
@Component
public class DemoData{
    @ExcelProperty(value = "字符串标题",index = 0)
    private String title;
    
    @ExcelProperty(value = "日期标题",index = 1)
    private String dateTime;

    @NumberFormat("#.##%")
    @ExcelProperty(value = "数字标题",index = 2)
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

插入值

private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setTitle("字符串" + i);
            data.setDateTime(new DateTime().toString("yyyy年MM月dd日HH时mm分ss秒"));
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

开始导入

/**
     * 日期、数字或者自定义格式转换
     * <p>3. 直接写即可
     */
    @Test
    public void converterWrite() {
        String path = "D:\\excel\\";
        String fileName = path + "converterWrite" + ".xlsx";
        // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
    }

web中的写(实战篇)

实现用户数据导出

前端代码

<el-col :span="1.5">
        <el-button v-if="canAdd" type="primary" size="small" @click="handleExportExcel">导出<i class="el-icon-upload2"></i>
        </el-button>
      </el-col> 

methods:{
	handleExportExcel(){
      window.open("http://localhost:8800/shiyi/system/user/export")
    }
}

后端代码

导出模板数据

@Data
@Builder
@EqualsAndHashCode(callSuper = false)
@AllArgsConstructor
@NoArgsConstructor
public class UserDto implements Serializable {
    @ApiModelProperty(value = "用户账号")
    @ExcelProperty(value = {"用户信息数据","用户账号"},order = 0)
    private String username;

    @ApiModelProperty(value = "登录密码")
    @ExcelProperty(value = {"用户信息数据","登录密码"},order = 1)
    private String password;

    @ApiModelProperty(value = "状态")
    @ExcelProperty(value = {"用户信息数据","状态"},order = 2)
    private Integer status;

    @ApiModelProperty(value = "创建时间")
    @TableField(fill = FieldFill.INSERT)
    @ExcelProperty(value = {"用户信息数据","创建时间"},order = 3)
    private String createTime;

    @ApiModelProperty(value = "最后更新时间")
    @ExcelProperty(value = {"用户信息数据","最后更新时间"},order = 4)
    @TableField(fill = FieldFill.UPDATE)
    private String updateTime;

    @ApiModelProperty(value = "最后登录时间")
    @ExcelProperty(value = {"用户信息数据","最后登录时间"},order = 5)
    @TableField(fill = FieldFill.UPDATE)
    private String lastLoginTime;

    @ApiModelProperty(value = "IP地址")
    @ExcelProperty(value = {"用户信息数据","IP地址"},order = 6)
    private String ipAddress;

    @ApiModelProperty(value = "IP来源")
    @ExcelProperty(value = {"用户信息数据","IP来源"},order = 7)
    private String ipSource;

    @ApiModelProperty(value = "登录系统")
    @ExcelProperty(value = {"用户信息数据","登录系统"},order = 8)
    private String os;

    @ApiModelProperty(value = "浏览器")
    @ExcelProperty(value = {"用户信息数据","浏览器"},order = 9)
    private String browser;
}

数据的值从数据库中查询得到

开始导出

public void export(HttpServletResponse response) throws IOException {
        //文件需要设置成StandardCharsets.ISO_8859_1编码，否则导出的excel文件名乱码
        String excelName = new String("用户信息数据.xlsx".getBytes(), StandardCharsets.ISO_8859_1);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-disposition", "attachment;filename=" + excelName );
    	//从数据库查询所有用户数据
        List<User> userList = baseMapper.selectList(null);

        List<UserDto> userDtoList = new ArrayList<>();
        //数据格式化
        userList.stream().forEach(user -> {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            Date cTime = user.getCreateTime();
            Date uTime = user.getUpdateTime();
            Date lTime = user.getLastLoginTime();
            //日期格式化
            String createTime = simpleDateFormat.format(cTime);
            String updateTime = simpleDateFormat.format(uTime);
            String lastLoginTime = simpleDateFormat.format(lTime);

            UserDto userDto = new UserDto();
            userDto.setBrowser(user.getBrowser());
            userDto.setUsername(user.getUsername());
            userDto.setPassword(user.getPassword());
            userDto.setIpAddress(user.getIpAddress());
            userDto.setIpSource(user.getIpSource());
            userDto.setOs(user.getOs());
            userDto.setStatus(user.getStatus());
            userDto.setCreateTime(createTime);
            userDto.setUpdateTime(updateTime);
            userDto.setLastLoginTime(lastLoginTime);
            userDtoList.add(userDto);
        });
        ServletOutputStream outputStream = response.getOutputStream();
        EasyExcel.write(outputStream, UserDto.class)
                .sheet("user")
                .doWrite(userDtoList);
    	
    }

controller层

@GetMapping("/export")
@ApiOperation(value = "导出用户数据", httpMethod = "GET", response = ResponseResult.class, notes = "导出用户数据")
    public void excel(HttpServletResponse response) throws IOException {
        userService.export(response);
    }

浏览器直接访问：http://localhost:端口号/export

读Excel

实现用户数据导入

web中的读（实战篇）

模板对象

@Data
@Builder
@EqualsAndHashCode(callSuper = false)
@AllArgsConstructor
@NoArgsConstructor
public class UserDto implements Serializable {
    @ApiModelProperty(value = "用户账号")
    @ExcelProperty(value = {"用户信息数据","用户账号"},order = 0)
    private String username;

    @ApiModelProperty(value = "登录密码")
    @ExcelProperty(value = {"用户信息数据","登录密码"},order = 1)
    private String password;

    @ApiModelProperty(value = "状态")
    @ExcelProperty(value = {"用户信息数据","状态"},order = 2)
    private Integer status;

    @ApiModelProperty(value = "创建时间")
    @TableField(fill = FieldFill.INSERT)
    @ExcelProperty(value = {"用户信息数据","创建时间"},order = 3)
    private String createTime;

    @ApiModelProperty(value = "最后更新时间")
    @ExcelProperty(value = {"用户信息数据","最后更新时间"},order = 4)
    @TableField(fill = FieldFill.UPDATE)
    private String updateTime;

    @ApiModelProperty(value = "最后登录时间")
    @ExcelProperty(value = {"用户信息数据","最后登录时间"},order = 5)
    @TableField(fill = FieldFill.UPDATE)
    private String lastLoginTime;

    @ApiModelProperty(value = "IP地址")
    @ExcelProperty(value = {"用户信息数据","IP地址"},order = 6)
    private String ipAddress;

    @ApiModelProperty(value = "IP来源")
    @ExcelProperty(value = {"用户信息数据","IP来源"},order = 7)
    private String ipSource;

    @ApiModelProperty(value = "登录系统")
    @ExcelProperty(value = {"用户信息数据","登录系统"},order = 8)
    private String os;

    @ApiModelProperty(value = "浏览器")
    @ExcelProperty(value = {"用户信息数据","浏览器"},order = 9)
    private String browser;
}

开始导入

//用户数据导入
    @Override
    public void excelImport(MultipartFile file) throws IOException {
        List<UserDto> list = new ArrayList<>();
        EasyExcel.read(file.getInputStream(), UserDto.class, new PageReadListener<UserDto>(dataList -> {
            for (UserDto demoData : dataList) {
                //将JSON对象转换成实体类
                UserDto entity = JSONObject.parseObject(JSONObject.toJSONString(demoData), UserDto.class);
                list.add(entity);
            }
        })).sheet().doRead();
        //将导入的用户数据保存到数据库中
        list.stream().forEach(userDto ->{
            //设置值
            UserInfo userInfo = new UserInfo();
            userInfo.setAvatar("http://img.shiyit.com/66bb121d47e94b89945d29bb6e3e6cab.jpg");
            userInfo.setIntro("游客");
            String nickname = "游客" + RandomUtils.generationCapital(4) + RandomUtils.generationNumber(6);
            userInfo.setNickname(nickname);
            //用户信息绑定
            userInfoMapper.insert(userInfo);

            //查询获取userInfoId
            LambdaQueryWrapper<UserInfo> wrapper = new LambdaQueryWrapper<>();
            wrapper.eq(UserInfo::getNickname,nickname);
            UserInfo selectOne = userInfoMapper.selectOne(wrapper);
            Integer userInfoId = selectOne.getId();

            User user = new User();
            user.setUsername(userDto.getUsername());
            //密码加密
            user.setPassword(AesEncryptUtils.aesEncrypt(userDto.getPassword()));
            user.setStatus(userDto.getStatus());
            user.setRoleId(2);
            user.setLoginType(1);
            user.setUserInfoId(userInfoId);
            user.setOs(userDto.getOs());
            user.setIpAddress(userDto.getIpAddress());
            user.setIpSource(userDto.getIpSource());
            user.setBrowser(userDto.getBrowser());
            userService.save(user);
        });
    }

//PageReadListener此监听器可以自己实现，这里用的是官方封装好的。

Controller层

@PostMapping("/import")
@ApiOperation(value = "导入用户数据", httpMethod = "GET", response = ResponseResult.class, notes = "导入用户数据")
    public ResponseResult excelImport(MultipartFile file) throws IOException {
        userService.excelImport(file);
        return ResponseResult.success();
    }