1、在utils目录引入export2excel.js
import { saveAs } from 'file-saver'
import XLSX from 'xlsx'
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 = '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')
}
export function export_json_to_excel({
multiHeader = [],
header,
data,
filename,
merges = [],
autoWidth = true,
bookType = 'xlsx'
} = {}) {
/* original data */
filename = filename || 'excel-list'
data = [...data]
data.unshift(header)
for (let i = multiHeader.length - 1; i > -1; i--) {
data.unshift(multiHeader[i])
}
var ws_name = 'SheetJS'
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 * 1.5
}
}
}))
/* 以第一行为初始值*/
const 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]]
// 设置单元格框线
const borderAll = {
top: {
style: 'thin'
},
bottom: {
style: 'thin'
},
left: {
style: 'thin'
},
right: {
style: 'thin'
}
}
// 给所有单元格加上边框，内容居中，字体，字号，标题表头特殊格式部分后面替换
for (var i in dataInfo) {
if (i == '!ref' || i == '!merges' || i == '!cols' || i == '!rows') { } else {
dataInfo[i + ''].s = {
border: borderAll,
alignment: {
horizontal: 'center',
vertical: 'center'
},
font: {
name: '微软雅黑',
sz: 13
}
}
}
}
var wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
})
saveAs(new Blob([s2ab(wbout)], {
type: 'application/octet-stream'
}), `${filename}.${bookType}`)
}
2、在导出功能文件中引入export2excel.js中的导出方法
import { export_json_to_excel } from '@/utils/export2excel'
3、构建导出数据，执行导出
exportFile() {
//multiHeader 复杂表头结构
const multiHeader = [
[
'地区',
'家畜', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'家禽', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'蜂',
'实验性动物(只)',
'特种畜禽', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
'野生动物', '', '', '',
'犬',
'猫',
'出具检疫证明',
'合计'
],
[
'',
'猪(头)', '', '', '', '',
'牛(头)', '', '', '', '',
'羊(头)', '', '', '', '', '',
'其他家畜(头)',
'鸡(只)', '', '', '', '', '',
'鸭(只)', '', '', '', '', '',
'鹅(只)', '', '', '', '', '',
'鸽(只)',
'鹌鹑(只)',
'其他家禽(只)',
'蜜蜂(只)',
'',
'水貂(非食用)(只)', '貂(非食用)(只)', '羊驼(只)', '火鸡(只)', '珍珠鸡(只)', '雏鸡(只)', '鹧鸪(只)', '番鸭(只)', '绿头鸭(只)', '鸵鸟(只)', '鸸鹋(只)', '银狐(非食用)(只)', '北极狐(非食用)(只)', '鹿', '', '', '小计', // 特殊畜禽
'野生哺乳类动物(只)', '野生禽鸟类动物(只)', '其他野生动物(只)', '小计',
'', // 犬
'', // 猫
'', // 出具检疫证明
'' // 合计
]
]
// header 最有一层表头
const header = [
'', // 地区
'仔猪', '商品猪', '种猪', '其他猪', '小计',
'肉牛', '奶牛', '种公牛', '其他牛', '小计',
'绵羊', '山羊', '奶山羊', '种羊', '其他羊', '小计',
'',
'肉鸡', '蛋鸡', '雏鸡', '种鸡', '其他鸡', '小计',
'肉鸭', '蛋鸭', '雏鸭', '种鸭', '其他鸭', '小计',
'肉鹅', '蛋鹅', '雏鹅', '种鹅', '其他鹅', '小计',
'',
'',
'',
'',
'', // 实验性动物
'', '', '', '', '', '', '', '', '', '', '', '', '', '梅花鹿(只)', '马鹿(只)', '驯鹿(只)', '', // 特殊畜禽
'', '', '', '', // 野生动物
'', // 犬
'', // 猫
'', // 出具检疫证明
'' // 合计
]
// merges excel 单元格合并列的计算
const merges = [
'A1:A3', // 地区
'B1:R1', 'B2:F2', 'G2:K2', 'L2:Q2', 'R2:R3', // 家畜
'S1:AM1', 'S2:X2', 'Y2:AD2', 'AE2:AJ2', 'AK2:AK3', 'AL2:AL3', 'AM1:AM1', 'AM2:AM3', // 家禽
'AN1:AN1', 'AN2:AN3', // 蜂
'AO1:AO3', // 实验性动物
'AP1:BF1', 'AP2:AP3', 'AQ2:AQ3', 'AR2:AR3', 'AS2:AS3', 'AT2:AT3', 'AU2:AU3', 'AV2:AV3', 'AW2:AW3', 'AX2:AX3', 'AY2:AY3', 'AZ2:AZ3', 'BA2:BA3', 'BB2:BB3', 'BC2:BE2', 'BF2:BF3', // 特殊畜禽
'BG1:BJ1', 'BG2:BG3', 'BH2:BH3', 'BI2:BI3', 'BJ2:BJ3', // 野生动物
'BK1:BK3', 'BL1:BL3', 'BM1:BM3', 'BN1:BN3'
]
const filterVal = [
'AREANAME', // 地区
'ZAIZ', 'SHANGPZ', 'ZHONGZ', 'QITZ', 'pigTotal', // 猪
'ROUN', 'NAIN', 'ZHONGGN', 'QITN', 'cowTotal', // 牛
'MIANY', 'SHANY', 'NAISY', 'ZHONGYANG', 'QITYANG', 'sheepTotal', // 羊
'QITJC', // 其他家畜
'ROUJ', 'DANJ', 'CHUJ', 'ZHONGJ', 'QITJ', 'chickenTotal', // 鸡
'ROUY', 'DANY', 'CHUY', 'ZHONGYA', 'QITYA', 'duckTotal', // 鸭
'ROUE', 'DANE', 'CHUE', 'ZHONGE', 'QITE', 'gooseTotal', // 鹅
'GE', // 鸽
'ANC', // 鹌鹑
'QITJQ', // 其他家禽
'MIF', // 蜜蜂
'QITSYDW', // 实验性动物
'SHUID', // 水貂(非食用)
'HE', // 貉(非食用)
'YANGT', // 羊驼
'HUOJ', // 火鸡
'ZHENGZJ', // 珍珠鸡
'ZHIJ', // 雉鸡
'ZHEG', // 鹧鸪
'FANY', // 番鸭
'LVTY', // 绿头鸭
'TUON', // 鸵鸟
'ERM', // 鸸鹋
'YINH', // 银狐(非食用)
'BEIJH', // 北极狐(非食用)
'MEIHL', // 梅花鹿
'MAL', // 马鹿
'XUNL', // 驯鹿
'specialAnimalTotal', // 特殊畜禽小计
'YESPRLDW', // 野生哺乳类动物
'YESQLLDW', // 野生禽鸟类动物
'QITYSDW', // 其他野生动物
'wildAnimalTotal', // 野生动物小计
'MAO', // 猫
'QUAN', // 犬
'NUMTOTAL', // 出具检疫证明
'allTotal' // 合计
]
const data = this.formatJson(res.data, filterVal)
export_json_to_excel({
multiHeader,
header,
data,
filename: 'excel导出表格', // excel文件名
merges
})
},
// 导出数据构建
formatJson(data, filterVal) {
return data.map(v => filterVal.map(j => {
return v[j]
}))
},
导出结果
不喜勿喷，个人笔记而已
