1、第一张sheet的导入类
@Data
@ExcelTarget("PmAppServiceManageNewImportVO")
@EqualsAndHashCode(callSuper = false)
public class PmAppServiceManageNewImportVO extends ExcelVerifyInfo implements IExcelModel {
/** 应用名称 */
@Excel(name = "*应用名称", width = 18, orderNum = "1")
@NotNull
private String projectName;
/** 应用负责人 */
@Excel(name = "*应用负责人", width = 18, orderNum = "2")
@NotNull
private String applicationUser;
/** 业务负责人 */
@Excel(name = "*业务负责人", width = 18, orderNum = "3")
@NotNull
private String businessUser;
/** 应用状态;1使用,2停用 */
@Excel(name = "*应用状态", width = 18, orderNum = "4", replace = {"使用_1", "停用_2", "_null"}, addressList = true)
@NotNull
private Integer status;
/** 环境;1正式,2测试,3开发) */
@Excel(name = "*环境", width = 18, orderNum = "5", replace = {"开发_3", "测试_2", "正式_1", "_null"},addressList = true)
@NotBlank
private String environment;
/** 管理员账号 */
@Excel(name = "管理员账号", width = 18, orderNum = "6")
private String adminAccount;
/** 管理员密码 */
@Excel(name = "管理员密码", width = 18, orderNum = "7")
private String adminPassword;
/** 备注 */
@Excel(name = "备注", width = 18, orderNum = "8")
private String remarks;
@ExcelIgnore
private String errorMsg;
}
1.1、第二张sheet导入类
@Data
@ExcelTarget("PmAppServiceManageDetailsImportVO")
@EqualsAndHashCode(callSuper = false)
public class PmAppServiceManageDetailsImportVO extends ExcelVerifyInfo implements IExcelModel {
/** 服务名称 */
@Excel(name = "*服务名称", width = 18, orderNum = "1")
@NotBlank
private String serviceName;
/** 服务类型 */
@Excel(name = "*服务类型", width = 18, orderNum = "2", replace = {"前端服务_1", "后端服务_2", "数据服务_3", "MQ服务_4", "附件服务_5", "通用服务_6", "外部服务_7"},addressList = true)
@NotBlank
private String serviceType;
/** 运维负责人 */
@Excel(name = "*运维负责人", width = 18, orderNum = "3")
@NotNull
private String operationUser;
/** 开发负责人 */
@Excel(name = "*开发负责人", width = 18, orderNum = "4")
@NotNull
private String developmentUser;
/** 映射IP */
@Excel(name = "映射IP", width = 18, orderNum = "5")
private String mappingIp;
/** 映射端口 */
@Excel(name = "映射端口", width = 18, orderNum = "6")
private String mappingPort;
/** 内部IP地址 */
@Excel(name = "*内部IP", width = 18, orderNum = "7")
@NotBlank
private String insideIp;
/** 内部端口 */
@Excel(name = "*内部端口", width = 18, orderNum = "8")
@Pattern(regexp = "^[0-9]*$", message = "只能输入数字！")
@NotBlank
private String occupyPort;
/** 超融合集群 */
@Excel(name = "超融合集群", width = 18, orderNum = "9")
private String hyperconvergedCluster;
/** CPU用量 */
@Excel(name = "CPU用量", width = 18, orderNum = "10")
private String centralProcessingUnit;
/** 内存用量 */
@Excel(name = "内存用量", width = 18, orderNum = "11")
private String internalStorage;
/** 硬盘用量 */
@Excel(name = "硬盘用量", width = 18, orderNum = "12")
private String hardDiskSize;
@ExcelIgnore
private String errorMsg;
}
1.2、注解解释
- @ExcelTarget：表示要导出的目标对象，作用在实体类上。
- @EqualsAndHashCode(callSuper = false):排除父类字段。
- @Data相当于@Getter @Setter @RequiredArgsConstructor @ToString @EqualsAndHashCode这5个注解的合集。
- @ExcelIgnore：标注在成员变量上，默认所有字段都会和excel去匹配，加了这个注解会忽略该字段。
- @Excel： 作用到filed上面,是对Excel一列的一个描述
- name：列名。
- width：列宽。
- orderNum：列的排序。如果不加，将根据实体类的顺序进行导出。
- needMerge：纵向合并单元格。用于含list中创建多个row。
- replace：值的替换。也就是excel单元格表现为下拉框。
2、配置设置
2.1、导入第一张sheet
ImportParams params1 = new ImportParams();
// 读取第一张sheet
params1.setStartSheetIndex(0);
params1.setSheetNum(1);
//标题行2
params1.setTitleRows(2);
//表头1
params1.setHeadRows(1);
// 不需要保存
params1.setNeedSave(false);
//设置读取空值
params1.setKeyIndex(null);
// 开启验证
params1.setNeedVerify(true);
String message = "";
//创建HSSFWorkbook
Workbook hssfWorkbook = new HSSFWorkbook();
try (InputStream inputStream = file.getInputStream())
{
//easypoi导入，数据量不大
ExcelImportResult<PmAppServiceManageNewImportVO> appServiceResult =
ExcelImportUtil.importExcelMore(inputStream, PmAppServiceManageNewImportVO.class, params1);
//创建空数组，用于导入内容的新增或者更新
List<PmAppServiceManageNew> pmAppServiceManageNewAddList = new ArrayList<>();
List<PmAppServiceManageNew> pmAppServiceManageNewUpdateList = new ArrayList<>();
hssfWorkbook = appServiceResult.getWorkbook();
//显示错误信息
for (int i = 0; i < appServiceResult.getFailList().size(); i++)
{
PmAppServiceManageNewImportVO excel = appServiceResult.getFailList().get(i);
if (appServiceResult.getFailList().get(i).getErrorMsg() != null
&& !"".equals(appServiceResult.getFailList().get(i).getErrorMsg()))
{
message = message + "--> 第" + excel.getRowNum() + "行,"
+ appServiceResult.getFailList().get(i).getErrorMsg();
}
}
appServiceResult.getList().stream().forEach(appService -> {
String projectName = appService.getProjectName();
String environment = appService.getEnvironment();
String applicationUser = appService.getApplicationUser();
String businessUser = appService.getBusinessUser();
// 应用名称-环境唯一
PmAppServiceManageNew selectOne = pmAppServiceManageMapper
.selectList(new LambdaQueryWrapper<PmAppServiceManageNew>()
.eq(PmAppServiceManageNew::getProjectName, projectName)
.eq(PmAppServiceManageNew::getEnvironment, environment)
.eq(PmAppServiceManageNew::getDelFlag, DelFlagEnum.NORMALL.getValue()))
.stream()
.findFirst()
.orElse(null);
// 存入数据库
PmAppServiceManageNew pmAppServiceManageNew = new PmAppServiceManageNew();
pmAppServiceManageNew.setProjectName(appService.getProjectName());
pmAppServiceManageNew.setApplicationUser(applicationUser);
pmAppServiceManageNew.setBusinessUser(businessUser);
pmAppServiceManageNew.setStatus(appService.getStatus());
pmAppServiceManageNew.setEnvironment(appService.getEnvironment());
pmAppServiceManageNew.setAdminAccount(appService.getAdminAccount());
pmAppServiceManageNew.setAdminPassword(appService.getAdminPassword());
pmAppServiceManageNew.setDelFlag(DelFlagEnum.NORMALL.getValue());
pmAppServiceManageNew.setCreateBy(SecurityUtils.getUser().getId());
pmAppServiceManageNew.setCreateTime(LocalDateTime.now());
// 新增
if (ObjectUtil.isNull(selectOne))
{
pmAppServiceManageNewAddList.add(pmAppServiceManageNew);
}
else
{
// 更新
pmAppServiceManageNew.setId(selectOne.getId());
pmAppServiceManageNew.setUpdateBy(SecurityUtils.getUser().getId());
pmAppServiceManageNew.setUpdateTime(LocalDateTime.now());
pmAppServiceManageNewUpdateList.add(pmAppServiceManageNew);
}
});
if (ObjectUtil.isNotEmpty(pmAppServiceManageNewAddList))
{
saveBatch(pmAppServiceManageNewAddList);
}
if (ObjectUtil.isNotEmpty(pmAppServiceManageNewUpdateList))
{
updateBatchById(pmAppServiceManageNewUpdateList);
}
}
2.2、导入后面的sheet
// 导入后面的详情表
for (int numSheet = 1; numSheet <= hssfWorkbook.getNumberOfSheets() - 1; numSheet++)
{
//设置sheet的名称：projectName-environment
String sheetName = hssfWorkbook.getSheetName(numSheet);
if (!sheetName.contains("-"))
{
throw new BizException("第" + numSheet + "个sheet命名不规范，请重新编辑！");
}
String[] split = sheetName.split("-");
String projectName = split[0];
String environment = "";
switch (split[1])
{
case "正式":
{
environment = "1";
break;
}
case "测试":
{
environment = "2";
break;
}
case "开发":
{
environment = "3";
break;
}
default:
break;
}
// 校验sheetName对应的project是否存在
PmAppServiceManageNew selectOne =
pmAppServiceManageMapper.selectList(new LambdaQueryWrapper<PmAppServiceManageNew>()
.eq(PmAppServiceManageNew::getProjectName, projectName)
.eq(PmAppServiceManageNew::getEnvironment, environment)
.eq(PmAppServiceManageNew::getDelFlag, DelFlagEnum.NORMALL.getValue()))
.stream().findFirst().orElse(null);
if (ObjectUtil.isNull(selectOne))
{
throw new BizException("应用:" + sheetName + "不存在,请先添加对应应用！");
}
ImportParams params2 = new ImportParams();
// 读取第二个sheet到第一千个sheet
params2.setStartSheetIndex(numSheet);
params2.setSheetNum(1);
params2.setNeedSave(false);
params2.setKeyIndex(null);
params2.setNeedVerify(true);
List<PmAppServiceManageDetails> pmAppServiceManageDetailsAddList = new ArrayList<>();
List<PmAppServiceManageDetails> pmAppServiceManageDetailsUpdateList = new ArrayList<>();
try (InputStream inputStream1 = file.getInputStream())
{
ExcelImportResult<PmAppServiceManageDetailsImportVO> appServiceDetailsResult =
ExcelImportUtil.importExcelMore(inputStream1, PmAppServiceManageDetailsImportVO.class, params2);
//显示报错信息
for (int i = 0; i < appServiceDetailsResult.getFailList().size(); i++)
{
PmAppServiceManageDetailsImportVO excel = appServiceDetailsResult.getFailList().get(i);
if (appServiceDetailsResult.getFailList().get(i).getErrorMsg() != null
&& !"".equals(appServiceDetailsResult.getFailList().get(i).getErrorMsg()))
{
message = message + "--> sheet:" + sheetName + "中," + "第" + (excel.getRowNum()) + "行"
+ appServiceDetailsResult.getFailList().get(i).getErrorMsg();
}
}
appServiceDetailsResult.getList().stream().forEach(appServiceDetail -> {
String insideIp = appServiceDetail.getInsideIp();
String occupyPort = appServiceDetail.getOccupyPort();
String operationUser = appServiceDetail.getOperationUser();
String developmentUser = appServiceDetail.getDevelopmentUser();
// 查询内部IP-内部端口唯一
PmAppServiceManageDetails details = detailsMapper
.selectList(new LambdaQueryWrapper<PmAppServiceManageDetails>()
.eq(PmAppServiceManageDetails::getInsideIp, insideIp)
.eq(PmAppServiceManageDetails::getOccupyPort, occupyPort)
.eq(PmAppServiceManageDetails::getDelFlag, DelFlagEnum.NORMALL.getValue()))
.stream()
.findFirst()
.orElse(null);
String serviceType = appServiceDetail.getServiceType();
String serviceName = appServiceDetail.getServiceName();
// 前端服务校验端口是否满足在30000-31500之间
if ("1".equals(serviceType))
{
if (!(30000 < Integer.parseInt(occupyPort) && 31500 > Integer.parseInt(occupyPort)))
{
throw new BizException(
"sheet:" + sheetName + "中,服务:" + serviceName + "为前端服务,端口需要满足在30000-31500之间！");
}
}
// 后端服务校验端口是否满足在31501-32767之间
if ("2".equals(serviceType))
{
if (!(31501 < Integer.parseInt(occupyPort) && 32767 > Integer.parseInt(occupyPort)))
{
throw new BizException(
"sheet:" + sheetName + "中,服务:" + serviceName + "为后端服务,端口需要满足在31501-32767之间！");
}
}
// 存入数据库
PmAppServiceManageDetails pmAppServiceManageDetails = new PmAppServiceManageDetails();
pmAppServiceManageDetails.setPmAppServiceManageId(selectOne.getId());
pmAppServiceManageDetails.setServiceName(serviceName);
pmAppServiceManageDetails.setServiceType(serviceType);
pmAppServiceManageDetails.setOperationUser(operationUser);
pmAppServiceManageDetails.setDevelopmentUser(developmentUser);
pmAppServiceManageDetails.setMappingIp(appServiceDetail.getMappingIp());
pmAppServiceManageDetails.setMappingPort(appServiceDetail.getMappingPort());
pmAppServiceManageDetails.setInsideIp(appServiceDetail.getInsideIp());
pmAppServiceManageDetails.setOccupyPort(occupyPort);
pmAppServiceManageDetails.setHyperconvergedCluster(appServiceDetail.getHyperconvergedCluster());
pmAppServiceManageDetails.setCentralProcessingUnit(appServiceDetail.getCentralProcessingUnit());
pmAppServiceManageDetails.setInternalStorage(appServiceDetail.getInternalStorage());
pmAppServiceManageDetails.setHardDiskSize(appServiceDetail.getHardDiskSize());
pmAppServiceManageDetails.setDelFlag(DelFlagEnum.NORMALL.getValue());
pmAppServiceManageDetails.setCreateBy(SecurityUtils.getUser().getId());
pmAppServiceManageDetails.setCreateTime(LocalDateTime.now());
// 新增
if (ObjectUtil.isNull(details))
{
pmAppServiceManageDetailsAddList.add(pmAppServiceManageDetails);
}
else
{
// 校验不同应用内部IP端口相同
PmAppServiceManageNew pmAppServiceManageNew =
pmAppServiceManageMapper.selectById(details.getPmAppServiceManageId());
if (!ObjectUtil.equal(pmAppServiceManageNew.getId(), selectOne.getId()))
{
throw new BizException("sheet:" + sheetName + "中,服务的内部IP和内部端口,"
+ appServiceDetail.getInsideIp() + ":" + appServiceDetail.getOccupyPort() + "在应用"
+ pmAppServiceManageNew.getProjectName() + "中已存在！");
}
// 更新
pmAppServiceManageDetails.setId(details.getId());
pmAppServiceManageDetails.setUpdateBy(SecurityUtils.getUser().getId());
pmAppServiceManageDetails.setUpdateTime(LocalDateTime.now());
pmAppServiceManageDetailsUpdateList.add(pmAppServiceManageDetails);
}
});
if (ObjectUtil.isNotEmpty(pmAppServiceManageDetailsAddList))
{
pmAppServiceManageDetailsService.saveBatch(pmAppServiceManageDetailsAddList);
}
if (ObjectUtil.isNotEmpty(pmAppServiceManageDetailsUpdateList))
{
pmAppServiceManageDetailsService.updateBatchById(pmAppServiceManageDetailsUpdateList);
}
}
}
if (!"".equals(message))
{
throw new BizException(message);
}
2.3、最后导入失败，抛出异常，导入成功返回信息
try{
//代码块
} catch (Exception e)
{
throw new BizException(e.getMessage());
}
return R.ok("导入成功！");
3、导入excel模板截图
1647
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
