使用设计模式基于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...
	}
}

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

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

AsyncExcelTask
多线程调度任务类

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

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

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);
	}
}

通用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;
	}
}

分发器

根据前段传递的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;
	}


}

处理器

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();
}

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)};


}

业务逻辑处理service接口

IAsyncExcelService

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

策略模型

AsyncExcelPolicy 策略接口
规范策略实现

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

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. 配置对应的handler权限定名。
2. 实现handler类，根据自己的excel样式设置参数，用于解析excel数据。
3. 实现service服务接口，实现校验数据方法和导入方法