异步导出Excel表格实例

逃离我推掉我的手 2021-11-17 19:06 618阅读 0赞

在最近的开发需求中用到了Excel导出的问题，由于导出操作比较频繁，避免系统压力过大，采用异步的方式进行导出，思路如下：  
将每个导出任务的导出方法、接口、查询条件、操作人等信息封装到ExcelProcessFlowEntity类中，运用定时任务和反射机制，排队执行导出任务。下面贴出相关代码，记录实现过程，也分享给大家。

1.实体ExcelProcessFlowEntity

@Table(name="Excel_process_flow",pkFiled="key")
    public class ExcelProcessFlowEntity{
    	private String key;
    	//创建时间
    	private String createTime;
    	//结束时间
    	private String endTime;
    	//状态（0：待处理，1：正在处理，2：处理完成）
    	private String state;
    	//导出的方法名
    	private String methodName;
    	//导出接口名
    	private Stirng managerName;
    	//附件名
    	private String fileName;
    	//查询条件
    	private Stirng queryString;
    	//导出人编号
    	private String code;
    	//导出量
    	private Stirng numSize;
    	/**
    	get()
    	set()
    	*/
    }

2.按状态查找导出任务，定义查找ExcelProcessFlowQo

public class ExcelProcessFlowQo extends PagingOrder{
    	private String state;
    	private String code;
    	private Date endTime;
    	/**
    		get();
    		set();
    	*/
    }

3.Controller层的导出接口  
将导出任务的相关参数传给manager层处理

@Autowired
    private ExcelProcessFlowManager  excelProcessFlowManager;
    
    @RequestMapping("/export")
    public String export(HttpSession session,AomCastQo qo){                 //这里的AomCastQo封装了需要导出内容的查询条件
    	try{
    		String managerName = “aomCastManager";
    		String methodName = "castExport";
    		LoginUser user = SessionUtils.getSessionUser(session);
    		String className = "客户明细表";
    		String fileName = className+"_"+user.getCode+"_"+System.currentTimeMillis()+".xls";    //导出文件名
    		String queryString = excelProcessFlowManager.createQuery(queryString,user.getCode,managerName,methodName,fileName);
    		return "";
    	}catch(Exception e){
    		e.printStackTrace();
    		return "";
    	}
    }

4.manager业务处理  
将导出任务的相关参数存入ExcellProcessFlowEntity，放入数据库，以备导出

@AutoWired
    private ExcellProcessFlowService excellProcessFlowService;
    public boolean createQuery(String queryString,String code,Stirng managerName,String methodName,Stirng fileName){
    	try{
    		ExcellProcessFlowEntity excelEntity = new ExcelProcessFlowEntity();
    		excelEntity.setCreateTime(new Date());
    		excelEntity.setEndTime(null);
    		excelEntity.setCode(code);
    		excelEntity.setManagerName(managerName);    //接口名
    		excelEntity.setMethodName(methodName);       //方法名，用于反射找到对应的导出方法
    		excelEntity.setState("0");
    		excelEntity.setFileName(fileName);
    		excelEntity.setQueryString(queryString);
    		excellProcessFlowService.create(excelEntity);      //存入导出任务表
    	}catch(){
    		e.printStackTrance();
    		return false;
    	}
    }

5.定时查询ExcellProcessFlowEntity，并利用反射，执行导出操作

@Autowired
    private ExcelProcessFlowManager excelProcessFlowManager;
    @Autowired
    private AomCastManager aomCastManaer;
    
    @Async("deployStartExecutor")
    @Scheduled(cron = "0/10 * * * * *")
    public void delayDeploy(){
    	ExcelProcessFlowQo excelProcessFlowQo = new ExcelProcessFlowQo();
    	excelProcessFlowQo.setState("0");
    	PageList<ExcelProcessFlowEntity> ExcelProcessFlowEntities = excelProcessFlowManager.queryPageList(excelProcessFlowQo );   //查询未导出的任务
    	if(ExcelProcessFlowEntities.size>0){
    		ExcelProcessFlowEntity excelEntity = ExcelProcessFlowEntities.get(0);    //获取队列中的第一个任务
    		try{
    			excelEntity.setState("1");          //正在处理
    			excelProcessFlowManager.update(excelEntity);
    			String managerName = excelEntity.getManagerName();
    			String methodName = excelEntity.getMethodName();
    			Stirng code = excelEntity.getCode();
    			String queryStirng = excelEntity.getQueryString();
    			String fileName = excelEntity.getFileName();
    			witchOne(managerName,methodName,queryString,code,fileName);             //根据条件，判断执行哪个导出任务
    			excelEntity.setEndTime(new Date());
    			excelEntity.setState("2");    //已完成
    			excelProcessFlowManager.update(excelEntity);
    		}catch(Exception e){     
    			e.printStackTrance();
    			excelEntity.setState("0")  //待处理
    			excelProcessFlowManager.update(excelEntity);
    		}
    	}
    }

下面是匹配方法：

public void witchOne(String managerName,String methodName,String queryString,String code,String fileName){
    	if("aomCastManager".equals(managerName) && "exportExcel".equals(methodName)){
    		aomCastManager.exportExcel(queryString,code,fileName);
    	}
    }

6.由于cron的执行机制是等待上次任务完成后才会开始下一次的任务，当上一次任务执行时间过长会导致整个调度阻塞，感受不到定时的效果，所以我这里加入了线程池配置deployStartExecutor，代码如下：

public class ExecutorConfig{
    	@Bean
    	public Executor deployStartExecutor(){
    		ThreadPoolTaskExecutor executor = new ThreadPoolTaskExecutor();
    		executor.setCorePoolSize(2);
    		executor.setMaxPoolSize(4);
    		executor.setQueueCapacity(10);
    		executor.setThreadNamePrefix("deploy-start-executor-");
    		executor.setRejectedExecutioHandler(new ThreadPoolExecutor.CallerRunsPolicy());
    		executor.initialize();
    		return executor;
    	}
    }

7.调用自己manager层的导出实现

@Autowired
    private AomCastService aomCastService;
    
    public void exportExcel(Stirng queryString,String code,String fileName){
    	try{
    		String[] queryStrings = queryString.split(",");
    		String castNo = queryStirngs[0];
    		String castName = queryStirng[1];
    		AomCastQo qo = new AomCastQo();
    		qo.setCastNo(castNo);
    		qo.setCastName(castName);
    		PageList<AomCastEntity> casts = aomCastService.queryPageList(qo);
    		String exportHead = "客户号#castNo,客户名#castName“;                               //excel的表头行
    		String[] header = exportHead.split(",");
    		Stirng className = "客户表”                                                 //生成的excel默认文件名和sheet页
    		HSSFWorkbook wb = ExcelUtils.export(className,header,casts);
    		String path = SystemParameterUtil.getValue("excelPath");
    		FileOutputStream fos = new FileOutputStream(path+fileName);
    		wb.write(fos);
    		fos.close();
    	}catch(Exception e){
    		e.printStackTrace();
    	}
    }

8.ExcelUtils.export导出excel方法

public static<T>HSSFWorkbook export(String fileName,String[] header,Collection<T> dataList) throws Exception{
    	HSSFWorkbook wb = new HSSFWorkbook();                                                //创建一个Workbook即一个excel文件
    	HSSFCellStyle titleStyle = wb.createCellStyle();                                            //设置标题样式
    	//设置单元格边框样式
    	titleStyle.setBoederTop(HSSFCellStyle.BORDER_THIN);                              //上边框细边线
    	titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);						  //下边框细边线
    	titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);							  //左边框细边线
    	titleStyle.setBoderRight(HSSFCellStyle.BORDER_THIN);							  //又边框细边线
    	//设置单元格样式
    	titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);							 //水平居中
    	titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);			 //垂直居中
    	//设置表头字体样式
    	Font titleFont = wb.createFont();
    	titleFont.setFontHightInPoints((short)15);													//字体高度
    	titleFont.setFontName("黑体");																	//字体样式
    	titleStyle.setFont(titleFont);
    	//在Workbook中添加一个sheet
    	HSSFSheet sheet = wb.createSheet(fileName);
    	//标题数组
    	String[] titleArray = new String[header.length];
    	//字段名数组
    	String fieldArray = new String[header.length];
    	for(int i=0;i<header.length;i++){
    		Stirng[] tempArray = header[i].split("#");                         							//临时数组，将表头和方法名按”#“分割
    		titleArray[i] = tempArray[0];
    		fieldArray[i] = tempArray[1];
    	}
    	//在sheet中添加标题行
    	HSSFRow row = sheet.createRow(0);              											//行数从0开始
    	HSSFCell sequenceCell = row.createCell(0);												//cell列，从0开始，第一列添加序号
    	sequenceCell.setCellValue("序号");
    	sequenceCell.setCellStyle(titleStyle);
    	sheet.autoSizeColum(0);																				//自动设置宽度
    	//为标题行赋值
    	for(int i=0;i<titleArray.length;i++){
    		HSSFCell titleCell = row.createCell(i+1);													//0号位被占用，所以需要+1
    		titleCell.setCellValue(titleArray[i]);
    		titleCell.setCellStyle(titleStyle);
    		sheet.autoSizeColum(i+1);
    	}
    	//数据样式
    	HSSFCellStyle dataStyle = wb.createCellStyle();
    	dataStyle.setBoederTop(HSSFCellStyle.BORDER_THIN);
    	dataStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    	dataStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    	dataStyle.setBoderRight(HSSFCellStyle.BORDER_THIN);
    	//设置居中样式
    	dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    	dataStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    	//设置数据字体
    	Font dataFont= wb.createFont();
    	dataStyle.setFontHightInPoints((short)12);
    	dataStyle.setFontName("宋体");
    	dataStyle.setFont(dataFont);
    	//遍历集合数据，产生数据行
    	Iterator<T> it = dataList.iterator();
    	int index = 0;																									
    	while(it.hasNext()){
    		index++;																								//0号位被占需+1
    		row = sheet.createRow(index);
    		//为序号列赋值
    		HSSFCell seqCellValue = row.createCell(0);										//序号值永远在第0列
    		seqCellValue.setCellValues(index);
    		seqCellValue.setCellStyle(dataStyle);
    		sheet.autoSizeColum(0);
    		T t = it.next();
    		//利用反射，根据传过来的字段名数组，动态调用对应的getXx()方法获取值
    		for(int i=0;i<fieldArray.length;i++){
    			HSSFCell dataCell = row.createCell(i+1);
    			dataCell.setCellStyle(dataStyle);
    			sheet.autoSizeColum(i+1);
    			String fieldName = fieldArray[i];
    			Stirng getMethodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.subStirng(1);     //获取对应的getXx()方法
    			Class<? extends Object> tCls.getMethod(getMethodName,new Class[]{});											//泛型为Object以及所有Object的子类
    			Object value = getMethod.invoke(t,new Object[]{});																			//调用get方法获取值
    			if(value!=null){
    				dataCell.setCellValue(value.toSting());																							//为当前单元格赋值
    			}
    		}
    		return wb;
    	}
    }

以上是项目中遇到的异步导出的解决方法，记录一下方便以后再用  
（持久层用的Hibernate,相关查询方法是经过封装的，读者可自行替换，这边提供下思路，分享给大家）