Kendo UI框架提供了强大的Excel导出功能,通过Grid的saveAsExcel能方便地导出Grid中的数据,而且格式美观大方,使用起来也非常方便。但是在实际使用中不是很理想,主要有以下两个问题:
1. 导出的列数据是原始值
Kendo UI默认导出的是该列的value值,及查出来的值,有时候我们会用template渲染一下导出的列,比如将“Y”显示成“是”,把“N”显示成“否”。而Kendo UI导出的却是Y/N这种只有程序员看得懂的数据库标识,显然不是我们需要的,这种情况非常常见。
2. 不能灵活控制可导出的列
Kendo UI的Excel导出主要看两点,一是该列(column)是field字段,而不是自定义的name;二是该列不是隐藏的(hidden:true)。这样我们无法灵活导出我们需要的列。
为了解决上面两个问题,我查看了Kendo UI的源代码,提取并改进了源代码。主要更改点及使用方法请看下面源代码。主要针对以上两点做了更改,只需要在grid定义columns时加上isExport或exportTemplate即可:
- 在导出数据,先看该列有没有自定义exportTemplate(),没有则看Kendo UI自带的template(),再没有才会导出查出来的值。
- 判断是否导出该列不再看hidden属性,而是看列的isExport属性,如果为false则不导出,其它情况一律导出该列。
经过这两个更改,基本可以应对所有业务场景,可以方便快捷地开发了!
但要注意,Kendo UI自带的导出功能无法应用于导出大量数据,似乎是浏览器的jvm溢出了,建议超过5万条的数据导出还是老老实实写后台导出功能吧。下篇文章将介绍使用POI导出百万级别数据。
js代码下载链接:https://download.csdn.net/download/qq_28830129/10628102
/**
* @summary ExcelExport
* @description 基于Kendo UI框架的Excel导出操作
* 使用方式:
* 1.引入该js,需在kendo ui的js之后
* 2.定义一个grid,通过id获取其JQuery对象
* 3.调用saveAsExclePro()方法
* 如:$("#Grid").saveAsExclePro()
* grid新增column辅助属性说明:
* isExport:
* 作为grid.column的补充属性,为false时该column不导出
* 与grid.saveAsExcle()不同,hidden为true时同样会导出
* exportTemplate:
* 类似grid.column的template属性,是一个函数,参数为dataItem
* 如果不指定该属性,将使用grid.column的template属性的渲染值作为导出值,也没有就以原始Value导出
* 注意:调用该导出方法不会触发grid的导出Excel相关事件
* @version 1.0
* @author chong.luo
*/
!(function ($) {
if (!window.ExcelExport) {
ExcelExport = {
version: '1.0'
};
MyExcelExporter = kendo.Class.extend({
init: function (options) {
options.columns = this._trimColumns(options.columns || []);
this.allColumns = $.map(this._leafColumns(options.columns || []), this._prepareColumn);
//---更改1---更改导出列的筛选,isExport为false不导出
this.columns = $.grep(this.allColumns, function (column) {
if(column.isExport == false){
return false;
}
return true;
//return !column.hidden;
});
this.options = options;
var dataSource = options.dataSource;
if (dataSource instanceof kendo.data.DataSource) {
this.dataSource = new dataSource.constructor($.extend({}, dataSource.options, {
page: options.allPages ? 0 : dataSource.page(),
filter: dataSource.filter(),
pageSize: options.allPages ? dataSource.total() : dataSource.pageSize(),
sort: dataSource.sort(),
group: dataSource.group(),
aggregate: dataSource.aggregate()
}));
var data = dataSource.data();
if (data.length > 0) {
this.dataSource._data = data;
var transport = this.dataSource.transport;
if (dataSource._isServerGrouped() && transport.options && transport.options.data) {
transport.options.data = null;
}
}
} else {
this.dataSource = kendo.data.DataSource.create(dataSource);
}
},
_trimColumns: function (columns) {
var that = this;
return $.grep(columns, function (column) {
var result = !!column.field;
if (!result && column.columns) {
result = that._trimColumns(column.columns).length > 0;
}
return result;
});
},
_leafColumns: function (columns) {
var result = [];
for (var idx = 0; idx < columns.length; idx++) {
if (!columns[idx].columns) {
result.push(columns[idx]);
continue;
}
result = result.concat(this._leafColumns(columns[idx].columns));
}
return result;
},
workbook: function () {
return $.Deferred($.proxy(function (d) {
this.dataSource.fetch().then($.proxy(function () {
var workbook = {
sheets: [{
columns: this._columns(),
rows: this._rows(),
freezePane: this._freezePane(),
filter: this._filter()
}]
};
d.resolve(workbook, this.dataSource.view());
}, this));
}, this)).promise();
},
_prepareColumn: function (column) {
if (!column.field) {
return;
}
var value = function (dataItem) {
return dataItem.get(column.field);
};
var values = null;
if (column.values) {
values = {};
$.each(column.values, function () {
values[this.value] = this.text;
});
value = function (dataItem) {
return values[dataItem.get(column.field)];
};
}
return $.extend({}, column, {
value: value,
values: values,
groupHeaderTemplate: kendo.template(column.groupHeaderTemplate || '#= title #: #= value #'),
groupFooterTemplate: column.groupFooterTemplate ? kendo.template(column.groupFooterTemplate) : null,
footerTemplate: column.footerTemplate ? kendo.template(column.footerTemplate) : null
});
},
_filter: function () {
if (!this.options.filterable) {
return null;
}
var depth = this._depth();
return {
from: depth,
to: depth + this.columns.length - 1
};
},
_dataRow: function (dataItem, level, depth) {
if (this._hierarchical()) {
level = this.dataSource.level(dataItem) + 1;
}
var cells = [];
for (var li = 0; li < level; li++) {
cells[li] = {
background: '#dfdfdf',
color: '#333'
};
}
if (depth && dataItem.items) {
var column = $.grep(this.allColumns, function (column) {
return column.field == dataItem.field;
})[0];
var title = column && column.title ? column.title : dataItem.field;
var template = column ? column.groupHeaderTemplate : null;
var value = title + ': ' + dataItem.value;
var group = $.extend({
title: title,
field: dataItem.field,
value: column && column.values ? column.values[dataItem.value] : dataItem.value,
aggregates: dataItem.aggregates
}, dataItem.aggregates[dataItem.field]);
if (template) {
value = template(group);
}
cells.push({
value: value,
background: '#dfdfdf',
color: '#333',
colSpan: this.columns.length + depth - level
});
var rows = this._dataRows(dataItem.items, level + 1);
rows.unshift({
type: 'group-header',
cells: cells
});
return rows.concat(this._footer(dataItem));
} else {
var dataCells = [];
for (var ci = 0; ci < this.columns.length; ci++) {
dataCells[ci] = this._cell(dataItem, this.columns[ci]);
}
if (this._hierarchical()) {
dataCells[0].colSpan = depth - level + 1;
}
return [{
type: 'data',
cells: cells.concat(dataCells)
}];
}
},
_dataRows: function (dataItems, level) {
var depth = this._depth();
var rows = [];
for (var i = 0; i < dataItems.length; i++) {
rows.push.apply(rows, this._dataRow(dataItems[i], level, depth));
}
return rows;
},
_footer: function (dataItem) {
var rows = [];
var footer = false;
var cells = $.map(this.columns, $.proxy(function (column) {
if (column.groupFooterTemplate) {
footer = true;
return {
background: '#dfdfdf',
color: '#333',
value: column.groupFooterTemplate($.extend({}, this.dataSource.aggregates(), dataItem.aggregates, dataItem.aggregates[column.field]))
};
} else {
return {
background: '#dfdfdf',
color: '#333'
};
}
}, this));
if (footer) {
rows.push({
type: 'group-footer',
cells: $.map(new Array(this.dataSource.group().length), function () {
return {
background: '#dfdfdf',
color: '#333'
};
}).concat(cells)
});
}
return rows;
},
_isColumnVisible: function (column) {
return this._visibleColumns([column]).length > 0 && (column.field || column.columns);
},
_visibleColumns: function (columns) {
var that = this;
return $.grep(columns, function (column) {
//---更改1---更改导出列的筛选,isExport为false不导出
if(column.isExport == false){
var result = false;
}else{
var result = true;
}
//var result = !column.hidden;
if (result && column.columns) {
result = that._visibleColumns(column.columns).length > 0;
}
return result;
});
},
_headerRow: function (row, groups) {
var headers = $.map(row.cells, function (cell) {
return {
background: '#7a7a7a',
color: '#fff',
value: cell.title,
colSpan: cell.colSpan > 1 ? cell.colSpan : 1,
rowSpan: row.rowSpan > 1 && !cell.colSpan ? row.rowSpan : 1
};
});
if (this._hierarchical()) {
headers[0].colSpan = this._depth() + 1;
}
return {
type: 'header',
cells: $.map(new Array(groups.length), function () {
return {
background: '#7a7a7a',
color: '#fff'
};
}).concat(headers)
};
},
_prependHeaderRows: function (rows) {
var groups = this.dataSource.group();
var headerRows = [{
rowSpan: 1,
cells: [],
index: 0
}];
this._prepareHeaderRows(headerRows, this.options.columns);
for (var idx = headerRows.length - 1; idx >= 0; idx--) {
rows.unshift(this._headerRow(headerRows[idx], groups));
}
},
_prepareHeaderRows: function (rows, columns, parentCell, parentRow) {
var row = parentRow || rows[rows.length - 1];
var childRow = rows[row.index + 1];
var totalColSpan = 0;
var column;
var cell;
for (var idx = 0; idx < columns.length; idx++) {
column = columns[idx];
if (this._isColumnVisible(column)) {
cell = {
title: column.title || column.field,
colSpan: 0
};
row.cells.push(cell);
if (column.columns && column.columns.length) {
if (!childRow) {
childRow = {
rowSpan: 0,
cells: [],
index: rows.length
};
rows.push(childRow);
}
cell.colSpan = this._trimColumns(this._visibleColumns(column.columns)).length;
this._prepareHeaderRows(rows, column.columns, cell, childRow);
totalColSpan += cell.colSpan - 1;
row.rowSpan = rows.length - row.index;
}
}
}
if (parentCell) {
parentCell.colSpan += totalColSpan;
}
},
_rows: function () {
var groups = this.dataSource.group();
var rows = this._dataRows(this.dataSource.view(), 0);
if (this.columns.length) {
this._prependHeaderRows(rows);
var footer = false;
var cells = $.map(this.columns, $.proxy(function (column) {
if (column.footerTemplate) {
footer = true;
var aggregates = this.dataSource.aggregates();
return {
background: '#dfdfdf',
color: '#333',
value: column.footerTemplate($.extend({}, aggregates, aggregates[column.field]))
};
} else {
return {
background: '#dfdfdf',
color: '#333'
};
}
}, this));
if (footer) {
rows.push({
type: 'footer',
cells: $.map(new Array(groups.length), function () {
return {
background: '#dfdfdf',
color: '#333'
};
}).concat(cells)
});
}
}
return rows;
},
_headerDepth: function (columns) {
var result = 1;
var max = 0;
for (var idx = 0; idx < columns.length; idx++) {
if (columns[idx].columns) {
var temp = this._headerDepth(columns[idx].columns);
if (temp > max) {
max = temp;
}
}
}
return result + max;
},
_freezePane: function () {
var columns = this._visibleColumns(this.options.columns || []);
var colSplit = this._visibleColumns(this._trimColumns(this._leafColumns($.grep(columns, function (column) {
return column.locked;
})))).length;
return {
rowSplit: this._headerDepth(columns),
colSplit: colSplit ? colSplit + this.dataSource.group().length : 0
};
},
_cell: function (dataItem, column) {
//-----更改2-----导出excel的值由temple决定,如果没有temple才取value
if(!!column.exportTemplate){//存在自定义导出数据模板
return { value: column.exportTemplate(dataItem)};
}
return { value: (!!column.template)?column.template(dataItem):column.value(dataItem) };
//return { value: column.value(dataItem) };
},
_hierarchical: function () {
return this.options.hierarchy && this.dataSource.level;
},
_depth: function () {
var dataSource = this.dataSource;
var depth = 0;
var view, i, level;
if (this._hierarchical()) {
view = dataSource.view();
for (i = 0; i < view.length; i++) {
level = dataSource.level(view[i]);
if (level > depth) {
depth = level;
}
}
depth++;
} else {
depth = dataSource.group().length;
}
return depth;
},
_columns: function () {
var depth = this._depth();
var columns = $.map(new Array(depth), function () {
return { width: 20 };
});
return columns.concat($.map(this.columns, function (column) {
return {
width: parseInt(column.width, 10),
autoWidth: column.width ? false : true
};
}));
}
});
//定义一个kendo对象:Excle
Excel = kendo.Class.extend({
/*extend: function (proto) {
proto.events.push('excelExport');
proto.options.excel = $.extend(proto.options.excel, this.options);
proto.saveAsExcel = this.saveAsExcel;
},*/
options: {
proxyURL: '',
allPages: true,
filterable: true,
fileName: 'list.xlsx'
},
saveAsExcel: function (grid) {
//console.log("--------开始进行导出操作,时间"+ kendo.toString(new Date(), "yyyy-MM-dd HH:mm:ss") +"----------");
var excel = grid.options.excel || {};
var exporter = new MyExcelExporter({
columns: grid.columns,
dataSource: grid.dataSource,
allPages: excel.allPages,
filterable: excel.filterable,
hierarchy: excel.hierarchy
});
exporter.workbook().then($.proxy(function (book, data) {
/*if (!this.trigger('excelExport', {
workbook: book,
data: data
})) {*/
var workbook = new kendo.ooxml.Workbook(book);
kendo.saveAs({
dataURI: workbook.toDataURL(),
fileName: book.fileName || excel.fileName,
proxyURL: excel.proxyURL,
forceProxy: excel.forceProxy
});
//}
}, this));
}
})
//给JQuery对象添加全局方法
$.fn.saveAsExclePro = function(){
//只允许Kendo UI的Grid组件调用
if($(this).data('kendoGrid') instanceof kendo.ui.Grid){
Excel.prototype.saveAsExcel($(this).data('kendoGrid'));
}
}
}
})(jQuery)