一、目前excel导入导出大致有三种工具：
- poi:功能强大
- easypoi：基于poi的二次封装，支持注解
- easyexcel：阿里基于poi的二次封装，支持注解，解决大数据量时内存溢出的问题。
本文主要给出一个项目中直接使用的poi案例，不展开讨论其他内容， 如预了解三种工具课参考下文： 传送门.
二、客户需求：
导入项目开发计划的excel模板：
一级计划：
二级计划：
三级计划：
约束条件：
- excel模板有三个sheet:一级计划、二级计划、三级计划；
- 其中一、二、三级计划具有父子关系；
- 单条计划可指派给多个部门，每个模板对应的部门不定；
- 每个部门内的计划明细 具有序号、名称、时间、责任人；
三、思路
约束一：可通过校验sheet的有无解决
约束二：可校验序号的关系解决（设定三个set集合分别对应一级序号、二级序号、三级序号）
约束三：部门可从特定部位遍历
约束四：时间检验格式、前后时间大小；责任人的检验可将系统用户取出放入set，判断模板上的责任人是否为系统用户
四、代码：（项目中直接粘过来，请酌情修改）
注：解析序号的时候有时1.1会变成1.10000000001；解决方案请看：传送门.
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 解析exel
*/
@Override
public R poiImportTemplat(MultipartFile file) throws IOException {
if(file==null){
return R.fail("文件不可为空！");
}
log.info("<--文件获取成功，开始解析-->");
final String PREFIX = "模板有误：";
final String ERROR_PREFIX = "导入失败：";
Workbook workbook = new XSSFWorkbook(file.getInputStream());
Sheet sheet0 = workbook.getSheet("一级计划");
Sheet sheet1 = workbook.getSheet("二级计划");
Sheet sheet2 = workbook.getSheet("三级计划");
if(sheet0==null){
return R.fail(PREFIX+"未找到一级计划！");
}
if(sheet1==null){
return R.fail(PREFIX+"未找到二级计划！");
}
//校验是否存在部门
Row sheet1row1 = sheet1.getRow(1);
if(sheet1row1==null||sheet1row1.getPhysicalNumberOfCells()<=8){
return R.fail(PREFIX+"未找到二级计划的协作部门！");
}
// 日期校验、责任人校验 TODO 去重
//_获取user全集，放入set，用于校验
Set userSet = new CopyOnWriteArraySet();
R<List<UserBaseInfoDTO>> listR = iSysUserQueryService.queryAllUserInfo();
if(listR.getCode()!=200){
log.info("<--全体用户获取失败-->");
return R.fail(ERROR_PREFIX+"系统全体用户获取失败!");
}
Map<String,UserBaseInfoDTO> userMap = new ConcurrentHashMap();
if(!listR.getData().isEmpty()){
listR.getData().stream().forEach(p->userSet.add(p.getUserName()));
userMap = listR.getData().stream().collect(Collectors.toMap(p -> p.getUserName(), p -> p));
}else{
return R.fail(ERROR_PREFIX+"系统全体用户为空，请联系系统管理员完善系统用户!");
}
R checkDateUserR1 = this.checkDateUser(sheet0,1,userSet);
if(checkDateUserR1.getCode()==500){
return checkDateUserR1;
}
R checkDateUserR2 = this.checkDateUser(sheet1,2,userSet);
if(checkDateUserR2.getCode()==500){
return checkDateUserR2;
}
if(sheet2!=null){
R checkDateUserR3 = this.checkDateUser(sheet2,3,userSet);
if(checkDateUserR3.getCode()==500){
return checkDateUserR3;
}
}
log.info("<--日期、责任人校验通过-->");
//TODO data验证
/* int numberOfCells = sheet0.getRow(0).getPhysicalNumberOfCells();
if(numberOfCells!=5){
return R.fail(PREFIX+"一级计划,列数有误！");
}*/
/*a.处理模板主信息_获取模板名*/
String originalFilename = file.getOriginalFilename();
originalFilename = originalFilename.substring(0,originalFilename.lastIndexOf("."));
//_模板去重
Integer existSign = planTemplateMapper.searchTemplateByName(originalFilename);
if(existSign.intValue()>0){
return R.fail(ERROR_PREFIX+"同名模板已存在！");
}
//创建人信息
AndawellUser user = AuthUtil.getUser();
Kv detail = user.getDetail();
String userId = (String)detail.getObj("sysUserId");
String userName = (String)detail.getObj("userName");
List depts = (List)detail.getObj("depts");
Map o = (Map)depts.get(0);
String sysOrgId = (String) o.get("sysOrgId");
String orgName = (String) o.get("orgName");
String sysDeptId = (String) o.get("sysDeptId");
String deptName = (String) o.get("deptName");
//todo 信息完善 & 判重；1模板编号，2创建人信息
String tempNo = "001";
PlPlanTemplatePO plPlanTemplatePO = PlPlanTemplatePO.builder()
.tempNo(tempNo)
.tempName(originalFilename)
.tempVersion("V0")
.tempCreatUserId(userId)
.tempCreatUserName(userName)
.tempCreatUserOrgId(sysOrgId)
.tempCreatUserOrgName(orgName)
.tempCreatUserDeptId(sysDeptId)
.tempCreatUserDeptName(deptName)
.tempCreatTime(new Date())
.tempStatus("0")
.build();
R addTemplateR = this.addTemplateImport(plPlanTemplatePO);
if(addTemplateR.getCode()==200){
log.info("<--模板主信息保存成功-->");
}else{
log.info("<--模板主信息保存失败-->");
return R.fail(ERROR_PREFIX+"模板主信息保存失败!");
}
/*b.处理模板协作部门_获取模板名_*/
//存部门(模板id,拿部门名称，选算出来几个)
R addDeptR = this.addDept(sheet1row1, plPlanTemplatePO.getPlPlanTemplateId());
List<PlPlanTemplateDeptPO> deptList = new CopyOnWriteArrayList();
if(addDeptR.getCode()==200){
log.info("<--模板部门保存成功，开始解析计划-->");
deptList= (List) addDeptR.getData();
}else{
log.info("<--模板部门保存失败-->");
return R.fail(ERROR_PREFIX+"模板部门保存失败!");
}
/*c.拿到模板id、协作部门信息，新增任务信息*/
//初始化计划（空集合）
PlanTemplateTaskAddVO planTemplate = new PlanTemplateTaskAddVO();
//设置计划所属模板
planTemplate.setPlPlanTemplateId(plPlanTemplatePO.getPlPlanTemplateId());
//初始化计划明细（空集合）
planTemplate.setMainTaskList(new CopyOnWriteArrayList<>());
Set orderNoSet0 = new CopyOnWriteArraySet();//一级计划orderNoSet
Set orderNoSet1 = new CopyOnWriteArraySet();//二级计划orderNoSet
Set orderNoSet2 = new CopyOnWriteArraySet();//三级计划orderNoSet
//TODO 先校验再存,如果在已有的下面，上面的序号存在，维护一个set
//c.a 解析一级计划
//todo 一级计划 sheet校验，长度,字段
//todo 一级计划 校验字段,序号长1.1 pid在，自己不在
//TODO 10月19 1序号只校验了空，2日期未校验，
for(int i=2;i<sheet0.getPhysicalNumberOfRows()&&!(sheet0.getRow(i).getCell(0)==null||getValue(sheet0.getRow(i).getCell(0),1)=="");i++){
String value = getValue(sheet0.getRow(i).getCell(0), 2);
boolean add = orderNoSet0.add(value);
if(add){
//单明细
PlPlanTemplateDPO planTemplateDetail = PlPlanTemplateDPO.builder()
.beginTime(sheet0.getRow(i).getCell(2).getDateCellValue())
.overTime(sheet0.getRow(i).getCell(3).getDateCellValue())
.build();
//明细list
CopyOnWriteArrayList<PlPlanTemplateDPO> detailTaskList = new CopyOnWriteArrayList<>();
detailTaskList.add(planTemplateDetail);
//单计划
PlPlanTemplateListVO planTemplateList = PlPlanTemplateListVO.builder()
.plPlanTemplateId(plPlanTemplatePO.getPlPlanTemplateId())
.orderNo(value)
.taskName(getValue(sheet0.getRow(i).getCell(1),2))
.parentId("0")
.taskClass("1")
.taskDesc(getValue(sheet0.getRow(i).getCell(4),2))
.detailTaskList(detailTaskList)
.build();
planTemplate.getMainTaskList().add(planTemplateList);
}else{
return R.fail(PREFIX+"一级计划存在重复序号——>"+value);
}
}
log.info("<--一级计划解析成功-->");
//c.b 解析二级计划
//todo 一级计划 sheet校验，1长度,2字段对不对 3序号 4pid在， 自己不在
for(int i=3;i<sheet1.getPhysicalNumberOfRows()&&!(sheet1.getRow(i).getCell(0)==null||getValue(sheet1.getRow(i).getCell(0),1)=="");i++){
//截取pid,
String value = getValue(sheet1.getRow(i).getCell(0),2);
int i1 = value.indexOf(".");
if(i1==-1){
boolean add = orderNoSet0.add(value);
if(add){
return R.fail(PREFIX+"二级计划中存在一级计划没有的一级序号——>"+value);
}
}else{
String[] split = value.split("\\.");
if(split.length>2){
return R.fail(PREFIX+"二级计划存在错误序号——>"+value);
}
else if(split.length==2){
boolean add = orderNoSet0.add(split[0]);
if(add){
return R.fail(PREFIX+"二级计划存在错误序号——>"+value);
}else{
boolean ad = orderNoSet1.add(value);
if(ad){
CopyOnWriteArrayList<PlPlanTemplateDPO> detailTaskList = new CopyOnWriteArrayList<>();
int physicalNumberOfCells = sheet1row1.getPhysicalNumberOfCells();
for(int j=2,sign=0;j<physicalNumberOfCells;j=j+6,sign++){
String stringCellValue1 = getValue(sheet1.getRow(i).getCell(j),2);
String stringCellValue2 = getValue(sheet1.getRow(i).getCell(j+1),2);
String stringCellValue3 = getValue(sheet1.getRow(i).getCell(j+2),2);
String stringCellValue4 = getValue(sheet1.getRow(i).getCell(j+3),2);
String stringCellValue5 = getValue(sheet1.getRow(i).getCell(j+4),2);
String stringCellValue6 = getValue(sheet1.getRow(i).getCell(j+5),2);
boolean stringCellValueSign = !stringCellValue1.isEmpty()
||!stringCellValue2.isEmpty()
||!stringCellValue3.isEmpty()
||!stringCellValue4.isEmpty()
||!stringCellValue5.isEmpty()
||!stringCellValue6.isEmpty();
if(stringCellValueSign){
//TODO 默认时间对, 责任人对， 信息全集 userMap
String perInchargeName = sheet1.getRow(i).getCell(j + 2).getStringCellValue();
PlPlanTemplateDPO planTemplateDetail = PlPlanTemplateDPO.builder()
.plTeamDeptId(deptList.get(sign).getPlPlanTemplateDeptId())
.beginTime(sheet1.getRow(i).getCell(j).getDateCellValue())
.overTime(sheet1.getRow(i).getCell(j+1).getDateCellValue())
.perInchargeId(userMap.get(perInchargeName).getSysUserId())
.perInchargeName(sheet1.getRow(i).getCell(j+2).getStringCellValue())
.perInchargeDeptId(userMap.get(perInchargeName).getSysDeptId())
.perInchargeDeptName(userMap.get(perInchargeName).getDeptName())
.perInchargeOrgId(userMap.get(perInchargeName).getSysOrgId())
.perInchargeOrgName(userMap.get(perInchargeName).getOrgName())
.inputFile(getValue(sheet1.getRow(i).getCell(j+3),2))
.outputFile(getValue(sheet1.getRow(i).getCell(j+4),2))
.checkStandard(getValue(sheet1.getRow(i).getCell(j+5),2))
.build();
detailTaskList.add(planTemplateDetail);
}
}
PlPlanTemplateListVO planTemplateList = PlPlanTemplateListVO.builder()
.plPlanTemplateId(plPlanTemplatePO.getPlPlanTemplateId())
.orderNo(value)
.taskName(getValue(sheet1.getRow(i).getCell(1),2))
.parentId(split[0])
.taskClass("2")
.detailTaskList(detailTaskList)
.build();
planTemplate.getMainTaskList().add(planTemplateList);
}else{
return R.fail(PREFIX+"二级计划存在重复序号——>"+sheet1.getRow(i).getCell(0).getStringCellValue());
}
}
}
}
}
log.info("<--二级计划解析成功-->");
//c.c 新增三级计划
if(sheet2!=null){
//todo 一级计划 sheet校验，1长度,2字段对不对 3序号 4pid在，自己不在
for(int i=3;i<sheet2.getPhysicalNumberOfRows()&&!(sheet2.getRow(i).getCell(0)==null||getValue(sheet2.getRow(i).getCell(0),1)=="");i++){
//截取pid,
String value = getValue(sheet2.getRow(i).getCell(0), 2);
int i1 = value.indexOf(".");
if(i1==-1){
boolean add = orderNoSet0.add(value);
if(add){
return R.fail(PREFIX+"三级计划中存在一级计划没有的一级序号——>"+value);
}
}else{
String[] split = value.split("\\.");
if(split.length==2){
boolean add = orderNoSet1.add(value);
if(add){
return R.fail(PREFIX+"三级计划中存在二级计划没有的二级序号——>"+value);
}
}
else if(split.length==3){
//TODO 截取pid
int lastIndexOf = value.lastIndexOf(".");
String substringPid = value.substring(0, lastIndexOf);
boolean add1 = orderNoSet1.add(substringPid); //应false
if(add1){
return R.fail(PREFIX+"三级计划中存在错误序号——>"+value);
}
boolean add2 = orderNoSet2.add(value);
if(add2){
CopyOnWriteArrayList<PlPlanTemplateDPO> detailTaskList = new CopyOnWriteArrayList<>();
int physicalNumberOfCells = sheet1row1.getPhysicalNumberOfCells();
for(int j=2,sign=0;j<physicalNumberOfCells;j=j+6,sign++){
String stringCellValue1 = getValue(sheet2.getRow(i).getCell(j),2);
String stringCellValue2 = getValue(sheet2.getRow(i).getCell(j+1),2);
String stringCellValue3 = getValue(sheet2.getRow(i).getCell(j+2),2);
String stringCellValue4 = getValue(sheet2.getRow(i).getCell(j+3),2);
String stringCellValue5 = getValue(sheet2.getRow(i).getCell(j+4),2);
String stringCellValue6 = getValue(sheet2.getRow(i).getCell(j+5),2);
boolean stringCellValueSign = !stringCellValue1.isEmpty()
||!stringCellValue2.isEmpty()
||!stringCellValue3.isEmpty()
||!stringCellValue4.isEmpty()
||!stringCellValue5.isEmpty()
||!stringCellValue6.isEmpty();
if(stringCellValueSign){
//TODO 默认时间对, 责任人对
String perInchargeName = sheet1.getRow(i).getCell(j + 2).getStringCellValue();
PlPlanTemplateDPO planTemplateDetail = PlPlanTemplateDPO.builder()
.plTeamDeptId(deptList.get(sign).getPlPlanTemplateDeptId())
.beginTime(sheet2.getRow(i).getCell(j).getDateCellValue())
.overTime(sheet2.getRow(i).getCell(j+1).getDateCellValue())
.perInchargeId(userMap.get(perInchargeName).getSysUserId())
.perInchargeName(sheet1.getRow(i).getCell(j+2).getStringCellValue())
.perInchargeDeptId(userMap.get(perInchargeName).getSysDeptId())
.perInchargeDeptName(userMap.get(perInchargeName).getDeptName())
.perInchargeOrgId(userMap.get(perInchargeName).getSysOrgId())
.perInchargeOrgName(userMap.get(perInchargeName).getOrgName())
.inputFile(sheet2.getRow(i).getCell(j+3).getStringCellValue())
.outputFile(sheet2.getRow(i).getCell(j+4).getStringCellValue())
.checkStandard(sheet2.getRow(i).getCell(j+5).getStringCellValue())
.build();
detailTaskList.add(planTemplateDetail);
}
}
PlPlanTemplateListVO planTemplateList = PlPlanTemplateListVO.builder()
.plPlanTemplateId(plPlanTemplatePO.getPlPlanTemplateId())
.orderNo(value)
.taskName(sheet2.getRow(i).getCell(1).getStringCellValue())
.parentId(substringPid)
.taskClass("3")
.detailTaskList(detailTaskList)
.build();
planTemplate.getMainTaskList().add(planTemplateList);
}else{
return R.fail(PREFIX+"三级计划存在重复序号——>"+value);
}
}
else if(split.length>3){
//TODO 截取pid
int lastIndexOf = value.lastIndexOf(".");
String substringPid = value.substring(0, lastIndexOf);
boolean add = orderNoSet2.add(substringPid);//pid应false
if(add){
return R.fail(PREFIX+"三级计划存在错误序号或错位序号——>"+value);
}else{
boolean ad = orderNoSet2.add(value);
if(ad){
CopyOnWriteArrayList<PlPlanTemplateDPO> detailTaskList = new CopyOnWriteArrayList<>();
int physicalNumberOfCells = sheet1row1.getPhysicalNumberOfCells();
for(int j=2,sign=0;j<physicalNumberOfCells;j=j+6,sign++){
String stringCellValue1 = getValue(sheet2.getRow(i).getCell(j),2);
String stringCellValue2 = getValue(sheet2.getRow(i).getCell(j+1),2);
String stringCellValue3 = getValue(sheet2.getRow(i).getCell(j+2),2);
String stringCellValue4 = getValue(sheet2.getRow(i).getCell(j+3),2);
String stringCellValue5 = getValue(sheet2.getRow(i).getCell(j+4),2);
String stringCellValue6 = getValue(sheet2.getRow(i).getCell(j+5),2);
boolean stringCellValueSign = !stringCellValue1.isEmpty()
||!stringCellValue2.isEmpty()
||!stringCellValue3.isEmpty()
||!stringCellValue4.isEmpty()
||!stringCellValue5.isEmpty()
||!stringCellValue6.isEmpty();
if(stringCellValueSign){
//TODO 默认时间对, 责任人对
String perInchargeName = sheet1.getRow(i).getCell(j + 2).getStringCellValue();
PlPlanTemplateDPO planTemplateDetail = PlPlanTemplateDPO.builder()
.plTeamDeptId(deptList.get(sign).getPlPlanTemplateDeptId())
.beginTime(sheet2.getRow(i).getCell(j).getDateCellValue())
.overTime(sheet2.getRow(i).getCell(j+1).getDateCellValue())
.perInchargeId(userMap.get(perInchargeName).getSysUserId())
.perInchargeName(sheet1.getRow(i).getCell(j+2).getStringCellValue())
.perInchargeDeptId(userMap.get(perInchargeName).getSysDeptId())
.perInchargeDeptName(userMap.get(perInchargeName).getDeptName())
.perInchargeOrgId(userMap.get(perInchargeName).getSysOrgId())
.perInchargeOrgName(userMap.get(perInchargeName).getOrgName())
.inputFile(sheet2.getRow(i).getCell(j+3).getStringCellValue())
.outputFile(sheet2.getRow(i).getCell(j+4).getStringCellValue())
.checkStandard(sheet2.getRow(i).getCell(j+5).getStringCellValue())
.build();
detailTaskList.add(planTemplateDetail);
}
}
PlPlanTemplateListVO planTemplateList = PlPlanTemplateListVO.builder()
.plPlanTemplateId(plPlanTemplatePO.getPlPlanTemplateId())
.orderNo(value)
.taskName(sheet2.getRow(i).getCell(1).getStringCellValue())
.parentId(substringPid)
.taskClass("3")
.detailTaskList(detailTaskList)
.build();
planTemplate.getMainTaskList().add(planTemplateList);
}else{
return R.fail(PREFIX+"三级计划存在重复序号——>"+value);
}
}
}
}
}
}
log.info("<--三级计划解析成功-->");
file.getInputStream().close();
R addTemplateDetailR = this.addTemplateDetail(planTemplate);
if(addTemplateDetailR.getCode()==200){
log.info("<--计划信息保存成功，模板导入成功-->");
}else{
log.info("<--计划信息保存失败，模板导入失败-->");
return R.fail(ERROR_PREFIX+"计划信息保存失败!");
}
return R.success("校验通过，导入成功！");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——校验日期与责任人
*/
private R checkDateUser(Sheet sheet, int SheetLevel,Set userSet) {
if(SheetLevel==1){
Sheet sheet0 = sheet;
//1.循环校验一级计划
for(int i=2;i<sheet0.getPhysicalNumberOfRows()&&!(sheet0.getRow(i).getCell(0)==null||getValue(sheet0.getRow(i).getCell(0),1)=="");i++){
R checkDate = this.checkDate(sheet0.getRow(i).getCell(2),sheet0.getRow(i).getCell(3));
//1.1如果校验失败
if(checkDate.getCode()==500){
return R.fail("模板有误：一级计划第"+(i-1)+"行"+checkDate.getMsg());
}
}
}else if(SheetLevel==2){
Sheet sheet1 = sheet;
//2.循环校验二级计划
for(int i=3;i<sheet1.getPhysicalNumberOfRows()&&!(sheet1.getRow(i).getCell(0)==null||getValue(sheet1.getRow(i).getCell(0),1)=="");i++){
int physicalNumberOfCells = sheet1.getRow(1).getPhysicalNumberOfCells();
//2.1_循环任务详情
for(int j=2,sign=0;j<physicalNumberOfCells;j=j+6,sign++){
//2.2检验日期
R checkDate = this.checkDate(sheet1.getRow(i).getCell(j),sheet1.getRow(i).getCell(j + 1));
//_2.2.1 如果校验失败
if(checkDate.getCode()==500){
String row;
switch (checkDate.getMsg()) {
case "开始时间格式有误": row = "第"+(j+1)+"列";break;
case "结束时间格式有误": row = "第"+(j+2)+"列";break;
case "结束时间小于开始时间": row = "第"+(j+1)+"列与"+"第"+(j+2)+"列";break;
default:
throw new IllegalStateException("Unexpected value: " + checkDate.getMsg());
}
return R.fail("模板有误：二级计划第"+(i-2)+"行，"+row+checkDate.getMsg());
}
//2.3检验用户
R checkUser = this.checkUser(sheet1.getRow(i).getCell(j+2),userSet);
if(checkUser.getCode()==500){
return R.fail("模板有误：二级计划第"+(i-2)+"行，"+"第"+(j+3)+"列"+checkUser.getMsg());
}
}
}
}else if(SheetLevel==3){
Sheet sheet2 = sheet;
//3.循环校验三级计划
for(int i=3;i<sheet2.getPhysicalNumberOfRows()&&!(sheet2.getRow(i).getCell(0)==null||getValue(sheet2.getRow(i).getCell(0),1)=="");i++){
int physicalNumberOfCells = sheet2.getRow(1).getPhysicalNumberOfCells();
//3.1_循环任务详情
for(int j=2,sign=0;j<physicalNumberOfCells;j=j+6,sign++){
R checkDate = this.checkDate(sheet2.getRow(i).getCell(j),sheet2.getRow(i).getCell(j + 1));
//3.2 如果校验失败
if(checkDate.getCode()==500){
String row;
switch (checkDate.getMsg()) {
case "开始时间格式有误": row = "第"+(j+1)+"列";break;
case "结束时间小于开始时间": row = "第"+(j+2)+"列";break;
case "结束时间格式有误": row = "第"+(j+1)+"列与"+"第"+(j+2)+"列";break;
default:
throw new IllegalStateException("Unexpected value: " + checkDate.getMsg());
}
return R.fail("模板有误：三级计划第"+(i-2)+"行，"+row+checkDate.getMsg());
}
//3.3检验用户
R checkUser = this.checkUser(sheet2.getRow(i).getCell(j+2),userSet);
if(checkUser.getCode()==500){
return R.fail("模板有误：三级计划第"+(i-2)+"行，"+"第"+(j+3)+"列"+checkUser.getMsg());
}
}
}
}
return R.success("日期与责任人校验成功");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——>校验日期与责任人——>校验开始与结束日期
*/
private R checkDate(Cell cellBefore, Cell cellAfter) {
final String resultString1 = "开始时间格式有误";
final String resultString2 = "结束时间格式有误";
final String resultString3 = "结束时间小于开始时间";
//1.校验数据是否为空,且为data
//开始时间，0_非日期类型、1_日期类型
int signBefore = 0;
//结束时间，0_非日期类型、1_日期类型
int afterBefore = 0;
if(!(cellBefore==null||getValue(cellBefore,2)=="")){
CellType cellType = cellBefore.getCellType();
if(cellType==NUMERIC&&DateUtil.isCellDateFormatted(cellBefore)){
signBefore = 1;
}else{
return R.fail(resultString1);
}
}
if(!(cellAfter==null||getValue(cellAfter,2)=="")){
CellType cellType = cellAfter.getCellType();
if(cellType==NUMERIC&&DateUtil.isCellDateFormatted(cellAfter)){
afterBefore = 1;
}else{
return R.fail(resultString2);
}
}
//2.检验大小合理性，均存在时启动校验
if(signBefore==1&&afterBefore==1){
Date dateBefore = cellBefore.getDateCellValue();
Date dateAfter = cellAfter.getDateCellValue();
if(!dateAfter.after(dateBefore)){
return R.fail(resultString3);
}
}
return R.success("日期校验成功");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——>校验日期与责任人——>校验责任人
*/
private R checkUser(Cell cell,Set userSet) {
if(!(cell==null||getValue(cell,2)=="")){
CellType cellType = cell.getCellType();
if(cellType==STRING){
String userName = getValue(cell,2);
if(userSet.add(userName)){
return R.fail("责任人不存在");
}
}else{
return R.fail("责任人格式有误");
}
}
return R.success("责任人校验成功");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——新增模板信息
*/
private R addTemplateImport(PlPlanTemplatePO plPlanTemplatePO) {
//1.校验数据
if(plPlanTemplatePO.getTempNo() == null || plPlanTemplatePO.getTempNo().isEmpty()){
return R.fail("模板编号不可为空！");
}
String sureTempStatus = "0";
if(plPlanTemplatePO.getTempStatus() == null || !sureTempStatus.equals(plPlanTemplatePO.getTempStatus())){
return R.fail("模板状态应为0！");
}
//2.新增计划模板主信息
int sign = planTemplateMapper.insert(plPlanTemplatePO);
if(sign==1){
return R.success("计划模板主信息新增成功");
}else{
return R.fail("计划模板主信息新增失败");
}
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——新增计划模板部门信息
*/
private R addDept(Row sheet1row1,String plPlanTemplateId) {
Lock myLock = new ReentrantLock();
List<PlPlanTemplateDeptPO> deptList = new CopyOnWriteArrayList();
myLock.lock();
try {
int physicalNumberOfCells = sheet1row1.getPhysicalNumberOfCells();
for(int i=2;i<physicalNumberOfCells;i=i+6){
Cell cell = sheet1row1.getCell(i);
//addTemplateDept (模板id,拿部门名称，去到空 或者 选算出来几个)
PlPlanTemplateDeptPO plPlanTemplateDeptPO = PlPlanTemplateDeptPO.builder()
.plPlanTemplateId(plPlanTemplateId)
.deptName(cell.getStringCellValue())
.build();
//2.新增计划模板单位信息
int sign = planTemplateMapper.addTemplateDept(plPlanTemplateDeptPO);
if(sign!=1){
return R.fail("新增计划模板单位信息失败");
}
deptList.add(plPlanTemplateDeptPO);
}
} finally {
myLock.unlock();
}
return R.data(deptList,"新增计划模板单位信息成功");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——获取cell值
*/
public String getValue(Cell cell,int type){
//匹配类型数据
if (cell != null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//1,校验；2获取值
if(type==1){
cellValue = cell.toString();
}else if(type==2){
//不是日期格式，[转换为字符串输出]
String cellstr = cell.toString();
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
if(cellValue.indexOf(".") > -1) {
cellValue = cellstr;
}
}
}
break;
case ERROR:
System.out.print("[数据类型错误]");
break;
}
return cellValue;
}else{
return "";
}
}
五、PoiUtil工具类:
package com.andawell.ipd.platform.plan.infrastructure.util;
import cn.hutool.core.date.DateTime;
import com.andawell.technology.platform.tools.api.R;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import static org.apache.poi.ss.usermodel.CellType.*;
/**
* @author：songwl
* @Date：2021/11/3 10:47
* @Description： poi相关的方法
*/
@Component
@SuppressWarnings("all")
@Slf4j
public class PoiUtil {
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——校验日期与责任人
* 参数：
* Sheet sheet 要检验的sheet
* int SheetLevel 要检验的sheet等级 1一级计划，2二级计划，3三级计划
* Set userSet 系统全体用户名称
*/
public R checkDateUser(Sheet sheet, int SheetLevel, Set userSet) {
if(SheetLevel==1){
Sheet sheet0 = sheet;
//1.循环校验一级计划
for(int i=2;i<sheet0.getPhysicalNumberOfRows()&&sheet0.getRow(i)!=null&&!(sheet0.getRow(i).getCell(0)==null||getValue(sheet0.getRow(i).getCell(0),1)=="");i++){
R checkDate = this.checkDate(sheet0.getRow(i).getCell(2),sheet0.getRow(i).getCell(3));
//1.1如果校验失败
if(checkDate.getCode()==500){
return R.fail("模板有误：一级计划第"+(i+1)+"行"+checkDate.getMsg());
}
}
}else if(SheetLevel==2){
Sheet sheet1 = sheet;
//2.循环校验二级计划
for(int i=3;i<sheet1.getPhysicalNumberOfRows()&&sheet1.getRow(i)!=null&&!(sheet1.getRow(i).getCell(0)==null||getValue(sheet1.getRow(i).getCell(0),1)=="");i++){
int physicalNumberOfCells = sheet1.getRow(2).getPhysicalNumberOfCells();
//2.1_循环任务详情
for(int j=2,sign=0;j+4<physicalNumberOfCells;j=j+6,sign++){
//2.2检验日期
R checkDate = this.checkDate(sheet1.getRow(i).getCell(j),sheet1.getRow(i).getCell(j + 1));
//_2.2.1 如果校验失败
if(checkDate.getCode()==500){
String row;
switch (checkDate.getMsg()) {
case "开始时间格式有误": row = "第"+getExcelColumn(j+1)+"列";break;
case "结束时间格式有误": row = "第"+getExcelColumn(j+2)+"列";break;
case "结束时间小于开始时间": row = "第"+getExcelColumn(j+1)+"列与"+"第"+getExcelColumn(j+2)+"列";break;
default:
throw new IllegalStateException("Unexpected value: " + checkDate.getMsg());
}
return R.fail("模板有误：二级计划第"+(i+1)+"行，"+row+checkDate.getMsg());
}
//2.3检验用户 20220228去除用户检验，注释掉
/* R checkUser = this.checkUser(sheet1.getRow(i).getCell(j+2),userSet);
if(checkUser.getCode()==500){
return R.fail("模板有误：二级计划第"+(i+1)+"行，"+"第"+getExcelColumn(j+3)+"列"+checkUser.getMsg());
}*/
}
}
}else if(SheetLevel==3){
Sheet sheet2 = sheet;
//3.循环校验三级计划
for(int i=3;i<sheet2.getPhysicalNumberOfRows()&&sheet2.getRow(i)!=null&&!(sheet2.getRow(i).getCell(0)==null||getValue(sheet2.getRow(i).getCell(0),1)=="");i++){
int physicalNumberOfCells = sheet2.getRow(2).getPhysicalNumberOfCells();
//3.1_循环任务详情
for(int j=2,sign=0;j+4<physicalNumberOfCells;j=j+6,sign++){
R checkDate = this.checkDate(sheet2.getRow(i).getCell(j),sheet2.getRow(i).getCell(j + 1));
//3.2 如果校验失败
if(checkDate.getCode()==500){
String row;
switch (checkDate.getMsg()) {
case "开始时间格式有误": row = "第"+getExcelColumn(j+1)+"列";break;
case "结束时间小于开始时间": row = "第"+getExcelColumn(j+2)+"列";break;
case "结束时间格式有误": row = "第"+getExcelColumn(j+1)+"列与"+"第"+getExcelColumn(j+2)+"列";break;
default:
throw new IllegalStateException("Unexpected value: " + checkDate.getMsg());
}
return R.fail("模板有误：三级计划第"+(i+1)+"行，"+row+checkDate.getMsg());
}
//3.3检验用户 20220228去除用户检验，注释掉
/* R checkUser = this.checkUser(sheet2.getRow(i).getCell(j+2),userSet);
if(checkUser.getCode()==500){
return R.fail("模板有误：三级计划第"+(i+1)+"行，"+"第"+getExcelColumn(j+3)+"列"+checkUser.getMsg());
}*/
}
}
}
return R.success("日期校验成功");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——>校验日期与责任人——>校验开始与结束日期
* 参数：
* Cell cellBefore 开始时间单元格
* Cell cellAfter 结束时间单元格
*/
public R checkDate(Cell cellBefore, Cell cellAfter) {
final String resultString1 = "开始时间格式有误";
final String resultString2 = "结束时间格式有误";
final String resultString3 = "结束时间小于开始时间";
//1.校验数据是否为空,且为data
//开始时间，0_非日期类型、1_日期类型
int signBefore = 0;
//结束时间，0_非日期类型、1_日期类型
int afterBefore = 0;
if(!(cellBefore==null||getValue(cellBefore,2)=="")){
CellType cellType = cellBefore.getCellType();
//20230504兼容公式,日期校验添加FORMULA类型的判断
if(cellType==FORMULA){
cellType = cellBefore.getCachedFormulaResultType();
}
if(cellType==NUMERIC && DateUtil.isCellDateFormatted(cellBefore)){
signBefore = 1;
}else{
return R.fail(resultString1);
}
}
if(!(cellAfter==null||getValue(cellAfter,2)=="")){
CellType cellType = cellAfter.getCellType();
//20230504兼容公式,日期校验添加FORMULA类型的判断
if(cellType==FORMULA){
cellType = cellBefore.getCachedFormulaResultType();
}
if(cellType==NUMERIC && DateUtil.isCellDateFormatted(cellAfter)){
afterBefore = 1;
}else{
return R.fail(resultString2);
}
}
//2.检验大小合理性，均存在时启动校验
if(signBefore==1&&afterBefore==1){
Date dateBefore = cellBefore.getDateCellValue();
Date dateAfter = cellAfter.getDateCellValue();
if(dateAfter.before(dateBefore)){
return R.fail(resultString3);
}
}
return R.success("日期校验成功");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——>校验日期与责任人——>校验责任人
* 参数：
* Cell cell 要校验的责任人单元格
* Set userSet 系统全体用户名称
*/
private R checkUser(Cell cell,Set userSet) {
if(!(cell==null||getValue(cell,2)=="")){
CellType cellType = cell.getCellType();
if(cellType==STRING){
String userName = getValue(cell,2);
if(userSet.add(userName)){
return R.fail("责任人不存在或此用户无效");
}
}else{
return R.fail("责任人格式有误");
}
}
return R.success("责任人校验成功");
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——获取cell值
* 参数：
* Cell cell 单元格；
* int type 1,校验（检验单元格是否为空）；2获取单元格值
*/
public String getValue(Cell cell,int type){
//匹配类型数据
if (cell != null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//1,校验；2获取值
if(type==1){
cellValue = cell.toString();
}else if(type==2){
//不是日期格式，[转换为字符串输出]
String cellstr = cell.toString();
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
if(cellValue.indexOf(".") > -1) {
cellValue = cellstr;
}
}
}
break;
case FORMULA:
try {
CellType resultType = cell.getCachedFormulaResultType();
return getFormulaValue(resultType, cell, type);
} catch (IllegalStateException e) {
e.printStackTrace();
return String.valueOf(cell.getRichStringCellValue());
}
case ERROR:
System.out.print("[数据类型错误]");
break;
}
return cellValue;
}else{
return "";
}
}
/**
* @author: songwl
* @Date: 2023/4/28 15:12
* @description: 获取cell为公式时的值
*/
public String getFormulaValue(CellType resultType,Cell cell,int type){
//匹配类型数据
if (cell != null) {
String cellValue = "";
switch (resultType) {
case STRING:
cellValue = cell.getStringCellValue();
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK:
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//1,校验；2获取值
if(type==1){
cellValue = cell.toString();
}else if(type==2){
//不是日期格式，[转换为字符串输出]
String cellstr = cell.toString();
cell.setCellType(CellType.STRING);
cellValue = cell.getStringCellValue();
if(cellValue.indexOf(".") > -1) {
cellValue = cellstr;
}
}
}
break;
case ERROR:
System.out.print("[数据类型错误]");
break;
}
return cellValue;
}else{
return "";
}
}
/**
* @author: songwl
* @Date: 2021/10/19 20:59
* @description: 模板导入——获取cell的Date值
* 参数：
* Cell cell 单元格；
*/
public Date getDateValue(Cell cell){
//能走到这的Date要么是Date类型要么是""
try {
if(cell==null){
return null;
}else{
return cell.getDateCellValue();
}
} catch (IllegalStateException e) {
log.debug("空时间");
return null;
}
}
//根据excel数字列号获取对应的字母列（excel数字列号从一开始的）
public String getExcelColumn(int num){
String excelColumn = "";
do{
int i = num % 26;
String s = numToCharforAscii(i + 64);
if(s.equals("overAsciiError")){
return "overAsciiError";
}else if(s.equals("overCapitalAsciiError")){
return "overCapitalAsciiError";
}else{
excelColumn = s.concat(excelColumn);
num = num / 26;
}
} while (num!=0);
return excelColumn;
}
//数字获取对应的ascii码
public String numToCharforAscii(int num){
String stringAscii = "";
if(num<0||num>127){
return "overAsciiError";
}else if(num<65||num>90){
return "overCapitalAsciiError";
}else{
char theAscii = (char)num;
stringAscii = String.valueOf(theAscii);
}
return stringAscii;
}
/* public static void main(String[] args) {
char theAscii1 = (char)65;
char theAscii2 = (char)66;
String s = theAscii1 + theAscii2 + "";
byte a = (byte) 'A';
String stringAscii = String.valueOf(theAscii1);
int i = 28 % 26;
int i2 = 28/26;
//String excelColumn = getExcelColumn(40);
}*/
/**
* @author: songwl
* @Date: 2022/2/28 14:55
* @description: 0、1转N、Y
*/
public String changeYn(String args) {
if("".equals(args)){
return "";
}else if("0".equals(args)){
return "N";
}else if("1".equals(args)){
return "Y";
}else{
//异常数据
return "";
}
}
/*********************word***********************/
//word替换text
/**
* @author: songwl
* @Date: 2022/7/6 16:25
* @description: 那个cell，那个Paragraphs（默认0），text要替换的内容
*/
/* public static void replaceParagraphText(XWPFTableCell xwpfTableCell, int i, String aa) {
XWPFParagraph paragraph = new XWPFParagraph();
xwpfTableCell.removeParagraph();
XWPFParagraph paragraph = document.createParagraph();
XWPFRun run = paragraph.createRun();
run.setText("666");
}*/
}
3123
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
