Coolite优化导出Excel文件实现代码

1。先来张图：

导出前数据：

导出结果：

设置列宽和屏蔽栏位：

结果2：

2.先把脚本文件定义了。
代码如下:

//Copyright 2009 无忧lwz0721@gmail.com 
var gridElse = { 
getJsonToHidden: function(hidden, grid, format, title, fileName) { 
hidden.setValue(this.getJsonDate(grid, format, title, fileName)); 
grid.submitData(true); 
return true; 
}, 
getJsonDate: function(grid, format, title, fileName) { 
if (fileName == null || fileName == "") fileName = title; 
var result = { 
title: title, 
format: format, 
fileName: fileName, 
dataCount: grid.store.reader.jsonData.length, 
columns: '', 
jsonDate: '' 
}; 
//获取分组ID 
var groupField; 
if (typeof (grid.view.getGroupField) == "undefined") 
{ groupField = false; } 
else { groupField = grid.view.getGroupField(); } 
//设置表头 
var columns = this.getColumns(grid); //.getColumnModel().columns; 
var columnCount = columns.length 
for (var i = 0; i < columnCount; i++) { 
if (columns[i].dataIndex != null && columns[i].dataIndex != "") { 
fld = grid.store.fields.get(columns[i].dataIndex); 
columns[i].recordFieldType = this.getRecordFieldType(fld); 
} 
if (groupField && groupField == columns[i].dataIndex) 
columns[i].BGroup = true; 
} 
result.columns = Ext.encode(columns); 
//返回数据 
if (result.dataCount > 0 && result.dataCount <= 500) { 
result.jsonDate = Ext.encode(grid.store.reader.jsonData); 
} 
else if (result.dataCount == null) result.dataCount = 0; 
return Ext.encode(result); 
}, 
getRecordFieldType: function(fld) { 
if (fld == null) return ""; 
switch (fld.type) { 
case "int": return "Int"; 
case "float": return "Float"; 
case "bool": 
case "boolean": return "Boolean"; 
case "date": return "Date"; 
case "string": return "String"; 
default: return "Auto"; 
} 
}, 
getColumns: function(grid) { 
var columns = grid.getColumnModel().columns; 
var columnCount = columns.length 
for (var i = columnCount - 1; i >= 0; i--) { 
if (columns[i].isColumnPlugin) columns.remove(columns[i]); 
} 
return columns; 
} 
}; 
 
3.调用方法： 
 代码如下:

gridElse.getJsonToHidden(#{存储控件},#{GridPanel控件},'xls','标题','文件名'); 
 
4.aspx页面： 
XXX.aspx 
 代码如下:

<ext:Hidden ID="HToFile" runat="server" /> 
...... 
<ext:Store ID="Sdate" runat="server" OnSubmitData="Sdate_SubmitData" > 
...... 
</ext:Store> 
...... 
<ism:GridPanel ID="GPData" runat="server" StoreID="Sdate"> 
...... 
<ext:Button ID="Button1" runat="server" Text="Submit"> 
<Listeners> 
<Click Handler="gridElse.getJsonToHidden(#{HToFile},#{GPData},'xls','标题','文件名');" /> 
</Listeners> 
</ext:Button> 
 
5.cs代码： 
XXX.aspx.cs 
 代码如下:

protected void Sdate_SubmitData(object sender, StoreSubmitDataEventArgs e) 
{ 
String json = HToFile.Value.ToString(); 
if (!String.IsNullOrEmpty(json)) 
{ 
ExportDate exportDate = JSON.Deserialize<ExportDate>(json); 
if (exportDate.dataCount > 0) 
{ 
if (exportDate.Dates == null || exportDate.Dates.Length < exportDate.dataCount) 
{ 
//如数据超过500条这重新查询数据导出 
} 
switch (exportDate.format) 
{ 
case "xls": 
GetToExcel(exportDate); 
break; 
case "pdf": 
...... 
break; 
} 
} 
} 
} 
public static void GetToExcel(ExportDate exportDate) 
{ 
if (exportDate.Dates == null) { return; } 
HttpContext context = HttpContext.Current; 
if (context != null) 
{ 
String rowid = ""; 
StringBuilder sb = new StringBuilder(); 
int columns = 0; 
foreach (GridColumnInfo item in exportDate.GridColumnInfos) 
{ 
if (!item.hidden || item.BGroup) 
{ 
columns++; 
} 
} 
#region 头部 
sb.Append("<?xml version=\"1.0\" encoding=\"utf-8\"?>"); 
sb.Append("<?mso-application progid=\"Excel.Sheet\"?>"); 
sb.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" xmlns:html=\"http://www.w3.org/TR/REC-html40\">"); 
sb.Append(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">"); 
sb.Append(" <Version>12.00</Version>"); 
sb.Append(" </DocumentProperties>"); 
sb.Append(" <OfficeDocumentSettings xmlns=\"urn:schemas-microsoft-com:office:office\">"); 
sb.Append(" <RemovePersonalInformation/>"); 
sb.Append(" </OfficeDocumentSettings>"); 
sb.Append(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 
sb.Append(" <WindowHeight>11640</WindowHeight>"); 
sb.Append(" <WindowWidth>19200</WindowWidth>"); 
sb.Append(" <WindowTopX>0</WindowTopX>"); 
sb.Append(" <WindowTopY>90</WindowTopY>"); 
sb.Append(" <ProtectStructure>False</ProtectStructure>"); 
sb.Append(" <ProtectWindows>False</ProtectWindows>"); 
sb.Append(" </ExcelWorkbook>"); 
#region 样式 
sb.Append("<Styles>"); 
sb.Append("<Style ss:ID=\"Default\">"); 
sb.Append("<Alignment ss:Vertical=\"Top\" ss:WrapText=\"1\" />"); 
sb.Append("<Font ss:FontName=\"宋体\" ss:Size=\"11\" />"); 
//sb.Append("<Borders>"); 
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
//sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
//sb.Append("</Borders>"); 
sb.Append("<Interior />"); 
sb.Append("<NumberFormat />"); 
sb.Append("<Protection />"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:ID=\"title\">"); 
sb.Append("<Borders />"); 
sb.Append("<Font ss:Size=\"16\" ss:Bold=\"1\" />"); 
sb.Append("<Alignment ss:WrapText=\"1\" ss:Vertical=\"Center\" ss:Horizontal=\"Center\" />"); 
sb.Append("<NumberFormat ss:Format=\"@\" />"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:ID=\"headercell\">"); 
sb.Append("<Font ss:Bold=\"1\" ss:Size=\"12\" />"); 
sb.Append("<Alignment ss:WrapText=\"1\" ss:Horizontal=\"Center\" />"); 
sb.Append("<Interior ss:Pattern=\"Solid\" ss:Color=\"#F2F2F2\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:ID=\"even\">"); 
sb.Append("<Interior ss:Pattern=\"Solid\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:Parent=\"even\" ss:ID=\"evendate\">"); 
sb.Append("<NumberFormat ss:Format=\"[ENG][$-409]dd-mmm-yyyy;@\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:Parent=\"even\" ss:ID=\"evenint\">"); 
sb.Append("<NumberFormat ss:Format=\"0\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:Parent=\"even\" ss:ID=\"evenfloat\">"); 
sb.Append("<NumberFormat ss:Format=\"0.00\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:ID=\"odd\">"); 
sb.Append("<Interior ss:Pattern=\"Solid\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:Parent=\"odd\" ss:ID=\"odddate\">"); 
sb.Append("<NumberFormat ss:Format=\"[ENG][$-409]dd-mmm-yyyy;@\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:Parent=\"odd\" ss:ID=\"oddint\">"); 
sb.Append("<NumberFormat ss:Format=\"0\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("<Style ss:Parent=\"odd\" ss:ID=\"oddfloat\">"); 
sb.Append("<NumberFormat ss:Format=\"0.00\" />"); 
sb.Append("<Borders>"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Top\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Bottom\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Left\" />"); 
sb.Append("<Border ss:Weight=\"1\" ss:LineStyle=\"Continuous\" ss:Position=\"Right\" />"); 
sb.Append("</Borders>"); 
sb.Append("</Style>"); 
sb.Append("</Styles>"); 
#endregion 
sb.AppendFormat("<Worksheet ss:Name=\"{0}\">", exportDate.title); 
sb.AppendFormat("<Table x:FullRows=\"1\" x:FullColumns=\"1\" ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\">", columns, exportDate.Dates.Length + 2); 
#endregion 
//表列宽度 
int ColumnWidthsZ = 0; 
foreach (GridColumnInfo item in exportDate.GridColumnInfos) 
{ 
if (!item.hidden || item.BGroup) 
{ 
ColumnWidthsZ += item.width; 
sb.AppendFormat("<Column ss:AutoFitWidth=\"1\" ss:Width=\"{0}\" />", item.width); 
} 
} 
//标题 
sb.Append("<Row ss:Height=\"28\">"); 
sb.AppendFormat("<Cell ss:StyleID=\"title\" ss:MergeAcross=\"{0}\">", columns - 1); 
sb.AppendFormat("<Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"Print_Titles\" />", exportDate.title); 
sb.Append("</Cell>"); 
sb.Append("</Row>"); 
//表头 
sb.Append("<Row ss:AutoFitHeight=\"1\">"); 
foreach (GridColumnInfo item in exportDate.GridColumnInfos) 
{ 
if (!item.hidden || item.BGroup) 
{ 
sb.AppendFormat("<Cell ss:StyleID=\"headercell\"><Data ss:Type=\"String\">{0}</Data><NamedCell ss:Name=\"Print_Titles\" /></Cell>", item.header); 
} 
} 
sb.Append("</Row>"); 
//数据 
int i = 0; 
string cellClass = ""; 
foreach (Dictionary<string, string> row in exportDate.Dates) 
{ 
i++; 
cellClass = ((i & 1) == 0) ? "odd" : "even"; 
sb.Append("<Row>"); 
foreach (GridColumnInfo item in exportDate.GridColumnInfos) 
{ 
rowid = item.id; 
if (string.IsNullOrEmpty(rowid)) rowid = item.dataIndex; 
if (!String.IsNullOrEmpty(rowid) && (!item.hidden || item.BGroup) && row.ContainsKey(rowid)) 
{ 
sb.AppendFormat("<Cell ss:StyleID=\"{0}{1}\"><Data ss:Type=\"{2}\">{3}</Data></Cell>", 
cellClass, exportDate.GetStyleID(item.recordFieldType), exportDate.GetDataType(item.recordFieldType), row[rowid]); 
} 
} 
sb.Append("</Row>"); 
} 
#region 尾部 
sb.Append("</Table>"); 
sb.Append("<WorksheetOptions>"); 
sb.Append("<PageSetup>"); 
sb.Append("<Layout x:CenterHorizontal=\"1\" x:Orientation=\"Landscape\" />"); 
sb.Append("<Footer x:Data=\"Page &P of &N\" x:Margin=\"0.5\" />"); 
sb.Append("<PageMargins x:Top=\"0.5\" x:Right=\"0.5\" x:Left=\"0.5\" x:Bottom=\"0.8\" />"); 
sb.Append("</PageSetup>"); 
sb.Append("<FitToPage />"); 
sb.Append("<Print>"); 
sb.Append("<PrintErrors>Blank</PrintErrors>"); 
sb.Append("<FitWidth>1</FitWidth>"); 
sb.Append("<FitHeight>32767</FitHeight>"); 
sb.Append("<ValidPrinterInfo />"); 
sb.Append("<VerticalResolution>600</VerticalResolution>"); 
sb.Append("</Print>"); 
sb.Append("<Selected />"); 
sb.Append("<DoNotDisplayGridlines />"); 
sb.Append("<ProtectObjects>False</ProtectObjects>"); 
sb.Append("<ProtectScenarios>False</ProtectScenarios>"); 
sb.Append("</WorksheetOptions>"); 
sb.Append("</Worksheet></Workbook>"); 
#endregion 
context.Response.Clear(); 
if (context.Request.Browser.Browser != "IE") 
context.Response.AppendHeader("Content-Disposition", String.Format("attachment; filename=\"{0}.xls\"", exportDate.fileName)); 
else context.Response.AppendHeader("Content-Disposition", String.Format("attachment; filename={0}.xls", System.Web.HttpUtility.UrlEncode(exportDate.fileName))); 
context.Response.ContentType = "application/excel"; 
context.Response.Write(sb.ToString()); 
context.Response.End(); 
} 
}