SpringBoot集成Hutool 实现数据库数据导出到Excel 功能

日常开发中，在B端页面上，经常需要将数据库中的数据，导出到Excel。我们今天用Hutool工具包，实现一下这个功能。

一、Hutool简介

Hutool是一个功能丰富且易用的Java工具库，通过诸多实用工具类的使用，旨在帮助开发者快速、便捷地完成各类开发任务。 这些封装的工具涵盖了字符串、数字、集合、编码、日期、文件、IO、加密、数据库JDBC、JSON、HTTP客户端等一系列操作， 可以满足各种不同的开发需求。

Pom文件依赖注入

<dependency>
    <groupId>cn.hutool</groupId>
		<artifactId>hutool-all</artifactId>
		<version>5.3.10</version>
</dependency>

二、代码实现

1、实体类

@Data
@EqualsAndHashCode(callSuper = false)
@TableName("test_user")
@ApiModel(value="User对象", description="普通用户表")
public class User implements Serializable {

    private static final long serialVersionUID=1L;

    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    @TableField(fill = FieldFill.INSERT)
    @ApiModelProperty(value = "创建人")
    private Long createBy;

    @TableField(fill = FieldFill.INSERT)
    @ApiModelProperty(value = "创建时间")
    private LocalDateTime createDate;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    @ApiModelProperty(value = "修改人")
    private Long updateBy;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    @ApiModelProperty(value = "修改时间")
    private LocalDateTime updateDate;

    @TableField(fill = FieldFill.INSERT)
    @ApiModelProperty(value = "删除状态")
    @TableLogic
    private Integer deleted;

    @ApiModelProperty(value = "邮箱")
    private String userName;

    @ApiModelProperty(value = "密码")
    private String password;

    @ApiModelProperty(value = "昵称")
    private String nickName;

    @ApiModelProperty(value = "手机号")
    private String phone;

    @ApiModelProperty(value = "头像地址")
    private String headUrl;

    @ApiModelProperty(value = "用户状态;0:正常;1封禁")
    private Integer status;

}

2、接口定义

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;

@RestController
@RequestMapping("/api/user")
@Api(tags = "用户导出测试")
public class UserExportController {

    @Resource
    private UserExportService userExportService;

    @GetMapping("/export")
    @ApiOperation(value = "导出订单")
    public void export(UserExportRequest request, HttpServletResponse response) {
        userExportService.exportService(request, response);
    }

}

3、DTO

请求对象

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
public class UserExportRequest {

    @ApiModelProperty(value = "邮箱")
    private String userName;

    @ApiModelProperty(value = "手机号")
    private String phone;

    @ApiModelProperty(value = "用户状态;0:正常;1封禁")
    private Integer status;

}

返回对象

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

@Data
public class UserExportResponse {

    @ApiModelProperty(value = "主键ID")
    private String id;

    @ApiModelProperty(value = "创建时间")
    private String createDate;

    @ApiModelProperty(value = "邮箱")
    private String userName;

    @ApiModelProperty(value = "昵称")
    private String nickName;

    @ApiModelProperty(value = "手机号")
    private String phone;

    @ApiModelProperty(value = "用户状态")
    private String status;

}

4、实现类

接口

import javax.servlet.http.HttpServletResponse;

public interface UserExportService {

    void exportService(UserExportRequest request, HttpServletResponse response);

}

实现类

@Service
public class UserExportServiceImpl implements UserExportService {

    @Resource
    private UserMapper userMapper;

    @Override
    public void exportService(UserExportRequest request, HttpServletResponse response) {
        // 初始化查询条件
        LambdaQueryWrapper<User> lambdaQueryWrapper = new LambdaQueryWrapper<>();
        if (StringUtils.isNotBlank(request.getUserName())) {
            lambdaQueryWrapper.like(User::getUserName, request.getUserName());
        }
        if (StringUtils.isNotBlank(request.getPhone())) {
            lambdaQueryWrapper.like(User::getPhone, request.getPhone());
        }
        if (ObjectUtils.isNotEmpty(request.getStatus())) {
            lambdaQueryWrapper.eq(User::getStatus, request.getStatus());
        }
        // 查询用户列表
        List<User> userList = userMapper.selectList(lambdaQueryWrapper);
        // 如果查询不到内容，直接结束方法
        if (CollectionUtils.isEmpty(userList)) {
            return;
        }
        // 组装输出内容
        List<UserExportResponse> userResponseList = userList.stream().map(user -> {
            UserExportResponse userExportResponse = new UserExportResponse();
            BeanUtil.copyProperties(user, userExportResponse, CopyOptions.create().setIgnoreError(true));
            // 用户状态赋值中文
            if (ObjectUtils.isNotEmpty(user.getStatus())) {
                switch (user.getStatus()) {
                    case 0:
                        userExportResponse.setStatus("正常");
                        break;
                    case 1:
                        userExportResponse.setStatus("封禁");
                        break;
                    default:
                        userExportResponse.setStatus("状态异常");
                        break;
                }
            }
            // 创建时间 = 年月日 日分秒
            DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            String createDate = df.format(user.getCreateDate());
            userExportResponse.setCreateDate(createDate);
            return userExportResponse;
        }).collect(Collectors.toList());
        // 导出
        exportExcel(userResponseList, response);
    }

    /**
     * 导出到Excel
     *
     * @param userResponseList    导出内容
     * @param httpServletResponse 输出流
     */
    public void exportExcel(List<UserExportResponse> userResponseList, HttpServletResponse httpServletResponse) {
        // 开始生成Excel
        ExcelWriter excelWriter = ExcelUtil.getWriter();
        // 注入属性
        excelWriter.addHeaderAlias("id", "用户ID");
        excelWriter.addHeaderAlias("userName", "用户邮箱");
        excelWriter.addHeaderAlias("nickName", "用户昵称");
        excelWriter.addHeaderAlias("phone", "手机号");
        excelWriter.addHeaderAlias("status", "用户状态");
        excelWriter.addHeaderAlias("createDate", "创建时间");
        // 写入
        if (!CollectionUtils.isEmpty(userResponseList)) {
            excelWriter.write(userResponseList, true);
        }
        httpServletResponse.setContentType("application/vnd.ms-excel;charset=utf-8");
        httpServletResponse.setHeader("Content-Disposition",
                "attachment;filename=" + URLEncoder.createDefault().encode("用户列表",
                        Charset.defaultCharset()) + ".xls");
        ServletOutputStream out = null;
        try {
            out = httpServletResponse.getOutputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        excelWriter.flush(out, true);
        // 关闭流
        excelWriter.close();
        IoUtil.close(out);
    }

}

三、测试结果

PostMan测试

将二进制流导出为文件，保存到本地

文件内容

所有你想象的一切，皆是现实！