文章目錄

• 1.環境準備
• 1.0. excel數據
• 1.1. pom
• 1.2. excle映射實體
• 1.3. 自定義日期轉換器
• • 1.4.自定義異常
• 2. 數據導出
• 3. 數據導入
• 3.1. excel解析監聽類
• 3.2. excel導入

1.環境準備

1.0. excel數據

1.1. pom

<dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-web</artifactId><version>2.1.4.RELEASE</version></dependency><!--slf4j日志--><dependency><groupId>org.slf4j</groupId><artifactId>slf4j-simple</artifactId><version>1.7.25</version><scope>compile</scope></dependency><!--lombok--><dependency><groupId>org.projectlombok</groupId><artifactId>lombok</artifactId><version>1.18.4</version></dependency><!--easyexcel--><dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.1.6</version></dependency><!--gson--><dependency><groupId>com.google.code.gson</groupId><artifactId>gson</artifactId><version>2.8.0</version></dependency>

1.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 Converter<LocalDateTime> {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 AnalysisEventListener<ExcelBO> {private Gson gson;//節點層級分組map (key:層級；value：該層級下的節點集合)private Map<Integer, List<TreeNodeBO>> 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);//最大層級List<TreeNodeBO> 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(Map<Integer, String> headMap, AnalysisContext context) {log.info("解析到一條頭數據:{}", gson.toJson(headMap));if(1==context.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();Set<ConstraintViolation<ExcelBO>> constraintViolations = validator.validate(target);Iterator<ConstraintViolation<ExcelBO>> iterator = constraintViolations.iterator();while (iterator.hasNext()) {ConstraintViolation<ExcelBO> 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;}List<TreeNodeBO> currentHierarchy = hierarchyMap.get(hierarchy);//當前層級List<TreeNodeBO> parentHierarchy = hierarchyMap.get(--hierarchy);//當前層級的 父級層級if (CollectionUtils.isNotEmpty(currentHierarchy)) {parentHierarchy.stream().forEach(parent -> {//當前父節點 的子節點集合List<TreeNodeBO> 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詳解

總結

以上是生活随笔為你收集整理的Springboot 下 EasyExcel 的数据导入导出的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。