Excel导入模板下载及数据导入

         
            cn.afterturn
            easypoi-spring-boot-starter
            4.2.0
        

模板下载：

    @ApiOperation("sim卡模板下载")
    @GetMapping("/downloadSimCard")
    @ResponseBody
    public void downloadSimCardTemplate(HttpServletResponse response){
        try {
            FileUtils.downloadFile(response,SIM_CARD_import_TEMPLATE_NAME);
        } catch (Exception e) {
            e.printStackTrace();
            throw new BssException(BssResultCode.FILE_DOWNLOAD_FAIL);
        }
    }

package com.wit.tsp.bss.util;

import org.apache.commons.compress.utils.IOUtils;
import org.springframework.core.io.ClassPathResource;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;


public class FileUtils {

    private static final String FILE_PATH = "template/";

    
    public static void downloadFile(HttpServletResponse response, String filename)
            throws Exception {

        InputStream inputStream = null;
        ServletOutputStream servletOutputStream = null;
        try {
            ClassPathResource classPathResource = new ClassPathResource(FILE_PATH + filename);
            response.setContentType("application/vnd.ms-excel");
            response.addHeader("Cache-Control", "no-cache, no-store, must-revalidate");
            response.addHeader("charset", "utf-8");
            response.addHeader("Pragma", "no-cache");
            String encodeName = URLEncoder.encode(filename, StandardCharsets.UTF_8.toString());
            response.setHeader("Content-Disposition", "attachment; filename="" + encodeName + ""; filename*=utf-8''" + encodeName);
            inputStream = classPathResource.getInputStream();
            servletOutputStream = response.getOutputStream();
            IOUtils.copy(inputStream, servletOutputStream);
            response.flushBuffer();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (servletOutputStream != null) {
                    servletOutputStream.close();
                }
                if (inputStream != null) {
                    inputStream.close();
                }
                // 召唤jvm的垃圾回收器
                System.gc();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

Excel数据导入：

@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ApiModel("导入统一响应模型")
public class importResponse {

    @ApiModelProperty(value = "本次导入返回的流水号")
    private Long serialNo;

    @ApiModelProperty(value = "总共导入条数")
    private int importCount;

    @ApiModelProperty(value = "导入成功条数")
    private int successCount;

    @ApiModelProperty(value = "导入失败条数")
    private int failCount;

    @ApiModelProperty(value = "失败记录")
    private List failRecords;
}
     

public class Excelimport{
@Override
    public CommonResult> importData(MultipartFile file) {
        try {
            importParams params = new importParams();
            params.setNeedVerify(true);
            params.setimportFields(SimCardimport.MUST_FILL);
            ExcelimportResult result = ExcelimportUtil.importExcelMore(file.getInputStream(),
                    SimCardimport.class, params);
            List successList = result.getList();
            List failList = result.getFailList();
            int successCount = CollectionUtils.isEmpty(successList) ? 0 : successList.size();
            int failCount = CollectionUtils.isEmpty(failList) ? 0 : failList.size();
            if (successCount + failCount == 0) {
                throw new BssException(BssResultCode.MSG_import_NO_DATA);
            }
            if (successCount + failCount > Constants.import_MAX) {
                throw new BssException(BssResultCode.MSG_import_LIMIT_500_DATA);
            }
            //去除重复的记录id
            successList = successList.stream().collect(Collectors.collectingAndThen(
                    Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(SimCardimport::getIccid))), ArrayList::new));
            List dataList = checkimportData(successList, failList);
            if (dataList.size() != 0) {
                insertData(dataList);
            }
            successCount = dataList.size();
            if (dataList.size() != successList.size()) {
                result.setVerifyFail(true);
            }
            long time = System.currentTimeMillis();
            importResponse response = importResponse.builder()
                    .successCount(successCount)
                    .serialNo(time).build();
            if (result.isVerifyFail()) {
                response.setFailRecords(failList);
                response.setFailCount(failList.size());
                response.setimportCount(failList.size()+successCount);
                return CommonResult.failed(BssResultCode.import_FAIL, response);
            }
            return CommonResult.success(response);
        } catch (Exception e) {
            e.printStackTrace();
            log.error("sim卡导入失败！");
            throw new BssException(BssResultCode.INVALID_ARGUMENT);
        }
    }

    
    public void insertData(List successList) {
        for (SimCardimport d : successList) {
            SimCard simCard = new SimCard();
            BeanUtils.copyProperties(d, simCard);
            simCard.setCreateTime(System.currentTimeMillis());
            simCard.setUpdateTime(System.currentTimeMillis());
            simCard.setSource(BssDataSource.MANUAL_ENTRY.getCode());
            simCardMapper.insert(simCard);
        }
    }

    
    public List checkimportData(List successList,
                                               List failList) {
        List successData = new ArrayList<>();
        for (SimCardimport d : successList) {
            if (null != simCardMapper.selectByIccId(d.getIccid())) {
                d.setErrorMsg(BssResultCode.SIM_ICCID_EXISTED.getMsg());
                failList.add(d);
                continue;
            }
            if (null != simCardMapper.selectByImsi(d.getImsi())) {
                d.setErrorMsg(BssResultCode.SIM_IMSI_EXISTED.getMsg());
                failList.add(d);
                continue;
            }
            if (null != simCardMapper.selectByMsisdn(d.getMsisdn())) {
                d.setErrorMsg(BssResultCode.SIM_MSISDN_EXISTED.getMsg());
                failList.add(d);
                continue;
            }
            successData.add(d);
        }
        return successData;

    }
}