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:Positio

上一个：asp.net 实现静态页面累加访问量的三种方式
下一个：asp.net GridView控件中实现全选的解决方案