easyexcel调用公共导出方法导出数据

r囧r小猫 2024-02-05 16:48 35阅读 0赞

easyexcel备忘

@Slf4j
    public class ConditionDownloadUtil {
        
    
    	//扫描在xboot 包下所有IService 接口的子类, 每次启动服务后, 重新扫描
    	public final static Class[] classesExtendsIService = ClassUtil.scanPackageBySuper("cn.exrick.xboot", IService.class).toArray(new Class[1]);
    
    	public static <T, E> void nonMultiQueryDownload(
    		Class<T> clazz,
    		QueryWrapper<T> queryWrapper,
    		String excelName,
    		Class<E> excelClazz,
    		HttpServletResponse response) {
        
    
    		if (ObjectUtils.isNotEmpty(clazz)) {
        
    
    			//1. 根据入参中的class 来获取对应的IService Bean
    			IService<T> targetIService = getTargetIService(clazz);
    
    			/*
    			 * 2. 由于查询/排序字段在每个模块中不同, 所以需要让调用方提供, 不再统一提供
    			 * 调用IService 的list() 方法获取查询符合条件的数据
    			 */
    			List<T> list = targetIService.list(queryWrapper);
    
    			if (list == null || list.size() == 0) {
        
    
    				throw ExceptionUtil.wrapRuntime("数据为空, 无法下载excel!");
    
    			}
    
    			//3. 将实体类List 转换为Excel 类List
    			List<E> excelList = list.stream().map(x -> BeanUtil.toBean(x, excelClazz, CopyOptions.create().setIgnoreError(true))).collect(Collectors.toList());
    
    			//4. 调用EasyExcel 通用方法输出Excel
    			afterMultiQueryDownload(excelName, excelList, excelClazz, new CustomMergeStrategy(excelClazz, excelList.size()), response);
    
    		} else {
        
    
    			throw ExceptionUtil.wrapRuntime("实体类不能为空!");
    
    		}
    
    	}
    
    	public static <E> void afterMultiQueryDownload(
    		String excelName,
    		List<E> excelList,
    		Class<E> excelClass,
    		WriteHandler writeHandler,
    		HttpServletResponse response) {
        
    
    		//1. 判断需要输入的Excel List 是否为空, 如果不为空, 则输入, 否则, 抛出异常
    		if (CollectionUtil.isNotEmpty(excelList)) {
        
    
    			response.setContentType("application/vnd.ms-excel");
    			response.setCharacterEncoding("utf-8");
    			response.setHeader("Content-disposition", "attachment;filename=" + URLUtil.encode(excelName, StandardCharsets.UTF_8)  + ".xlsx");
    
    			//2. 输入Excel
    			try {
        
    
    				ExcelWriterSheetBuilder excelWriterSheetBuilder = EasyExcel.write(response.getOutputStream())
    						.excelType(ExcelTypeEnum.XLSX)
    						.head(excelClass)
    						.sheet();
    
    				if (ObjectUtil.isNotEmpty(writeHandler)) {
        
    
    					excelWriterSheetBuilder.registerWriteHandler(writeHandler);
    
    				}
    
    				//3. 清除Convertor 中的线程变量
    				DictConverter.removeThreadLocal();
    
    				excelWriterSheetBuilder.doWrite(excelList);
    
    			} catch (Exception e) {
        
    
    				StringWriter sw = new StringWriter();
    				PrintWriter pw = new PrintWriter(sw);
    				e.printStackTrace(pw);
    
    				log.error(sw.toString());
    
    				if (e.getCause() instanceof ExcelDataConvertException) {
        
    
    					ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) e.getCause();
    					String cellMsg = "";
    					CellData cellData = excelDataConvertException.getCellData();
    					//这里有一个celldatatype的枚举值,用来判断CellData的数据类型
    					CellDataTypeEnum type = cellData.getType();
    					if (type.equals(CellDataTypeEnum.NUMBER)) {
        
    						cellMsg = cellData.getNumberValue().toString();
    					} else if (type.equals(CellDataTypeEnum.STRING)) {
        
    						cellMsg = cellData.getStringValue();
    					} else if (type.equals(CellDataTypeEnum.BOOLEAN)) {
        
    						cellMsg = cellData.getBooleanValue().toString();
    					}
    
    					String errorMsg = String.format("excel表格:第%s行,第%s列,数据值为:%s,该数据值不符合要求,请检验后重新导入!<span style=\"color:red\">请检查其他的记录是否有同类型的错误!</span>", excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex(), cellMsg);
    					log.error(errorMsg);
    
    				}
    
    			}
    
    		} else {
        
    
    			throw ExceptionUtil.wrapRuntime("数据为空, 无法下载excel!");
    
    		}
    
    	}
    
    	public <T, E> void dynamicHeadDownload(
    		List<List<String>> headList,
    		String excelName,
    		List data,
    		HttpServletResponse response) {
        
    
    		//1. 判断Head List 是否为空, 如果不为空, 则输入, 否则, 抛出异常
    		if (ObjectUtil.isNotEmpty(headList)) {
        
    
    			response.setContentType("application/vnd.ms-excel");
    			response.setCharacterEncoding("utf-8");
    			response.setHeader("Content-disposition", "attachment;filename=" + excelName + ".xlsx");
    
    			//2. 输入Excel
    			try {
        
    
    				EasyExcel.write(response.getOutputStream())
    					.head(headList)
    					.sheet()
    					.doWrite(data);
    
    			} catch (Exception e) {
        
    
    				StringWriter sw = new StringWriter();
    				PrintWriter pw = new PrintWriter(sw);
    				e.printStackTrace(pw);
    
    				log.error(sw.toString());
    
    				if (e.getCause() instanceof ExcelDataConvertException) {
        
    
    					ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) e.getCause();
    					String cellMsg = "";
    					CellData cellData = excelDataConvertException.getCellData();
    					//这里有一个celldatatype的枚举值,用来判断CellData的数据类型
    					CellDataTypeEnum type = cellData.getType();
    					if (type.equals(CellDataTypeEnum.NUMBER)) {
        
    						cellMsg = cellData.getNumberValue().toString();
    					} else if (type.equals(CellDataTypeEnum.STRING)) {
        
    						cellMsg = cellData.getStringValue();
    					} else if (type.equals(CellDataTypeEnum.BOOLEAN)) {
        
    						cellMsg = cellData.getBooleanValue().toString();
    					}
    
    					String errorMsg = String.format("excel表格:第%s行,第%s列,数据值为:%s,该数据值不符合要求,请检验后重新导入!<span style=\"color:red\">请检查其他的记录是否有同类型的错误!</span>", excelDataConvertException.getRowIndex() + 1, excelDataConvertException.getColumnIndex(), cellMsg);
    					log.error(errorMsg);
    
    				}
    
    			}
    
    		} else {
        
    
    			throw new RuntimeException("数据为空, 无法下载excel!");
    
    		}
    
    	}
    
    	/**
    	 * 添加表头
    	 * @param head
    	 * @param headers
    	 */
    	public static void addHead(String head, List<List<String>> headers) {
        
    
    		ArrayList<String> list = new ArrayList<>(1);
    
    		list.add(head);
    
    		headers.add(list);
    
    	}
    
    	/*
    	@SneakyThrows
    	public void tablesDownload(ReportDownloadModel reportDownloadModel, HttpServletResponse response) {
    
    		String excelName = URLEncoder.encode(reportDownloadModel.getExcelName(), "utf-8");
    		List<ReportTableModel> tableList = reportDownloadModel.getTables();
    
    		response.setContentType("application/vnd.ms-excel");
    		response.setCharacterEncoding("utf-8");
    		response.setHeader("Content-disposition", "attachment;filename=" + excelName + ".xlsx");
    
    		ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build();
    		WriteSheet writeSheet = EasyExcel.writerSheet()
    			.registerWriteHandler(new CustomCellWriteHandler())
    			.registerWriteHandler(new CustomCellWriteHeightConfig())
    			.needHead(Boolean.TRUE).build();
    
    		//1. 定义标题二维数组
    		ArrayList<List<String>> titleHead = new ArrayList<>();
    
    		//2. 定义最终输出的excel 二维数组
    		ArrayList<List<Object>> finalData = new ArrayList<>();
    
    		//3. 定义空行
    		ArrayList<Object> emptyLine = new ArrayList<>();
    		emptyLine.add("");
    
    		try {
    
    			for (int i = 0; i < tableList.size() * 2; i = i + 2) {
    
    				ReportTableModel reportTableModel = tableList.get(i / 2);
    
    				List<List<String>> heads = reportTableModel.getHeads();
    
    				List<List<Object>> data = reportTableModel.getData();
    
    				String title = reportTableModel.getTitle();
    
    				//3. 定义表名行
    				ArrayList<String> titleLine = new ArrayList<>();
    				titleLine.add(title);
    				titleHead.add(titleLine);
    				for (int j = 0; j < heads.size() - 1; j++) {
    					ArrayList<String> empty = new ArrayList<>();
    					empty.add("");
    					titleHead.add(empty);
    				}
    
    				WriteTable writeTableForTitle = EasyExcel.writerTable(i).needHead(Boolean.TRUE).head(titleHead).build();
    				excelWriter.write(null, writeSheet, writeTableForTitle);
    
    				//4. 在表数据后添加空行
    				finalData.addAll(data);
    				finalData.add(emptyLine);
    
    				WriteTable writeTableForData = EasyExcel.writerTable(i + 1).needHead(Boolean.TRUE).head(heads).build();
    				excelWriter.write(finalData, writeSheet, writeTableForData);
    
    				titleHead.clear();
    				finalData.clear();
    
    			}
    
    		} catch (Exception e) {
    
    			throw new RuntimeException("导出Excel 失败, 原因:" + e.getMessage());
    
    		} finally {
    
    			if (excelWriter != null) {
    
    				excelWriter.finish();
    
    			}
    
    		}
    
    	}*/
    
    	/**
    	 * 根据入参的类, 查找IService 中第一个泛型类型为入参类型的IService Bean
    	 * @param referenceType
    	 * @return
    	 */
    	private static <T> IService<T> getTargetIService(Class<T> referenceType) {
        
    
    		Class targetClass = Arrays.stream(classesExtendsIService)
    				.filter(classExtendsIService -> ClassUtil.getTypeArgument(classExtendsIService).equals(referenceType))
    				.findFirst()
    				.orElseThrow(() -> ExceptionUtil.wrapRuntime("没有对应的类!"));
    
    		return (IService<T>) SpringUtil.getBean(targetClass);
    
    	}
    
    }

public class CustomMergeStrategy implements RowWriteHandler {
        
        /**
         * 主键下标
         */
        private Integer pkIndex;
    
        /**
         * 需要合并的列的下标集合
         */
        private List<Integer> needMergeColumnIndex = new ArrayList<>();
    
        /**
         * DTO数据类型
         */
        private Class<?> elementType;
    
        private Integer total;
    
        public CustomMergeStrategy(Class<?> elementType, Integer total) {
        
            this.elementType = elementType;
            this.total = total;
        }
    
        @Override
        public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
        
            // 如果是标题,则直接返回
            if (isHead) {
        
                return;
            }
    
            // 获取当前sheet
            Sheet sheet = writeSheetHolder.getSheet();
    
            if (null == pkIndex) {
        
                this.lazyInit(writeSheetHolder);
            }
    
            // 判断是否需要和上一行进行合并
            // 不能和标题合并，只能数据行之间合并
            if (row.getRowNum() <= 1) {
        
                return;
            }
            // 获取上一行数据
            Row lastRow = sheet.getRow(row.getRowNum() - 1);
            // 将本行和上一行是同一类型的数据(通过主键字段进行判断)，则需要合并
            if (!lastRow.getCell(pkIndex).getStringCellValue().equalsIgnoreCase(row.getCell(pkIndex).getStringCellValue())
                    || row.getRowNum() == total) {
        
    
                int maxRow = sheet.getMergedRegions().stream()
                        .max(Comparator.comparingInt(CellRangeAddressBase::getLastRow))
                        .orElse(new CellRangeAddress(0, 0, 0, 0))
                        .getLastRow();
    
                needMergeColumnIndex.forEach(needMerIndex -> {
        
    
                    int rowStart = maxRow + 1;
                    int rowEnd = row.getRowNum() == total ? row.getRowNum() : row.getRowNum() - 1;
    
                    if (rowEnd > rowStart) {
        
    
                        sheet.addMergedRegionUnsafe(new CellRangeAddress(
                                rowStart,
                                rowEnd,
                                needMerIndex,
                                needMerIndex));
    
                    }
    
                });
    
            }
        }
    
        /**
         * 初始化主键下标和需要合并字段的下标
         */
        private void lazyInit(WriteSheetHolder writeSheetHolder) {
        
    
            // 获取当前sheet
            Sheet sheet = writeSheetHolder.getSheet();
    
            // 获取标题行
            Row titleRow = sheet.getRow(0);
            // 获取DTO的类型
            Class<?> eleType = this.elementType;
    
            // 获取DTO所有的属性
            Field[] fields = eleType.getDeclaredFields();
    
            // 遍历所有的字段，因为是基于DTO的字段来构建excel，所以字段数 >= excel的列数
            Arrays.stream(fields).forEach(field -> {
        
    
                // 获取@ExcelProperty注解，用于获取该字段对应在excel中的列的下标
                ExcelProperty easyExcelAnno = field.getAnnotation(ExcelProperty.class);
                // 为空,则表示该字段不需要导入到excel,直接处理下一个字段
                if (null == easyExcelAnno) {
        
                    return;
                }
                // 获取自定义的注解，用于合并单元格
                CustomMerge customMerge = field.getAnnotation(CustomMerge.class);
    
                // 没有@CustomMerge注解的默认不合并
                if (null == customMerge) {
        
                    return;
                }
    
                for (int index = 0; index < fields.length; index++) {
        
                    Cell theCell = titleRow.getCell(index);
                    // 当配置为不需要导出时，返回的为null，这里作一下判断，防止NPE
                    if (null == theCell) {
        
                        continue;
                    }
                    // 将字段和excel的表头匹配上
                    if (easyExcelAnno.value()[0].equalsIgnoreCase(theCell.getStringCellValue())) {
        
                        if (customMerge.isPk()) {
        
                            pkIndex = index;
                        }
    
                        if (customMerge.needMerge()) {
        
                            needMergeColumnIndex.add(index);
                        }
                    }
                }
    
            });
    
            // 没有指定主键，则异常
            if (null == this.pkIndex) {
        
                throw new IllegalStateException("使用@CustomMerge注解必须指定主键");
            }
    
        }
    
    
    }

@Data
    @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.CENTER, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
    public class RiskAnnualExcel implements Serializable {
        
    
        @ExcelProperty("风险编号")
        @CustomMerge(needMerge = true, isPk = true)
        @ColumnWidth(15)
        @NotNull
        private String number;
    
        @ExcelProperty(value = "风险类别", converter = DictConverter.class)
        @DictType("risk-type")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @NotNull
        private String type;
    
        @ExcelProperty("风险名称")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @NotNull
        private String name;
    
        @ExcelProperty("风险描述")
        @CustomMerge(needMerge = true)
        @ColumnWidth(30)
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
        @NotNull
        private String description;
    
        @ExcelProperty("可能造成的后果")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
        @NotNull
        private String consequence;
    
        @ExcelProperty(value = "风险等级", converter = DictConverter.class)
        @DictType("risk-level-for-company")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @NotNull
        private String level;
    
        @ExcelProperty(value = "风险性质", converter = DictConverter.class)
        @DictType("risk-character")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @NotNull
        private String character;
    
        @ExcelProperty(value = "风险主要防控措施")
        @ColumnWidth(80)
        @ContentStyle(horizontalAlignment = HorizontalAlignmentEnum.LEFT, verticalAlignment = VerticalAlignmentEnum.CENTER, wrapped = BooleanEnum.TRUE)
        @NotNull
        private String precaution;
    
        @ExcelProperty(value = "措施等级", converter = DictConverter.class)
        @DictType("risk-precaution-level")
        @ColumnWidth(15)
        @NotNull
        private String precautionLevel;
    
        @ExcelProperty("风险认领领导")
        @ColumnWidth(20)
        private String leaders;
    
        @ExcelProperty("管控部门")
        @ColumnWidth(20)
        @NotNull
        private String deptsResponsible;
    
        @ExcelProperty("风险涉及单位")
        @ColumnWidth(20)
        private String deptsInvolved;
    
        @ExcelProperty("措施类型")
        @ColumnWidth(15)
        @NotNull
        private String typeMeasures;
    
    
        @ExcelProperty("措施类别")
        @ColumnWidth(15)
        @NotNull
        private String categoryMeasures;
    
    
        @ExcelProperty("预警条件")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @NotNull
        private String warningConditions;
    
    
        @ExcelProperty("风险点")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @NotNull
        private String riskPoints;
    
        @ExcelProperty("应急措施")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        @NotNull
        private String emergencyMeasure;
    
    
        @ExcelProperty("备注")
        @CustomMerge(needMerge = true)
        @ColumnWidth(15)
        private String remark;
    
    }

调用公共导出方法导出数据

ConditionDownloadUtil.
    afterMultiQueryDownload(riskBankAnnual.getName(),
     excelList, RiskAnnualExcel.class, 
     new CustomMergeStrategy(RiskAnnualExcel.class,
      excelList.size()), httpServletResponse);