easyui datagrid 导出excel
来源：互联网 发布：如何投诉广电网络 编辑：程序博客网 时间：2024/06/10 07:38
本片文章是介绍如何用ajax的方式将easyui中的datagrid数据导出到excel中。
声明：该代码是在参考前人的代码写出来的，并非我原创，我只是新增了些功能
。
新增部分：
1. 对于列是枚举列的可以设置枚举值，导出时会自动替换列中的枚举值，详细见用例代码。
2. 新增导出时不导出首列或末列指定个数的列，详细接用例代码。
下面是插件源码：
/**Jquery easyui datagrid js导出excel修改自extgrid导出excel* allows for downloading of grid data (store) directly into excel* Method: extracts data of gridPanel store, uses columnModel to construct XML excel document,* converts to Base64, then loads everything into a data URL link.** @author Guo Jing <iTrucks>* 2015-01-12**/$.extend($.fn.datagrid.methods, { getExcelXml: function (jq, param) { var worksheet = this.createWorksheet(jq, param); //alert($(jq).datagrid('getColumnFields')); var totalWidth = 0; var cfs = $(jq).datagrid('getColumnFields'); /* **判断是否删除第一行以及最后一行************begin****************/ //console.dir(JSON.stringify(param.enums[0].type)); if (param.removeFirstCol) { if (param.removeLastCol) { for (var i = param.removeFirstCol; i < cfs.length - param.removeLastCol; i++) { totalWidth += $(jq).datagrid('getColumnOption', cfs[i]).width; } } else { for (var i = param.removeFirstCol; i < cfs.length; i++) { totalWidth += $(jq).datagrid('getColumnOption', cfs[i]).width; } } } else { if (param.removeLastCol) { for (var i = 0; i < cfs.length - param.removeLastCol; i++) { totalWidth += $(jq).datagrid('getColumnOption', cfs[i]).width; } } else { for (var i = 0; i < cfs.length; i++) { totalWidth += $(jq).datagrid('getColumnOption', cfs[i]).width; } } } /*****************************end*********************************/ //var totalWidth = this.getColumnModel().getTotalWidth(includeHidden); return '<?xml version="1.0" encoding="utf-8"?>' +//xml申明有问题，以修正，注意是utf-8编码，如果是gb2312，需要修改动态页文件的写入编码 '<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">' + '<o:DocumentProperties><o:Title>' + param.title + '</o:Title></o:DocumentProperties>' + '<ss:ExcelWorkbook>' + '<ss:WindowHeight>' + worksheet.height + '</ss:WindowHeight>' + '<ss:WindowWidth>' + worksheet.width + '</ss:WindowWidth>' + '<ss:ProtectStructure>False</ss:ProtectStructure>' + '<ss:ProtectWindows>False</ss:ProtectWindows>' + '</ss:ExcelWorkbook>' + '<ss:Styles>' + '<ss:Style ss:ID="Default">' + '<ss:Alignment ss:Vertical="Top" />' + '<ss:Font ss:FontName="arial" ss:Size="10" />' + '<ss:Borders>' + '<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top" />' + '<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom" />' + '<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left" />' + '<ss:Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right" />' + '</ss:Borders>' + '<ss:Interior />' + '<ss:NumberFormat />' + '<ss:Protection />' + '</ss:Style>' + '<ss:Style ss:ID="title">' + '<ss:Borders />' + '<ss:Font />' + '<ss:Alignment ss:Vertical="Center" ss:Horizontal="Center" />' + '<ss:NumberFormat ss:Format="@" />' + '</ss:Style>' + '<ss:Style ss:ID="headercell">' + '<ss:Font ss:Bold="1" ss:Size="10" />' + '<ss:Alignment ss:Horizontal="Center" />' + '<ss:Interior ss:Pattern="Solid" />' + '</ss:Style>' + '<ss:Style ss:ID="even">' + '<ss:Interior ss:Pattern="Solid" />' + '</ss:Style>' + '<ss:Style ss:Parent="even" ss:ID="evendate">' + '<ss:NumberFormat ss:Format="yyyy-mm-dd" />' + '</ss:Style>' + '<ss:Style ss:Parent="even" ss:ID="evenint">' + '<ss:NumberFormat ss:Format="0" />' + '</ss:Style>' + '<ss:Style ss:Parent="even" ss:ID="evenfloat">' + '<ss:NumberFormat ss:Format="0.00" />' + '</ss:Style>' + '<ss:Style ss:ID="odd">' + '<ss:Interior ss:Pattern="Solid" />' + '</ss:Style>' + '<ss:Style ss:Parent="odd" ss:ID="odddate">' + '<ss:NumberFormat ss:Format="yyyy-mm-dd" />' + '</ss:Style>' + '<ss:Style ss:Parent="odd" ss:ID="oddint">' + '<ss:NumberFormat ss:Format="0" />' + '</ss:Style>' + '<ss:Style ss:Parent="odd" ss:ID="oddfloat">' + '<ss:NumberFormat ss:Format="0.00" />' + '</ss:Style>' + '</ss:Styles>' + worksheet.xml + '</ss:Workbook>'; }, createWorksheet: function (jq, param) { // Calculate cell data types and extra class names which affect formatting var cellType = []; var cellTypeClass = []; //var cm = this.getColumnModel(); var totalWidthInPixels = 0; var colXml = ''; var headerXml = ''; var visibleColumnCountReduction = 0; var cfs = $(jq).datagrid('getColumnFields'); var colCount = cfs.length; var cstartIndex = 0, cendIndex = colCount; var colWidthEnumsIndex = [];//记录枚举列的下标和枚举数组对应的下标。 //alert(param.removeFirstCol + "," + param.removeLastCol); if (param.removeFirstCol) { cstartIndex = param.removeFirstCol; if (param.removeLastCol) { cendIndex = colCount - param.removeLastCol; } else { cendIndex = colCount; } } else { cstartIndex = 0; if (param.removeLastCol) { cendIndex = colCount - param.removeLastCol; } else { cendIndex = colCount; } } for (var i = cstartIndex; i < cendIndex; i++) { if (cfs[i] != '') { var w = $(jq).datagrid('getColumnOption', cfs[i]).width; totalWidthInPixels += w; if (cfs[i] === "") { cellType.push("None"); cellTypeClass.push(""); ++visibleColumnCountReduction; } else { colXml += '<ss:Column ss:AutoFitWidth="1" ss:Width="130" />'; headerXml += '<ss:Cell ss:StyleID="headercell">' + '<ss:Data ss:Type="String">' + $(jq).datagrid('getColumnOption', cfs[i]).title.replace("<br>","") + '</ss:Data>' + '<ss:NamedCell ss:Name="Print_Titles" /></ss:Cell>'; cellType.push("String"); cellTypeClass.push(""); if (param.enums) { //遍历枚举数组，检测是否有列属于枚举类型 for (var j = 0; j < param.enums.length; j++) { if ($(jq).datagrid('getColumnOption', cfs[i]).title == param.enums[j].type) { //console.dir($(jq).datagrid('getColumnOption', cfs[i]).title + "," + i); colWidthEnumsIndex.push({ colIndex: i, enumIndex: j }); } } } } } } //console.dir(JSON.stringify(colWidthEnumsIndex.length)); var visibleColumnCount = cellType.length - visibleColumnCountReduction; var result = { height: 9000, width: Math.floor(totalWidthInPixels * 30) + 50 }; var rows = $(jq).datagrid('getRows'); // Generate worksheet header details. var t = '<ss:Worksheet ss:Name="' + param.title + '">' + '<ss:Names>' + '<ss:NamedRange ss:Name="Print_Titles" ss:RefersTo="=\'' + param.title + '\'!R1:R2" />' + '</ss:Names>' + '<ss:Table x:FullRows="1" x:FullColumns="1"' + ' ss:ExpandedColumnCount="' + (visibleColumnCount + 2) + '" ss:ExpandedRowCount="' + (rows.length + 2) + '">' + colXml + '<ss:Row ss:AutoFitHeight="1">' + headerXml + '</ss:Row>'; // Generate the data rows from the data in the Store //for (var i = 0, it = this.store.data.items, l = it.length; i < l; i++) { for (var i = 0, it = rows, l = it.length; i < l; i++) { t += '<ss:Row>'; var cellClass = (i & 1) ? 'odd' : 'even'; r = it[i]; var k = 0; for (var j = cstartIndex; j < cendIndex; j++) { //if ((cm.getDataIndex(j) != '') if (cfs[j] != '') { //var v = r[cm.getDataIndex(j)]; var v = r[cfs[j]]; if (cellType[k] !== "None") { t += '<ss:Cell ss:StyleID="' + cellClass + cellTypeClass[k] + '"><ss:Data ss:Type="' + cellType[k] + '">'; if (cellType[k] == 'DateTime') { t += v.format('Y-m-d'); } else { //判断该列是否属于枚举，如果是，则进行转换 for (var lp = 0; lp < colWidthEnumsIndex.length; lp++) { if (j == colWidthEnumsIndex[lp].colIndex) {//当前列是枚举列 //console.dir(JSON.stringify(param.enums[l].value)); //遍历枚举中的值，对元数据进行替换 for (var mp = 0; mp < param.enums[lp].value.length; mp++) { if (v == param.enums[lp].value[mp].key) { v = param.enums[lp].value[mp].text; } //switch (v) { // case param.enums[l].value[m].key: // v = param.enums[l].value[m].text; // break; //} } } } t += v; } t += '</ss:Data></ss:Cell>'; } k++; } } t += '</ss:Row>'; } result.xml = t + '</ss:Table>' + '<x:WorksheetOptions>' + '<x:PageSetup>' + '<x:Layout x:CenterHorizontal="1" x:Orientation="Landscape" />' + '<x:Footer x:Data="Page &P of &N" x:Margin="0.5" />' + '<x:PageMargins x:Top="0.5" x:Right="0.5" x:Left="0.5" x:Bottom="0.8" />' + '</x:PageSetup>' + '<x:FitToPage />' + '<x:Print>' + '<x:PrintErrors>Blank</x:PrintErrors>' + '<x:FitWidth>1</x:FitWidth>' + '<x:FitHeight>32767</x:FitHeight>' + '<x:ValidPrinterInfo />' + '<x:VerticalResolution>600</x:VerticalResolution>' + '</x:Print>' + '<x:Selected />' + '<x:DoNotDisplayGridlines />' + '<x:ProtectObjects>False</x:ProtectObjects>' + '<x:ProtectScenarios>False</x:ProtectScenarios>' + '</x:WorksheetOptions>' + '</ss:Worksheet>'; return result; }});用例
//导出 function Save_Excel() {//导出Excel文件 //处理列中有枚举类型的值，传入枚举名值对程序将自动进行替换 var enumsVal = [{ type: "车辆类型", value: [{ key: 10, text: "普通厢式货车" }, { key: 20, text: "城市配送标准车" }, { key: 30, text: "冷藏冷冻车" }, { key: 40, text: "其他配送车辆" }] }, { type: "通行证类型", value: [{ key: 10, text: "特通" }, { key: 20, text: "甲通" }, { key: 30, text: "乙通" }] }, { type: "燃料类型", value: [{ key: 10, text: "汽油" }, { key: 20, text: "CNG" }, { key: 30, text: "柴油" }, { key: 40, text: "LNG" }, { key: 50, text: "电动" }] }, { type: "状态", value: [{ key: true, text: "已启用" }, { key: false, text: "未启用" }] } ]; //getExcelXML有一个JSON对象的配置，配置项看了下只有title配置，为excel文档的标题 var data = $('#m_dgvList').datagrid('getExcelXml', { title: 'datagrid import to excel', removeFirstCol: 0, removeLastCol: 1, enums: enumsVal }); //获取datagrid数据对应的excel需要的xml格式的内容 //用ajax发动到动态页动态写入xls文件中 var url = '../../datagrid-export-excel.ashx'; //如果为asp注意修改后缀 var dlurl = "../../DownloadFile.ashx"; $.ajax({ url: url, data: { data: data }, type: 'POST', dataType: 'text', success: function (fn) { window.location = dlurl + "?FileName=车辆查询.xls&FilePath=" + fn; //执行下载操作 //window.open(dlurl + "?FileName=车辆查询.xls&FilePath=" + fn); }, error: function (xhr) { alert('动态页有问题\nstatus：' + xhr.status + '\nresponseText：' + xhr.responseText) } }); return false; }datagrid-export-excel.ashx:<%@ WebHandler Language="C#" Class="datagrid_export_excel" %>using System;using System.Web;public class datagrid_export_excel : IHttpHandler { public void ProcessRequest(HttpContext context) { string fn = System.Configuration.ConfigurationManager.AppSettings["temFilePath"].ToString() + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; string data = context.Request.Form["data"]; System.IO.File.WriteAllText(context.Server.MapPath(fn), data, System.Text.Encoding.Unicode);//如果是gb2312的xml申明，第三个编码参数修改为Encoding.GetEncoding(936) context.Response.Write(fn);//返回文件名提供下载 } public bool IsReusable { get { return false; } }}DownLoadFile.ashx:
<%@ WebHandler Language="C#" Class="DownLoadFile" %>using System;using System.Web;using System.Linq;using System.Web.SessionState;using System.IO;public class DownLoadFile : IHttpHandler, IReadOnlySessionState{ public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/plain"; //获取文件路径 string path = context.Request["FilePath"]; if (string.IsNullOrEmpty(path)) throw new Exception("非法请求！"); //获取文件名 var fileName = context.Request["FileName"]; if (string.IsNullOrEmpty(fileName)) fileName = path.Split('/').Last().ToString(); //获取虚拟目录的物理路径。 path = context.Server.MapPath(path); fileName = fileName.Replace(" ", ""); if (System.IO.File.Exists(path)) { //删除除了本次请求的历史文件 DeleteOldFiles(path, context.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["temFilePath"].ToString())); System.IO.FileInfo fileInfo = new System.IO.FileInfo(path); context.Response.Clear(); context.Response.ClearContent(); context.Response.ClearHeaders(); //根据不同的浏览器对文件名进行不同的编码方式 if (context.Request.UserAgent.ToLower().IndexOf("msie", System.StringComparison.Ordinal) > -1)//IE浏览器 { context.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName)); } else //其他浏览器 { context.Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlDecode(fileName, System.Text.Encoding.UTF8)); } context.Response.AddHeader("Content-Length", fileInfo.Length.ToString()); context.Response.AddHeader("Content-Transfer-Encoding", "binary"); context.Response.ContentType = "application/octet-stream;charset=gbk"; context.Response.ContentEncoding = System.Text.Encoding.UTF8; context.Response.WriteFile(fileInfo.FullName); context.Response.Flush(); context.Response.End(); } else { context.Response.ContentType = "text/html"; context.Response.Write("<script type='text/javascript'>alert('对不起，文件已不存在。');window.close();</script>"); } } public bool IsReusable { get { return false; } } /// <summary> /// 删除目录下指定文件外的所有文件 /// </summary> /// <param name="fileName"></param> /// <param name="filePath"></param> public void DeleteOldFiles(string fileName, string filePath) { try { DirectoryInfo di = new DirectoryInfo(filePath); FileInfo[] fi = di.GetFiles();//获得目录下文件 foreach (FileInfo f in fi) { if (f.FullName != fileName) { //判断指定文件是否存在 File.Delete(f.FullName); } } } catch (Exception ex) { HttpContext.Current.Response.Write(ex.Message); } }}代码中都有注释，就不具体详解了，有问题欢迎讨论。
0 0
- easyui datagrid 导出excel
- EasyUI----DataGrid 导出 Excel
- easyui datagrid 导出Excel
- 实现easyui的datagrid导出为excel
- 实现easyui的datagrid导出为excel
- easyUI datagrid导出excel（所见即所得）
- 实现easyui的datagrid导出为excel
- easyui-datagrid-JavaScript-导出Excel、合并单元格
- easyui的datagrid导出为excel
- easyui Jquery datagrid 导出Excel(转)
- easyUI datagrid数据导出为Excel
- 实现easyui的datagrid导出为excel
- easyui datagrid 导出excel及乱码解决
- easyui datagrid标题和数据 导出excel
- jquery easyui datagrid纯js导出到excel
- 实现easyui的datagrid导出为excel，已处理导出乱码
- easyui datagrid 增删改查分页 导出 先上传后导入 NPOI批量导入 导出EXCEL
- DataGrid导出为Excel
- 图解Android - Zygote, System Server 启动分析 .
- SQL示例
- 地宫寻宝
- unity学习之Unity3D 脚本常用方法快速参考
- 【Qt5开发及实例】22、文件浏览器
- easyui datagrid 导出excel
- 图结构（八）- Prim算法
- filter用法
- 枕叶癫痫治疗 01iycpu0
- Unity3D Shader官方教程翻译Unity3D 的渲染管道
- cocos2d-x 2.x版本文字描边研究01_使用shader描边
- 啊哈C——学习2.7练习
- [3D理论] Unity3D技术之为 Android 构建插件
- unity3d 学习之EZGUI简易教程
