583 lines
17 KiB
JavaScript
583 lines
17 KiB
JavaScript
/*
|
||
* @Author: Jeffrey Wang
|
||
* @Desc: 整理强大的 SheetJS 功能，依赖 XLSX.js 和 FileSaver
|
||
* @Version: v1.4
|
||
* @Date: 2018-03-24 09:54:17
|
||
* @Last Modified by: Jeffrey Wang
|
||
* @Last Modified time: 2019-01-15 11:49:09
|
||
*/
|
||
layui.define(['jquery'], function(exports){
|
||
var $ = layui.jquery;
|
||
exports('excel', {
|
||
/**
|
||
* 兼容老版本的导出函数
|
||
* @param {[type]} data [description]
|
||
* @param {[type]} filename [description]
|
||
* @param {[type]} type [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
downloadExl: function(data, filename, type) {
|
||
type = type ? type : 'xlsx';
|
||
this.exportExcel({sheet1: data}, filename+'.'+type, type, null);
|
||
},
|
||
/**
|
||
* 导出Excel并弹出下载框，具体使用方法和范围请参考文档
|
||
* @param data object
|
||
* @param {[type]} filename [description]
|
||
* @param {[type]} type [description]
|
||
* @param {[type]} opt [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
exportExcel : function(data, filename, type, opt) {
|
||
type = type ? type : 'xlsx';
|
||
filename = filename ? filename : '导出数据.'+type;
|
||
|
||
// 创建一个 XLSX 对象
|
||
var wb = XLSX.utils.book_new();
|
||
// 1. 定义excel对的基本属性
|
||
var Props = {
|
||
Title: filename,
|
||
Subject: 'Export From web browser',
|
||
Author: "excel.wj2015.com",
|
||
Manager: '',
|
||
Company: '',
|
||
Category: '',
|
||
Keywords: '',
|
||
Comments: '',
|
||
LastAuthor: '',
|
||
CreatedData: new Date(),
|
||
};
|
||
opt && opt.Props && (Props = $.extend(Props, opt.Props));
|
||
// 默认进行压缩
|
||
wb.compression = opt ? opt.compression : true
|
||
if(wb.compression !== false) {
|
||
wb.compression = true
|
||
}
|
||
wb.Props = Props;
|
||
// 特殊属性实现，比如合并单元格
|
||
var wbExtend = {
|
||
'!merges': null
|
||
,'!margins': null
|
||
,'!cols': null
|
||
,'!rows': null
|
||
,'!protect': null
|
||
,'!autofilter': null
|
||
};
|
||
opt && opt.extend && (wbExtend = $.extend(wbExtend, opt.extend));
|
||
// 清理空配置
|
||
for (var key in wbExtend) {
|
||
if (!wbExtend.hasOwnProperty(key)) {
|
||
continue;
|
||
}
|
||
if (!wbExtend[key]) {
|
||
delete wbExtend[key];
|
||
}
|
||
}
|
||
|
||
// 判断 data 如果是 sheet 级别数据，自动加 sheet1
|
||
if ($.isArray(data)) {
|
||
data = {sheet1: data};
|
||
}
|
||
|
||
for(var sheet_name in data) {
|
||
if (!data.hasOwnProperty(sheet_name)) {
|
||
continue;
|
||
}
|
||
var content = data[sheet_name];
|
||
// 2. 设置sheet名称
|
||
wb.SheetNames.push(sheet_name);
|
||
// 3. 分配工作表对象到 sheet
|
||
var is_aoa = false;
|
||
if (content.length && content[0] && $.isArray(content[0])) {
|
||
is_aoa = true;
|
||
}
|
||
if (is_aoa) {
|
||
ws = XLSX.utils.aoa_to_sheet(content);
|
||
} else {
|
||
var option = {};
|
||
if (content.length) {
|
||
option.headers = content.unshift();
|
||
option.skipHeader = true;
|
||
// 分离并重组样式
|
||
var splitRes = this.splitContent(content);
|
||
}
|
||
var ws = XLSX.utils.json_to_sheet(content, option);
|
||
// 特殊属性，支持单独设置某个sheet的属性
|
||
if (wbExtend[sheet_name]) {
|
||
$.extend(ws, wbExtend[sheet_name]);
|
||
} else {
|
||
$.extend(ws, wbExtend);
|
||
}
|
||
// 合并样式
|
||
if (typeof splitRes !== 'undefined') {
|
||
this.mergeCellOpt(ws, splitRes.style);
|
||
}
|
||
}
|
||
wb.Sheets[sheet_name] = ws;
|
||
};
|
||
|
||
// 4. 输出工作表
|
||
var wbout = XLSX.write(wb, {bookType: type, type: 'binary', cellStyles: true, compression: wb.compression});
|
||
|
||
// 5. 跨浏览器支持，采用 FileSaver 三方库
|
||
saveAs(new Blob([this.s2ab(wbout)], {type: "application/octet-stream"}), filename);
|
||
},
|
||
/**
|
||
* 分离内容和样式
|
||
* @param {[type]} content [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
splitContent: function(content) {
|
||
var styleContent = {};
|
||
// 扫描每个单元格，如果是对象则等表格转换完毕后分离出来重新赋值
|
||
for (var line = 0; line < content.length; line++) {
|
||
var lineData = content[line];
|
||
var rowIndex = 0;
|
||
for (var row in lineData) {
|
||
if (!lineData.hasOwnProperty(row)) {
|
||
continue;
|
||
}
|
||
var rowData = lineData[row];
|
||
if (typeof rowData === 'object') {
|
||
// typeof null == object
|
||
if (rowData !== null) {
|
||
styleContent[this.numToTitle(rowIndex+1)+(parseInt(line)+1)] = rowData;
|
||
} else {
|
||
lineData[row] = '';
|
||
}
|
||
} else {
|
||
// JeffreyWang 2019-03-10针对 0 的hack处理
|
||
if (rowData === 0) {
|
||
rowData = {
|
||
v: '0',
|
||
s: {
|
||
alignment: {
|
||
horizontal: 'right'
|
||
}
|
||
}
|
||
}
|
||
}
|
||
styleContent[this.numToTitle(rowIndex+1)+(parseInt(line)+1)] = rowData;
|
||
}
|
||
rowIndex++;
|
||
}
|
||
}
|
||
return {
|
||
content: content,
|
||
style: styleContent
|
||
};
|
||
},
|
||
/**
|
||
* 合并内容和样式
|
||
* @param {[type]} ws [description]
|
||
* @param {[type]} style [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
mergeCellOpt: function(ws, style) {
|
||
for (var row in style) {
|
||
if (!style.hasOwnProperty(row)) {
|
||
continue;
|
||
}
|
||
var rowOpt = style[row];
|
||
if (ws[row]) {
|
||
// 其他属性做一个初始化
|
||
var otherOpt = ['t', 'w', 'f', 'r', 'h', 'c', 'z', 'l', 's'];
|
||
for (var i = 0; i < otherOpt.length; i++) {
|
||
ws[row][otherOpt[i]] = ws[row][otherOpt[i]];
|
||
}
|
||
$.extend(ws[row], rowOpt);
|
||
}
|
||
}
|
||
},
|
||
// 测试代码：
|
||
// for(i=1;i<100;i++){var change = layui.excel.numToTitle(i);console.log(i, change, layui.excel.titleToNum(change));}
|
||
// numsToTitle备忘录提效
|
||
numsTitleCache: {},
|
||
// titleToTitle 备忘录提效
|
||
titleNumsCache: {},
|
||
/**
|
||
* 将数字(从一开始)转换为 A、B、C...AA、AB
|
||
* @param {[int]} num [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
numToTitle: function(num) {
|
||
if (this.numsTitleCache[num]) {
|
||
return this.numsTitleCache[num];
|
||
}
|
||
var ans = '';
|
||
if (num > 26) {
|
||
// 要注意小心 26 的倍数导致的无线递归问题
|
||
var dec = num % 26;
|
||
ans = this.numToTitle((num - dec)/26) + this.numToTitle(dec?dec:26);
|
||
this.numsTitleCache[num] = ans;
|
||
this.titleNumsCache[ans] = num;
|
||
return ans;
|
||
} else {
|
||
// A 的 ascii 为 0，顺位相加
|
||
ans = String.fromCharCode(64 + num);
|
||
this.numsTitleCache[num] = ans;
|
||
this.titleNumsCache[ans] = num;
|
||
return ans;
|
||
}
|
||
},
|
||
/**
|
||
* 将A、B、AA、ABC转换为 1、2、3形式的数字
|
||
* @param {[type]} title [description]
|
||
* @return {number} [description]
|
||
*/
|
||
titleToNum: function(title) {
|
||
if (this.titleNumsCache[title]) {
|
||
return this.titleNumsCache[title];
|
||
}
|
||
var len = title.length;
|
||
var total = 0;
|
||
for (var index in title) {
|
||
if (!title.hasOwnProperty(index)) {
|
||
continue;
|
||
}
|
||
var char = title[index];
|
||
var code = char.charCodeAt() - 64;
|
||
total += code * Math.pow(26, len - index - 1);
|
||
}
|
||
this.numsTitleCache[total] = title;
|
||
this.titleNumsCache[title] = total;
|
||
return total;
|
||
},
|
||
/**
|
||
* 批量设置单元格属性
|
||
* @param {array} data [sheet级别的数据]
|
||
* @param {string} range [范围字符串，比如 A1:C12，开始位置默认 A1，结束位置默认整个表格右下角]
|
||
* @param {object} config [批量设置的单元格属性]
|
||
* @param {function} filter [回调函数，传递函数生效，返回值作为新的值（可用于过滤、规则替换样式等骚操作）]
|
||
* @return {array} [重新渲染后的 sheet 数据]
|
||
*/
|
||
setExportCellStyle: function(data, range, config, filter) {
|
||
if (typeof data !== 'object' || !data.length || !data[0] || !Object.keys(data[0]).length) {
|
||
return [];
|
||
}
|
||
|
||
// 以 rowIndex 为键，field 为值
|
||
var fieldKeys = Object.keys(data[0]);
|
||
var maxCol = data.length -1;
|
||
var maxRow = fieldKeys.length - 1;
|
||
// 默认 A1 ~ 右下角
|
||
var startPos = {c: 0, r: 0};
|
||
var endPos = {c: maxCol, r: maxRow};
|
||
|
||
if (range && typeof range === 'string') {
|
||
var rangeArr = range.split(':');
|
||
if (rangeArr[0].length) {
|
||
startPos = this.splitPosition(rangeArr[0]);
|
||
}
|
||
if (typeof rangeArr[1] !== 'undefined' && rangeArr[1] !== '') {
|
||
endPos = this.splitPosition(rangeArr[1]);
|
||
}
|
||
} else {
|
||
// pass
|
||
}
|
||
// position范围限制 - 考虑到特殊情况取消此限制
|
||
// startPos.c = startPos.c < maxCol ? startPos.c : maxCol;
|
||
// endPos.c = endPos.c < maxCol ? endPos.c : maxCol;
|
||
// startPos.r = startPos.r < maxRow ? startPos.r : maxRow;
|
||
// endPos.r = endPos.r < maxRow ? endPos.r : maxRow;
|
||
|
||
if (startPos.c > endPos.c) {
|
||
console.error('开始列不得大于结束列');
|
||
}
|
||
if (startPos.r > endPos.r) {
|
||
console.error('开始行不得大于结束行');
|
||
}
|
||
|
||
// 遍历范围内的数据，进行样式设置，按从上到下从左到右按行遍历
|
||
for (var currentRow = startPos.r; currentRow <= endPos.r; currentRow++) {
|
||
for (var currentCol = startPos.c; currentCol <= endPos.c; currentCol++) {
|
||
// 如果有回调则执行回调判断，否则全部更新，如果遇到超出数据范围的，自动置空
|
||
var row = data[currentRow];
|
||
if (!row) {
|
||
row = {};
|
||
for (var key = 0; key < fieldKeys.length; key++) {
|
||
row[fieldKeys[key]] = '';
|
||
}
|
||
data[currentRow] = row;
|
||
}
|
||
var cell = row[fieldKeys[currentCol]];
|
||
var newCell = null;
|
||
if (cell === null || cell === undefined) {
|
||
cell = '';
|
||
}
|
||
|
||
// 手工合并（相同的则以当前函数config为准）
|
||
if (typeof cell === 'object') {
|
||
newCell = $.extend(true, {}, cell, config);
|
||
} else {
|
||
newCell = $.extend(true, {}, {v: cell}, config);
|
||
}
|
||
|
||
if (
|
||
typeof filter === 'function'
|
||
) {
|
||
newCell = filter(cell, newCell, row, config, currentRow, currentCol, fieldKeys[currentCol]);
|
||
} else {
|
||
}
|
||
// 回写
|
||
data[currentRow][fieldKeys[currentCol]] = newCell;
|
||
}
|
||
}
|
||
return data;
|
||
},
|
||
/**
|
||
* 合并单元格快速生成配置的函数 传入 [ ['开始坐标 A1', '结束坐标 D2'], ['开始坐标 B2', '结束坐标 E3'] ]
|
||
* @param {[type]} origin [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
makeMergeConfig: function(origin) {
|
||
var merge = [];
|
||
for (var index = 0; index < origin.length; index++) {
|
||
merge.push({
|
||
s: this.splitPosition(origin[index][0]),
|
||
e: this.splitPosition(origin[index][1]),
|
||
});
|
||
}
|
||
return merge;
|
||
},
|
||
/**
|
||
* 自动生成列宽配置
|
||
* @param {$ObjMap} data [A、B、C的宽度映射]
|
||
* @param {number} defaultNum [description]
|
||
* @return {$ObjMap} [description]
|
||
*/
|
||
makeColConfig: function(data, defaultNum) {
|
||
defaultNum = defaultNum > 0 ? defaultNum : 50;
|
||
// 将列的 ABC 转换为 index
|
||
var change = [];
|
||
var startIndex = 0;
|
||
for (var index in data) {
|
||
if (!data.hasOwnProperty(index)) {
|
||
continue;
|
||
}
|
||
var item = data[index];
|
||
if (index.match && index.match(/[A-Z]*/)) {
|
||
var currentIndex = this.titleToNum(index) - 1;
|
||
// 填充未配置的单元格
|
||
while (startIndex < currentIndex) {
|
||
change.push({wpx: defaultNum});
|
||
startIndex++;
|
||
}
|
||
startIndex = currentIndex+1;
|
||
change.push({wpx: item > 0 ? item : defaultNum});
|
||
}
|
||
};
|
||
return change;
|
||
},
|
||
/**
|
||
* 自动生成列高配置
|
||
* @param {[type]} data [description]
|
||
* @param {[type]} defaultNum [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
makeRowConfig: function(data, defaultNum) {
|
||
defaultNum = defaultNum > 0 ? defaultNum : 10;
|
||
// 将列的 ABC 转换为 index
|
||
var change = [];
|
||
var startIndex = 0;
|
||
for (var index in data) {
|
||
if (!data.hasOwnProperty(index)) {
|
||
continue;
|
||
}
|
||
var item = data[index];
|
||
if (index.match && index.match(/[0-9]*/)) {
|
||
var currentIndex = parseInt(index) - 1;
|
||
// 填充未配置的行
|
||
while (startIndex < currentIndex) {
|
||
change.push({hpx: defaultNum});
|
||
startIndex++;
|
||
}
|
||
startIndex = currentIndex+1;
|
||
change.push({hpx: item > 0 ? item : defaultNum});
|
||
}
|
||
};
|
||
return change;
|
||
},
|
||
/**
|
||
* 将A1分离成 {c: 0, r: 0} 格式的数据
|
||
* @param {string} pos [description]
|
||
* @return {{r: number, c: number}} [description]
|
||
*/
|
||
splitPosition: function(pos) {
|
||
var res = pos.match('^([A-Z]+)([0-9]+)$');
|
||
if (!res) {
|
||
return {c: 0, r: 0};
|
||
}
|
||
// 转换结果相比需要的结果需要减一转换
|
||
return {
|
||
c: this.titleToNum(res[1]) - 1,
|
||
r: parseInt(res[2]) - 1
|
||
}
|
||
},
|
||
/**
|
||
* 将二进制数据转为8位字节
|
||
* @param {[type]} s [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
s2ab: function(s) {
|
||
var buf = new ArrayBuffer(s.length);
|
||
var view = new Uint8Array(buf);
|
||
for (var i = 0; i < s.length; i++) {
|
||
view[i] = s.charCodeAt(i) & 0xFF;
|
||
}
|
||
return buf;
|
||
},
|
||
/**
|
||
* 将导出的数据格式，转换为可以aoa导出的格式
|
||
* @return {[type]} [description]
|
||
*/
|
||
filterDataToAoaData: function(filterData){
|
||
var aoaData = [];
|
||
layui.each(filterData, function(index, item) {
|
||
var itemData = [];
|
||
for (var i in item) {
|
||
if (!item.hasOwnProperty(i)) {
|
||
continue;
|
||
}
|
||
itemData.push(item[i]);
|
||
}
|
||
aoaData.push(itemData);
|
||
});
|
||
return aoaData;
|
||
},
|
||
/**
|
||
* 梳理导出的数据，包括字段排序和多余数据过滤，具体功能请参见文档
|
||
* @param {[type]} data [需要梳理的数据]
|
||
* @param {[type]} fields [支持数组和对象，用于映射关系和字段排序]
|
||
* @return {[type]} [description]
|
||
*/
|
||
filterExportData: function(data, fields) {
|
||
// PS:之所以不直接引用 data 节省内存，是因为担心如果 fields 可能存在如下情况： { "id": 'test_id', 'test_id': 'new_id' }，会导致处理异常
|
||
var exportData = [];
|
||
var true_fields = [];
|
||
// filed 支持两种模式，数组则单纯排序，对象则转换映射关系，为了统一处理，将数组转换为符合要求的映射关系对象
|
||
if (Array.isArray(fields)) {
|
||
for (var i = 0; i< fields.length; i++) {
|
||
true_fields[fields[i]] = fields[i];
|
||
}
|
||
} else {
|
||
true_fields = fields;
|
||
}
|
||
for (var i = 0; i < data.length; i++) {
|
||
var item = data[i];
|
||
exportData[i] = {};
|
||
for (var key in true_fields) {
|
||
if (!true_fields.hasOwnProperty(key)) {
|
||
continue;
|
||
}
|
||
var new_field_name = key;
|
||
var old_field_name = true_fields[key];
|
||
// 如果传入的是回调，则回调的值则为新值
|
||
if (typeof old_field_name === 'function' && old_field_name.apply) {
|
||
exportData[i][new_field_name] = old_field_name.apply(window, [item[new_field_name], item, data]);
|
||
} else {
|
||
if (typeof item[old_field_name] !== 'undefined') {
|
||
exportData[i][new_field_name] = item[old_field_name];
|
||
} else {
|
||
exportData[i][new_field_name] = '';
|
||
}
|
||
}
|
||
}
|
||
}
|
||
return exportData;
|
||
},
|
||
/**
|
||
* 梳理导入的数据，参数意义可参考 filterExportData
|
||
* @param {[type]} data [description]
|
||
* @param {[type]} fields [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
filterImportData: function(data, fields) {
|
||
var that = this;
|
||
layui.each(data, function(fileindex, xlsx) {
|
||
layui.each(xlsx, function(sheetname, content) {
|
||
xlsx[sheetname] = that.filterExportData(content, fields);
|
||
});
|
||
});
|
||
return data;
|
||
},
|
||
/**
|
||
* 读取Excel，支持多文件多表格读取
|
||
* @param {[type]} files [description]
|
||
* @param {[type]} opt [description]
|
||
* @param {Function} callback [description]
|
||
* @return {[type]} [description]
|
||
*/
|
||
importExcel: function(files, opt, callback) {
|
||
var option = {
|
||
header: 'A',
|
||
range: null,
|
||
fields: null,
|
||
};
|
||
$.extend(option, opt);
|
||
var that = this;
|
||
|
||
if (files.length < 1) {
|
||
throw {code: 999, 'message': '传入文件为空'};
|
||
}
|
||
var supportReadMime = [
|
||
'application/vnd.ms-excel',
|
||
'application/msexcel',
|
||
'application/x-msexcel',
|
||
'application/x-ms-excel',
|
||
'application/x-excel',
|
||
'application/x-dos_ms_excel',
|
||
'application/xls',
|
||
'application/x-xls',
|
||
'application/vnd-xls',
|
||
'application/csv',
|
||
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
|
||
''
|
||
];
|
||
layui.each(files, function(index, item) {
|
||
if (supportReadMime.indexOf(item.type) === -1) {
|
||
throw {code: 999, message: item.name+'（'+item.type+'）为不支持的文件类型'};
|
||
}
|
||
});
|
||
|
||
// 按照二进制读取
|
||
var data = {};
|
||
layui.each(files, function(index, item) {
|
||
var reader = new FileReader();
|
||
if (!reader) {
|
||
throw {code: 999, message: '不支持FileReader，请更换更新的浏览器'};
|
||
}
|
||
// 读取excel表格对象
|
||
reader.onload = function(ev) {
|
||
var wb = XLSX.read(ev.target.result, {
|
||
type: 'binary'
|
||
});
|
||
var excelData = {};
|
||
layui.each(wb.Sheets, function(sheet, sheetObj) {
|
||
// 全为空的去掉
|
||
if (wb.Sheets.hasOwnProperty(sheet)) {
|
||
var opt = {
|
||
header: option.header
|
||
};
|
||
if (option.range) {
|
||
opt.range = option.range;
|
||
}
|
||
excelData[sheet] = XLSX.utils.sheet_to_json(sheetObj, opt);
|
||
// 支持梳理数据
|
||
if (option.fields) {
|
||
excelData[sheet] = that.filterExportData(excelData[sheet], option.fields);
|
||
}
|
||
}
|
||
});
|
||
data[index] = excelData;
|
||
// 全部读取完毕才执行
|
||
if (index === files.length - 1) {
|
||
callback && callback.apply && callback.apply(window, [data]);
|
||
}
|
||
};
|
||
reader.readAsBinaryString(item);
|
||
});
|
||
}
|
||
});
|
||
});
|