一个js插件,用于导出生成Excel文件。
npm install xlsx
import { Message, Loading } from 'element-ui';
// 解析excel数据
export const resolveXLSX = e => {
const loadingInstance = Loading.service({
text: '导入中...',
spinner: 'el-icon-loading',
background: 'rgba(0, 0, 0, 0.8)',
});
return new Promise((resolve, rejected) => {
const files = e.target.files;
const fileReader = new FileReader();
for (let i = 0; i < files.length; i++) {
if (!/\.(xlsx|xls)$/.test(files[i].name)) {
throw new Error('必须上传excle文件!');
}
}
import(/* webpackChunkName: "xlsx" */ 'xlsx').then(XLSX => {
fileReader.onload = ev => {
try {
const data = ev.target.result;
const workbook = XLSX.read(data, {
type: 'binary',
}); // 以二进制流方式读取得到整份excel表格对象
let dataList = []; // 存储获取到的数据
for (const sheet in workbook.Sheets) {
// eslint-disable-next-line no-prototype-builtins
if (workbook.Sheets.hasOwnProperty(sheet)) {
// eslint-disable-line
dataList = dataList.concat(
XLSX.utils.sheet_to_json(workbook.Sheets[sheet])
);
break; // 如果只取第一张表,就取消注释这行
}
}
resolve(dataList);
} catch (error) {
rejected(error);
}
};
// 以二进制方式打开文件
if (files[0]) fileReader.readAsBinaryString(files[0]);
});
})
.then(data => {
loadingInstance && loadingInstance.close();
// Message.success({
// message: '导入成功!'
// });
return data;
})
.catch(e => {
loadingInstance && loadingInstance.close();
Message.error({
message: e.message || '导入失败!',
});
return Promise.reject(e);
});
};
/**
* 导出excel
* @param {*} data 数据
* @param {*} name excel名称
* @param {*} headerNameMap {prop:{value:'列明',width:20}}
* @param {*} mergeCell 是否合并单元格
*/
export const exportExcel = (
data,
name,
headerNameMap = null,
mergeCell = false
) => {
import(/* webpackChunkName: "xlsx" */ 'xlsx').then(XLSX => {
let labeledData = [];
// 生成的表格列标题根据headerNameMap生成中文
if (
headerNameMap &&
Object.prototype.toString.call(headerNameMap) == '[object Object]'
) {
labeledData = data.map(item => {
let obj = {};
for (let key in headerNameMap) {
obj[headerNameMap[key].value] = item[key];
}
return obj;
});
} else {
labeledData = data;
}
const ws = XLSX.utils.json_to_sheet(labeledData);
const range = XLSX.utils.decode_range(ws['!ref']);
// 解决导出纯数字以科学计数显示的问题
for (var r = range.s.r; r <= range.e.r; r++) {
for (var c = range.s.c; c <= range.e.c; c++) {
var cellName = XLSX.utils.encode_cell({ c: c, r: r });
ws[cellName].z = '@';
}
}
// 设置列宽度
if (
headerNameMap &&
Object.prototype.toString.call(headerNameMap) == '[object Object]'
) {
let columnsWidthArr = [];
for (let key in headerNameMap) {
columnsWidthArr.push({
wpx: headerNameMap[key].width || 120, // 字符长度
});
}
ws['!cols'] = columnsWidthArr;
}
// 合并单元格,不建议合并,大数据量消耗较大
if (mergeCell) {
ws['!merges'] = collectMergeCellPos(labeledData);
}
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, 'Sheet1');
// 保存
XLSX.writeFile(wb, `${name}.xlsx`);
});
};
// 收集要合并的单元格位置
function collectMergeCellPos(arr) {
let results = [];
let fields = getRowFields(arr[0]);
for (let c = 0; c < fields.length; c++) {
let pos = computedMergeCellPos(0, c, arr, fields);
if (pos) {
results.push(...pos);
}
}
return results;
}
/**
* 第几行第几列
* r: 行index
* c: 列index
* arr: 原数据
*/
function computedMergeCellPos(r, c, arr, fieldsKey = []) {
let mergeCellsPos = [];
let rowLength = arr.length;
let startRowIndex = r;
let endRowIndex = 0;
let fieldKey = fieldsKey[c];
getCellMergePos(r, c, arr, fieldsKey);
function getCellMergePos(r, c, arr, fieldsKey = []) {
if (r < rowLength - 1) {
let nextCellRowIndex = r + 1;
let nextCellValue = arr[nextCellRowIndex][fieldKey];
if (
arr[startRowIndex][fieldKey] === nextCellValue &&
nextCellValue !== '' &&
nextCellValue !== null
) {
endRowIndex = nextCellRowIndex;
getCellMergePos(nextCellRowIndex, c, arr, fieldsKey);
} else {
if (endRowIndex) {
mergeCellsPos.push({
s: { r: startRowIndex + 1, c: c },
e: { r: endRowIndex + 1, c: c },
});
}
startRowIndex = r + 1;
endRowIndex = null;
getCellMergePos(startRowIndex, c, arr, fieldsKey);
}
} else {
// 最后一行的情况
if (endRowIndex) {
mergeCellsPos.push({
s: { r: startRowIndex + 1, c: c },
e: { r: endRowIndex + 1, c: c },
});
}
}
}
if (mergeCellsPos.length > 0) {
return mergeCellsPos;
} else {
return null;
}
}
// 获取行字段,保证遍历的字段值对其
function getRowFields(rowObj) {
let result = [];
if (Object.prototype.toString.call(rowObj) == '[object Object]') {
for (let key in rowObj) {
result.push(key);
}
}
return result;
}