前些时间老大说客户要求提供将表格中的数据导出到Excel中，因为有时候他们需要将价格资料导出以便制作报价表，于是上网找了一些资料，发现网上其实有很多例子都有浏览器兼容性的问题，于是自己整合，改进之后，终于能兼容支持和浏览器了，遂在这里与大家分享、交流：
首先你需要一个将GridPanel的数据转换成标准Excel格式的JS文件，文件内容如下（貌似CSDN博客不支持上传文件给大家下载，所以唯有直接贴代码了）：
代码语言：javascript复制// JavaScript Document
/**
* 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);
}
else if((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;
} else if (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);
var excelTitle = "";
if(typeof(this.title) != "undefined" && this.title != ""){
excelTitle = this.title;
}else{
excelTitle = "原点商业平台导出数据";
}
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>' excelTitle '</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
};
var excelTitle = "";
if(typeof(this.title) != "undefined" && this.title != ""){
excelTitle = this.title;
}else{
excelTitle = "原点商业平台导出数据";
}
// Generate worksheet header details.
var t = '<ss:Worksheet ss:Name="' excelTitle '">'
'<ss:Names>'
'<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=/'' excelTitle '/'!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 &P of &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;
}
});
你可以将上述代码复制到一个独立的JS文件中，在需要用到的时候再加载就可以了。事实上这个文件是比较大的，并且导出GridPanel的功能可能很多页面都可能被需要，所以个人认为一开始就以<script>标签对的形式加载很浪费资源，因为事实上很多时候用户并不需要这个功能。所以
我把它做成在用户点击了“导出到EXCEL”按钮的时候才去加载这个JS文件
