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 {
   
    @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(