Jquery easyui datagrid 导出Excel
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) { 14 var worksheet = this.createWorksheet(jq, param); 15 //alert($(jq).datagrid(\'getColumnFields\')); 16 var totalWidth = 0; 17 var cfs = $(jq).datagrid(\'getColumnFields\'); 18 for (var i = 1; i < cfs.length; i++) { 19 totalWidth += $(jq).datagrid(\'getColumnOption\', cfs[i]).width; 20 } 21 //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden); 22 return \'<?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 86 var cellType = []; 87 var cellTypeClass = []; 88 //var cm = this.getColumnModel(); 89 var totalWidthInPixels = 0; 90 var colXml = \'\'; 91 var headerXml = \'\'; 92 var visibleColumnCountReduction = 0; 93 var cfs = $(jq).datagrid(\'getColumnFields\'); 94 var colCount = cfs.length; 95 for (var i = 1; i < colCount; i++) { 96 if (cfs[i] != \'\') { 97 var w = $(jq).datagrid(\'getColumnOption\', cfs[i]).width; 98 totalWidthInPixels += w; 99 if (cfs[i] === "") { 100 cellType.push("None"); 101 cellTypeClass.push(""); 102 ++visibleColumnCountReduction; 103 } 104 else { 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 } 114 var visibleColumnCount = cellType.length - visibleColumnCountReduction; 115 var result = { 116 height: 9000, 117 width: Math.floor(totalWidthInPixels * 30) + 50 118 }; 119 var rows = $(jq).datagrid(\'getRows\'); 120 // Generate worksheet header details. 121 var 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++) { 134 for (var i = 0, it = rows, l = it.length; i < l; i++) { 135 t += \'<ss:Row>\'; 136 var cellClass = (i & 1) ? \'odd\' : \'even\'; 137 r = it[i]; 138 var k = 0; 139 for (var j = 1; j < colCount; j++) { 140 //if ((cm.getDataIndex(j) != \'\') 141 if (cfs[j] != \'\') { 142 //var v = r[cm.getDataIndex(j)]; 143 var v = r[cfs[j]]; 144 if (cellType[k] !== "None") { 145 t += \'<ss:Cell ss:StyleID="\' + cellClass + cellTypeClass[k] + \'"><ss:Data ss:Type="\' + cellType[k] + \'">\'; 146 if (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>\'; 179 return result; 180 } 181 }); 182 </script>
View Code
实例：
Html:
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文档的标题 8 var data = $(\'#dg\').datagrid(\'getExcelXml\', { title: \'datagrid import to excel\' }); //获取datagrid数据对应的excel需要的xml格式的内容 9 //用ajax发动到动态页动态写入xls文件中 10 var 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 }); 20 return false; 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>
View Code
asp.net后台代码：
1 <%@ WebHandler Language="C#" Class="datagrid_to_excel" %> 2 3 using System; 4 using System.Web; 5 using System.IO; 6 using System.Text; 7 public class datagrid_to_excel : IHttpHandler 8 { 9 public void ProcessRequest(HttpContext context) 10 { 11 string fn = DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; 12 string data = context.Request.Form["data"]; 13 File.WriteAllText(context.Server.MapPath(fn), data, Encoding.UTF8);//如果是gb2312的xml申明，第三个编码参数修改为Encoding.GetEncoding(936) 14 15 context.Response.Write(fn);//返回文件名提供下载 16 } 17 public bool IsReusable { 18 get { 19 return false; 20 } 21 } 22 23 }
View Code
效果图：
注意：如果在使用中报“从客户端(exportContent=”<xml version=”1.0″ e…”)中检测到有潜在危险的 Request.Form 值。”，需要修改一下webconfig文件
在Web.Config文件中的配置节</system.web>之前加上如下一句配置就可以了
<httpRuntime requestValidationMode=”2.0″ />
版权声明：本文为tahn30625原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。