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

某个功能如果需要添加大量的数据，通过一条条的方式添加的方式，肯定不合理，本文通过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.commons.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 {@Autowiredprivate 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 {@Overridepublic 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;//当前登录用户idString 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(