Export2Excel.js
/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from 'xlsx-style'
function generateArray(table) {
var out = [];
var rows = table.querySelectorAll('tr');
var ranges = [];
for (var R = 0; R < rows.length; ++R) {
var outRow = [];
var row = rows[R];
var columns = row.querySelectorAll('td');
for (var C = 0; C < columns.length; ++C) {
var cell = columns[C];
var colspan = cell.getAttribute('colspan');
var rowspan = cell.getAttribute('rowspan');
var cellValue = cell.innerText;
if (cellValue !== "" && cellValue == +cellValue) cellValue = +cellValue;
//Skip ranges
ranges.forEach(function (range) {
if (R >= range.s.r && R <= range.e.r && outRow.length >= range.s.c && outRow.length <= range.e.c) {
for (var i = 0; i <= range.e.c - range.s.c; ++i) outRow.push(null);
}
});
//Handle Row Span
if (rowspan || colspan) {
rowspan = rowspan || 1;
colspan = colspan || 1;
ranges.push({
s: {
r: R,
c: outRow.length
},
e: {
r: R + rowspan - 1,
c: outRow.length + colspan - 1
}
});
};
//Handle Value
outRow.push(cellValue !== "" ? cellValue : null);
//Handle Colspan
if (colspan)
for (var k = 0; k < colspan - 1; ++k) outRow.push(null);
}
out.push(outRow);
}
return [out, ranges];
};
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, fontStyle) {
var ws = {};
var range = {
s: {
c: 10000000,
r: 10000000
},
e: {
c: 0,
r: 0
}
};
let _opts = []
if (opts) {
_opts = opts
}
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 (_opts) {
if (R == _opts[0] && C == _opts[1]) {
cell = {
v: data[R][C],
s: defaultCellStyle
}
}
}
//给某一单元格的字体自定义样式
if (fontStyle) {
if (R == data.length - 3 && C == 1) {
cell = {
v: data[R][C],
s: fontCellStyle
}
}
}
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 < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
let fontCellStyle = {
font: {
name: '宋体',
sz: 18,
color: { rgb: "ff0000" },
bold: true,
},
alignment: {//对齐方式
horizontal: 'center',//水平居中
vertical: 'center',//竖直居中
},
}
let defaultCellStyle = {
alignment: {
horizontal: 'center',
vertical: 'center',
indent: 0
},
border: {
diagonalDown: true,//斜线方向
diagonal: {
color: { rgb: '303133' },
style: 'thin'
}//diagonalDown与diagonal必须同时使用
}
}
function Workbook() {
if (!(this instanceof Workbook)) return new Workbook();
this.SheetNames = [];
this.Sheets = {};
}
function s2ab(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;
}
export function export_table_to_excel(id) {
var theTable = document.getElementById(id);
var oo = generateArray(theTable);
var ranges = oo[1];
/* original data */
var data = oo[0];
var ws_name = "SheetJS";
var wb = new Workbook(),
ws = sheet_from_array_of_arrays(data);
/* add ranges to worksheet */
// ws['!cols'] = ['apple', 'banan'];
ws['!merges'] = ranges;
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var wbout = XLSX.write(wb, {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), "test.xlsx")
}
/*自定义表头样式*/
let arr = [
"A1","B1","C1","D1","E1","F1","G1",
"H1","I1","J1","K1","L1","M1","N1",
"O1","P1","Q1","R1","S1","T1",
"U1","V1","W1","X1","Y1","Z1"
];
let arr1 = [
"A2","B2","C2","D2","E2","F2","G2",
"H2","I2","J2","K2","L2","M2","N2",
"O2","P2","Q2","R2","S2","T2",
"U2","V2","W2","X2","Y2","Z2"
];
let arr2 = [
"A3","B3","C3","D3","E3","F3","G3",
"H3","I3","J3","K3","L3","M3","N3",
"O3","P3","Q3","R3","S3","T3",
"U3","V3","W3","X3","Y3","Z3"
];
let style = {
font: {
color: { rgb: "000000" },
bold: true,
},
border: {
color: {
auto: 1,
},
top: {
style: 'thin',
},
bottom: {
style: 'thin',
},
left: {
style: 'thin',
},
right: {
style: 'thin',
},
},
alignment: {
horizontal: "center",
vertical: "center"
},
fill: {
fgColor: {rgb: "DCDFE6"},
},
};
/*二级表头*/
export function export_json_to_excel({
multiHeader = [], // 第一行表头
multiHeader2 = [], // 第二行表头
data,
filename, //文件名
merges = [], // 合并
autoWidth = true,
bookType = 'xlsx',
diagonal = [],//斜线
fontStyle = false,
} = {}) {
/* original data */
filename = filename || '列表';
data = [...data];
/*data.unshift(header);*/
var ws_name = "SheetJS";
data.unshift(multiHeader)
data.unshift(multiHeader2)
var wb = new Workbook()//加样式,如斜线
if (diagonal) {
var ws = sheet_from_array_of_arrays(data, diagonal, fontStyle);
} else {
var ws = sheet_from_array_of_arrays(data);
}
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(item => {
ws['!merges'].push(XLSX.utils.decode_range(item))
})
}
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val == null) {
return {
'wch': 10
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length
};
}
}))
/*以第一行为初始值*/
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['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var dataInfo = wb.Sheets[wb.SheetNames[0]];
for (var i = 0; i < multiHeader.length; i++) {
dataInfo[arr[i]].s = style;
}
for (var j = 0; j < multiHeader2.length; j++) {
dataInfo[arr1[j]].s = style;
}
var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), `${filename}.${bookType}`)
}
/*三级表头的*/
export function export_json_to_excel1({
multiHeader = [], // 第一行表头
multiHeader2 = [], // 第二行表头
header, // 第三行表头
data,
filename, //文件名
merges = [], // 合并
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || '列表';
data = [...data];
var ws_name = "SheetJS";
data.unshift(multiHeader)
data.unshift(multiHeader2)
data.unshift(header);
var wb = new Workbook()
var ws = sheet_from_array_of_arrays(data);
if (merges.length > 0) {
if (!ws['!merges']) ws['!merges'] = [];
merges.forEach(item => {
ws['!merges'].push(XLSX.utils.decode_range(item))
})
}
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
const colWidth = data.map(row => row.map(val => {
/*先判断是否为null/undefined*/
if (val == null) {
return {
'wch': 10
};
}
/*再判断是否为中文*/
else if (val.toString().charCodeAt(0) > 255) {
return {
'wch': val.toString().length * 2
};
} else {
return {
'wch': val.toString().length
};
}
}))
/*以第一行为初始值*/
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['!cols'] = result;
}
/* add worksheet to workbook */
wb.SheetNames.push(ws_name);
wb.Sheets[ws_name] = ws;
var dataInfo = wb.Sheets[wb.SheetNames[0]];
for (var i = 0; i < multiHeader.length; i++) {
dataInfo[arr[i]].s = style;
}
for (var j = 0; j < multiHeader2.length; j++) {
dataInfo[arr1[j]].s = style;
}
for (var k = 0; k < header.length; k++) {
dataInfo[arr2[k]].s = style;
}
var wbout = XLSX.write(wb, { bookType: 'xlsx', bookSST: false, type: 'binary' });
saveAs(new Blob([s2ab(wbout)], { type: "application/octet-stream" }), `${filename}.${bookType}`)
}
注意:此js中使用了xlsx-style需要安装,安装完成之后需要修改
修改此js中的var cpt = require(‘./cpt’ + ‘able’); 替换为var cpt = cptable;否则会报错
具体使用
1、二级表头
handleDownload() {
import('@/vendor/Export2Excel').then(excel => {
// 这里要注意 multiHeader 和 filterVal 的数组长度一定要相等,否则会报错
const multiHeader = ['', '', '测试3', '测试4', '测试5']
const multiHeader2 = ['测试1', '测试2', '测试', '', '']
const filterVal = ['cs1', 'cs2', 'cs3', 'cs4', 'cs5']
const merges = ['A1:A2', 'B1:B2', 'C1:E1'] // 合并单元格的数据,如何合并参考上面图片讲解
const data = this.formatJson(filterVal, this.crud.data)
const filterName = '测试'
excel.export_json_to_excel({
multiHeader,
multiHeader2,
data,
merges,
autoWidth: true,
filename: filterName
})
})
},
// 拼接数据
formatJson(filterVal, jsonData) {
console.log(jsonData)
return jsonData.map(v => filterVal.map(j => {
return v[j]
}))
}
二级实现效果
2、三级表头
handleDownload() {
import('@/vendor/Export2Excel').then(excel => {
const header = ['测试1', '测试2', '测试', '测试-1', '', '', '']
const multiHeader2 = ['', '', '', '测试4', '', '测试5', '']
const multiHeader = ['', '', '测试3', '测试6', '测试7', '测试8', '测试9']
const filterVal = ['cs1', 'cs2', 'cs3', 'cs6', 'cs7', 'cs8', 'cs9']
// 这里要注意 header 和 filterVal 的数组长度一定要相等,否则会报错
const merges = ['A1:A3', 'B1:B3', 'C1:C2', 'D1:G1', 'D2:E2', 'F2:G2']
const data = this.formatJson(filterVal, this.crud.data)
const filterName = '测试'
excel.export_json_to_excel1({
multiHeader,
multiHeader2,
header,
data,
merges,
autoWidth: true,
filename: filterName
})
})
},
// 拼接数据
formatJson(filterVal, jsonData) {
console.log(jsonData)
return jsonData.map(v => filterVal.map(j => {
return v[j]
}))
}