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

赞 (0)

相关推荐

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

    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)); gr

  • django使用xlwt导出excel文件实例代码

    本文研究的主要是记录一下下导出的方法,并没有做什么REST处理和异常处理. 维护统一的style样式,可以使导出的数据更加美观. def export_excel(request): # 设置HttpResponse的类型 response = HttpResponse(content_type='application/vnd.ms-excel') response['Content-Disposition'] = 'attachment;filename=user.xls' # new一个文

  • C#导入导出EXCEL文件的代码实例

    复制代码 代码如下: using System;using System.Data;using System.Data.OleDb; namespace ZFSoft.Joint{    public class ExcelIO    {        private int _ReturnStatus;        private string _ReturnMessage; /// <summary>        /// 执行返回状态        /// </summary&g

  • 通过jxl.jar 读取、导出excel的实例代码

    复制代码 代码如下: package export.excel; import java.io.BufferedInputStream;import java.io.BufferedOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.OutputStream;import java.util.ArrayList;import j

  • python技能之数据导出excel的实例代码

    python技能之数据导出excel的实例代码

    本文介绍了python技能之导出excel的实例代码,正好能用到,写出来分享给大家 作为一个数据分析师,下面的需求是经常会遇到的. 从数据库或者现有的文本文件中提取符合要求的数据,做一个二次处理,处理完成后的数据最终存储到excel表格中供其他部门的人继续二次分析. 在这里Excel作为一个必不可少桥梁,合适的工具和方法可以避免我们将处理完的数据耗费时间一行行复制黏贴过去. python编程也是一个数据分析师的必备技能,你永远无法预料你的数据会来自哪里,需要经过怎样复杂的过滤,筛选,排序,组合处

  • asp导出excel文件最简单方便的方法

    由于excel软件能识别table格式的数据,所以asp只需要输出table格式的html代码,同时设置好contenttype,增加保存为附件的响应头即可将输出的html代码保存为xls文件. asp导出excel文件源代码如下: 复制代码 代码如下: <% Response.ContentType = "application/excel" Response.AddHeader "Content-Disposition", "attachment

  • Java数据导出功能之导出Excel文件实例

    Java数据导出功能之导出Excel文件实例

    在编程中经常需要使用到表格(报表)的处理主要以Excel表格为主.下面给出用java写入数据到excel表格方法: 1.添加jar文件 java导入导出Excel文件要引入jxl.jar包,最关键的是这套API是纯Java的,并不依赖Windows系统,即使运行在Linux下,它同样能够正确的处理Excel文件.下载地址:http://www.andykhan.com/jexcelapi/ 2.jxl对Excel表格的认识 可以参见:http://www.jb51.net/article/686

  • EasyUI 结合JS导出Excel文件的实现方法

    废话俺就少说了,直接进入正题!!单纯的JS能够导出Excel的不多见,一般都需要调用客户端所安装的Office Excel组件来完成这个工作.这里我主要讲EasyUI内的DataGrid如何结合JS导出Excel文件 一. 导出Excel的核心代码段如下所示 function Exproter() { //获取Datagride的列 var rows = $('#test').datagrid('getRows'); var oXL = new ActiveXObject("Excel.Appl

  • C# Winform实现导入和导出Excel文件

    本文实例为大家分享了Winform实现导入导出Excel文件的具体代码,供大家参考,具体内容如下 /// <summary> /// 导出Excel文件 /// </summary> /// /// <param name="dataSet"></param> /// <param name="dataTable">数据集</param> /// <param name="isS

  • django admin后台添加导出excel功能示例代码

    django admin后台添加导出excel功能示例代码

    Django功能强大不单在于他先进的编程理念,很多现有的功能模块更是可以直接拿来使用,比如这个牛掰的admin模块,可以作为一个很好的信息登记管理系统. admin模块中的actioin是可以自定义添加的,比如这次要介绍的导出excel功能,就可以在action中触发. 本文将详细介绍如何导出admin中录入的数据为excel,可以直接提交给你的leader观看. 首先我们要安装 xlwt 这个工具模块: pip install xlwt import的准备 修改admin.py: #-*-co

随机推荐