EasyExcel实现excel导入(模版上传)

目录 效果 pom.xml application.yml controller service 依赖类 前台 vue代码

某个功能如果需要添加大量的数据，通过一条条的方式添加的方式，肯定不合理，本文通过excel导入的方式来实现该功能，100条数据导入成功85条，失败15条，肯定需要返回一个表格给前台或者返回1个错误excel给前台，本文就实现这两个功能

效果

模版 storeTemplate.xlsx

点击确定，会直接下载excel错误文件，打开如下图 删除第一行 第一列后，修正后，可再次导入，方便调整错误数据 pom.xml <!-- easypoi-spring-boot-starter --> <dependency> <groupId>cn.afterturn</groupId> <artifactId>easypoi-spring-boot-starter</artifactId> <version>4.1.3</version> </dependency> application.yml #####模板下载位置 downloadTemplate: storePath: storeTemplate.xlsx 提供部分代码，主要是了解思路，可参考实现 controller package com.zyee.iopace.web.controller; import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper; import com.baomidou.mybatisplus.extension.plugins.pagination.Page; import com.github.pagehelper.PageInfo; import com.zyee.iopace.web.config.annotation.Log; import com.zyee.iopace.web.config.exception.BusinessException; import com.zyee.iopace.web.dto.station.DeleteBatchPhotoDto; import com.zyee.iopace.web.dto.station.StationStoreAddDto; import com.zyee.iopace.web.dto.station.StationStoreDto; import com.zyee.iopace.web.dto.store.DeleteStoreBatchPhotoDto; import com.zyee.iopace.web.dto.store.StationStoreUpdateDto; import com.zyee.iopace.web.dto.store.StoreBindStationDto; import com.zyee.iopace.web.dto.store.StoreChangeTrackDto; import com.zyee.iopace.web.entity.*; import com.zyee.iopace.web.enums.BusinessType; import com.zyee.iopace.web.enums.EcologyType; import com.zyee.iopace.web.enums.StationStateEnum; import com.zyee.iopace.web.enums.StoreStatusType; import com.zyee.iopace.web.response.ResponseResult; import com.zyee.iopace.web.service.*; import com.zyee.iopace.web.utils.BeanCopyUtils; import com.zyee.iopace.web.utils.PageUtils; import com.zyee.iopace.web.vo.station.StationStoreDetailVo; import com.zyee.iopace.web.vo.station.StationStoreVo; import com.zyee.iopace.web.vo.store.StoreChangeTrackListVo; import io.swagger.annotations.Api; import io.swagger.annotations.ApiOperation; import lombok.extern.slf4j.Slf4j; import org.apache mons.collections4.CollectionUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.transaction.annotation.Transactional; import org.springframework.web.bind.annotation.*; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.io.File; import java.util.*; import java.util.stream.Collectors; @Slf4j @Api(tags = "库存管理") @RestController @RequestMapping("/stationStore") public class StationStoreController { @Autowired private StoreReviseRecordService storeReviseRecordService; @ApiOperation(value = "导入库存信息") @Log(title = "导入库存信息", businessType = BusinessType.IMPORT) @PostMapping("/importStore") public ResponseResult importStore(@RequestParam MultipartFile file, HttpServletResponse response) throws Exception { return ResponseResult.SUCCESS(stationStoreService.importStore(file,response)); } } service package com.zyee.iopace.web.service; import com.zyee.iopace.web.dto.dept.DeptSearchDto; import com.zyee.iopace.web.dto.station.StationStoreAddDto; import com.zyee.iopace.web.dto.station.StationStoreDto; import com.zyee.iopace.web.dto.store.StationStoreUpdateDto; import com.zyee.iopace.web.dto.store.StoreBindStationDto; import com.zyee.iopace.web.dto.store.StoreChangeTrackDto; import com.zyee.iopace.web.entity.StationStore; import com.baomidou.mybatisplus.extension.service.IService; import com.zyee.iopace.web.entity.StoreChangeTrack; import com.zyee.iopace.web.vo.element.ElementImportResponseVo; import org.springframework.web.multipart.MultipartFile; import javax.servlet.http.HttpServletResponse; import java.util.List; public interface StationStoreService extends IService<StationStore> { /** * 导入库存信息 * @param file * @param response * @return 失败返回错误地址 */ ElementImportResponseVo importStore(MultipartFile file, HttpServletResponse response) throws Exception; } @Service public class StationStoreServiceImpl extends ServiceImpl<StationStoreMapper, StationStore> implements StationStoreService { @Override public ElementImportResponseVo importStore(MultipartFile file, HttpServletResponse response) throws Exception { List<StoreImportVo> stationImportVos = PoiUtils.importExcel(file, StoreImportVo.class, null); //成功 List<StationStore> stationStoreList = new ArrayList<>(); //失败信息的集合 List<StoreImportVo> errorList = new ArrayList<>(); //导入成功的数量 int success = 0; //失败条数 int failure = 0; //当前登录用户id String userId = userUtils.getUserId(); //型号 List<StoreCompanyModel> companyModelList = storeCompanyModelService.list(); //序列号集合 List<StationStore> stationStores = list(new LambdaQueryWrapper<StationStore>().select(StationStore::getSerialNumber)); List<String> serialNumberList = new ArrayList<>(); if(CollectionUtil.isNotEmpty(stationStores)){ serialNumberList = stationStores.stream().map(StationStore::getSerialNumber).distinct().collect(Collectors.toList()); } //站点集合 List<Station> stationList = stationService.list(new LambdaQueryWrapper<Station>().orderByAsc(Station::getStationCode)); if(CollectionUtil.isEmpty(stationList)){ stationList = new ArrayList<>(); } for (StoreImportVo storeImport : stationImportVos) { StringBuilder errMsg = new StringBuilder(); StationStore stationStore = checkStore(storeImport, errMsg, userId, companyModelList, serialNumberList, stationList); if (errMsg.toString().length() > 0) { failure++; storeImport.setErrorCause(errMsg.toString()); errorList.add(storeImport); }else{ success++; stationStoreList.add(stationStore); } } if(CollectionUtil.isNotEmpty(stationStoreList)){ //批量新增 saveBatch(stationStoreList); //增加送检判断 stationStoreList.stream().forEach(store->{ if(Objects.nonNull(store.getRectifyTime())){ //库存送检记录 StationStoreAddDto dto = new StationStoreAddDto(); dto.setRectifyTime(store.getRectifyTime()); saveRevise(dto, store); } if(Objects.nonNull(store.getStationId())){ //绑定站点 stationChangeTrack(null, store); } }); } //将错误数据上传 String visitUrl = ""; String msg = "批量站点信息完成，成功导入" + success + "条数据,失败" + failure + "条"; if (failure > 0) { try { String name = UUID.randomUUID()+".xlsx"; Workbook workbook = null; StoreExcelStyler.setProperties(stationService,storeCompanyService,storeCompanyModelService); workbook = PoiUtils.exportErrorExcel(errorList, name, name, StoreImportVo.class, name, StoreExcelStyler.class, response); StoreExcelStyler.setData(workbook); FileOutputStream outputStream = new FileOutputStream(pathService.getAbsoTemplateExcelPath(name)); //写入 workbook.write(outputStream); visitUrl = pathService.getTemplateExcelPath(name); } catch (Exception e) { e.printStackTrace(); } } ElementImportResponseVo vo = new ElementImportResponseVo(); vo.setMsg(msg); vo.setVisitUrl(visitUrl); return vo; } /** * 验证格式有问题 * @param storeImport * @param errMsg * @param userId * @return */ private StationStore checkStore(StoreImportVo storeImport, StringBuilder errMsg, String userId, List<StoreCompanyModel> companyModelList, List<String> serialNumberList, List<Station> stationList) { StationStore store = new StationStore(); if(StringUtils.isBlank(storeImport.getCompanyName(