Coolite optimizes the Excel file export implementation code

1. Let's take a picture first:

Data before export:

Export result:

Set the column width and the blocked column:

Result 2:

2. Define the script file first.

Copy codeThe Code is as follows: // Copyright 2009 worry-free 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 :''
};
// Obtain the group ID
Var groupField;
If (typeof (grid. view. getGroupField) = "undefined ")
{GroupField = false ;}
Else {groupField = grid. view. getGroupField ();}
// Set the header
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! = ""){
Topology = grid. store. fields. get (columns [I]. dataIndex );
Columns [I]. recordFieldType = this. getRecordFieldType (response );
}
If (groupField & groupField = columns [I]. dataIndex)
Columns [I]. BGroup = true;
}
Result. columns = Ext. encode (columns );
// Return data
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 (callback ){
If (condition = null) return "";
Switch (rule. 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. Call method:Copy codeThe Code is as follows: gridElse. getJsonToHidden (# {storage control}, # {GridPanel control}, 'xls ', 'title', 'filename ');

4. aspx page:
XXX. aspxCopy codeThe Code is as follows: <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 ', 'title', 'filename');"/>
</Listeners>
</Ext: Button>

5. cs code:
XXX. aspx. csCopy codeThe Code is as follows: 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)
{
// If there are more than 500 data records, re-query the data and export it.
}
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 Header
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 \ "> ");
Sb. Append ("<Version> 12.00 </Version> ");
Sb. Append ("</DocumentProperties> ");
Sb. Append ("<OfficeDocumentSettings xmlns = \" urn: schemas-microsoft-com: office \ "> ");
Sb. Append ("<RemovePersonalInformation/> ");
Sb. Append ("</OfficeDocumentSettings> ");
Sb. Append ("<ExcelWorkbook xmlns = \" urn: schemas-microsoft-com: office: excel \ "> ");
Sb. Append ("<WindowHeight> 11640 </WindowHeight> ");
Sb. Append ("<symbol wwidth> 19200 </Symbol wwidth> ");
Sb. Append ("<WindowTopX> 0 </WindowTopX> ");
Sb. Append ("<WindowTopY> 90 </WindowTopY> ");
Sb. Append ("<ProtectStructure> False </ProtectStructure> ");
Sb. Append ("<ProtectWindows> False </ProtectWindows> ");
Sb. Append ("</ExcelWorkbook> ");
# Region Style
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 \ "type =" regxph "text =" yourobjectname "/> ");
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 \ "type =" regxph "text =" yourobjectname "/> ");
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
// Column width
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 );
}
}
// Title
Sb. Append ("<Row ss: Height = \" 28 \ "> ");
Sb. AppendFormat ("<Cell ss: StyleID = \" title \ "ss: merge1_ss = \" {0} \ ">", columns-1 );
Sb. appendFormat ("<Data ss: Type = \" String \ ">{0} </Data> <NamedCell ss: Name = \" Print_Titles \ "/>", exportDate. title );
Sb. Append ("</Cell> ");
Sb. Append ("</Row> ");
// Header
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> ");
// Data
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 tail
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=02.16.xls", System. Web. HttpUtility. UrlEncode (exportDate. fileName )));
Context. Response. ContentType = "application/excel ";
Context. Response. Write (sb. ToString ());
Context. Response. End ();
}
}