首页 > 代码库 > easyui datagrid 导出excel
easyui datagrid 导出excel
本片文章是介绍如何用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=http://www.mamicode.com/"Page &P of &N" x:Margin="0.5" />' +>
用例//导出
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 = http://www.mamicode.com/context.Request.Form["data"];>
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);
}
}
}
代码中都有注释，就不具体详解了，有问题欢迎讨论。
easyui datagrid 导出excel
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。