Spring Boot项目中实现Excel的导出功能
Spring Boot是一个开源的Java应用程序框架，用于构建独立的、基于Java的企业级应用程序。在实现Excel导入导出功能时，借助EasyExcel工具可以简化开发过程，提高效率。下面是一个简单示例来演示如何在Spring Boot项目中实现Excel的导入导出功能：
- 首先，需要在
pom.xml文件中添加EasyExcel的依赖：
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.7</version>
</dependency>
- 创建实体类来表示Excel中的数据：
package com.zbkj.common.response;
import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
/**
* 用户响应对象
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@ApiModel(value = "UserExcelVo对象", description = "用户响应对象")
public class UserExcelVo implements Serializable {
private static final long serialVersionUID = -8330957183745338822L;
@ExcelProperty("用户id")
@ApiModelProperty(value = "用户id")
private Integer id;
// @ApiModelProperty(value = "标签Ids")
// private String tagId;
@ExcelProperty("用户昵称")
@ApiModelProperty(value = "用户昵称")
private String nickname;
@ExcelProperty("真实姓名")
@ApiModelProperty(value = "真实姓名")
private String realName;
@ExcelProperty("企业名称")
@ApiModelProperty(value = "企业名称")
private String entName;
@ExcelProperty("手机号码")
@ApiModelProperty(value = "手机号码")
private String phone;
// @ApiModelProperty(value = "用户头像")
// private String avatar;
// @ApiModelProperty(value = "性别")
// private Integer sex;
@ExcelProperty("生日")
@ApiModelProperty(value = "生日")
private String birthday;
// @ExcelProperty("国家")
// @ApiModelProperty(value = "国家，中国CN，其他OTHER")
// private String country;
@ExcelProperty("省份")
@ApiModelProperty(value = "省份")
private String province;
@ExcelProperty("城市")
@ApiModelProperty(value = "城市")
private String city;
@ExcelProperty("详情地址")
@ApiModelProperty(value = "详情地址")
private String address;
// @ApiModelProperty(value = "1为正常，0为禁止")
// private Boolean status;
@ExcelProperty("注册类型")
@ApiModelProperty(value = "注册类型：public-公众号，mini-小程序，H5-H5")
private String registerType;
//
// @ApiModelProperty(value = "是否关联公众号")
// private Boolean isWechatPublic;
//
// @ApiModelProperty(value = "是否关联小程序")
// private Boolean isWechatRoutine;
//
// @ApiModelProperty(value = "是否为推广员")
// private Boolean isPromoter;
//
// @ApiModelProperty(value = "备注")
// private String mark;
// @ExcelProperty("用户积分")
// @ApiModelProperty(value = "用户积分")
// private Integer integral;
//
// @ExcelProperty("用户余额")
// @ApiModelProperty(value = "用户余额")
// private BigDecimal nowMoney;
// @ExcelProperty("创建时间")
// @ApiModelProperty(value = "创建时间")
// private Date createTime;
// @ExcelProperty("最后一次登录时间")
// @ApiModelProperty(value = "最后一次登录时间")
// private Date lastLoginTime;
// @ApiModelProperty(value = "上级推广员id")
// private Integer spreadUid;
//
// @ApiModelProperty(value = "上级推广员昵称")
// private String spreadName;
//
// @ApiModelProperty(value = "是否注销")
// private Boolean isLogoff;
//
//
@ExcelProperty("身份类型")
@ApiModelProperty(value = "身份类型")
private String identityType;
@ExcelProperty("销售总额")
@ApiModelProperty(value = "销售总额")
private BigDecimal payPrice;
@ExcelProperty("用户标签")
@ApiModelProperty(value = "用户标签")
private String tagName;
@ExcelProperty("邮箱")
@ApiModelProperty(value = "邮箱")
private String personEmail;
// @ApiModelProperty(value = "个体户经营许可证照片")
// private String personLicensePhoto;
@ExcelProperty("注册地址")
@ApiModelProperty(value = "注册地址")
private String entRegisteredAddress;
@ExcelProperty("注册电话")
@ApiModelProperty(value = "注册电话")
private String entRegisteredPhone;
//
// @ExcelProperty("企业税号")
// @ApiModelProperty(value = "企业税号")
// private String entTaxNumber;
@ExcelProperty("联系人")
@ApiModelProperty(value = "联系人")
private String entContactPerson;
@ExcelProperty("联系电话")
@ApiModelProperty(value = "联系电话")
private String entContactPhone;
@ExcelProperty("详细地址")
@ApiModelProperty(value = "详细地址")
private String entDetailedAddress;
//
// @ExcelProperty("联系邮编")
// @ApiModelProperty(value = "联系邮编")
// private String entPostalCode;
@ExcelProperty("所在地区-省")
@ApiModelProperty(value = "所在地区-省")
private String entProvince;
@ExcelProperty("所在地区-市")
@ApiModelProperty(value = "所在地区-市")
private String entCity;
@ExcelProperty("所在地区-区")
@ApiModelProperty(value = "所在地区-区")
private String entDistrict;
@ExcelProperty("销售码")
@ApiModelProperty(value = "销售码")
private String saleId;
@ExcelProperty("销售名称")
@ApiModelProperty(value = "销售名称")
private String saleName;
@ExcelProperty("审核状态")
@ApiModelProperty(value = "审核状态：1-待审核，2-审核通过，3-审核拒绝")
private String auditStatus;
@ExcelProperty("审核备注")
@ApiModelProperty(value = "审核备注")
private String auditReason;
@ExcelProperty("申请时间")
@ApiModelProperty(value = "申请时间")
private Date createTime;
//
// @ApiModelProperty(value = "审核员ID")
// private Integer auditorId;
//
// @ApiModelProperty(value = "大区")
// private Long regionId;
//
// @ApiModelProperty(value = "权限")
// private String powerIds;
//
// @ApiModelProperty(value = "等级")
// private Integer level;
//
// @ApiModelProperty(value = "商品权限开关")
// private Integer isOpenPower;
}
- 编写Excel导出的Controller：
@RestController
@RequestMapping("/excel")
public class ExcelController {
@ApiOperation(value = "导出用户审核Excel")
@RequestMapping(value = "/auditUser/excel", method = RequestMethod.GET)
public CommonResult<HashMap<String, String>> exportAuditUser(@ModelAttribute @Validated UserSearchRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
request.setDataType(2);
exportService.exportUser(request,response);
return CommonResult.success();
}
}
- 在service中创建对应的接口
void exportUser(UserSearchRequest request, HttpServletResponse response) throws UnsupportedEncodingException;
2.实现该接口
@Override
public void exportUser(UserSearchRequest request, HttpServletResponse response) throws UnsupportedEncodingException {
if(request.getDataType()!=null && request.getDataType()==2){
setExcelRespProp(response, "企业注册申请导出");
}else{
setExcelRespProp(response, "用户导出");
}
PageInfo<UserResponse> pageData = userService.getPlatformPage(request, null);
List<UserResponse> list = pageData.getList();
if (CollUtil.isEmpty(list)) {
throw new CrmebException(CommonResultCode.VALIDATE_FAILED, "没有可导出的数据！");
}
List<UserExcelVo> voList = CollUtil.newArrayList();
for(UserResponse userResponse : list){
Integer identityType = userResponse.getIdentityType();
UserExcelVo userExcelVo = new UserExcelVo();
BeanUtils.copyProperties(userResponse, userExcelVo);
userExcelVo.setCreateTime(userResponse.getCreateTime());
if(Objects.isNull(userResponse.getPayPrice())){
userExcelVo.setPayPrice(BigDecimal.ZERO);
}
if(Objects.nonNull(identityType)){
userExcelVo.setIdentityType(identityType == 1 ? "个人" : "企业");
}
UserAddress userAddress = userAddressService.getDefaultByUid(userResponse.getId());
if(Objects.nonNull(userAddress)){
userExcelVo.setEntContactPerson(userAddress.getRealName());
}
if(Objects.nonNull(userResponse.getAuditStatus())){
if(userResponse.getAuditStatus() == 1){
userExcelVo.setAuditStatus("待审核");
}else if(userResponse.getAuditStatus() == 2){
userExcelVo.setAuditStatus("审核通过");
}else if(userResponse.getAuditStatus() == 3){
userExcelVo.setAuditStatus("审核拒绝");
}
}
String tagId = userResponse.getTagId();
UserTag userTag = userTagService.getById(tagId);
if(Objects.nonNull(userTag)){
userExcelVo.setTagName(userTag.getName());
}
if(Objects.nonNull(userResponse.getRegisterType())){
userExcelVo.setRegisterType(userResponse.getRegisterType() == "mini" ? "小程序":"H5");
}
voList.add(userExcelVo);
}
try {
//按照id进行倒序
Collections.sort(voList, Comparator.comparing(UserExcelVo::getId).reversed());
// ExcelExportUtil.export(voList,fileName,response);
if(request.getDataType()!=null && request.getDataType()==2){
EasyExcel.write(response.getOutputStream())
.head(UserAuditExcelVo.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("企业注册信息导出")
.doWrite(voList);
}else{
EasyExcel.write(response.getOutputStream())
.head(UserExcelVo.class)
.excelType(ExcelTypeEnum.XLSX)
.sheet("用户导出")
.doWrite(voList);
}
} catch (IOException e) {
throw new RuntimeException(e);
}
}
通过以上步骤，你可以在Spring Boot项目中实现Excel的导入功能，并且借助EasyExcel工具简化开发过程。希望这篇文章对你有所帮助。