Jquery easyui datagrid 导出Excel

                  1 <script>
  2/**
  3        Jquery easyui datagrid js导出excel
  4        修改自extgrid导出excel
  5        * allows for downloading of grid data (store) directly into excel
  6        * Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,
  7        * converts to Base64, then loads everything into a data URL link.
  8        *
  9        * @author Animal <extjs support team>
 10        *
 11*/ 12        $.extend($.fn.datagrid.methods, {
 13            getExcelXml: function (jq, param) {
 14var worksheet = this.createWorksheet(jq, param);
 15//alert($(jq).datagrid(‘getColumnFields‘)); 16var totalWidth = 0;
 17var cfs = $(jq).datagrid(‘getColumnFields‘);
 18for (var i = 1; i < cfs.length; i++) {
 19                     totalWidth += $(jq).datagrid(‘getColumnOption‘, cfs[i]).width;
 20                }
 21//var totalWidth = this.getColumnModel().getTotalWidth(includeHidden); 22return‘<?xml version="1.0" encoding="utf-8"?>‘ +//xml申明有问题，以修正，注意是utf-8编码，如果是gb2312，需要修改动态页文件的写入编码 23‘<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:o="urn:schemas-microsoft-com:office:office">‘ +
 24‘<o:DocumentProperties><o:Title>‘ + param.title + ‘</o:Title></o:DocumentProperties>‘ +
 25‘<ss:ExcelWorkbook>‘ +
 26‘<ss:WindowHeight>‘ + worksheet.height + ‘</ss:WindowHeight>‘ +
 27‘<ss:WindowWidth>‘ + worksheet.width + ‘</ss:WindowWidth>‘ +
 28‘<ss:ProtectStructure>False</ss:ProtectStructure>‘ +
 29‘<ss:ProtectWindows>False</ss:ProtectWindows>‘ +
 30‘</ss:ExcelWorkbook>‘ +
 31‘<ss:Styles>‘ +
 32‘<ss:Style ss:ID="Default">‘ +
 33‘<ss:Alignment ss:Vertical="Top"  />‘ +
 34‘<ss:Font ss:FontName="arial" ss:Size="10" />‘ +
 35‘<ss:Borders>‘ +
 36‘<ss:Border  ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />‘ +
 37‘<ss:Border  ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />‘ +
 38‘<ss:Border  ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />‘ +
 39‘<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />‘ +
 40‘</ss:Borders>‘ +
 41‘<ss:Interior />‘ +
 42‘<ss:NumberFormat />‘ +
 43‘<ss:Protection />‘ +
 44‘</ss:Style>‘ +
 45‘<ss:Style ss:ID="title">‘ +
 46‘<ss:Borders />‘ +
 47‘<ss:Font />‘ +
 48‘<ss:Alignment  ss:Vertical="Center" ss:Horizontal="Center" />‘ +
 49‘<ss:NumberFormat ss:Format="@" />‘ +
 50‘</ss:Style>‘ +
 51‘<ss:Style ss:ID="headercell">‘ +
 52‘<ss:Font ss:Bold="1" ss:Size="10" />‘ +
 53‘<ss:Alignment  ss:Horizontal="Center" />‘ +
 54‘<ss:Interior ss:Pattern="Solid"  />‘ +
 55‘</ss:Style>‘ +
 56‘<ss:Style ss:ID="even">‘ +
 57‘<ss:Interior ss:Pattern="Solid"  />‘ +
 58‘</ss:Style>‘ +
 59‘<ss:Style ss:Parent="even" ss:ID="evendate">‘ +
 60‘<ss:NumberFormat ss:Format="yyyy-mm-dd" />‘ +
 61‘</ss:Style>‘ +
 62‘<ss:Style ss:Parent="even" ss:ID="evenint">‘ +
 63‘<ss:NumberFormat ss:Format="0" />‘ +
 64‘</ss:Style>‘ +
 65‘<ss:Style ss:Parent="even" ss:ID="evenfloat">‘ +
 66‘<ss:NumberFormat ss:Format="0.00" />‘ +
 67‘</ss:Style>‘ +
 68‘<ss:Style ss:ID="odd">‘ +
 69‘<ss:Interior ss:Pattern="Solid"  />‘ +
 70‘</ss:Style>‘ +
 71‘<ss:Style ss:Parent="odd" ss:ID="odddate">‘ +
 72‘<ss:NumberFormat ss:Format="yyyy-mm-dd" />‘ +
 73‘</ss:Style>‘ +
 74‘<ss:Style ss:Parent="odd" ss:ID="oddint">‘ +
 75‘<ss:NumberFormat ss:Format="0" />‘ +
 76‘</ss:Style>‘ +
 77‘<ss:Style ss:Parent="odd" ss:ID="oddfloat">‘ +
 78‘<ss:NumberFormat ss:Format="0.00" />‘ +
 79‘</ss:Style>‘ +
 80‘</ss:Styles>‘ +
 81             worksheet.xml +
 82‘</ss:Workbook>‘;
 83            },
 84            createWorksheet: function (jq, param) {
 85// Calculate cell data types and extra class names which affect formatting 86var cellType = [];
 87var cellTypeClass = [];
 88//var cm = this.getColumnModel(); 89var totalWidthInPixels = 0;
 90var colXml = ‘‘;
 91var headerXml = ‘‘;
 92var visibleColumnCountReduction = 0;
 93var cfs = $(jq).datagrid(‘getColumnFields‘);
 94var colCount = cfs.length;
 95for (var i = 1; i < colCount; i++) {
 96if (cfs[i] != ‘‘) {
 97var w = $(jq).datagrid(‘getColumnOption‘, cfs[i]).width;
 98                         totalWidthInPixels += w;
 99if (cfs[i] === "") {
100                             cellType.push("None");
101                             cellTypeClass.push("");
102                             ++visibleColumnCountReduction;
103                        }
104else {
105                             colXml += ‘<ss:Column ss:AutoFitWidth="1" ss:Width="130" />‘;
106                             headerXml += ‘<ss:Cell ss:StyleID="headercell">‘ +
107‘<ss:Data ss:Type="String">‘ + $(jq).datagrid(‘getColumnOption‘, cfs[i]).title + ‘</ss:Data>‘ +
108‘<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>‘;
109                             cellType.push("String");
110                             cellTypeClass.push("");
111                        }
112                    }
113                }
114var visibleColumnCount = cellType.length - visibleColumnCountReduction;
115var result = {
116                     height: 9000,
117                     width: Math.floor(totalWidthInPixels * 30) + 50118                };
119var rows = $(jq).datagrid(‘getRows‘);
120// Generate worksheet header details.121var t = ‘<ss:Worksheet ss:Name="‘ + param.title + ‘">‘ +
122‘<ss:Names>‘ +
123‘<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\‘‘ + param.title + ‘\‘!R1:R2" />‘ +
124‘</ss:Names>‘ +
125‘<ss:Table x:FullRows="1" x:FullColumns="1"‘ +
126‘ ss:ExpandedColumnCount="‘ + (visibleColumnCount + 2) +
127‘" ss:ExpandedRowCount="‘ + (rows.length + 2) + ‘">‘ +
128             colXml +
129‘<ss:Row ss:AutoFitHeight="1">‘ +
130             headerXml +
131‘</ss:Row>‘;
132// Generate the data rows from the data in the Store
133//for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) {134for (var i = 0, it = rows, l = it.length; i < l; i++) {
135                     t += ‘<ss:Row>‘;
136var cellClass = (i & 1) ? ‘odd‘ : ‘even‘;
137                     r = it[i];
138var k = 0;
139for (var j = 1; j < colCount; j++) {
140//if ((cm.getDataIndex(j) != ‘‘)141if (cfs[j] != ‘‘) {
142//var v = r[cm.getDataIndex(j)];143var v = r[cfs[j]];
144if (cellType[k] !== "None") {
145                                 t += ‘<ss:Cell ss:StyleID="‘ + cellClass + cellTypeClass[k] + ‘"><ss:Data ss:Type="‘ + cellType[k] + ‘">‘;
146if (cellType[k] == ‘DateTime‘) {
147                                     t += v.format(‘Y-m-d‘);
148                                 } else {
149                                     t += v;
150                                }
151                                 t += ‘</ss:Data></ss:Cell>‘;
152                            }
153                             k++;
154                        }
155                    }
156                     t += ‘</ss:Row>‘;
157                }
158                 result.xml = t + ‘</ss:Table>‘ +
159‘<x:WorksheetOptions>‘ +
160‘<x:PageSetup>‘ +
161‘<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />‘ +
162‘<x:Footer x:Data="Page &P of &N" x:Margin="0.5" />‘ +
163‘<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />‘ +
164‘</x:PageSetup>‘ +
165‘<x:FitToPage />‘ +
166‘<x:Print>‘ +
167‘<x:PrintErrors>Blank</x:PrintErrors>‘ +
168‘<x:FitWidth>1</x:FitWidth>‘ +
169‘<x:FitHeight>32767</x:FitHeight>‘ +
170‘<x:ValidPrinterInfo />‘ +
171‘<x:VerticalResolution>600</x:VerticalResolution>‘ +
172‘</x:Print>‘ +
173‘<x:Selected />‘ +
174‘<x:DoNotDisplayGridlines />‘ +
175‘<x:ProtectObjects>False</x:ProtectObjects>‘ +
176‘<x:ProtectScenarios>False</x:ProtectScenarios>‘ +
177‘</x:WorksheetOptions>‘ +
178‘</ss:Worksheet>‘;
179return result;
180            }
181        });
182     </script>

                 1 <div style="margin-bottom:5px" id="tb">
 2 <a href="#"class="easyui-linkbutton" onclick="return Save_Excel()" iconCls="icon-save" plain="true" title="导出excel文件"></a>
 3 </div>
 4 <table id="dg"></table>
 5 <script>
 6         function Save_Excel() {//导出Excel文件
 7//getExcelXML有一个JSON对象的配置，配置项看了下只有title配置，为excel文档的标题 8var data = $(‘#dg‘).datagrid(‘getExcelXml‘, { title: ‘datagrid import to excel‘ }); //获取datagrid数据对应的excel需要的xml格式的内容
 9//用ajax发动到动态页动态写入xls文件中10var url = ‘datagrid-to-excel.ashx‘; //如果为asp注意修改后缀11             $.ajax({ url: url, data: { data: data }, type: ‘POST‘, dataType: ‘text‘,
12                success: function (fn) {
13                     alert(‘导出excel成功！‘);
14                     window.location = fn; //执行下载操作15                },
16                error: function (xhr) {
17                     alert(‘动态页有问题\nstatus：‘ + xhr.status + ‘\nresponseText：‘ + xhr.responseText)
18                }
19            });
20returnfalse;
21        }
22        $(function () {
23             $(‘#dg‘).datagrid({
24                 singleSelect: true,
25                 toolbar:‘#tb‘,
26                 url: ‘product.json‘, 
27                 fitColumns: true, pagination: true, pageSize: 3,
28                 title: ‘easyui datagrid数据导出excel文件示例‘,
29                 width: 400,
30                 height: 300,
31                 columns: [[{ field: ‘itemid‘, width: 80, title: ‘Item ID‘ },
32                  { field: ‘productname‘, width: 100, editor: ‘text‘, title: ‘Product Name‘ },
33                  { field: ‘listprice‘, width: 80, align: ‘right‘, title: ‘List Pirce‘ },
34                  { field: ‘unitcost‘, width: 80, align: ‘right‘, title: ‘Unit Cost‘}]]
35            });
36        });
37     </script>

                 1 <%@ WebHandler Language="C#" Class="datagrid_to_excel" %>
 2 3using System;
 4using System.Web;
 5using System.IO;
 6using System.Text;
 7publicclass datagrid_to_excel : IHttpHandler
 8{
 9publicvoid ProcessRequest(HttpContext context)
10    {
11string fn = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
12string data = context.Request.Form["data"];
13         File.WriteAllText(context.Server.MapPath(fn), data, Encoding.UTF8);//如果是gb2312的xml申明，第三个编码参数修改为Encoding.GetEncoding(936)1415         context.Response.Write(fn);//返回文件名提供下载16    }
17publicbool IsReusable {
18get {
19returnfalse;
20        }
21    }
2223 }