目前主流网站建设软件,房天下官方网站,网站备案 登陆,保健品网站可以做网站文章目录1.环境准备1.0. excel数据1.1. pom1.2. excle映射实体1.3. 自定义日期转换器1.4.自定义异常2. 数据导出3. 数据导入3.1. excel解析监听类3.2. excel导入1.环境准备
1.0. excel数据 1.1. pom dependencygroupIdorg.springframework.boot/groupIddependencygroupIdorg.springframework.boot/groupIdartifactIdspring-boot-starter-web/artifactIdversion2.1.4.RELEASE/version/dependency!--slf4j日志--dependencygroupIdorg.slf4j/groupIdartifactIdslf4j-simple/artifactIdversion1.7.25/versionscopecompile/scope/dependency!--lombok--dependencygroupIdorg.projectlombok/groupIdartifactIdlombok/artifactIdversion1.18.4/version/dependency!--easyexcel--dependencygroupIdcom.alibaba/groupIdartifactIdeasyexcel/artifactIdversion2.1.6/version/dependency!--gson--dependencygroupIdcom.google.code.gson/groupIdartifactIdgson/artifactIdversion2.8.0/version/dependency1.2. excle映射实体
Data
Builder
AllArgsConstructor
NoArgsConstructor
ContentRowHeight(15)//内容单元格高度
HeadRowHeight(15)//表头单元格高度
ColumnWidth(10)//单元格宽度
public class TreeNode {//编码ExcelProperty(index 0,value {编码})private String code;//名称ExcelProperty(index 1,value {名称})private String name;//单位ExcelProperty(index 2,value 单位)private String unit;//数量ExcelProperty(index 3,value 数量)private BigDecimal qty;//单价ExcelProperty(index 4,value 单价)private BigDecimal price;//金额ExcelProperty(index 5,value 金额)private BigDecimal amt;//备注ExcelProperty(index 6,value 备注)private String remake;//生产日期ExcelProperty(index 7,value 生产日期)private LocalDateTime dateInProduced;
}1.3. 自定义日期转换器
public class LocalDateTimeConverter implements ConverterLocalDateTime {private DateTimeFormatter timeFormatter DateTimeFormatter.ofPattern(yyyy-MM-dd HH:mm:ss);Override//java类型public Class supportJavaTypeKey() {return LocalDateTime.class;}Override//Excel内部数据类型public CellDataTypeEnum supportExcelTypeKey() {return CellDataTypeEnum.STRING;}Overridepublic LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return LocalDateTime.parse(cellData.getStringValue(), timeFormatter);}Overridepublic CellData convertToExcelData(LocalDateTime dateTime, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {return new CellData(dateTime.format(timeFormatter));}
}1.4.自定义异常
/*** excel行解析异常不中断后续行解析操作 br* author lls* version 1.0.0* date 2021/5/20*/
public class ExcelRowAnalysisException extends RuntimeException {public ExcelRowAnalysisException() {}public ExcelRowAnalysisException(String message) {super(message);}}2. 数据导出 PostMapping(/export)public void download(HttpServletResponse response) throws Exception {response.setContentType(application/vnd.ms-excel);response.setCharacterEncoding(UTF-8);response.setHeader(Content-disposition, attachment);EasyExcel.write(response.getOutputStream(), ExcelBO.class).registerConverter(new LocalDateTimeConverter()).sheet(sheet1).doWrite(getDataList());}3. 数据导入
3.1. excel解析监听类
/*** 解析监听类 br** author lls* version 1.0.0* date 2021/5/19*/
Slf4j
public class ExcelReadListener extends AnalysisEventListenerExcelBO {private Gson gson;//节点层级分组map (key:层级value该层级下的节点集合)private MapInteger, ListTreeNodeBO hierarchyMap;//异常信息private StringBuilder errorMsg;//bean验证实例private Validator validator;//最大层级private Integer maxHierarchy -1;//编码层级分隔符private String splitStr -;public ExcelReadListener() {this.gson new Gson();this.hierarchyMap new HashMap();this.errorMsg new StringBuilder();this.validator Validation.buildDefaultValidatorFactory().getValidator();}/*** 每行数据解析 动作 br** param data:* param context:* return void* see*/Overridepublic void invoke(ExcelBO data, AnalysisContext context) {context.readWorkbookHolder().getReadWorkbook().setAutoTrim(true);//读取进datalog.info(当前解析行数据data :{} , gson.toJson(data));validate(context.getCurrentRowNum(), data);//参数校验TreeNodeBO treeNodeBO new TreeNodeBO();BeanUtils.copyProperties(data, treeNodeBO);Integer hierarchy countHierarchy(treeNodeBO.getCode()); //层级结构 code中 splitStr 出现的次数treeNodeBO.setHierarchy(hierarchy);maxHierarchy Math.max(maxHierarchy, hierarchy);//最大层级ListTreeNodeBO nodeList Optional.ofNullable(hierarchyMap.get(hierarchy)).orElse(new ArrayList());nodeList.add(treeNodeBO);hierarchyMap.put(hierarchy, nodeList);}/*** 解析完成后动作 br** param context:* return void* see*/Overridepublic void doAfterAllAnalysed(AnalysisContext context) {if (0 ! errorMsg.length()) {//模板解析不通过不执行解析后动作throw new RuntimeException(errorMsg.toString());}log.info(解析到的所有数据集合excelBOList :{} , gson.toJson(hierarchyMap));initTreeNodeBO(maxHierarchy);//初始化树结构log.info(解析后的所有数据集合excelBOList :{} , gson.toJson(hierarchyMap));}/*** 每个sheet页的头行触发函数 br** param headMap:* param context:* return void* see*/Overridepublic void invokeHeadMap(MapInteger, String headMap, AnalysisContext context) {log.info(解析到一条头数据:{}, gson.toJson(headMap));if(1context.getCurrentRowNum()){//模板文档第二行为实际有效表头//校验excel模板的正确性抛出异常并中断后续invoke()解析//通过 ExcelBO.class 获取field域上ExcelProperty注解。与headMap对比Field[] excelBOFields ExcelBO.class.getDeclaredFields();if(excelBOFields.length!headMap.size()){throw new RuntimeException(导入文档 头数据格式错误);}for (Field excelBOField : excelBOFields) {ExcelProperty excelProperty excelBOField.getAnnotation(ExcelProperty.class);int index excelProperty.index();String[] values excelProperty.value();if(!headMap.get(index).equals(values[0])){throw new RuntimeException(导入文档 头数据格式错误);}}}}/*** 发生异常时触发函数 br** param exception:* param context:* return void* see*/Overridepublic void onException(Exception exception, AnalysisContext context) throws Exception {log.info(捕捉到一条异常:{}, exception);if (exception instanceof ExcelRowAnalysisException) {errorMsg.append(exception.getMessage());//某行解析错误只中断本行不中断后续行得解析} else {throw exception;}}/*** 参数校验 br** param rowNum:* param target:* return java.lang.String* see*/private void validate(Integer rowNum, ExcelBO target) {//本行数据校验StringBuilder rowErrorMsg new StringBuilder();SetConstraintViolationExcelBO constraintViolations validator.validate(target);IteratorConstraintViolationExcelBO iterator constraintViolations.iterator();while (iterator.hasNext()) {ConstraintViolationExcelBO error iterator.next();rowErrorMsg.append(第).append(rowNum).append(行:).append(error.getMessage()).append(;);}if (rowErrorMsg.length() ! 0) {throw new ExcelRowAnalysisException(rowErrorMsg.toString());//中断本行后续解析}}/*** 初始化父节点、金额、数量 br** param hierarchy: 最大层级* return void* see*/private void initTreeNodeBO(Integer hierarchy) {if (hierarchy 0) { //0层级无父级层级 、跳出递归return;}ListTreeNodeBO currentHierarchy hierarchyMap.get(hierarchy);//当前层级ListTreeNodeBO parentHierarchy hierarchyMap.get(--hierarchy);//当前层级的 父级层级if (CollectionUtils.isNotEmpty(currentHierarchy)) {parentHierarchy.stream().forEach(parent - {//当前父节点 的子节点集合ListTreeNodeBO childNode currentHierarchy.stream().filter(child - child.getCode().indexOf(parent.getCode()) 0).collect(Collectors.toList());childNode.stream().forEach(child - child.setParentCode(parent.getCode()));//子节点集合设置 parentCodeBigDecimal childAmount childNode.stream().map(TreeNodeBO::getAmount).reduce(BigDecimal.ZERO, BigDecimal::add);//子节点金额和BigDecimal childQty childNode.stream().map(TreeNodeBO::getQty).reduce(BigDecimal.ZERO, BigDecimal::add);//子节点数量和parent.setAmount(parent.getAmount().add(childAmount));parent.setQty(parent.getQty().add(childQty));});}initTreeNodeBO(hierarchy);}/*** 计算节点层级结构层级结构 分隔符出现的次数 br** param code: 编码* return java.lang.Integer* see*/private Integer countHierarchy(String code) {int before code.length();int after code.replace(splitStr, ).length();return before - after;}}3.2. excel导入 PostMapping(/import)public BaseRspBO upload(RequestParam(file) MultipartFile file) throws Exception {String filename file.getOriginalFilename();//文件名InputStream inputStream null;inputStream new ByteArrayInputStream(file.getBytes());//文件转输入流EasyExcel.read(inputStream, ExcelBO.class, build()).sheet(sheet1)//指定要读取的sheet页.headRowNumber(2)//指定行头数量.registerConverter(new LocalDateTimeConverter())//注入自定义转换器.doRead();return BaseRspBO.builder().code(HttpStatus.OK.toString()).msg(success).build();}private ReadListener build() {return new ExcelReadListener();}
参考链接 easyExcel官方文档 EasyExcel全面教程快速上手 converter详解
