EasyExcel复杂excel导入
easyexcel
官方都是一些简单的导入到处示例,复杂的excel文档导入,还得自己去慢慢琢磨、百度、思考、总结、学习、观察。
代码地址在文档的最后,如果你也遇到这种需求,不妨动动你的小拇指,点个关注,要是可以点个赞,那就更好咯,做人嘛,不要这么小气,不要吝啬你的赞美,哈哈。
要导入的excel
格式,如下图:
第一个sheet
内容:
第二个sheet
内容,一对多,一个阶段对应多个任务,一个任务对应多个动作:
废话不多说,直接上代码,能看懂多少就看你自己的功力了
controller
层
@RestController
@RequestMapping("/sakura/easyexcel")
@Api(value = "复杂excel导入", tags = {"复杂excel导入"})
public class ProjectExcelController {
@Autowired
ProjectEasyExcelService projectEasyExcelService;
@ApiOperation("复杂excel导入")
@PostMapping(value = "/complex/upload")
public CommonResult<Object> upload(@RequestParam("file") MultipartFile file){
projectEasyExcelService.projectRead(file);
return CommonResult.success();
}
}
service
层
/**
* 项目信息excel
*/
@Transactional(rollbackFor = Exception.class)
public void projectRead(MultipartFile file) {
EasyExcelListener easyExcelListener = new EasyExcelListener();
ExcelReader excelReader = null;
try {
excelReader = EasyExcelFactory.read(file.getInputStream(), easyExcelListener).build();
} catch (IOException e) {
throw new YErrorException("项目信息导入出错!");
}
// step2. 获取各个sheet页信息
List<ReadSheet> sheets = excelReader.excelExecutor().sheetList();
// step3. 获取各个Shhet页表格内容存于map
Map<Integer, List<LinkedHashMap<String, String>>> sheetInfos = new HashMap<>(sheets.size());
for (ReadSheet sheet : sheets) {
Integer sheetNo = sheet.getSheetNo();
excelReader.read(sheet);
sheetInfos.put(sheetNo, easyExcelListener.getListMap());
}
//保存数据到数据库
saveExcelInfo(sheetInfos);
}
上面projectRead
方法用到的EasyExcelListener
类
@Slf4j
public class EasyExcelListener extends AnalysisEventListener<Object> {
// 创建list集合封装最终的数据
private List<Object> list = new ArrayList<>();
// sheet页索引
private int sheetNo = 0;
@Override
public void invoke(Object t, AnalysisContext context) {
// 读取excle内容
int currentSheetNo = context.readSheetHolder().getSheetNo();
if (currentSheetNo != sheetNo) {
// 如果不根据sheet页索引更新状态重新创建list,list会反复添加前面的sheet页对象值
list = new ArrayList<>();
sheetNo = currentSheetNo;
}
list.add(t);
}
// 读取excel表头信息
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
}
// 读取完成后执行
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
}
/**
* 将表格转化为map集合(复杂excel读取用此方法)
*
* @return map集合
*/
public List<LinkedHashMap<String, String>> getListMap() {
String jsonObj = JSON.toJSONString(list);
return JSON.parseArray(jsonObj, LinkedHashMap.class);
}
}
上面projectRead
中的方法
public void saveExcelInfo(Map<Integer, List<LinkedHashMap<String, String>>> sheetInfos) {
for (Integer sheetNo : sheetInfos.keySet()) {
List<LinkedHashMap<String, String>> maps = sheetInfos.get(sheetNo);
// 不同sheet页数据处理方式不同
switch (sheetNo) {
case 0:
saveProject(maps);
break;
case 1:
saveTarget(maps);
break;
default:
break;
}
}
}
保存第一个sheet
的项目信息,并返回全局的项目id,代码很长,简化了一部分,全部代码可以看最后github
的地址
public void saveProject(List<LinkedHashMap<String, String>> maps) {
ProjectManage projectManage = new ProjectManage();
for (LinkedHashMap<String, String> map : maps) {
if (map.containsValue("项目名称")) {
String projectName = map.getOrDefault("1", "");
if (StringUtils.isBlank(projectName)) {
throw new YWarmingException("项目名称不能为空!");
}
projectManage.setProjectName(projectName);
}
if (map.containsValue("项目编号")) {
String projectCode = map.getOrDefault("4", "");
projectManage.setProjectCode(projectCode);
}
if (map.containsValue("合同签订时间")) {
String contractSignTimeStr = map.getOrDefault("1", "");
if (StringUtils.isNotBlank(contractSignTimeStr)) {
Date contractSignDate = DateUtil.parse(contractSignTimeStr);
Instant instant = contractSignDate.toInstant();
LocalDateTime contractSignTime = instant.atZone(ZoneId.systemDefault()).toLocalDateTime();
projectManage.setContractSignTime(contractSignTime);
}
}
if (map.containsValue("合同规定终验时间")) {
String contractRuleFinalAcceptTimeStr = map.getOrDefault("3", "");
if (StringUtils.isNotBlank(contractRuleFinalAcceptTimeStr)) {
Date contractSignDate = DateUtil.parse(contractRuleFinalAcceptTimeStr);
Instant instant = contractSignDate.toInstant();
LocalDateTime contractRuleFinalAcceptTime = instant.atZone(ZoneId.systemDefault()).toLocalDateTime();
projectManage.setContractRuleFinalAcceptTime(contractRuleFinalAcceptTime);
}
}
if (map.containsValue("开工时间")) {
String startTimeStr = map.getOrDefault("5", "");
if (StringUtils.isNotBlank(startTimeStr)) {
Date startDate = DateUtil.parse(startTimeStr);
Instant instant = startDate.toInstant();
LocalDateTime startTime = instant.atZone(ZoneId.systemDefault()).toLocalDateTime();
projectManage.setStartTime(startTime);
}
}
}
//这里项目背景的行数,写死了,后面调整表格时,会影响这一块
Map<String, String> projectBackgroudMap = maps.get(8);
String projectBackgroud = projectBackgroudMap.getOrDefault("0", "").toString();
projectManage.setProjectBackgroud(projectBackgroud);
projectManageService.save(projectManage);
projectId = projectManage.getId();
}
保存第二个sheet
的目标,将数据组装成多级嵌套的集合。有点绕,要花点时间去想想,项目赶工,瞎几把乱写了,没想到可以凑合着用。
public void saveTarget(List<LinkedHashMap<String, String>> list) {
if (projectId == 0) {
throw new YErrorException("请先成功导入项目!");
}
FirstTarget firstTarget = new FirstTarget();
SecondTarget secondTarget = new SecondTarget();
List<FirstTarget> firstTargetList = new ArrayList<>();
List<SecondTarget> secondTargetList = new ArrayList<>();
List<ThirdTarget> thirdTargetList = new ArrayList<>();
for (Map<String, String> map : list) {
System.err.println(map);
String firstTargetName = map.getOrDefault("0", "");
String firstTargetType = map.getOrDefault("1", "");
String secondTargetName = map.getOrDefault("2", "");
String thirdTargetName = map.getOrDefault("3", "");
String thirdTargetBudget = map.getOrDefault("4", "");
String positions = map.getOrDefault("5", "");
if (StringUtils.isNotBlank(firstTargetName)) {
//第一阶段名称不为空时,二三阶段一定不为空
if (StringUtils.isNotBlank(secondTargetName)) {
//第二阶段名称不为空
if (secondTargetList.size() != 0) {
List<SecondTarget> secondTargets = firstTarget.getSecondTargets();
secondTargets.addAll(secondTargetList);
}
secondTarget = new SecondTarget();
thirdTargetList = new ArrayList<>();
firstTarget = new FirstTarget();
secondTargetList = new ArrayList<>();
secondTarget.setSecondTargetName(secondTargetName);
ThirdTarget thirdTarget = new ThirdTarget();
thirdTarget.setThirdTargetName(thirdTargetName);
thirdTarget.setThirdTargetBudget(thirdTargetBudget);
thirdTargetList.add(thirdTarget);
secondTarget.setThirdTargets(thirdTargetList);
secondTargetList.add(secondTarget);
firstTarget.setSecondTargets(secondTargetList);
firstTarget.setFirstTargetName(firstTargetName);
firstTarget.setFirstTargetType(firstTargetType);
firstTargetList.add(firstTarget);
}
} else {
//第一阶段名称为空,且第二阶段名称不为空
if (StringUtils.isNotBlank(secondTargetName)) {
//去重
if (secondTargetList.size() != 0) {
SecondTarget st = secondTargetList.get(0);
List<SecondTarget> secondTargets = firstTarget.getSecondTargets();
SecondTarget secondT = secondTargets.get(0);
if (!st.getSecondTargetName().equals(secondT.getSecondTargetName())) {
secondTargets.addAll(secondTargetList);
}
}
secondTarget = new SecondTarget();
thirdTargetList = new ArrayList<>();
secondTargetList = new ArrayList<>();
secondTarget.setSecondTargetName(secondTargetName);
ThirdTarget thirdTarget = new ThirdTarget();
thirdTarget.setThirdTargetName(thirdTargetName);
thirdTarget.setThirdTargetBudget(thirdTargetBudget);
thirdTargetList.add(thirdTarget);
secondTarget.setSecondTargetName(secondTargetName);
secondTarget.setThirdTargets(thirdTargetList);
secondTargetList.add(secondTarget);
} else {
//第一阶段名称为空,第二阶段名称为空,第三阶段不为空
ThirdTarget thirdTarget = new ThirdTarget();
thirdTarget.setThirdTargetBudget(thirdTargetBudget);
thirdTarget.setThirdTargetName(thirdTargetName);
thirdTargetList.add(thirdTarget);
}
}
}
// 保存最后一条数据
if (secondTargetList.size() != 0) {
List<SecondTarget> secondTargets = firstTarget.getSecondTargets();
secondTargets.addAll(secondTargetList);
}
System.err.println(firstTargetList);
}
还有几个实体类就不放上来了,博客太长了,看完有一点点思路,可以帮助到你,我还是很开心的,全部代码和导入的excel
放下面了,可以拿去看看,参考参考。
excel
地址:项目导入.xlsx