异步导出Excel表格实例

在最近的开发需求中用到了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,相关查询方法是经过封装的，读者可自行替换，这边提供下思路，分享给大家）