更多操作参考：https://www.yuque.com/easyexcel/doc/easyexcel

增加依赖

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

操作excel

定义导出entity


@Data
@ColumnWidth(25)
@HeadRowHeight(20)
@ContentRowHeight(18)
public class UserExcel implements Serializable {
    private static final long serialVersionUID = 1L;
    @ColumnWidth(15)
    @ExcelProperty("租户编号")
    private String tenantId;
    @ColumnWidth(15)
    @ExcelProperty("账户")
    private String account;
    @ColumnWidth(10)
    @ExcelProperty("昵称")
    private String name;
    @ColumnWidth(10)
    @ExcelProperty("姓名")
    private String realName;
    @ExcelProperty("邮箱")
    private String email;
    @ColumnWidth(15)
    @ExcelProperty("手机")
    private String phone;
    @ExcelIgnore
    @ExcelProperty("角色ID")
    private String roleId;
    @ExcelIgnore
    @ExcelProperty("部门ID")
    private String deptId;
    @ExcelIgnore
    @ExcelProperty("岗位ID")
    private String postId;
    @ExcelProperty("角色名称")
    private String roleName;
    @ExcelProperty("部门名称")
    private String deptName;
    @ExcelProperty("岗位名称")
    private String postName;
    @ColumnWidth(20)
    @ExcelProperty("生日")
    private Date birthday;
}

定义导入监听器


@Data
@RequiredArgsConstructor
@EqualsAndHashCode(callSuper = true)
public class UserImportListener extends AnalysisEventListener<UserExcel> {
    /**
     * 默认每隔3000条存储数据库
     */
    private int batchCount = 3000;
    /**
     * 缓存的数据列表
     */
    private List<UserExcel> list = new ArrayList<>();
    /**
     * 用户service
     */
    private final IUserService userService;
    @Override
    public void invoke(UserExcel data, AnalysisContext context) {
        list.add(data);
        // 达到BATCH_COUNT，则调用importer方法入库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= batchCount) {
            // 调用importer方法
            userService.importUser(list);
            // 存储完成清理list
            list.clear();
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 调用importer方法
        userService.importUser(list);
        // 存储完成清理list
        list.clear();
    }
}

定义UserService


/**
 * 服务类
 *
 * @author xq
 */
public interface IUserService extends BaseService<User> {
        /**
     * 导入用户数据
     *
     * @param data
     * @return
     */
    void importUser(List<UserExcel> data);
    /**
     * 获取导出用户数据
     *
     * @param queryWrapper
     * @return
     */
    List<UserExcel> exportUser(Wrapper<User> queryWrapper);
}
/**
 * 服务实现类
 *
 * @author Chill
 */
@Service
@AllArgsConstructor
public class UserServiceImpl extends BaseServiceImpl<UserMapper, User> implements IUserService {
    @Override
    public void importUser(List<UserExcel> data) {
        data.forEach(userExcel -> {
            User user = Objects.requireNonNull(BeanUtil.copy(userExcel, User.class));
            // 设置部门ID
            user.setDeptId(sysClient.getDeptIds(userExcel.getTenantId(), userExcel.getDeptName()));
            // 设置岗位ID
            user.setPostId(sysClient.getPostIds(userExcel.getTenantId(), userExcel.getPostName()));
            // 设置角色ID
            user.setRoleId(sysClient.getRoleIds(userExcel.getTenantId(), userExcel.getRoleName()));
            // 设置默认密码
            user.setPassword(CommonConstant.DEFAULT_PASSWORD);
            this.submit(user);
        });
    }
    @Override
    public List<UserExcel> exportUser(Wrapper<User> queryWrapper) {
        List<UserExcel> userList = baseMapper.exportUser(queryWrapper);
        userList.forEach(user -> {
            user.setRoleName(StringUtil.join(sysClient.getRoleNames(user.getRoleId())));
            user.setDeptName(StringUtil.join(sysClient.getDeptNames(user.getDeptId())));
            user.setPostName(StringUtil.join(sysClient.getPostNames(user.getPostId())));
        });
        return userList;
    }   
}

定义mapper

public interface UserMapper extends BaseMapper<User> {
    /**
     * 获取导出用户数据
     *
     * @param queryWrapper
     * @return
     */
    List<UserExcel> exportUser(@Param("ew") Wrapper<User> queryWrapper);
}
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.springblade.system.user.mapper.UserMapper">
    <!-- 通用查询映射结果 -->
    <resultMap id="userResultMap" type="org.springblade.system.user.entity.User">
        <result column="id" property="id"/>
        <result column="tenant_id" property="tenantId"/>
        <result column="create_user" property="createUser"/>
        <result column="create_time" property="createTime"/>
        <result column="update_user" property="updateUser"/>
        <result column="update_time" property="updateTime"/>
        <result column="status" property="status"/>
        <result column="is_deleted" property="isDeleted"/>
        <result column="code" property="code"/>
        <result column="account" property="account"/>
        <result column="password" property="password"/>
        <result column="name" property="name"/>
        <result column="real_name" property="realName"/>
        <result column="email" property="email"/>
        <result column="phone" property="phone"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="role_id" property="roleId"/>
        <result column="dept_id" property="deptId"/>
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="baseColumnList">
        select id,
        create_user AS createUser,
        create_time AS createTime,
        update_user AS updateUser,
        update_time AS updateTime,
        status,
        is_deleted AS isDeleted,
        account, password, name, real_name, email, phone, birthday, sex, role_id, dept_id
    </sql>
    <select id="exportUser" resultType="org.clown.system.user.excel.UserExcel">
        SELECT id, tenant_id, account, name, real_name, email, phone, birthday, role_id, dept_id, post_id FROM user ${ew.customSqlSegment}
    </select>
</mapper>

定义导入导出接口

@RestController
@RequestMapping
@AllArgsConstructor
public class UserController {
    private IUserService userService;
    /**
     * 导入用户
     */
    @PostMapping("import-user")
    @ApiOperationSupport(order = 12)
    @ApiOperation(value = "导入用户", notes = "传入excel")
    public R importUser(MultipartFile file, Integer isCovered) {
        String filename = file.getOriginalFilename();
        if (StringUtils.isEmpty(filename)) {
            throw new RuntimeException("请上传文件!");
        }
        if ((!StringUtils.endsWithIgnoreCase(filename, ".xls") && !StringUtils.endsWithIgnoreCase(filename, ".xlsx"))) {
            throw new RuntimeException("请上传正确的excel文件!");
        }
        InputStream inputStream;
        try {
            UserImportListener importListener = new UserImportListener(userService);
            inputStream = new BufferedInputStream(file.getInputStream());
            ExcelReaderBuilder builder = EasyExcel.read(inputStream, UserExcel.class, importListener);
            builder.doReadAll();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return R.success("操作成功");
    }
    /**
     * 导出用户
     */
    @SneakyThrows
    @GetMapping("export-user")
    @ApiOperationSupport(order = 13)
    @ApiOperation(value = "导出用户", notes = "传入user")
    public void exportUser(@ApiIgnore @RequestParam Map<String, Object> user, BladeUser bladeUser, HttpServletResponse response) {
        QueryWrapper<User> queryWrapper = Condition.getQueryWrapper(user, User.class);
        if (!SecureUtil.isAdministrator()){
            queryWrapper.lambda().eq(User::getTenantId, bladeUser.getTenantId());
        }
        queryWrapper.lambda().eq(User::getIsDeleted, BladeConstant.DB_NOT_DELETED);
        List<UserExcel> list = userService.exportUser(queryWrapper);
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(Charsets.UTF_8.name());
        String fileName = URLEncoder.encode("用户数据导出", Charsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("用户数据表").doWrite(list);
    }
    /**
     * 导出模板
     */
    @SneakyThrows
    @GetMapping("export-template")
    @ApiOperationSupport(order = 14)
    @ApiOperation(value = "导出模板")
    public void exportUser(HttpServletResponse response) {
        List<UserExcel> list = new ArrayList<>();
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding(Charsets.UTF_8.name());
        String fileName = URLEncoder.encode("用户数据模板", Charsets.UTF_8.name());
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), UserExcel.class).sheet("用户数据表").doWrite(list);
    }
}

SpringBoot使用easyexcel操作excel

增加依赖

操作excel

定义导出entity

定义导入监听器

定义UserService

定义mapper

定义导入导出接口