vue纯前端导出excel
提供数据
从后端获取list数据或者前端定义list数据。
引用包
"dependencies": {
"file-saver": "^2.0.1",
"xlsx-style": "^0.8.13",
}
导出
分别定义表格中展示的名称和list中对应的字段
const tHeader = [
"登录名",
"姓名",
"手机号码",
"邮箱",
"状态",
"类型",
"机构名称",
"更新时间"
];
const filterVal = [
"userName",
"realName",
"mobile",
"email",
"statusDesc",
"typeDesc",
"tierName",
"createTime"
];
获取list数据
略。包含定义的字段即可
导出实现
downloadExcel("导出数据", tHeader, filterVal, list, () => {});
导出方法
export function downloadExcel(filename, colNameList, colKeyList, list, callback, sheetName) {
const autoWidth = true
const bookType = 'xlsx'
import('@/vendor/Export2Excel').then(excel => {
const tHeader = colNameList
const filterVal = colKeyList
const data = formatJson(filterVal, list)
filename = (filename != null && filename != '') ? filename : 'excel'
excel.export_json_to_excel({
header: tHeader,
data,
filename: filename,
autoWidth: autoWidth,
bookType: bookType,
sheetName: sheetName
})
if (callback != null && callback != undefined) {
callback()
}
})
}
function formatJson(filterVal, jsonData) {
const data = []
for (let i = 0; i < jsonData.length; i++) {
const row = jsonData[i]
const val = []
for (let j = 0; j < filterVal.length; j++) {
val.push(row[filterVal[j]])
}
data.push(val)
}
return data
}
Export2Excel
/* eslint-disable */
import { saveAs } from 'file-saver'
import XLSX from "xlsx-style"
// import XLSX from 'xlsx'
// 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) {
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 < 10000000) ws['!ref'] = XLSX.utils.encode_range(range);
return ws;
}
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 = "Sheet1";
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")
}
export function export_json_to_excel_multi({
dataArray,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
// var dataArray = [{header, data, sheetName}]
let finalDataArr = []
let wb = new Workbook();
if (dataArray != null && dataArray != []) {
for (let i = 0; i < dataArray.length; i++) {
let data = [...dataArray[i].data]
let topHeader = []
for (let km = 0; km < dataArray[i].header.length; km++) {
if (km==0) {
topHeader.push(dataArray[i].sheetName)
} else {
topHeader.push("")
}
}
data.unshift(dataArray[i].header);
data.unshift(topHeader);
let start = getColumnNameFromIdNotAdd([1, 0])
let end = getColumnNameFromIdNotAdd([1, dataArray[i].header.length -1])
merges = [start + ":" + end];
/* for (let j = dataArray[i].multiHeader.length - 1; j > -1; j--) {
data.unshift(dataArray[i].multiHeader[j])
}*/
finalDataArr.push(data)
let ws_name = dataArray[i].sheetName;
let 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;
//excel标题样式
let dataInfo = wb.Sheets[ws_name];
let overCells = []
let overPosArr = dataArray[i].overPosArr
if (overPosArr != null && overPosArr != undefined && overPosArr != '') {
for (let n = 0; n < overPosArr.length; n++) {
overCells.push(getColumnNameFromId(overPosArr[n]))
}
}
for (let m in dataInfo) {
if (m == "!ref" || m == "!merges" || m == "!cols" || m == "!rows") { } else {
let num = getNum(m+"");
if (num != null && num != undefined && num == 1) {
dataInfo[m + ""].s = getHeaderStyle();
}
if (overCells.indexOf(m + "") >= 0) {
dataInfo[m + ""].s = getOverStyle();
}
}
}
/* for (let m = 0; m < dataArray[i].header.length; m++) {
dataInfo[arr[i]].s = style;
}*/
}
let wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
/*data = [...data]
data.unshift(header);
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}*/
}
function getNum(str) {
let numArr = ['1', '2', '3', '4', '5', '6', '7', '8', '9', '0'];
let num
if (str != null && str != '') {
for (let i = 0; i < str.length; i++) {
if (numArr.indexOf(str[i]) >= 0) {
num = str.substring(i)
return num
}
}
}
}
function getHeaderStyle() {
const borderAll = {
top: {
style: "thin"
},
bottom: {
style: "thin"
},
left: {
style: "thin"
},
right: {
style: "thin"
}
};
let style = {
// border: borderAll,
font: {
// name: "宋体",
// sz: 18,
color: { rgb: "000000" },
bold: true,
// italic: false,
// underline: false
},
alignment: {
horizontal: "center",
vertical: "center"
}
// fill: {
// fgColor: {rgb: "008000"},
// },
};
return style
}
function getOverStyle() {
let style = {
font: {
// name: "宋体",
// sz: 18,
color: { rgb: "FF0000" },
bold: false,
}
};
return style
}
export function export_json_to_excel({
multiHeader = [],
header,
data,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = [...data]
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
data.unshift(header);
var ws_name = "Sheet1";
var wb = new Workbook(),
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 wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
});
saveAs(new Blob([s2ab(wbout)], {
type: "application/octet-stream"
}), `${filename}.${bookType}`);
}
/**
* 根据坐标获取列名
* @param {Object} cellId
*/
function getColumnNameFromId(cellId){
if (! Array.isArray(cellId)) {
cellId = cellId.split('-');
}
var i = cellId[1];
var letter = '';
if (i > 701) {
letter += String.fromCharCode(64 + parseInt(i / 676));
letter += String.fromCharCode(64 + parseInt((i % 676) / 26));
} else if (i > 25) {
letter += String.fromCharCode(64 + parseInt(i / 26));
}
letter += String.fromCharCode(65 + (i % 26));
return letter + (parseInt(cellId[0]) + 3);
}
/**
* 根据坐标获取列名
* @param {Object} cellId
*/
function getColumnNameFromIdNotAdd(cellId){
if (! Array.isArray(cellId)) {
cellId = cellId.split('-');
}
var i = cellId[1];
var letter = '';
if (i > 701) {
letter += String.fromCharCode(64 + parseInt(i / 676));
letter += String.fromCharCode(64 + parseInt((i % 676) / 26));
} else if (i > 25) {
letter += String.fromCharCode(64 + parseInt(i / 26));
}
letter += String.fromCharCode(65 + (i % 26));
return letter + (parseInt(cellId[0]));
}
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 ALLBS!
评论