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页面：

<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代码：

 

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