最近项目中牵扯到大数据量导出到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。
这个有空再去尝试。