前端VUE导出excel多sheet,适用单多导出
安装
npm install --save xlsx file-saver
npm install --save xlsx-style
完整代码
/* eslint-disable */
import {saveAs} from 'file-saver'
import XLSX from 'xlsx-style'
function datenum(v, date1904) {
if (date1904) v += 1462;
var epoch = Date.parse(v);
return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000);
}
//这个方法把单个数据与表格坐标对应，也标记了内容的类型
function sheet_from_array_of_arrays(data, opts) {
var ws = {};
var range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
for (var R = 0; R != data.length; ++R) {
for (var C = 0; C != data[R].length; ++C) {
if (range.s.r > R) range.s.r = R;
if (range.s.c > C) range.s.c = C;
if (range.e.r < R) range.e.r = R;
if (range.e.c < C) range.e.c = C;
var cell = {
v: data[R][C]
};
if (cell.v == null) continue;
var cell_ref = XLSX.utils.encode_cell({
c: C,
r: R
});
if (typeof cell.v === 'number') cell.t = 'n';
else if (typeof cell.v === 'boolean') cell.t = 'b';
else if (cell.v instanceof Date) {
cell.t = 'n';
cell.z = XLSX.SSF._table[14];
cell.v = datenum(cell.v);
}
else cell.t = 's';
ws[cell_ref] = cell;
}
}
if (range.s.c {
let multiHeader = excelData.multiHeader || [], // 第二行表头
wch = excelData.wch,
describe = excelData.describe, // 最后一行设置特殊样式
header = excelData.header, // 第四行表头
sheetname = excelData.sheetname, //需要多填写一个sheet名
merges = excelData.merges || [], // 合并
autoWidth = true
/* original data */
//接口数据 包含数据首列行名
let data = [...excelData.data]
//把表头插入
data.unshift(header);
//把上面几行包含标题在内的几行合并表格的表头插入
if (multiHeader.length && multiHeader[0].length > 1) {
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
}
ws.push(sheet_from_array_of_arrays(data))
//这里还没有去标记合并项 只是列出待合并区域为''
if (merges.length > 0) {
if (!ws[index]['!merges']) ws[index]['!merges'] = [];
merges.forEach(item => {
ws[index]['!merges'].push(XLSX.utils.decode_range(item))
})
}
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
if (wch) {
return {
'wch': wch
}
} else {
/*先判断是否为null/undefined*/
if (val == null) {
return {
'wch': 12
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2.5 > 10 ? val.toString().length * 2.5 : 12
};
} else {
return {
'wch': val.toString().length * 2.1 > 10 ? val.toString().length * 2.1: 12
};
}
}
}))
let result = colWidth[0];
for (let i = 1; i < colWidth.length; i++) {
for (let j = 0; j < colWidth[i].length; j++) {
if (result[j]['wch'] < colWidth[i][j]['wch']) {
result[j]['wch'] = colWidth[i][j]['wch'];
}
}
}
ws[index]['!cols'] = result;
}
var ws_name = sheetname || ('Sheet' + (index +1))
wb.SheetNames.push(ws_name);
//这里就分sheet了
wb.Sheets[ws_name] = ws[index];
var dataInfo = wb.Sheets[wb.SheetNames[index]];
// 设置单元格框线
const borderAll = {
top: {
style: "NO_LINE"
},
bottom: {
style: "NO_LINE"
},
left: {
style: "NO_LINE"
},
right: {
style: "NO_LINE"
}
};
// 给所有单元格加上边框，内容居中，字体，字号，标题表头特殊格式部分后面替换
for (var i in dataInfo) {
if ( i === "!ref" || i === "!merges" || i === "!cols" || i === "!rows" || i === "A1" ) { } else {
dataInfo[i + ""].s = {
border: borderAll,
alignment: {
horizontal: "center",
vertical: "center"
},
font: {
name: "微软雅黑",
sz: 14
}
};
}
}
// 设置表格样式
let arrabc = []
const abc = ["A",
"B",
"C",
"D",
"E",
"F",
"G",
"H",
"I",
"J",
"K",
"L",
"M",
"N",
"O",
"P",
"Q",
"R",
"S",
"T",
"U",
"V",
"W",
"X",
"Y",
"Z"
]
let arrabcLength = data[0].length
for (let i = 0;i {
let alignment = {
horizontal: "left",
vertical: "center"
}
if (index === 0) {
alignment = {
horizontal: "center",
vertical: "center"
}
}
dataInfo[v + d].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 10,
},
alignment: alignment
}
})
for (let j = 1; j < multiHeader.length + 2; j++) {
const _v = v + j
if (dataInfo[_v]) {
dataInfo[_v].s = {};
// 标题部分A1-Z1
if (j == 1) {
dataInfo[v + j].s = {
font: {
name: "微软雅黑",
sz: 16,
color: {
rgb: "C1E9FF"
},
bold: true,
italic: false,
underline: false
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "094AA4"
},
},
};
} else {
// 表头部分,根据表头特殊格式设置
if (multiHeader.length == 0) {
// multiHeader.length = 0 时表头没有合并单元格，表头只占1行A2-Z2
const fv = v + (multiHeader.length + 2)
dataInfo[fv].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 15,
bold: true,
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
},
},
}
} else if (multiHeader.length == 1) {
// multiHeader.length = 0 时表头有合并单元格，表头只占2行A2-Z2，A3-Z3，这是没有描述信息只有表头合并的
dataInfo[v + j].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 15,
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {
rgb: "f0f0f0"
}
},
}
} else {
// multiHeader.length = 0 时表头有合并单元格，表头多行
dataInfo[v + j].s = {
border: borderAll,
font: {
name: "微软雅黑",
sz: 14,
},
alignment: {
horizontal: "center",
vertical: "center"
}
}
}
}
}
}
});
})
//循环之后使用插件写入文件数据
var wbout = XLSX.write(wb, { bookType: bookType, bookSST: false, type: 'binary' });
//转buffer然后bold导出
if (blobResult) {
return new Blob([s2ab(wbout)], {
type: "application/octet-stream"
})
} else {
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
}
export default table2excel
使用
const dataList =
[
{
data: [['李毅','18']], // 数据
header: [‘姓名’,'年龄'], // 头部 多表头有数据就是最后一行标题
multiHeader: [], // 多表头
merges: [], // 合并列 [A1:L1','C2:D2']
sheetname: ‘姓名’, // 工作簿名称
mch: 15 // 表格宽度
}
]
table2excel(dataList, '列表')
问题
Can‘t resolve ‘./cptable‘ in ‘xxx
ode_modules_xlsx
在vue.config.js中引入以下代码
configureWebpack: {
externals: {
'./cptable': 'var cptable'
},
},
