• 使用设计模式基于easypoi优雅的设计通用excel导入功能

    文章目录

    概要

    基于java原生 + easypoi结合适配器模式、策略模式、工厂模式设计一个通用的excel导入框架

    整体架构流程

    在这里插入图片描述

    代码设计

    由上到下，分别讲解代码

    配置类

    ExcelConfigEnum
    该配置类是声明导入业务类型，导入参数与Handler之间的实例化关系。

    @Getter
public enum ExcelConfigEnum {
    // 测试配置
    TEST("test", "测试", "com.xxx.TestExcelHandler"),
    ;
    
    private String type;
    private String desc;
    private String importClazz;

	ExcelConfigEnum (String type, String desc, String importClazz) {
		// ... 全参构造函数
	}

	// 根据type获取enum
	public static ExcelConfigEnum getByType(String type) {
		// codes...
	}
}

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    ExcelPolicyConfiguration
    该注解类用于配置handler对应的策略

    @Documented
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.TYPE)
public @interface AsyncExcelPolicyConfiguration
/**
* 导入策略
*/
Class<?extends AsyncExcelPolicy>policy();
/**
* 分片大小
*/
int shardingNum()default 1000;
}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15

    AsyncExcelTask
    多线程调度任务类

    
@Sf4j
public class AsyncExcelTask implements Runnable{
	// 处理器
	private AsyncExcelHandler handler;

	AsyncExcelTask(AsyncExcelHandler handler){
		this.handler=handler;
	}
	@Override
	public void run(){handler.importExcel();}
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    Factory
    handler工厂，用于实例化handler

    public class ExcelHandlerFactory {

	public static AsyncExcelHandler getInstance(String type,InputStream in,Object param,String importId,String userId)
					throws NoSuchMethodException,IllegalAccessException,InvocationTargetException,InstantiationException,ClassNotFoundException {
	
		AsyncExcelConfigEnum moduleEnum = AsyncExcelConfigEnum.getByType(type);
		Class clz = Class.forName(moduleEnum.getImportclazz());
		if (Objects.isNuLl(clz)){
			return null;
		}
		AsyncExcelPolicyConfiguration annotation = (AsyncExcelPolicyConfiguration)clz.getAnnotation(AsyncExcelPolicyConfiguration.class);
		if (Objects.isNuLL(annotation)){
			log.error("缺失导入策略注解")；
			throw new ServiceException((500,"缺失导入策略注解")；
		}
		Class<?extends AsyncExcelPolicy>policyClass = annotation.policy();
		int shardingNum = annotation.shardingNum();
		AsyncExcelPolicy policy = policyClass.newInstance();
		Constructor<?extends AbstractAsyncExcelHandler> constructor =
		clz.getDeclaredConstructor(InputStream.class,Object.class,String.class,String.class,AsyncExcelPolicy.class,int.class);
		return constructor.newInstance(in,param,importId,userId,policy,shardingNum);
	}
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24

    通用API

    @Api(va1ue="异形exce1守人守出"，tags={"异步exce1手人子田")
@RestController
@RequestMapping("/common/asyncExcel")
public class AsyncExcelController {
	@Autowired
	private AuthUtils authUtils;
	
	private static final String MODULE_AME="异步exce1导入导出";
	
	@OperLogOption(module MODULE_NAME,oper OperLogOption.Oper.IMPORT,desc ="excel")
	@PostMapping("/import")
	public Boolean asyncImportExcel(ExcelImportParams excelImportParams){
	
		AsyncExcelDispatcher.asyncDispatch(excelImportParams.getMultipartFiles[0],excelImportParams.getType(),
											excelImportParams.getObject(),authUtils.currentUserId());
		return true;
	}
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    分发器

    根据前段传递的type通过factory构建handler

    @s1f4j
public class AsyncExcelDispatcher {

public static void asyncDispatch(MultipartFile file,String type,Object param,String userId){
	//构造异步导入excel记录对象
	UserAsyncExcel userAsyncExcel buildAsyncExceLEntity(file,userId);
	try {
		AsyncExcelConfigEnum excelEnum AsyncExcelConfigEnum.getByType(type);
		// 检查导入配置是否存在
		if (Objects.isNuLL(excelEnum) || StringUtils.isBLank(excelEnum.getImportClazz())){
			throw new ServiceException(BizExceptionCommonEnum.INTERFACE_NOT_EXSIT_ERROR);
		}
		// 保存导入信息，状态执行中
		IUserFeignService userFeignService Springutils.getBean(IUserFeignService.class);
		userFeignService.save(userAsyncExcel);
		//获取handLer实例
		InputStream in = file.getInputStream();
		AsyncExcelHandler handler = ExcelHandlerFactory.getInstance(type,in,param,userAsyncExcel.getId(),userId);
		ExecutorService executor ThreadPoolFactory.ThreadPoolEnum.IMPORT_EXCEL.getThreadPool();
		//异步提交任务
		AsyncExcelTask asyncExcelTask = new AsyncExcelTask(handler);
		executor.submit(asyncExcelTask);
	catch (IOException | InstantiationException | InvocationTargetException | NoSuchMethodException |	IllegalAccessException | ClassNotFoundException ex){
		log.error("获取实例失败"，ex);
		//写入状态
		IUserFeignService userFeignService SpringUtils.getBean(IUserFeignService.class);
		userAsyncExcel.setStatus(AsyncExcelStatus.EXCEPTION);
		userAsyncExcel.setMessage(ex.getMessage());
		userFeignService.update(userAsyncExcel);
		throw new ServiceException(BizExceptionCommonEnum.INTERFACE_NOT_EXSIT_ERROR);
	}

	private static UserAsyncExcel buildAsyncExcelEntity(MultipartFile file,String userId){
		UserAsyncExceluserAsyncExcel new UserAsyncExcel();
		String id UuidUtils.getId();
		userAsyncExcel.setCreateTime(Timestamp.valueof(LocalDateTime.now(Clock.systemDefaultZone())));
		userAsyncExcel.setCreateUserId(userId);
		userAsyncExcel.setstatus(AsyncExcelStatus.EXECUTING);
		userAsyncExcel.setTitle(file.getoriginalFilename());
		userAsyncExcel.setType("I");
		userAsyncExcel.setId(id)j
		return userAsyncExcel;
	}


}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47

    处理器

    IAsyncExcelHandler
    handler接口， 规范编码

    public interface AsyncExcelHandler<T extends ExcelImportBaseEntity> {
	// 导入excel
	void importExcel();
	// 重置导入参数
	void resetImportParams();
	// 设置数据处理器
	void setDataHandler(IExcelDataHandler dataHandler);

	// 设置字典处理器
	void setDictHandler(IExcelDictHandler dictHandler);
	
	// 是否需要字段校验
	void needverify(boolean needverify);

	// 获取业务服务对象
	IAsyncExcelService getservice();
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18

    AbstractAsyncExcelHandler
    处理器抽象实现类

    public abstract class AbstractAsyncExcelHandler<T extends ExcelImportBaseEntity> implements AsyncExcelHandler{
	/*
	*文件流
	*/
	protected InputStream in;
	/**
	*导入参数
	*/
	protected Object object;
	/*
	*导入id
	*/
	protected String importId;
	/*
	*导入用户
	*/
	protected String userId;
	/*
	*策略
	*/
	protected AsyncExcelPolicy policy;
	/*
	* 分片大小
	*/
	protected int shardingNum;
	/
	*
	* 导入设置参数
	*/
	protected ImportParams importParams new ImportParams();
	/*
	 *文件服务
	 */
	protected IFileFeignService fileFeignService=SpringUtils.getBean(IFileFeignService.class);
	/*
	* user服务
	*/
	protected IUserFeignService userFeignService SpringUtils.getBean(IUserFeignService .class);
	/*
	 * 构造函数
	*/
	public AbstractAsyncExcelHandler(InputStream in,Object object,String importId,String userId,AsyncExcelPolicy policy,int shardingNum){
		this.in = in;
		this.object = object;
		this.importId = importId;
		this.userId = userId;
		this.policypolicy = policy;
		this.shardingNum = shardingNum;
	}
	@Override
	public void importExcel(){
		//1导入之前设置参效
		resetImportParams();
		setDataHandler(new DefaultDataHandler());
		setDictHandler(new DefaultExcelDictHandler());
		needverify(true);
		
		ParameterizedType parameterizedType = (ParameterizedType) this.getclass().getGenericSuperclass();
		Class clazz = (Class) parameterizedType.getActualTypeArguments()[0];
		ExcelImportResult<T> result;
		ByteArrayOutputStream bos = new ByteArrayOutputStream();
		ByteArrayInputStream bis = null;
		try {
			//复制一份流用于输出校验结果
			IOUtils.copy(in,bos);
			bis = new ByteArrayInputStream(bos.toByteArray());
			result = ExcelImportUtil.importExceLMore(bis,clazz,importParams);
		}
		catch (Exception ex) {
			IOUtils.closeQuietly(this.in);
			IOUtils.cLoseQuietly(bos);
			IOUtils.closeQuietLy(bis);
			log,error("excel解析失败“，ex);
			// 更新异常状态
			updateMessage4Exception(ex.getMessage());
			throw new ServiceException(BizExceptionCommonEnum.POI_ERROR)
		}
		
		//导入后处理
		//注解校验结哭
		if (result.isverifyFail()) {
			// 校验结果处理
			postverifyFailed(result,bos,bis);
			return;
		}
		// 校验是否实现service
		IAsyncExcelService service = getService();
		if (Objects.isNuLL(service)){
			Log.error("业务服务未返回service对象");
			//更新导入异常状态
			updateMessage4Exception("业务服务未实现");
			throw new ServiceException(500,"业务服务未实现");
		}
		// 业务校验
		service.verify(result);
		if (result.isVerifyFail()){
			//校验结果处理
			postverifyFailed(result,bos,bis);
			return;
		}
		//业务逻辑
		runPolicy(result.getList());
	}
	
	// 策略运行
	protected void runPolicy(List<T> list){
		try {
			policy.runPolicy(list,shardingNum,getService(),importId);
		} catch (Exception ex){
			//标识异常
			Log.error("业务数据插入异常。"，e×);
			UserAsyncExcel userAsyncExcel new UserAsyncExcel();
			userAsyncExcel.setId(importId);
			userAsyncExcel.setstatus(AsyncExcelStatus.EXCEPTION);
			userAsyncExcel.setMessage("分片号入发生异常");
			userFeignService.update(userAsvncExcel):
		}
	}
	// 校验失败后置处理
	private void postVerifyFailed(ExcelImportResult<T> result,ByteArrayOutputstream bos,ByteArrayInputstream bis){
		try {
			List<T> failList = result.getFailList();
			bis.reset();
			Workbook workbook = WorkbookFactory.create(bis);
			Sheet sheet = workbook.getsheetAt(index:0);
			int titleRowNum = importParams.getTitleRows(>+importParams.getHeadRows();
			short lastCellNum = sheet.getRow(titleRowNum).getLastCellNum();
			CellStyle errorCellStyle = createCellStyle(workbook);
			for (T failEntity failList){
				Integer rowNum failEntity.getRowNum();
				Rowrow sheet.getRow(rowNum)j
				Cell cell row.createCell(lastCellNum);
				cell.setCellValue(failEntity.getErrorMsg());
				cell.setCellStyle(errorCellStyle);
			}
			
			workbook.getsheetAt(0).setColumnwidth(lastCellNum, 5000);
			UserAsyncExcel userAsyncExcel = userFeignService.getById(importId).getData();
			
			// 流清空，重用
			bos.flush();
			bos.reset();
			workbook.write(bos);
			Map<String,String> errFileMap = uploadVerifyFailedExcel(bos,userAsyncExcel.getTitle());
			// 更新导入结果为校验失败
			String fileId = String.join(StringPool.COMMA,errFileMap.keySet());
			userAsyncExcel.setstatus(AsyncExcelStatus.VERIFY_FATLED);
			userAsyncExcel.setResFileId(fileId);
			userFeignService.update(userAsyncExcel);
			}
		catch (IOException ex) {
			IOUtils.closeQuietly(bos);
			IOUtils.cLoseQuietly(bis);
			Log.error("设置校验结果异常"，ex);
			/更新导入异常状态
			updateMessage4Exception(ex.getMessage());
			throw new ServiceException(5OB,"业务校验失败")；
		} finally {
			Log.info("原始流复件关闭");
			IOUtils.cLoseQuietly(bos);
			IOUtils.cLoseQuietly(bis);
		}
	}

	private Map<String,String>uploadVerifyFailedExcel(ByteArrayOutputStream erroros, String fileName){
		long time System.currentTimeMiLLis();
		int idx fileName.lastIndexof(StringPool.DOT);
		String pre fileName.substring(0,idx);
		String suffer fileName.substring(idx);
		fileName pre time suffer;
		MultipartFile[]multipartFiles new MultipartFile[]{new MockMultipartFile(Constants.ExcelConstants.UPLOAD_FILE_FIELD,
		fileName,MediaType.MUL TIPART_FOR_DATA_VALUE,erroros.toByteArray())};
		return fileFeignService.uploadTemp(multipartFiles).getData();
	}
	
	protected Cellstyle createCellStyle(Workbook workbook){
		CellStyle errorCellStyle workbook.createCellstyle();
		Font font workbook.createFont();
		font.setColor(Font.COLOR_RED);
		errorCellStyle.setFont(font);
		// 背景色
		// 设置背景色填充方式为实线填充
		// errorCeLlStyle.setFiLLPattern(FillPatternType.SOLID_FOREGROUND);
		// errorceLlStyle.setFiLLForegroundcolor(IndexedColors.RED.getIndex());
		errorCellstyle.setWrapText(true);
		return errorCellStyle;
	}
	
	private void updateMessage4Exception(String message){
		UserAsyncExcel userAsyncExcel = new UserAsyncExcel();
		userAsyncExcel.setId(importId);
		userAsyncExcel.setStatus(AsyncExcelStatus.EXCEPTION);
		userAsyncExcel.setMessage(message);
		userFeignService.update(userAsyncExcel);
	}
@Override
public void resetImportParams(){
	//设置标题行数
	this.importParams.setTitleRows(1);
	//设置表头行数
	this.importParams.setHeadRows(2);
}

	@Override
	public void setDataHandler(IExcelDataHandler dataHandler){this.importParams.setDataHandler(dataHandler)};
	@Override
	public void setDictHandler(IExcelDictHandler dictHandler){this.importParams.setDictHandler(dictHandler)};
	@Override
	public void needverify(boolean needVerify){this.importParams.setNeedVerify(needVerify)};


}



    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36
    • 37
    • 38
    • 39
    • 40
    • 41
    • 42
    • 43
    • 44
    • 45
    • 46
    • 47
    • 48
    • 49
    • 50
    • 51
    • 52
    • 53
    • 54
    • 55
    • 56
    • 57
    • 58
    • 59
    • 60
    • 61
    • 62
    • 63
    • 64
    • 65
    • 66
    • 67
    • 68
    • 69
    • 70
    • 71
    • 72
    • 73
    • 74
    • 75
    • 76
    • 77
    • 78
    • 79
    • 80
    • 81
    • 82
    • 83
    • 84
    • 85
    • 86
    • 87
    • 88
    • 89
    • 90
    • 91
    • 92
    • 93
    • 94
    • 95
    • 96
    • 97
    • 98
    • 99
    • 100
    • 101
    • 102
    • 103
    • 104
    • 105
    • 106
    • 107
    • 108
    • 109
    • 110
    • 111
    • 112
    • 113
    • 114
    • 115
    • 116
    • 117
    • 118
    • 119
    • 120
    • 121
    • 122
    • 123
    • 124
    • 125
    • 126
    • 127
    • 128
    • 129
    • 130
    • 131
    • 132
    • 133
    • 134
    • 135
    • 136
    • 137
    • 138
    • 139
    • 140
    • 141
    • 142
    • 143
    • 144
    • 145
    • 146
    • 147
    • 148
    • 149
    • 150
    • 151
    • 152
    • 153
    • 154
    • 155
    • 156
    • 157
    • 158
    • 159
    • 160
    • 161
    • 162
    • 163
    • 164
    • 165
    • 166
    • 167
    • 168
    • 169
    • 170
    • 171
    • 172
    • 173
    • 174
    • 175
    • 176
    • 177
    • 178
    • 179
    • 180
    • 181
    • 182
    • 183
    • 184
    • 185
    • 186
    • 187
    • 188
    • 189
    • 190
    • 191
    • 192
    • 193
    • 194
    • 195
    • 196
    • 197
    • 198
    • 199
    • 200
    • 201
    • 202
    • 203
    • 204
    • 205
    • 206
    • 207
    • 208
    • 209
    • 210
    • 211
    • 212
    • 213
    • 214

    业务逻辑处理service接口

    IAsyncExcelService

    public interface IAsyncExcelService<T extends ExcelImportBaseEntity>{
	/*
	* 业务校验
	* @param result
	*/
	void verify(ExcelImportResult<T> result);
	/**
	* 处理导入数据
	*
	*/
	void dealResultData(List<T> list);
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

    策略模型

    AsyncExcelPolicy 策略接口
    规范策略实现

    public interface IAsyncExcelService<T extends ExcelImportBaseEntity> {
	/**
	* 业务校验
	*/
	void verify(ExcelImportResult<T> result);
	/**
	* 处理导入数据
	*/
	void dealResultData(List<T> list);
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    ShardingPolicy分片策略

    Public class phardangPolicy implements AsyncExcelPolicy{

	@Override
	publie void runPolicy(List list,int shardingNun,IAsyncExcelService service,String impertId){
		//数据分片
		NongoTerplate mongoTeplate = Springutils.getBean(NangoTerplate.class);
		List<List> splitList = Listutil.splitlist(shardinghun,list);
		List<AsyncExcelShardingEntity> nongoEntityList = splitList.streas().map(v->{
			AsyncExcelShardingEntity entity new AsyncExcelshardingEntity(
			entity.setImportId(impertId);
			entity.setshardingId(Uuidutils.getId());
			entity.setSucceeded(falae);
			entity.setData(v);
			return entity;
		}).collect(Collectors.tolist());
		// 分片数据存入mongo
		mongoTerplate.insertAll(mongoEntityList);
		Log.info("分片数据：{}", ongoEntitylist);
		//分片进入业务导入方法
		for (AsyncExcelShardingEntity entity mangofntitylist){
			List subList = (List) entity.gotData();
			service.dealResultData(subList);
			// 执行成功成功删除数据
			mangoTenplate.remove(entity);
		}
	}
}


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    小结

    业务服务使用只需要三步

    1. 配置对应的handler权限定名。
    2. 实现handler类，根据自己的excel样式设置参数，用于解析excel数据。
    3. 实现service服务接口，实现校验数据方法和导入方法
  • 相关阅读:
    看得见风和日丽，看不见风起“云”涌
    如何在 PyTorch 中冻结模型权重以进行迁移学习：分步教程
    为AI电脑生态注入强悍动力，安耐美PlatiGemini 1200W高性能电源
    JDK线程池的总结
    这个时代，让我们一起格局打开！【2022戴尔科技峰会预告】
    BioVendor人俱乐部细胞蛋白(CC16)Elisa试剂盒研究领域
    LeetCode：88. 合并两个有序数组
    关于波兰式、逆波兰式
    接口测试学习
    【Qt】QGroundControl入门2：下载、编译、错误处理、运行
  • 原文地址：https://blog.csdn.net/zps925458125/article/details/134072972