excel之-extjs

ExtJS 导出 Excel

这两天项目上要求做一个导出Excel的功能，项目前台用的ExtJS,后台用的JAVA，网上查了查，大概有3种做法。
1.apache公司提供的POI
2.韩国公司的JXL
3.据说是官方提供的JS调用方法
前两种都要引入外包，懒得找包了，采用了第三种，所需引入JS代码如下：

/Files/rockblue1988/export.txt

/**
* allows for downloading of grid data (store) directly into excel
* Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
* converts to Base64, then loads everything into a data URL link.
*
* @author Animal <extjs support team>
*
*/

/**
* base64 encode / decode
*
* @location http://www.webtoolkit.info/
*
*/

var Base64 = (function() {
// Private property
var keyStr ="ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=";

// Private method for UTF-8 encoding
function utf8Encode(string) {
string = string.replace(/\r\n/g,"\n");
var utftext ="";
for (var n =0; n < string.length; n++) {
var c = string.charCodeAt(n);
if (c <128) {
utftext += String.fromCharCode(c);
}
elseif((c >127) && (c <2048)) {
utftext += String.fromCharCode((c >>6) |192);
utftext += String.fromCharCode((c &63) |128);
}
else {
utftext += String.fromCharCode((c >>12) |224);
utftext += String.fromCharCode(((c >>6) &63) |128);
utftext += String.fromCharCode((c &63) |128);
}
}
return utftext;
}

// Public method for encoding
return {
encode : (typeof btoa =='function') ? function(input) {
return btoa(utf8Encode(input));
} : function (input) {
var output ="";
var chr1, chr2, chr3, enc1, enc2, enc3, enc4;
var i =0;
input = utf8Encode(input);
while (i < input.length) {
chr1 = input.charCodeAt(i++);
chr2 = input.charCodeAt(i++);
chr3 = input.charCodeAt(i++);
enc1 = chr1 >>2;
enc2 = ((chr1 &3) <<4) | (chr2 >>4);
enc3 = ((chr2 &15) <<2) | (chr3 >>6);
enc4 = chr3 &63;
if (isNaN(chr2)) {
enc3 = enc4 =64;
} elseif (isNaN(chr3)) {
enc4 =64;
}
output = output +
keyStr.charAt(enc1) + keyStr.charAt(enc2) +
keyStr.charAt(enc3) + keyStr.charAt(enc4);
}
return output;
}
};
})();

Ext.override(Ext.grid.GridPanel, {
getExcelXml: function(includeHidden) {
var worksheet =this.createWorksheet(includeHidden);
var totalWidth =this.getColumnModel().getTotalWidth(includeHidden);
return'<xml version="1.0" encoding="utf-8">'+
'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">'+
'<o:DocumentProperties><o:Title>'+this.title +'</o:Title></o:DocumentProperties>'+
'<ss:ExcelWorkbook>'+
'<ss:WindowHeight>'+ worksheet.height +'</ss:WindowHeight>'+
'<ss:WindowWidth>'+ worksheet.width +'</ss:WindowWidth>'+
'<ss:ProtectStructure>False</ss:ProtectStructure>'+
'<ss:ProtectWindows>False</ss:ProtectWindows>'+
'</ss:ExcelWorkbook>'+
'<ss:Styles>'+
'<ss:Style ss:ID="Default">'+
'<ss:Alignment ss:Vertical="Top" ss:WrapText="1" />'+
'<ss:Font ss:FontName="arial" ss:Size="10" />'+
'<ss:Borders>'+
'<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />'+
'<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />'+
'<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />'+
'<ss:Border ss:Color="#e4e4e4" ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />'+
'</ss:Borders>'+
'<ss:Interior />'+
'<ss:NumberFormat />'+
'<ss:Protection />'+
'</ss:Style>'+
'<ss:Style ss:ID="title">'+
'<ss:Borders />'+
'<ss:Font />'+
'<ss:Alignment ss:WrapText="1" ss:Vertical="Center" ss:Horizontal="Center" />'+
'<ss:NumberFormat ss:Format="@" />'+
'</ss:Style>'+
'<ss:Style ss:ID="headercell">'+
'<ss:Font ss:Bold="1" ss:Size="10" />'+
'<ss:Alignment ss:WrapText="1" ss:Horizontal="Center" />'+
'<ss:Interior ss:Pattern="Solid" ss:Color="#A3C9F1" />'+
'</ss:Style>'+
'<ss:Style ss:ID="even">'+
'<ss:Interior ss:Pattern="Solid" ss:Color="#CCFFFF" />'+
'</ss:Style>'+
'<ss:Style ss:Parent="even" ss:ID="evendate">'+
'<ss:NumberFormat ss:Format="yyyy-mm-dd" />'+
'</ss:Style>'+
'<ss:Style ss:Parent="even" ss:ID="evenint">'+
'<ss:NumberFormat ss:Format="0" />'+
'</ss:Style>'+
'<ss:Style ss:Parent="even" ss:ID="evenfloat">'+
'<ss:NumberFormat ss:Format="0.00" />'+
'</ss:Style>'+
'<ss:Style ss:ID="odd">'+
'<ss:Interior ss:Pattern="Solid" ss:Color="#CCCCFF" />'+
'</ss:Style>'+
'<ss:Style ss:Parent="odd" ss:ID="odddate">'+
'<ss:NumberFormat ss:Format="yyyy-mm-dd" />'+
'</ss:Style>'+
'<ss:Style ss:Parent="odd" ss:ID="oddint">'+
'<ss:NumberFormat ss:Format="0" />'+
'</ss:Style>'+
'<ss:Style ss:Parent="odd" ss:ID="oddfloat">'+
'<ss:NumberFormat ss:Format="0.00" />'+
'</ss:Style>'+
'</ss:Styles>'+
worksheet.xml +
'</ss:Workbook>';
},

createWorksheet: function(includeHidden) {
// Calculate cell data types and extra class names which affect formatting
var cellType = [];
var cellTypeClass = [];
var cm =this.getColumnModel();
var totalWidthInPixels =0;
var colXml ='';
var headerXml ='';
var visibleColumnCountReduction =0;
var colCount = cm.getColumnCount();
for (var i =0; i < colCount; i++) {
if ((cm.getDataIndex(i) !='')
&& (includeHidden ||!cm.isHidden(i))) {
var w = cm.getColumnWidth(i)
totalWidthInPixels += w;
if (cm.getColumnHeader(i) ===""){
cellType.push("None");
cellTypeClass.push("");
++visibleColumnCountReduction;
}
else
{
colXml +='<ss:Column ss:AutoFitWidth="1" ss:Width="'+ w +'" />';
headerXml +='<ss:Cell ss:StyleID="headercell">'+
'<ss:Data ss:Type="String">'+ cm.getColumnHeader(i) +'</ss:Data>'+
'<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>';
var fld =this.store.recordType.prototype.fields.get(cm.getDataIndex(i));
switch(fld.type) {
case"int":
cellType.push("Number");
cellTypeClass.push("int");
break;
case"float":
cellType.push("Number");
cellTypeClass.push("float");
break;
case"bool":
case"boolean":
cellType.push("String");
cellTypeClass.push("");
break;
case"date":
cellType.push("DateTime");
cellTypeClass.push("date");
break;
default:
cellType.push("String");
cellTypeClass.push("");
break;
}
}
}
}
var visibleColumnCount = cellType.length - visibleColumnCountReduction;

var result = {
height: 9000,
width: Math.floor(totalWidthInPixels *30) +50
};

// Generate worksheet header details.
var t ='<ss:Worksheet ss:Name="'+this.title +'">'+
'<ss:Names>'+
'<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + this.title + '\'!R1:R2" />'+
'</ss:Names>'+
'<ss:Table x:FullRows="1" x:FullColumns="1"'+
' ss:ExpandedColumnCount="'+ (visibleColumnCount +2) +
'" ss:ExpandedRowCount="'+ (this.store.getCount() +2) +'">'+
colXml +
'<ss:Row ss:Height="38">'+
'<ss:Cell ss:StyleID="title" ss:MergeAcross="'+ (visibleColumnCount -1) +'">'+
'<ss:Data xmlns:html="http://www.w3.org/TR/REC-html40" ss:Type="String">'+
'<html:B></html:B></ss:Data><ss:NamedCell ss:Name="Print_Titles" />'+
'</ss:Cell>'+
'</ss:Row>'+
'<ss:Row ss:AutoFitHeight="1">'+
headerXml +
'</ss:Row>';

// Generate the data rows from the data in the Store
for (var i =0, it =this.store.data.items, l = it.length; i < l; i++) {
t +='<ss:Row>';
var cellClass = (i &1) ?'odd' : 'even';
r = it[i].data;
var k =0;
for (var j =0; j < colCount; j++) {
if ((cm.getDataIndex(j) !='')
&& (includeHidden ||!cm.isHidden(j))) {
var v = r[cm.getDataIndex(j)];
if (cellType[k] !=="None") {
t +='<ss:Cell ss:StyleID="'+ cellClass + cellTypeClass[k] +'"><ss:Data ss:Type="'+ cellType[k] +'">';
if (cellType[k] =='DateTime') {
t += v.format('Y-m-d');
} else {
t += v;
}
t +='</ss:Data></ss:Cell>';
}
k++;
}
}
t +='</ss:Row>';
}

result.xml = t +'</ss:Table>'+
'<x:WorksheetOptions>'+
'<x:PageSetup>'+
'<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />'+
'<x:Footer x:Data="Page &amp;P of &amp;N" x:Margin="0.5" />'+
'<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />'+
'</x:PageSetup>'+
'<x:FitToPage />'+
'<x:Print>'+
'<x:PrintErrors>Blank</x:PrintErrors>'+
'<x:FitWidth>1</x:FitWidth>'+
'<x:FitHeight>32767</x:FitHeight>'+
'<x:ValidPrinterInfo />'+
'<x:VerticalResolution>600</x:VerticalResolution>'+
'</x:Print>'+
'<x:Selected />'+
'<x:DoNotDisplayGridlines />'+
'<x:ProtectObjects>False</x:ProtectObjects>'+
'<x:ProtectScenarios>False</x:ProtectScenarios>'+
'</x:WorksheetOptions>'+
'</ss:Worksheet>';
return result;
}
});

ExtJS中的导出按钮代码如下：

{
text : "导出到excel",
style : {
marginRight : '20px'
},
handler : function() {
var vExportContent = gridpanel.getExcelXml(); //获取数据
if (Ext.isIE8||Ext.isIE6 || Ext.isIE7 || Ext.isSafari
|| Ext.isSafari2 || Ext.isSafari3) { //判断浏览器

var fd = Ext.get('frmDummy');
if (!fd) {
fd = Ext.DomHelper.append(
Ext.getBody(), {
tag : 'form',
method : 'post',
id : 'frmDummy',
action : 'exportUrl.jsp',
target : '_blank',
name : 'frmDummy',
cls : 'x-hidden',
cn : [ {
tag : 'input',
name : 'exportContent',
id : 'exportContent',
type : 'hidden'
} ]
}, true);

}
fd.child('#exportContent').set( {
value : vExportContent
});
fd.dom.submit();
} else {
document.location ='data:application/vnd.ms-excel;base64,'+ Base64
.encode(vExportContent);
}
}}

exportUrl.jsp页面的代码如下：

<%
response.setHeader("Content-Type","application/force-download");
response.setHeader("Content-Type","application/vnd.ms-excel");
response.setHeader("Content-Disposition","attachment;filename=export.xls");
out.print(request.getParameter("exportContent"));
%>

注意事项：
1.exportUrl.jsp的页面只需要以上的代码，多余的都不要了，否则生成的excel文件内容为Null
2.生成的xls文件在Excel打开过程中可能会出现如下错误：
---
加载期间在下述区域中出现了问题(P):
表格
由于错误，此文件无法打开。错误列于:C:\Documents and Settings\..\Local Settings\Temporary Internet
---
这是由于输入的数据中存在空格或者与设置中的类型不符，我遇到的问题就是由于Columns中的header中有类似于“日吨水<br>综合费”这样的属性而产生的，修改即可
3.在Google、FireFox、IE8/IE9测试通过