再用EasyExcel读取excel文件（包括时间类型）

1.导依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.1</version>
        </dependency>

2.实体类

@Data
public class CertificateExcelDTO {
    //    名称	能力等级	编号	发证日期	有效期	开发完成日期	发证公司	查询网址	备注	联系人	联系人电话

    @ExcelProperty(index = 1, value = "名称")
    private String certName;

    @ExcelProperty(index = 2, value = "能力等级")
    private String abilityLevel;

    @ExcelProperty(index = 3, value = "编号")
    private String certNum;

    @ExcelProperty(index = 4, value = "发证日期")
    @DateTimeFormat(value = "yyyy-MM-dd")
    private String issueDate;

    @ExcelProperty(index = 5, value = "有效期")
    @DateTimeFormat(value = "yyyy-MM-dd")
    private String effectiveDate;

    @ExcelProperty(index = 6, value = "开发完成日期")
    @DateTimeFormat(value = "yyyy-MM-dd")
    private String developCompletionDate;

    @ExcelProperty(index = 7, value = "发证公司")
    private String issueCompany;

    @ExcelProperty(index = 8, value = "查询网址")
    private String issueSearchUrl;

    @ExcelProperty(index = 9, value = "备注")
    private String comment;

    @ExcelProperty(index = 10, value = "联系人")
    private String contact;

    @ExcelProperty(index = 11, value = "联系人电话")
    private String contactPhone;
}

3.Read类

package com.dpzn.easyExcel;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.dpzn.entity.Certificate;
import com.dpzn.entity.CertificateExcelDTO;
import com.dpzn.service.CertificateService;
import com.dpzn.service.impl.CertificateServiceImpl;
import com.dpzn.util.CommonUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @program: easy_excel_demo
 * @description:
 **/
@Slf4j
public class EasyExcelRead extends AnalysisEventListener<CertificateExcelDTO> {

    private final List<CertificateExcelDTO> list = new ArrayList<>();

    /**
     * 从第二行开始一行一行的读excel表中的数据
     */
    @Override
    public void invoke(CertificateExcelDTO certificateExcelDTO, AnalysisContext analysisContext) {
        //将读取到的数据存储到集合中
        list.add(certificateExcelDTO);
        log.info("开始逐行读取数据。{}", certificateExcelDTO);
    }

    /**
     * 读表头。即excel中的第一行数据
     */
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        log.info("表头数据：{}", headMap);
    }

    /**
     * 读操作完成后执行的方法
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        saveData();
        log.info("===============读操作完成===============");
    }

    
    private void saveData() {
        log.info("开始保存数据到数据库。{}", list);
        //去除集合中证书名称为空的对象
        List<Certificate> certificateList = list.stream().filter(item -> item.getCertName() != null).map(item -> {
            Certificate certificate = new Certificate();
            certificate.setUploadPeopleName("admin");
            certificate.setUploadTime(new Date());
            BeanUtils.copyProperties(item, certificate);
            if (item.getIssueDate() != null) {
                certificate.setIssueDate(CommonUtil.strToDateYMD(item.getIssueDate()));
            }
            if (item.getEffectiveDate() != null) {
                certificate.setEffectiveDate(CommonUtil.strToDateYMD(item.getEffectiveDate()));
            }
            if (item.getDevelopCompletionDate() != null) {
                certificate.setDevelopCompletionDate(CommonUtil.strToDateYMD(item.getDevelopCompletionDate()));
            }
            return certificate;
        }).collect(Collectors.toList());
        //批量保存
        CertificateService certificateService = new CertificateServiceImpl();
        certificateService.saveOrUpdateBatch(certificateList);
        log.info("数据批量保存结束。【{}条】certificateList：{}", certificateList.size(), certificateList);
    }


}

4.调用

    @PostMapping("/exportExcel")
    public ResponseEntity<Object> excelExport(@RequestParam("file") MultipartFile file) throws IOException {
        //下面的new EasyExcelRead()对象建议使用new的方式，注入的方式由于是单例的，会在内存中保存上一次导入的excel数据
        EasyExcel.read(file.getInputStream(), CertificateExcelDTO.class, new EasyExcelRead()).sheet().doRead();
        return new ResponseEntity<>("excel文件导入成功。O(∩_∩)O~", HttpStatus.OK);
    }