java +js + poi 实现对Excel报表导出至浏览器下载的功能

java +js + poi 实现对Excel报表导出至浏览器下载的功能

jar依赖：

org.apache.poi:poi:3.17
org.apache.poi:poi-ooxml:3.17
org.apache.poi:poi-ooxml-schemas3.17

前端js代码：

$("#exportExcel").click(function(){
	var queryParams = {};
	var url = "aaa/bbb?param="+valus;
	var xhr;
	if(window.XMLHttpRequest){
		xhr = new XMLHttpRequest();
	}else if(window.ActiveXObject){
		try{
			xhr = new ActiveXObject('Msxml2.XMLHTTP');
		}catch(e){
			try{
				xhr = new ActiveXObject('Microsoft.XMLHTTP');
			}catch(e){
				return "";
			}
		}
	}else{
		return "";
	}
	xhr.open("POST",url,true);
	xhr.responseType = "arraybuffer";//解决导出Excel乱码的问题，不能写在open之前，否则不支持IE浏览器
	xhr.onload = function(){
		var blob = new Blob([this.response],{type:"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"});
		if(blob.size < 1){
			alert("报表导出失败！");
			return;
		}
		if(window.navigator.msSaveOrOpenBlob){//如果是IE浏览器
			window.navigator.msSaveBlob(blob,"IE报表.xlsx");
		}else{//其他浏览器
			var alink = document.createElement("a");
			alink.style.display = "none";
			alink.href = window.URL.createObjectURL(blob);
			alink.download = "其他浏览器报表.xlsx";
			document.body.appendChild(alink);
			alink.click();
			document.body.removeChild(alink);
			return;
		}
	};
	xhr.setRequestHeader("Access-Token",sessionStorage.getItem("token"));
	xhr.setRequestHeader("Content-Type","application/json");
	xhr.send(JSON.stringify(queryParams));
	alert("报表导出成功！");
})

后端Java代码：
controller：

@RequestMapping(value="/aaa/bbb",method = {RequestMethod.POST})
public void exportExcel(String param, HttpServletResponse response){
	service.exportExcel(param, response);
}

Service:

public void exportExcel(String param, HttpServletResponse response){
	//创建Excel对象
	HSSFWorkbook workbook = new HSSFWorkbook();
	//导出的数据
	List<Xxx> dataset = dao.getData(param);//Xxx:数据封装的实体类
	OutputStream output = null;
	try{
		//Excel导出的工具类
		ExportExcel<Xxx> ex = new ExportExcel<Xxx>();
		//导出的标题列
		String[] headers = {"工号","姓名","年龄","电话","邮箱",...};
		output = response.getOutputStream();//获取响应输出流
		response.reset();//清空缓存
		String fileName = "报表.xlsx";
		response.setHeader("Content-disposition","attachment:filename="+new String("报表.xlsx".getBytes(),"utf-8"));
		response.setContentType("appication/vnd.openxmlformats-officedocument.spreadsheetxml.sheet");
		response.setHeader("Access-Control-Allow-Origin","*");//有的项目可能会遇到跨域请求被拦截的问题，我就遇到了。
		workbook = ex.exportExcel(workbook,null,headers,dataset,fileName);
		try{
			//导出文件
			workbook.write(output);
		}catch(IOException e){
			e.printStackTrace();
		}
		output.close();
	}catch(FileNotFoundException e){
		e.printStackTrace();
	}catch(Exception e){
		e.printStackTrace();
	}
}

工具类ExportExcel.java

public class ExportExcel<T>{
	public HSSFWorkbook exportExcel(HSSFWorkbook workbook, String title, String[] headers, Collection<T> dataset, String fileName){
		//声明一个sheet
		HSSFSheet sheet = workbook,createSheet(fileName);
		//设置表格默认列宽度为15
		sheet.setDefaultColumnWidth(15);
		//生成单元格样式
		HSSFCellStyle style = workbook.createCellStyle();
		//设置样式
		style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
		style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style.setBorderBottom(BorderStyle.THIN);
		style.setBorderLeft(BorderStyle.THIN);
		style.setBorderRight(BorderStyle.THIN);
		style.setBorderTop(BorderStyle.THIN);
		style.setAlignment(HorizontalAlignment.CENTER);
		//字体
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short)12);
		font.setBold(true);
		//把字体设置到当前样式
		style.setFont(font);
		//另一个样式
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		style2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		style2.setBorderBottom(BorderStyle.THIN);
		style2.setBorderLeft(BorderStyle.THIN);
		style2.setBorderRight(BorderStyle.THIN);
		style2.setBorderTop(BorderStyle.THIN);
		style2.setAlignment(HorizontalAlignment.CENTER);
		style2.setVerticalAlignment(VerticalAlignment.CENTER);
		//生成另一个字体
		HSSFFont font2 = workbook.createFont();
		style2.setFont(font2);
		//声明一个画图的顶级管理器
		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		//定义注释的大小和位置
		HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,0,0,0,(short)4,2,5));
		//设置注释内容
		comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		//设置注释作者，当鼠标移动到单元格上是可以在状态栏看到该内容
		comment.setAuthor("Antonio");
		//产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for(int i=0; i<headers.length; i++){
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(style);
			HSSFRichTextString text = new HSSFRichTextString(headers[i]);
			cell.setCellValue(text);
		}
		//遍历集合数据，产生数据行
		Iterator<T> it = dataset.iterator();
		int index = 0;
		while(it.hasNext()){
			index ++;
			row = sheet.createRow(index);
			T t = (T) it.next();
			//利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
			Field[] fields = t.getClass().getDeclaredFields();
			for(int i=0; i<fields.length; i++){
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(style2);
				Field field = fields[i];
				String fieldName = field.getName();
				String getMethodName = "get"+fieldName.substring(0,1).toUpperCase()+fieldName.substring(1);
				try{
					Class tCls = t.getClass();
					Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
					Object value = getMethod.invoke(t,new Object[]{});
					//判断类型后进行强制类型转换
					String value = null;
					//其他数据类型都当做字符串简单处理
					textValue = value == null ? "" : value.toString();
					//如果不是图片数据类型，就利用正则表达式判断textValue是否全部由数字组成
					if(textValue != null && StringUtils.isNotEmpty(textValue)){
						Pattern p = Pattern.compile("`//d+(//.//d+)?$");
						Matcher matcher = p.matcher(textValue);
						if(matcher.matches()){
							//是数字当做double处理
							cell.setCellValue(Double.parseDouble(textValue));
						}else{
							HSSFRichTextString richString = new HSSFRichTextString(textValue);
							HSSFFont font3 = workbook.createFont();
							font3.setColor(HSSFColor.BLUE.index);
							richString.applyFont(font3);
							cell.setCellValue(richString);
						}
					}
				}catch(SecurityException e){
					e.printStackTrace();
				}catch(NoSuchMethodException e){
					e.printStackTrace();
				}catch(IllegalArgumentException e){
					e.printStackTrace();
				}catch(IllegalAccessException e){
					e.printStackTrace();
				}catch(InvocationTargetException e){
					e.printStackTrace();
				}
			}
		}
		return workbook;
	}
}