1.引入POI依赖 :
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
<!-- hutool引入 -->
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>4.5.10</version>
</dependency>
2.controller层:
package com.techlutong.jzm.controller.admin;
/**
* Title: AdminJzmUserController
* Description: 会员管理相关接口
*
* @author jintian
* @created 2019/3/27 14:47
*/
import com.alibaba.fastjson.support.spring.annotation.FastJsonFilter;
import com.techlutong.jzm.config.JZMJsonView;
import com.techlutong.jzm.constant.CommonConstant;
import com.techlutong.jzm.service.JzmUserService;
import com.techlutong.jzm.vo.ResponseMessage;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.*;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.Date;
@Api(value = "AdminJzmUserController", description = "jzm:AdminJzmUserController")
@RestController
@RequestMapping(value = CommonConstant.ApiPath.V1, produces = {MediaType.APPLICATION_JSON_UTF8_VALUE})
public class AdminJzmUserController {
@Autowired
JzmUserService jzmUserService;
/**
* 导出商铺信息
*
*
* @param shopName
* @param shopCode
* @param jzmShopAuditStatus
* @param response
* @return
* @throws Exception
*/
@ApiOperation(value="导出商铺信息", notes = "")
@GetMapping(value = CommonConstant.ApiAuth.ANON + "/exportJzmUserInfo")
public ResponseMessage exportJzmUserInfo(String shopName,String shopCode,String jzmShopAuditStatus, HttpServletResponse response)throws Exception{
return jzmUserService.exportJzmUserInfo(shopName,shopCode,jzmShopAuditStatus,response);
}
}
3.service层:
package com.techlutong.jzm.service;
import com.techlutong.jzm.model.JzmUser;
import com.techlutong.jzm.vo.ResponseMessage;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
* Title: JzmUserService
* Description: TODO
*
* @author
* @created
*/
public interface JzmUserService extends CommonService<JzmUser> {
/**
* 导出商铺信息
*
*
* @param shopName
* @param shopCode
* @param jzmShopAuditStatus
* @param response
* @return
* @throws Exception
*/
ResponseMessage exportJzmUserInfo(String shopName,String shopCode,String jzmShopAuditStatus, HttpServletResponse response)throws Exception;
}
4.service实现层:
package com.techlutong.jzm.service.impl;
import cn.hutool.core.util.StrUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.github.wenhao.jpa.Specifications;
import com.techlutong.jzm.enums.*;
import com.techlutong.jzm.model.*;
import com.techlutong.jzm.repository.*;
import com.techlutong.jzm.service.*;
import com.techlutong.jzm.util.*;
import com.techlutong.jzm.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.text.SimpleDateFormat;
import java.util.*;
/**
* Title: JzmUserService
* Description: TODO
*
* @author
* @created
*/
@Slf4j
@Service
public class JzmUserServiceImpl implements JzmUserService {
@Autowired
private JzmUserRepository jzmUserRepository;
/**
* 导出商铺信息
*
* @param shopName
* @param shopCode
* @param jzmShopAuditStatus
* @param response
* @return
* @throws Exception
*/
@Override
public ResponseMessage exportJzmUserInfo(String shopName,String shopCode,String jzmShopAuditStatus,HttpServletResponse response) throws Exception {
//个体商铺信息
List<JzmUser> sellerListP = this.findByPersonal(shopName,shopCode,jzmShopAuditStatus);
//企业商铺信息
List<JzmUser> sellerListE = this.findByEnterprise(shopName,shopCode,jzmShopAuditStatus);
List<Map<String,Object>> resultListP = new ArrayList<>();
List<Map<String,Object>> resultListE = new ArrayList<>();
if(sellerListP.size()>0){
for (JzmUser temp:sellerListP) {
Map<String,Object> map = new HashMap<>();
//商铺名称
map.put("shopName",temp.getShopName());
//商铺类型
if(null != temp.getJzmShopType()){
if(temp.getJzmShopType().equals(JzmShopType.PERSONAL)){
map.put("jzmShopType","个人");
}
if(temp.getJzmShopType().equals(JzmShopType.ENTERPRISE)){
map.put("jzmShopType","企业");
}
}else{
map.put("jzmShopType","");
}
//是否为代理商
if(null != temp.getIsProxy()){
if(temp.getIsProxy()){
map.put("isProxy","是");
}
if(!temp.getIsProxy()){
map.put("isProxy","否");
}
}else{
map.put("isProxy","");
}
//创建时间
map.put("createDate",temp.getCreateDate());
//审核状态
if(null != temp.getJzmShopAuditStatus()){
if(temp.getJzmShopAuditStatus().equals(JzmShopAuditStatus.AUDIT_PASS)){
map.put("jzmShopAuditStatus","审核通过");
}
if(temp.getJzmShopAuditStatus().equals(JzmShopAuditStatus.UNAUDITED)){
map.put("jzmShopAuditStatus","未审核");
}
if(temp.getJzmShopAuditStatus().equals(JzmShopAuditStatus.AUDIT_NOPASS)){
map.put("jzmShopAuditStatus","审核不通过");
}
}else{
map.put("jzmShopAuditStatus","");
}
//姓名
map.put("name",temp.getBuyerUser().getName());
//身份证号
map.put("identityCardNo",temp.getBuyerUser().getIdentityCardNo());
//邮箱
map.put("email",temp.getEmail());
//联系电话
map.put("mobile",temp.getMobile());
//店铺介绍
map.put("shopDescribe",temp.getShopDescribe() != null?temp.getShopDescribe():"");
//经营范围
map.put("businessScope",temp.getBusinessScope());
//店铺地址
map.put("province",temp.getRegisteredAddress());
resultListP.add(map);
}
}
if(sellerListE.size()>0){
for (JzmUser temp:sellerListE) {
Map<String,Object> map = new HashMap<>();
//商铺名称
map.put("shopName",temp.getShopName());
//商铺类型
if(null != temp.getJzmShopType()){
if(temp.getJzmShopType().equals(JzmShopType.PERSONAL)){
map.put("jzmShopType","个人");
}
if(temp.getJzmShopType().equals(JzmShopType.ENTERPRISE)){
map.put("jzmShopType","企业");
}
}else{
map.put("jzmShopType","");
}
//是否为代理商
if(null != temp.getIsProxy()){
if(temp.getIsProxy()){
map.put("isProxy","是");
}
if(!temp.getIsProxy()){
map.put("isProxy","否");
}
}else{
map.put("isProxy","");
}
//创建时间
map.put("createDate",temp.getCreateDate());
//审核状态
if(null != temp.getJzmShopAuditStatus()){
if(temp.getJzmShopAuditStatus().equals(JzmShopAuditStatus.AUDIT_PASS)){
map.put("jzmShopAuditStatus","审核通过");
}
if(temp.getJzmShopAuditStatus().equals(JzmShopAuditStatus.UNAUDITED)){
map.put("jzmShopAuditStatus","未审核");
}
if(temp.getJzmShopAuditStatus().equals(JzmShopAuditStatus.AUDIT_NOPASS)){
map.put("jzmShopAuditStatus","审核不通过");
}
}else{
map.put("jzmShopAuditStatus","");
}
//注册资本
map.put("registeredCapital",temp.getSellerUser().getRegisteredCapital());
//企业类型
map.put("compnayType",temp.getSellerUser().getCompnayType());
//企业电话
map.put("companyPhone",temp.getSellerUser().getCompanyPhone());
//法人身份证号
map.put("corporateCardNo",temp.getSellerUser().getCorporateCardNo());
//注册地址
map.put("province",temp.getRegisteredAddress());
//企业联系人
map.put("companyContactName",temp.getSellerUser().getCompanyContactName());
//企业联系人邮箱
map.put("companyContactEmail",temp.getSellerUser().getCompanyContactEmail());
//经营范围
map.put("businessScope",temp.getBusinessScope());
//营业期限
map.put("businessTime",new SimpleDateFormat("yyyy年MM月dd日").format(temp.getSellerUser().getBusinessStartTime())+"-"+new SimpleDateFormat("yyyy年MM月dd日").format(temp.getSellerUser().getBusinessEndTime()));
//社会信用代码
map.put("creditCode",temp.getSellerUser().getCreditCode());
//所属行业
map.put("companyIndustry",temp.getSellerUser().getCompanyIndustry());
//法定代表人
map.put("corporate",temp.getSellerUser().getCorporate());
//法定身份证有效期
String time = temp.getSellerUser().getCorporateCardNoTime();
String[] arr = time.split(",");
map.put("corporateCardNoTime",DateUtil.format(DateUtil.parse(arr[0]),"yyyy年MM月dd日")+"-"+DateUtil.format(DateUtil.parse(arr[1]),"yyyy年MM月dd日"));
//企业联系人电话
map.put("companyContactPhone",temp.getSellerUser().getCompanyContactPhone());
//店铺介绍
map.put("shopDescribe",temp.getShopDescribe() != null?temp.getShopDescribe():"");
resultListE.add(map);
}
}
ExcelWriter writer = new ExcelWriter(false,"个体商铺信息");
//自定义标题别名
Map<String, String> headerAliasP = new LinkedHashMap<>();
headerAliasP.put("shopName", "商铺名称");
headerAliasP.put("jzmShopType", "商铺类型");
headerAliasP.put("isProxy", "是否为代理商");
headerAliasP.put("createDate", "创建时间");
headerAliasP.put("jzmShopAuditStatus","审核状态");
headerAliasP.put("name","姓名");
headerAliasP.put("identityCardNo","身份证号");
headerAliasP.put("email", "邮箱");
headerAliasP.put("mobile", "联系电话");
headerAliasP.put("shopDescribe", "店铺介绍");
headerAliasP.put("businessScope", "经营范围");
headerAliasP.put("province", "店铺地址");
writer.setHeaderAlias(headerAliasP);
// 合并单元格后的标题行,使用默认标题样式
writer.merge(11, "个人商铺信息");
// 一次性写出内容,使用默认样式
writer.write(resultListP,true);
//设置所有列为自动宽度,不考虑合并单元格
writer.autoSizeColumnAll();
writer.setSheet("企业商铺信息");
//自定义标题别名
Map<String, String> headerAliasE = new LinkedHashMap<>();
headerAliasE.put("shopName", "商铺名称");
headerAliasE.put("jzmShopType", "商铺类型");
headerAliasE.put("isProxy", "是否为代理商");
headerAliasE.put("createDate", "创建时间");
headerAliasE.put("jzmShopAuditStatus","审核状态");
headerAliasE.put("registeredCapital","注册资本");
headerAliasE.put("compnayType","企业类型");
headerAliasE.put("companyPhone", "企业电话");
headerAliasE.put("corporateCardNo", "法人身份证号");
headerAliasE.put("province", "注册地址");
headerAliasE.put("companyContactName", "企业联系人");
headerAliasE.put("companyContactEmail", "企业联系人邮箱");
headerAliasE.put("businessScope", "经营范围");
headerAliasE.put("businessTime", "营业期限");
headerAliasE.put("creditCode", "社会信用代码");
headerAliasE.put("companyIndustry", "所属行业");
headerAliasE.put("corporate", "法定代表人");
headerAliasE.put("corporateCardNoTime", "法定身份证有效期");
headerAliasE.put("companyContactPhone", "企业联系人电话");
headerAliasE.put("shopDescribe", "店铺介绍");
writer.setHeaderAlias(headerAliasE);
// 合并单元格后的标题行,使用默认标题样式
writer.merge(19, "企业商铺信息");
writer.write(resultListE,true);
//设置所有列为自动宽度,不考虑合并单元格
writer.autoSizeColumnAll();
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
String codedFileName = java.net.URLEncoder.encode("商铺信息", "UTF-8");
response.setHeader("Content-Disposition","attachment;filename="+ codedFileName + ".xls");
writer.flush(response.getOutputStream());
// 关闭writer,释放内存
writer.close();
return ResultUtil.success(ResultEnum.EXPORT_SUCCESS.getCode(),ResultEnum.EXPORT_SUCCESS.getMsg());
}
/**
* 根据条件查询个体商铺信息
*
* @param shopName
* @param shopCode
* @param jzmShopAuditStatus
* @return
*/
public List<JzmUser> findByPersonal(String shopName, String shopCode, String jzmShopAuditStatus) {
Specification<JzmUser> specification = Specifications.<JzmUser>and()
.like(StrUtil.isNotEmpty(shopName),"shopName","%"+shopName+"%")
.eq(StrUtil.isNotEmpty(shopCode),"shopCode",shopCode)
.eq(StrUtil.isNotEmpty(jzmShopAuditStatus),"jzmShopAuditStatus",JzmShopAuditStatus.getJzmShopAuditStatus(jzmShopAuditStatus))
.eq("jzmUserType", JzmUserType.SELLER)
.eq("jzmShopType",JzmShopType.PERSONAL)
.build();
return jzmUserRepository.findAll(specification);
}
/**
* 根据条件查询企业商铺信息
*
*
* @param shopName
* @param shopCode
* @param jzmShopAuditStatus
* @return
*/
public List<JzmUser> findByEnterprise(String shopName, String shopCode, String jzmShopAuditStatus) {
Specification<JzmUser> specification = Specifications.<JzmUser>and()
.like(StrUtil.isNotEmpty(shopName),"shopName","%"+shopName+"%")
.eq(StrUtil.isNotEmpty(shopCode),"shopCode",shopCode)
.eq(StrUtil.isNotEmpty(jzmShopAuditStatus),"jzmShopAuditStatus",JzmShopAuditStatus.getJzmShopAuditStatus(jzmShopAuditStatus))
.eq("jzmUserType", JzmUserType.SELLER)
.eq("jzmShopType",JzmShopType.ENTERPRISE)
.build();
return jzmUserRepository.findAll(specification);
}
}