导出excel，并生成多个sheet(使用Hutool工具类)

时间：2025-03-10 11:05:00

1.引入POI依赖 ：

<!-- /artifact//poi-ooxml -->
<dependency>
    <groupId></groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.17</version>
</dependency>
<!-- /artifact//poi-ooxml-schemas -->
<dependency>
	<groupId></groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>3.17</version>
</dependency>
<!-- hutool引入 -->
<dependency>
	<groupId></groupId>
	<artifactId>hutool-all</artifactId>
	<version>4.5.10</version>
</dependency>

层：

package ;
/**
 * Title: AdminJzmUserController
 * Description: 会员管理相关接口
 *
 * @author jintian
 * @created 2019/3/27 14:47
 */

import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import ;
import .*;

import ;
import ;
import ;


@Api(value = "AdminJzmUserController", description = "jzm:AdminJzmUserController")
@RestController
@RequestMapping(value = .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 =  + "/exportJzmUserInfo")
    public ResponseMessage exportJzmUserInfo(String shopName,String shopCode,String jzmShopAuditStatus, HttpServletResponse response)throws Exception{
        return (shopName,shopCode,jzmShopAuditStatus,response);
    }


   
    
  
}

层：

package ;

import ;
import ;

import ;
import ;


/**
 * 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;




}

实现层：

package ;

import ;
import ;
import ;
import .*;
import .*;
import .*;
import .*;
import .*;
import .*;
import .slf4j.Slf4j;
import ;
import ;
import ;

import ;
import ;
import ;
import .*;


/**
 * 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 = (shopName,shopCode,jzmShopAuditStatus);
        //企业商铺信息
        List<JzmUser> sellerListE = (shopName,shopCode,jzmShopAuditStatus);

        List<Map<String,Object>> resultListP = new ArrayList<>();
        List<Map<String,Object>> resultListE = new ArrayList<>();
        if(()>0){
            for (JzmUser temp:sellerListP) {
                Map<String,Object> map = new HashMap<>();
                //商铺名称
                ("shopName",());
                //商铺类型
                if(null != ()){
                    if(().equals()){
                        ("jzmShopType","个人");
                    }
                    if(().equals()){
                        ("jzmShopType","企业");
                    }
                }else{
                    ("jzmShopType","");
                }
                //是否为代理商
                if(null != ()){
                    if(()){
                        ("isProxy","是");
                    }
                    if(!()){
                        ("isProxy","否");
                    }
                }else{
                    ("isProxy","");
                }
                //创建时间
                ("createDate",());
                //审核状态
                if(null != ()){
                    if(().equals(JzmShopAuditStatus.AUDIT_PASS)){
                        ("jzmShopAuditStatus","审核通过");
                    }
                    if(().equals()){
                        ("jzmShopAuditStatus","未审核");
                    }
                    if(().equals(JzmShopAuditStatus.AUDIT_NOPASS)){
                        ("jzmShopAuditStatus","审核不通过");
                    }
                }else{
                    ("jzmShopAuditStatus","");
                }
                //姓名
                ("name",().getName());
                //身份证号
                ("identityCardNo",().getIdentityCardNo());
                //邮箱
                ("email",());
                //联系电话
                ("mobile",());
                //店铺介绍
                ("shopDescribe",() != null?():"");
                //经营范围
                ("businessScope",());
                //店铺地址
                ("province",());

                (map);
            }
        }
        if(()>0){
            for (JzmUser temp:sellerListE) {
                Map<String,Object> map = new HashMap<>();
                //商铺名称
                ("shopName",());
                //商铺类型
                if(null != ()){
                    if(().equals()){
                        ("jzmShopType","个人");
                    }
                    if(().equals()){
                        ("jzmShopType","企业");
                    }
                }else{
                    ("jzmShopType","");
                }
                //是否为代理商
                if(null != ()){
                    if(()){
                        ("isProxy","是");
                    }
                    if(!()){
                        ("isProxy","否");
                    }
                }else{
                    ("isProxy","");
                }
                //创建时间
                ("createDate",());
                //审核状态
                if(null != ()){
                    if(().equals(JzmShopAuditStatus.AUDIT_PASS)){
                        ("jzmShopAuditStatus","审核通过");
                    }
                    if(().equals()){
                        ("jzmShopAuditStatus","未审核");
                    }
                    if(().equals(JzmShopAuditStatus.AUDIT_NOPASS)){
                        ("jzmShopAuditStatus","审核不通过");
                    }
                }else{
                    ("jzmShopAuditStatus","");
                }
                //注册资本
                ("registeredCapital",().getRegisteredCapital());
                //企业类型
                ("compnayType",().getCompnayType());
                //企业电话
                ("companyPhone",().getCompanyPhone());
                //法人身份证号
                ("corporateCardNo",().getCorporateCardNo());
                //注册地址
                ("province",());
                //企业联系人
                ("companyContactName",().getCompanyContactName());
                //企业联系人邮箱
                ("companyContactEmail",().getCompanyContactEmail());
                //经营范围
                ("businessScope",());
                //营业期限
                ("businessTime",new SimpleDateFormat("yyyy年MM月dd日").format(().getBusinessStartTime())+"-"+new SimpleDateFormat("yyyy年MM月dd日").format(().getBusinessEndTime()));
                //社会信用代码
                ("creditCode",().getCreditCode());
                //所属行业
                ("companyIndustry",().getCompanyIndustry());
                //法定代表人
                ("corporate",().getCorporate());
                //法定身份证有效期
                String time = ().getCorporateCardNoTime();
                String[] arr = (",");
                ("corporateCardNoTime",((arr[0]),"yyyy年MM月dd日")+"-"+((arr[1]),"yyyy年MM月dd日"));
                //企业联系人电话
                ("companyContactPhone",().getCompanyContactPhone());
                //店铺介绍
                ("shopDescribe",() != null?():"");

                (map);
            }
        }

        ExcelWriter writer = new ExcelWriter(false,"个体商铺信息");
        //自定义标题别名
        Map<String, String> headerAliasP = new LinkedHashMap<>();
        ("shopName", "商铺名称");
        ("jzmShopType", "商铺类型");
        ("isProxy", "是否为代理商");
        ("createDate", "创建时间");
        ("jzmShopAuditStatus","审核状态");
        ("name","姓名");
        ("identityCardNo","身份证号");
        ("email", "邮箱");
        ("mobile", "联系电话");
        ("shopDescribe", "店铺介绍");
        ("businessScope", "经营范围");
        ("province", "店铺地址");
        (headerAliasP);
        // 合并单元格后的标题行，使用默认标题样式
        (11, "个人商铺信息");
        // 一次性写出内容，使用默认样式
        (resultListP,true);
        //设置所有列为自动宽度，不考虑合并单元格
        ();
        ("企业商铺信息");
        //自定义标题别名
        Map<String, String> headerAliasE = new LinkedHashMap<>();
        ("shopName", "商铺名称");
        ("jzmShopType", "商铺类型");
        ("isProxy", "是否为代理商");
        ("createDate", "创建时间");
        ("jzmShopAuditStatus","审核状态");
        ("registeredCapital","注册资本");
        ("compnayType","企业类型");
        ("companyPhone", "企业电话");
        ("corporateCardNo", "法人身份证号");
        ("province", "注册地址");
        ("companyContactName", "企业联系人");
        ("companyContactEmail", "企业联系人邮箱");
        ("businessScope", "经营范围");
        ("businessTime", "营业期限");
        ("creditCode", "社会信用代码");
        ("companyIndustry", "所属行业");
        ("corporate", "法定代表人");
        ("corporateCardNoTime", "法定身份证有效期");
        ("companyContactPhone", "企业联系人电话");
        ("shopDescribe", "店铺介绍");
        (headerAliasE);
        // 合并单元格后的标题行，使用默认标题样式
        (19, "企业商铺信息");
        (resultListE,true);
        //设置所有列为自动宽度，不考虑合并单元格
        ();
        ("application/-excel;charset=utf-8");
        //是弹出下载对话框的文件名，不能为中文，中文请自行编码
        String codedFileName = ("商铺信息", "UTF-8");
        ("Content-Disposition","attachment;filename="+ codedFileName + ".xls");
        (());
        // 关闭writer，释放内存
        ();
        return (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((shopName),"shopName","%"+shopName+"%")
                .eq((shopCode),"shopCode",shopCode)
                .eq((jzmShopAuditStatus),"jzmShopAuditStatus",(jzmShopAuditStatus))
                .eq("jzmUserType", )
                .eq("jzmShopType",)
                .build();

       return (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((shopName),"shopName","%"+shopName+"%")
                .eq((shopCode),"shopCode",shopCode)
                .eq((jzmShopAuditStatus),"jzmShopAuditStatus",(jzmShopAuditStatus))
                .eq("jzmUserType", )
                .eq("jzmShopType",)
                .build();

        return (specification);
    }
}

 

相关文章