当前位置：首页 > 资讯 > 技术文档

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

时间：2021-10-29 21:43 编辑：来源：阅读：
扫一扫，手机访问

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

1。先来张图：导出前数据： [img]http://img.1sucai.cn/uploads/article/2018010710/20180107100105_0_97121.jpg[/img] 导出结果： [img]http://img.1sucai.cn/uploads/article/2018010710/20180107100105_1_51181.jpg[/img] 设置列宽和屏蔽栏位： [img]http://img.1sucai.cn/uploads/article/2018010710/20180107100106_2_32704.jpg[/img] 结果2： [img]http://img.1sucai.cn/uploads/article/2018010710/20180107100106_3_60542.jpg[/img] 2.先把脚本文件定义了。

[u]复制代码[/u] 代码如下:

//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.调用方法：

[u]复制代码[/u] 代码如下:

gridElse.getJsonToHidden(#{存储控件},#{GridPanel控件},'xls','标题','文件名');

4.aspx页面： XXX.aspx

[u]复制代码[/u] 代码如下:

<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

[u]复制代码[/u] 代码如下:

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();
}
}

全部评论(0)

上一篇：ASP.NET Core应用中与第三方IoC/DI框架的整合
下一篇：Asp.Net 音频文件上传和播放代码

资讯排行榜
更多>>