Coolite 优化 导出 Excel 文件。可以设定列宽和导出列选择。

1。先来张图：

导出前数据：

导出结果：

设置列宽和屏蔽栏位：

结果2：

2.先把脚本文件定义了。

gridElse.js 脚本文件

//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" >

  <AjaxEventConfig IsUpload="true" /><!--这行重要前面忘记加了-->
......
</ 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 & amp;P of & amp;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();
}
}

 自定义实体:

 

/// <summary> 导出数据 </summary>
[Serializable]
public class ExportDate
{

/// <summary> 标题 </summary>
public String title { get ; set ; }
/// <summary> 文件名 </summary>
public String fileName { get ; set ; }
/// <summary> 导出格式 </summary>
public String format { get ; set ; }

/// <summary> 总数据数量 </summary>
public int dataCount { get ; set ; }
public String columns { get ; set ; }
public String jsonDate { get ; set ; }
List < GridColumnInfo > gridColumnInfos;

/// <summary> 标题集合 </summary>
public List < GridColumnInfo > GridColumnInfos
{
get
{
if (gridColumnInfos == null )
{
if ( ! string .IsNullOrEmpty(columns))
{
gridColumnInfos = JSON.Deserialize < List < GridColumnInfo >> (columns);
}
}
return gridColumnInfos;
}
}
Dictionary < string , string > [] dates;

/// <summary> 数据集合 </summary>
public Dictionary < string , string > [] Dates
{
get
{
if (dates == null )
{
if ( ! string .IsNullOrEmpty(jsonDate))
{
dates = JSON.Deserialize < Dictionary < string , string > [] > (jsonDate);
}
}
return dates;
}
set
{
dates = value;
}
}

public void ListToDictionary < T > (List < T > ts)
{
Type t = typeof (T);
dates = new Dictionary < string , string > [ts.Count];
int i = 0 ;
foreach (T rom in ts)
{
foreach (GridColumnInfo item in this .GridColumnInfos)
{
if ( ! item.hidden || item.BGroup)
{
if (dates[i] == null ) dates[i] = new Dictionary < string , string > ();
dates[i].Add(item.dataIndex,
t.GetProperty(item.dataIndex).GetValue(rom, null ).ToString());
}
}
i ++ ;
}
}


public void DataTableToDictionary(DataTable dt)
{
dates = new Dictionary < string , string > [dt.Rows.Count];
int i = 0 ;
foreach (DataRow rom in dt.Rows)
{
foreach (GridColumnInfo item in this .GridColumnInfos)
{
if ( ! item.hidden || item.BGroup)
{
if (dates[i] == null ) dates[i] = new Dictionary < string , string > ();
dates[i].Add(item.dataIndex,rom[item.dataIndex].ToString());
}
}
i ++ ;
}
}

public String GetStyleID(String rft)
{
switch (rft.ToLower())
{
case " int " : return " int " ;
case " float " : return " float " ;
case " date " : return " date " ;
default : return "" ;
}
}

public String GetDataType(String rft)
{
switch (rft.ToLower())
{
case " int " :
case " float " : return " Number " ;
case " date " : return " DateTime " ;
default : return " String " ;
}
}
}

 

 

[Serializable]
public class GridColumnInfo
{
public GridColumnInfo() { BGroup = false ; width = 100 ; }


/// <summary> id </summary>
public String id { get ; set ; }

/// <summary> 字段名 </summary>
public String dataIndex { get ; set ; }

/// <summary> 标题 </summary>
public String header { get ; set ; }

/// <summary> 汇总类型 </summary>
public String summaryType { get ; set ; }

/// <summary> 数据类型 </summary>
public String recordFieldType { get ; set ; }

/// <summary> 宽度 </summary>
public int width { get ; set ; }

/// <summary> 排序方式 </summary>
public bool sortable { get ; set ; }

/// <summary> 是否显示 </summary>
public bool hidden { get ; set ; }

/// <summary> 是否分组选中 </summary>
public bool BGroup { get ; set ; }
}

 

转载于:https://www.cnblogs.com/lwzblog/archive/2010/03/02/1676235.html