##需求及思路详解
根据产品及需求,我们需要实现对采购品各分类下的采购品进行导出功能。
(可能会出现的问题):①因为要对采购品各分类下采购品进行导出excel文件的功能,所以要考虑到根目录及子目录下采购品数量及导出的不同。②因为dba对数据要求每次查询不大于8mb,所以要对查询数据进行分批查询。③:文件名乱码问题。④:导入所需jar包到pom文件,因博主是由公司自己封装的esaypoi框架,故在此不列出。
##后台代码实现
##controller层#
@ApiOperation(value = "导出采购品到Excel文件", httpMethod = "GET")
@RequestMapping(value = "downCorpDataByExcel", method = RequestMethod.GET)
public void downCorpDataByExcel(@RequestParam(required = false) String name,
@RequestParam(required = false) String code,
@RequestParam(required = false) String spec,
@RequestParam(required = true) Long catalogId,
HttpServletResponse response,
HttpServletRequest request) {
TRegUser user = UserContext.getUser();
if (user == null) {
logger.warn("获取不到用户信息");
return;
}
CorpDirectorysQueryDto cdQuery = new CorpDirectorysQueryDto();
//公司id
cdQuery.setCompanyId(user.getCompanyId());
//采购品名称模糊查询
if (StringUtils.isNotBlank(name)) {
cdQuery.setNameLike(name.trim());
}
//采购品编码模糊查询
if (StringUtils.isNotBlank(code)) {
cdQuery.setCodeLike(code.trim());
}
//采购品规格型号模糊查询
if (StringUtils.isNotBlank(spec)) {
cdQuery.setSpecLike(spec.trim());
}
//采购品分类id(有前端从tree中获取)
if (catalogId != null) {
//判断当前目录是否有子目录
① boolean b = corpDirectoryService.hasExistChild(catalogId, user.getCompanyId());
logger.info("方法downCorpDataByExcel,公司{}目录{}是否有子目录:{}", user.getCompanyId(), catalogId, b);
if (b) {
//如果有,查询下当前子目录路径,通过路径去查询采购品列表
② CorpCatalogs corpCatalogs = corpCatalogService.findById(catalogId, user.getCompanyId());
//会查询like CONCAT(#{treepathLike},'%')
cdQuery.setTreepathLike(corpCatalogs.getTreepath());
} else {
cdQuery.setCatalogId(catalogId);
}
}
PageSet pageSet = new PageSet();
pageSet.setSortColumn("abandon asc,create_time desc");
String corpDataName = "采购品信息.xls";
//处理文件名乱码的问题
③ corpDataName = encodeDownloadFile(corpDataName, request.getHeader("User-Agent"));
response.setHeader("content-disposition", "attachment;filename=" + corpDataName);
//根据文件名自动获得文件类型
response.setContentType(request.getSession().getServletContext().getMimeType(corpDataName));
//告知服务器使用什么编码
response.setCharacterEncoding("UTF-8");
try {
④ HSSFWorkbook workbook = corpDirectoryService.createWorkbook(cdQuery, pageSet);
workbook.write(response.getOutputStream());
} catch (Exception e) {
logger.error("构建excel出错", e);
}
}
/**判断当前目录是否有子目录
*/
①:
public boolean hasExistChild(Long catalogId, Long companyId) {
ServiceResult<Boolean> booleanResult = dubboCorpCatalogsService.hasExistChild(catalogId, companyId);
if (!booleanResult.getSuccess()) {
logger.error("{}调用{}时发生未知异常,error Message:{}", "cn.bidlink.procurement.materials.app.service.CorpDirectoryService.hasExistChild",
" dubboCorpCatalogsService.hasExistChild(catalogId,companyId)", booleanResult.getCode() + "_" + booleanResult.getMessage());
throw new RuntimeException("err_code:" + booleanResult.getCode() + ",err_msg:" + booleanResult.getMessage());
}
Boolean result = booleanResult.getResult();
if (result == null) {
logger.warn("cn.bidlink.procurement.materials.app.service.CorpDirectoryService.hasExistChild时未获取到结果");
}
return result;
}
/**
如果有子目录,查询下当前子目录路径,通过路径去查询采购品列表
*/
②: