大数据量导出到Excel方法总结

最近项目中牵扯到大数据量导出到Excel。传统的jxl，poi等在后端生成excel的方法就不见得有多奏效。

1. JXL后端生成Excel代码（struts2 action方法代码）：

public String excel() throws Exception{
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		WritableWorkbook wbook = Workbook.createWorkbook(os);
		WritableSheet wsheet = wbook.createSheet("交易明细", 0);
		String[] titles = {"序号","业务流水","营销流水","缴费流水","电话号码","费用(元)","营销金额(元)","自由花费(元)"};
		for(int i=0;i<titles.length;i++){
			wsheet.addCell(new Label(i,0,titles[i]));
		}

<span style="white-space:pre">		</span>//封装分页查询参数
		BaseBean params = getParams();
		params.put("pagesize", 1000);
		int n=1;
		int i=0;
		while(true){
			params.put("page", n++);
			List<BaseBean> list = reportService.findList(params);
			if(list != null && list.size() > 0){
				BaseBean item = null;
				Iterator<BaseBean> it = list.iterator();
				while(it.hasNext()){
					i++;
					item = it.next();
					wsheet.addCell(new Number(0,i,i));
					wsheet.addCell(new Label(1,i,item.getString("appid")));
					wsheet.addCell(new Label(2,i,item.getString("openid")));
					wsheet.addCell(new Label(3,i,item.getString("payid")));
					wsheet.addCell(new Label(4,i,item.getString("phone")));
					wsheet.addCell(new Number(5,i,item.getDouble("cardmoney")));
					wsheet.addCell(new Number(6,i,item.getDouble("acmoney")));
					wsheet.addCell(new Number(7,i,item.getDouble("freemoney")));
				}
			}else{
				break;
			}
		}
		wbook.write();
		wbook.close();
		inputStream = new ByteArrayInputStream(os.toByteArray());
		os.close();
		fileName = new String("交易明细记录".getBytes("gb2312"),"ISO-8859-1");
		return SUCCESS;
	}

由以上代码可知，生成Excel的过程中会创建大量的对象（new Label等），除此之外，List集合，WritableSheet对象数据量较大，所以容易造成内存泄露的问题。

2. 解决方法

2.1 后端xml拼接的方式

	public String excel() throws Exception{
		String time = Long.toString(System.currentTimeMillis());
		FileWriter fw = new FileWriter("d:/" + time + ".xls");
		StringBuffer sb = new StringBuffer();
		sb.append("<?xml version=\"1.0\"?>");
		sb.append("\n");
		sb.append("<?mso-application progid=\"Excel.Sheet\"?>");
		sb.append("\n");
		sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
		sb.append("\n");
		sb.append("  xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
		sb.append("\n");
		sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
		sb.append("\n");
		sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
		sb.append("\n");
		sb.append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
		sb.append("\n");
		sb.append("<Styles>\n");
		sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">\n");
		sb.append("<Alignment ss:Vertical=\"Center\"/>\n");
		sb.append("<Borders/>\n");
		sb.append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>\n");
		sb.append("<Interior/>\n");
		sb.append("<NumberFormat/>\n");
		sb.append("<Protection/>\n");
		sb.append("</Style>\n");
		sb.append("</Styles>\n");
		
		String[] titles = {"序号","业务流水","营销流水","缴费流水","电话号码","卡费(元)","营销金额(元)","自由花费(元)"};
		BaseBean params = getParams();
		params.put("pagesize", 20000);    //分页查询中pagesize不宜过小，否则查询次数太多，速度太慢
		
		int recordcount = 60000;//每个sheet页面的条数
		int currentRecord = 0;
		int col = titles.length;
		
		sb.append("<Worksheet ss:Name=\"交易明细1\">");  
        sb.append("\n");  
        sb.append("<Table ss:ExpandedColumnCount=\"" + col  
                + "\" ss:ExpandedRowCount=\"" + (recordcount + 1)  
                + "\" x:FullColumns=\"1\" x:FullRows=\"1\">");  
        sb.append("\n"); 
       
        sb.append("<Row>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[0] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[1] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[2] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[3] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[4] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[5] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[6] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[7] + "</Data></Cell>");
		sb.append("<Cell><Data ss:Type=\"String\">" + titles[8] + "</Data></Cell>");
		sb.append("</Row>");
		
		int n=1;
		int i=0;
		int j=1;
		while(true){
			if ((currentRecord >= recordcount) && i != 0) {// 一个sheet写满
				currentRecord = 0;
				i=0;
				fw.write(sb.toString());  
                sb.setLength(0);
                
				sb.append("</Table>");  
                sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");  
                sb.append("\n");  
                sb.append("<ProtectObjects>False</ProtectObjects>");  
                sb.append("\n");  
                sb.append("<ProtectScenarios>False</ProtectScenarios>");  
                sb.append("\n");  
                sb.append("</WorksheetOptions>");  
                sb.append("\n");  
                sb.append("</Worksheet>");
                
				sb.append("<Worksheet ss:Name=\"交易明细" + ++j + "\">");
				sb.append("\n");
				sb.append("<Table ss:ExpandedColumnCount=\"" + col
						+ "\" ss:ExpandedRowCount=\"" + (recordcount + 1)
						+ "\" x:FullColumns=\"1\" x:FullRows=\"1\">");
				sb.append("\n");
				
				sb.append("<Row>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[0] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[1] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[2] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[3] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[4] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[5] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[6] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[7] + "</Data></Cell>");
				sb.append("<Cell><Data ss:Type=\"String\">" + titles[8] + "</Data></Cell>");
				sb.append("</Row>");
			}
			params.put("page", n++);
			List<BaseBean> list = reportService.findList(params);
			if(list != null && list.size() > 0){
				BaseBean item = null;
				Iterator<BaseBean> it = list.iterator();
				while(it.hasNext()){
					i++;
					item = it.next();
					
					sb.append("<Row>");
					sb.append("<Cell><Data ss:Type=\"String\">" + i +"</Data></Cell>");
					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("appid") + "</Data></Cell>");
					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("openid") + "</Data></Cell>");
					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("payid") + "</Data></Cell>");
					sb.append("<Cell><Data ss:Type=\"String\">" + item.getString("phone") + "</Data></Cell>");
					sb.append("<Cell><Data ss:Type=\"String\">" + item.getDouble("cardmoney") + "</Data></Cell>");
					sb.append("<Cell><Data ss:Type=\"String\">" + item.getDouble("acmoney") + "</Data></Cell>");
					sb.append("<Cell><Data ss:Type=\"String\">" + item.getDouble("freemoney") + "</Data></Cell>");
					sb.append("</Row>");
					
					if (i % 1000 == 0) {
						fw.write(sb.toString());
						fw.flush();
						sb.setLength(0);
					}
					sb.append("\n");
					currentRecord++;
				}
			}else{
				break;
			}
		}
		fw.write(sb.toString());
		fw.flush();
		sb.setLength(0);
		sb.append("</Table>");
		sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
		sb.append("\n");
		sb.append("<ProtectObjects>False</ProtectObjects>");
		sb.append("\n");
		sb.append("<ProtectScenarios>False</ProtectScenarios>");
		sb.append("\n");
		sb.append("</WorksheetOptions>");
		sb.append("\n");
		sb.append("</Worksheet>");
		sb.append("</Workbook>");
		sb.append("\n");
		fw.write(sb.toString());
		fw.flush();
		fw.close();
		File file = new File("d:/" + time + ".xls");
		inputStream = new FileInputStream(file);
		fileName = new String("交易明细记录".getBytes("gb2312"),"ISO-8859-1");
		return SUCCESS;
	}

以上采用xml拼接的方式，把数据写入到服务器，然后再下载到客户端，可以达到目的。

删除服务器端临时文件方法

由于struts2中数据流不关闭，文件是无法删除的

2.1.1 把所有临时文件放在同一个目录下，程序中创建文件的时候，检查该目录下是否有相关文件，如果有则删除即可

2.1.2 多线程的方式   点这里

2.2 前端ajax获取数据，最终生成下载文件的方式

Action方法代码

public String excel() throws Exception{
		String par = request.getParameter("par");
		BaseBean params = getParams();
		params.put("pagesize", 5000);		
		params.put("page", par);
		List<BaseBean> list = reportService.findList(params);
		inputStream = new ByteArrayInputStream(list.toString().getBytes());
		return SUCCESS;
	}

Struts2配置

<action name="excel" class="account_action" method="excel">
			<result type="stream" name="success">
				<param name="contentType">text/html</param>
				<param name="inputName">inputStream</param>
			</result>
			<result type="json" name="error">
				<param name="root">message.bean</param>
			</result>
		</action>

前端script方法

jQuery('#export_btn').click(function(){
				   <span style="white-space:pre">	</span>//判断浏览器
					var explorer = window.navigator.userAgent ;
					
					if (explorer.indexOf("MSIE") >= 0 || explorer.indexOf("Trident") >= 0) {
						explorer = 'ie';
					}
					else if (explorer.indexOf("Firefox") >= 0) {
						explorer = 'Firefox';
					}
					else if(explorer.indexOf("Chrome") >= 0){
						explorer = 'Chrome';
					}
					else if(explorer.indexOf("Opera") >= 0){
						explorer = 'Opera';
					}
					else if(explorer.indexOf("Safari") >= 0){
						explorer = 'Safari';
					}				
					
					var params = '';
					jQuery('#divfrom li').find('input[type=text],select').each(function(i,item){
						params += '&' + jQuery(item).attr('name') + '=' + jQuery(item).val();
					});
					params = '?' + params.substring(1);

				 	var results = "序号,业务流水,营销流水,缴费流水,电话号码,卡费(元),营销金额(元),自由花费(元)" + "\n";
					
           			var i = 0;
           			var j = 0;
           			for(var m=1;m<=i+1;m++){
	           			i++;
						jQuery.ajax({
							async: false,
							cache: false,
							type: "get",
							url: "$!{base}/main/account/report/excel.html" + params,
							data: "par=" + i,
							contentType: "text",
							success: function (data) {
								if(data != null && data.length>2){
									var data = eval(data);
									jQuery.each(data,function(i,n){
										j++;
										var temp = '';
										
										temp = j + "\t," +data[i].appid + "\t," + data[i].openid + "\t," + data[i].payid + "\t," + data[i].phone + "\t,"
										+ data[i].cardmoney + "\t," + data[i].acmoney + "\t," + data[i].freemoney + "\t\n';
										
										results += temp;
										
									});
									//输出
									results = results.replace(/null/g,"") 
									results = results.replace(/undefined/g,"") 
									if(explorer =='ie')
									{
										var path = prompt("输入保存路径和文件名", "d:\\交易明细" + m + ".csv")
										var fso = new ActiveXObject("Scripting.FileSystemObject");
										var s = fso.CreateTextFile(path, true);
										s.WriteLine(results);
										s.Close();
										results = "序号,业务流水,营销流水,缴费流水,电话号码,卡费(元),营销金额(元),自由花费(元)" + "\n";
									}
									else{
										results = encodeURIComponent(results);
										var uri = 'data:text/csv;charset=utf-8,\ufeff' + results;
										var downloadLink = document.createElement("a");
										downloadLink.href = uri;
										downloadLink.download = "交易明细" + m + ".csv";
										document.body.appendChild(downloadLink);
										downloadLink.click();
										document.body.removeChild(downloadLink);
										results = "序号,业务流水,营销流水,缴费流水,电话号码,卡费(元),营销金额(元),自由花费(元)" + "\n";
									}
									
								}else{
									i = -1;
								}
							}
						})
					}
				});

总结：采用xml方法，个人认为较好。

前端拼接数据的方法，可能会存在浏览器兼容的问题，不过为了减轻服务器压力，也暂无其它的方式，毕竟个人能力有限。

另：前端生成文件的方式，Chrome浏览器，数据量接近1W条的情况，就会存在崩溃的可能，所以采用生成多个文件的方式。

据说借助浏览器控件，如flash可以实现浏览器端IO的可能。也有一些相关的基于jquery的组件，如downloadify,dataTables。
这个有空再去尝试。