文章目录
0.POM依赖1.导出模板实现2.导入模板并分析实现3.git源码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@ToStringpublic 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, *校验码]";}}
控制层：
@RestControllerpublic 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@AllArgsConstructorpublic class InvoiceTemplateImportRspBO {//验证有效的发票信息private List<InvoiceTemplateBO> invoiceList;//验证无效的发票信息 提示private String invalidMsg;}
控制层：
@RestControllerpublic class ExcelController {@Autowiredprivate 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);}
@Servicepublic class ExcelImportServiceImpl implements ExcelImportService {@Overridepublic InvoiceTemplateImportRspBO invoiceTemplateImport(InputStream inputStream) {InvoiceTemplateAnalyseListener invoiceTemplateAnalyseListener = new InvoiceTemplateAnalyseListener();EasyExcel.read(inputStream, InvoiceTemplateBO.class,invoiceTemplateAnalyseListener).sheet().doRead();return invoiceTemplateAnalyseListener.getImportResult();}}
Excel导入监听类：
@Slf4jpublic 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());}//校验模板表头@Overridepublic 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);}}@Overridepublic void invoke(InvoiceTemplateBO data, AnalysisContext context) {log.info("读取到行数据："+data);//校验参数。 通过:data放入invoiceList ；不通过:异常信息放入validMsgvalidInvoiceParam(data,context);}@Overridepublic void doAfterAllAnalysed(AnalysisContext context) {//validMsg不为空抛出异常；为空 整理参数，查询发票是否有效if(0 == validMsg.length()){log.info("校验验证通过集合："+invoiceList);verifyInvoiceValidity(invoiceList);}else {invoiceList.clear();throw new RuntimeException(validMsg.toString());}}//校验参数。 通过:data放入invoiceList ；不通过:异常信息放入validMsgprivate 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。无效：组合 invalidInvoiceMsgprivate void verifyInvoiceValidity(List<InvoiceTemplateBO> invoiceList){//todo Constructor注入校验service，调用方法，处理返回值if(true){//验证通过，}else {//验证不通过，invoiceList remove无效记录。invalidInvoiceMsg添加无效信息}}}
3.git源码
/lls19950330/easyExcelDemo.git
