EasyExcel 实现模板导出、模板导入分析功能

0.POM依赖

 <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!-- junit -->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <!-- lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.0</version>
        </dependency>
        <!-- log4j-->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.25</version>
        </dependency>
        <!-- validation-->
        <dependency>
            <groupId>javax.validation</groupId>
            <artifactId>validation-api</artifactId>
        </dependency>
        <dependency>
            <groupId>org.hibernate.validator</groupId>
            <artifactId>hibernate-validator</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

<!--        easyExcel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.1.6</version>
        </dependency>
<!--        fastsjon-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.56</version>
        </dependency>

    </dependencies>

1.导出模板实现

导出模板 映射 ExcelBO:

@Data
@ToString
public class InvoiceTemplateBO {
    @NotEmpty(message = "，发票代码未填写")
    @ExcelProperty(value = "*发票代码")
    private String invoiceCode;
    @NotEmpty(message = "，发票号码未填写")
    @ExcelProperty(value = "*发票号码")
    private String invoiceNo;
    @NotNull(message = "，开票日期未填写")
    @ExcelProperty(value = "*开票日期\n" + "2000-00-00")
    @DateTimeFormat("yyyy-MM-dd")
    private Date invoiceDate;
    @NotEmpty(message = "，校验码未填写")
    @ExcelProperty(value = "*校验码")
    private String checkCode;

    public static String getTemplateHead(){
        return "[*发票代码, *发票号码, *开票日期\n" +
                "2000-00-00, *校验码]";
    }
}

控制层：

@RestController
public class ExcelController {

    // http://localhost:8989/invoiceTemplateDownload
    @GetMapping("/invoiceTemplateDownload")
    public void templateDownload(HttpServletResponse response) throws IOException {
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        String fileName = URLEncoder.encode("发票导入模板-普票s", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");

        ExcelWriterBuilder excelWriterBuilder = EasyExcel.write(response.getOutputStream(), InvoiceTemplateBO.class);
        excelWriterBuilder.registerWriteHandler(templateWriteHandler);
        excelWriterBuilder.registerWriteHandler(InvoiceTemplateColumnWidthStyleStrategy.getInstance());
        excelWriterBuilder.sheet().doWrite(new ArrayList());
    }

	private static WriteHandler templateWriteHandler;
	    static {
	        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
	        //字体
	        WriteFont headWriteFont = new WriteFont();
	        headWriteFont.setFontHeightInPoints((short) 11);
	        headWriteFont.setBold(false);
	        headWriteCellStyle.setWriteFont(headWriteFont);
	        //边框
	        headWriteCellStyle.setBorderBottom(BorderStyle.THIN);
	        headWriteCellStyle.setBorderLeft(BorderStyle.THIN);
	        headWriteCellStyle.setBorderRight(BorderStyle.THIN);
	        //前景色
	        headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
	        //是否换行
	        headWriteCellStyle.setWrapped(true);
	        headWriteCellStyle.setLocked(true);
	        templateWriteHandler = new HorizontalCellStyleStrategy(headWriteCellStyle,new WriteCellStyle());
	    }

}

2.导入模板并分析实现

验证功能返回值BO：

@Data
@NoArgsConstructor
@AllArgsConstructor
public class InvoiceTemplateImportRspBO {
    //验证有效的发票信息
    private List<InvoiceTemplateBO> invoiceList;
    //验证无效的发票信息 提示
    private String invalidMsg;

}

控制层：

@RestController
public class ExcelController {

    @Autowired
    private ExcelImportService excelImportService;

    @PostMapping("/invoiceTemplateImport")
    public InvoiceTemplateImportRspBO invoiceTemplateImport(@RequestParam("file") MultipartFile file) throws IOException{
        return excelImportService.invoiceTemplateImport(file.getInputStream());
    }
    
}

Service：

public interface ExcelImportService {

    InvoiceTemplateImportRspBO invoiceTemplateImport(InputStream inputStream);

}

@Service
public class ExcelImportServiceImpl implements ExcelImportService {

    @Override
    public InvoiceTemplateImportRspBO invoiceTemplateImport(InputStream inputStream) {
        InvoiceTemplateAnalyseListener invoiceTemplateAnalyseListener = new InvoiceTemplateAnalyseListener();
        EasyExcel.read(inputStream, InvoiceTemplateBO.class,invoiceTemplateAnalyseListener)
                .sheet()
                .doRead();
        return invoiceTemplateAnalyseListener.getImportResult();
    }

}

Excel导入监听类：

@Slf4j
public class InvoiceTemplateAnalyseListener  extends AnalysisEventListener<InvoiceTemplateBO> {

    private  List<InvoiceTemplateBO> invoiceList = new ArrayList<>();

    private  StringBuilder validMsg = new StringBuilder();

    private StringBuilder invalidInvoiceMsg = new StringBuilder();

    private static String templateHead = InvoiceTemplateBO.getTemplateHead();

    private static String TEMPLATE_ERROR = "导入模板有误";

    public InvoiceTemplateImportRspBO getImportResult(){
        return new InvoiceTemplateImportRspBO(invoiceList,invalidInvoiceMsg.toString());
    }

    //校验模板表头
    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        if(4 != headMap.size()){
            throw new RuntimeException(TEMPLATE_ERROR);
        }
        String importHead = headMap.values().toString();
        log.info("templateHead:{}",importHead);
        if(!templateHead.equals(importHead)){
            throw new RuntimeException(TEMPLATE_ERROR);
        }
    }

    @Override
    public void invoke(InvoiceTemplateBO data, AnalysisContext context) {
        log.info("读取到行数据："+data);
        //校验参数。 通过:data放入invoiceList ；不通过:异常信息放入validMsg
        validInvoiceParam(data,context);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        //validMsg不为空抛出异常；为空 整理参数，查询发票是否有效
        if(0 == validMsg.length()){
            log.info("校验验证通过集合："+invoiceList);
            verifyInvoiceValidity(invoiceList);
        }else {
            invoiceList.clear();
            throw new RuntimeException(validMsg.toString());
        }
    }

    //校验参数。 通过:data放入invoiceList ；不通过:异常信息放入validMsg
    private void validInvoiceParam(InvoiceTemplateBO target,AnalysisContext context){
        ValidatorFactory factory = Validation.buildDefaultValidatorFactory();
        Validator validator = factory.getValidator();
        Set<ConstraintViolation<InvoiceTemplateBO>> constraintViolations = validator.validate(target, Default.class);
        Iterator<ConstraintViolation<InvoiceTemplateBO>> iterator = constraintViolations.iterator();
        StringBuilder constraintViolationMsg = new StringBuilder();
        while (iterator.hasNext()){
            ConstraintViolation<InvoiceTemplateBO> constraintViolationItem = iterator.next();
            constraintViolationMsg.append(constraintViolationItem.getMessage());
        }
        if(0 == constraintViolationMsg.length()){
            invoiceList.add(target);
        }else {
            validMsg.append("发票第").append(context.getCurrentRowNum()+1).append("行").append(constraintViolationMsg).append("。");
        }
    }

    //校验发票有效性。有效：放入invoiceList。无效：组合 invalidInvoiceMsg
    private void verifyInvoiceValidity(List<InvoiceTemplateBO> invoiceList){
        //todo Constructor注入校验service，调用方法，处理返回值
        if(true){
            //验证通过，
        }else {
            //验证不通过，invoiceList remove无效记录。invalidInvoiceMsg添加无效信息
        }
    }

}

3.git源码

https://github.com/lls19950330/easyExcelDemo.git