前端导出Excel相关链接:
纯前端利用 js-xlsx 之合并单元格
前端导出excel多级表头
前端导出多sheet的excel
vue-admin-templateUI库-前端导出excel
安装依赖
npm install --save xlsx file-saver
table 直接导出
支持删除不需要导出的列,多级表头,表尾合计
<template>
<div>
<el-button plain size="mini" @click="handleDownloadExcel">导出测试</el-button>
<el-table :data="tableData" style="width: 100%" id="table">
<el-table-column prop="date" label="日期" width="150"> </el-table-column>
<el-table-column label="配送信息">
<el-table-column prop="name" label="姓名" width="120"> </el-table-column>
<el-table-column label="地址">
<el-table-column prop="province" label="省份" width="120"> </el-table-column>
<el-table-column prop="city" label="市区" width="120"> </el-table-column>
<el-table-column prop="address" label="地址" width="300"> </el-table-column>
<el-table-column prop="zip" label="邮编" width="120"> </el-table-column>
</el-table-column>
</el-table-column>
</el-table>
<el-table :data="tableData1" border style="width: 100%" id="table1">
<el-table-column fixed prop="date" label="日期" width="150"> </el-table-column>
<el-table-column prop="name" label="姓名" width="120"> </el-table-column>
<el-table-column prop="province" label="省份" width="120"> </el-table-column>
<el-table-column prop="city" label="市区" width="120"> </el-table-column>
<el-table-column prop="address" label="地址" width="300"> </el-table-column>
<el-table-column prop="zip" label="邮编" width="120"> </el-table-column>
<el-table-column fixed="right" label="操作" width="100">
<template slot-scope="scope">
<el-button type="text" size="small">查看</el-button>
<el-button type="text" size="small">编辑</el-button>
</template>
</el-table-column>
</el-table>
</div>
</template>
<script>
import excel_export from './excelExport'
export default {
data() {
return {
sheetData: [
{
sheetName: 'sheet11',
element: 'table',
delCols: []
},
{
sheetName: 'sheet22',
element: 'table1',
delCols: [0, 6] // 要删除的列
}
],
tableData: [
{
date: '2016-05-03',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-02',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-05',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-01',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-08',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-06',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-07',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
}
],
tableData1: [
{
date: '2016-05-02',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1518 弄',
zip: 200333
},
{
date: '2016-05-04',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1517 弄',
zip: 200333
},
{
date: '2016-05-01',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1519 弄',
zip: 200333
},
{
date: '2016-05-03',
name: '王小虎',
province: '上海',
city: '普陀区',
address: '上海市普陀区金沙江路 1516 弄',
zip: 200333
}
]
}
},
methods: {
handleDownloadExcel() {
excel_export({ sheetData: this.sheetData, exportType: 'table' })
}
}
}
</script>
JsonData 形式导出
支持表头合并,表尾合计,自动列宽,两级表头(有需要多级表头可以再添加)
<template>
<div>
<el-button @click="handleDownloadExcel">导出</el-button>
</div>
</template>
<script>
import excel_export from './excelExport'
export default {
data() {
return {
sheetData: [],
list: [
{
id: 1,
timestamp: 375676396994,
author: 'Melissa',
reviewer: 'Angela',
title: 'Gxjxpr Jdfjwf Lxssr Deyonm Moxy Ryxhzp Gxtukv Bvnmvmlo',
content_short: 'mock data',
forecast: 29.75,
importance: 2,
type: 'EU',
status: 'published',
display_time: '1998-10-24 01:53:18',
comment_disabled: true,
pageviews: 1670,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
},
{
id: 2,
timestamp: 14742538820,
author: 'Anthony',
reviewer: 'Angela',
title: 'Ueeislixa Heid Dvcspjcuw Rrjyqi Wsqgyjsw',
content_short: 'mock data',
forecast: 22.76,
importance: 2,
type: 'JP',
status: 'published',
display_time: '1990-03-21 05:30:52',
comment_disabled: true,
pageviews: 328,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
},
{
id: 3,
timestamp: 1315789932004,
author: 'Sharon',
reviewer: 'Brian',
title: 'Oofunoeoob Xreaujp Ndl Xoin Nyp Iedlihw Jlxyvu Ropkbfi Rljxpyl',
content_short: 'mock data',
forecast: 36.57,
importance: 1,
type: 'CN',
status: 'deleted',
display_time: '1992-08-24 05:55:59',
comment_disabled: true,
pageviews: 1815,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
},
{
id: 4,
timestamp: 538203551911,
author: 'Scott',
reviewer: 'Joseph',
title: 'Yxrxsh Vwstgjqg Xqokntiuh Yrhsc Iiiugfk Hmoc',
content_short: 'mock data',
forecast: 0.82,
importance: 2,
type: 'US',
status: 'draft',
display_time: '1982-10-19 21:06:25',
comment_disabled: true,
pageviews: 1575,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
},
{
id: 5,
timestamp: 1171719793154,
author: 'Barbara',
reviewer: 'Angela',
title: 'Xvewsrudv Sojbjzssbn Nqcnslcl Fwtx Sertvsqnnf Rxnom Jxhrdyqv Tyvktmi Dqeypf',
content_short: 'mock data',
forecast: 22.84,
importance: 2,
type: 'JP',
status: 'published',
display_time: '1995-06-18 08:45:39',
comment_disabled: true,
pageviews: 1086,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
},
{
id: 6,
timestamp: 98663463269,
author: 'Dorothy',
reviewer: 'Sharon',
title: 'Jaeiqv Murhokxs Jeocvo Mdwf Ktgobp',
content_short: 'mock data',
forecast: 84.76,
importance: 2,
type: 'EU',
status: 'published',
display_time: '1995-03-26 02:35:50',
comment_disabled: true,
pageviews: 1338,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
},
{
id: 7,
timestamp: 846870802127,
author: 'Richard',
reviewer: 'Timothy',
title: 'Hriiunzrh Jvt Xxtvpzype Gqoe Lkppdfbqh Txrfpgn Ykrloytzg Mgirqldy',
content_short: 'mock data',
forecast: 35.18,
importance: 3,
type: 'CN',
status: 'draft',
display_time: '1973-07-02 19:02:43',
comment_disabled: true,
pageviews: 1335,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
},
{
id: 8,
timestamp: 590582195083,
author: 'George',
reviewer: 'Helen',
title: 'Tgcwifqcw Gogg Jsuypoa Hutu Rofnrslx Tntc Xehodrw Qrnsro Bcyilwymy',
content_short: 'mock data',
forecast: 86.58,
importance: 2,
type: 'US',
status: 'draft',
display_time: '1980-06-28 20:47:17',
comment_disabled: true,
pageviews: 4937,
image_uri: 'https://wpimg.wallstcn.com/e4558086-631c-425c-9430-56ffb46e70b3',
platforms: ['a-platform']
}
],
rolesList: [
{
userId: 123,
userNum: 'admin', // 用户账号
userName: 'admin', // 用户名称
userDescription: '这个账户是超级管理员账户'
},
{
userId: 124,
userNum: 'wxf123', // 用户账号
userName: '张三', // 用户名称
userDescription: '这个账户是一级管理员账户'
},
{
userId: 125,
userNum: 'cz123', // 用户账号
userName: '李四', // 用户名称
userDescription: '这个账户是二级管理员账户'
}
]
}
},
methods: {
handleDownloadExcel() {
excel_export({ sheetData: this.sheetData, exportType: 'json' })
}
},
created() {
this.sheetData = [
{
tHeader: ['Id', 'Title', 'Author', 'Readings', 'Date'], // sheet表一头部
filterVal: ['id', 'title', 'author', 'pageviews', 'display_time'], // 表一的数据字段
data: this.list, // 表一的整体json数据
merge: ['A1:A2', 'B1:E1'], // 表头合并
multiHeader: ['序号', '信息'], // 合并表头的数据
sheetName: 'sheet11', // 表一的sheet名字
sum: ['合计', '', '', 14084, ''] //表尾合计
},
{
tHeader: ['序号', '标题', '作者', '服务'],
filterVal: ['id', 'title', 'author', 'reviewer'],
data: this.list,
merge: ['A1:A2', 'B1:C1'],
multiHeader: ['序号', '其他'],
sheetName: 'sheet22'
},
{
tHeader: ['序号', '名字', '描述'],
filterVal: ['userId', 'userName', 'userDescription'],
data: this.rolesList,
merge: [],
multiHeader: [],
sheetName: 'sheet33'
}
]
}
}
</script>
核心代码
/* eslint-disable */
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
}
function formatJson(filterVal, jsonData) {
return jsonData.map(v => filterVal.map(j => v[j]))
}
/**
*
* @param {sheetData} 数据格式如下
* [
{
sheetName: 'sheet1', // sheet的名字
element: null, // 要导出的table的id,DOM也可以
delCols: [] // 要删除的列,如操作列
}
]
*/
export function export_table_to_excel({ sheetData, filename, bookType }) {
const xlsxParam = { raw: true } // 导出的内容只做解析,不进行格式转换
if (sheetData.length < 1) {
console.log('请传入数据')
return
}
const workbook = XLSX.utils.book_new()
sheetData.forEach((sheet, index) => {
let table
const { element, delCols = [] } = sheet
if (element) {
if (typeof element === 'string') {
table = document.querySelector(`#${element}`).cloneNode(true)
} else {
table = element.cloneNode(true)
}
// 判断要导出的节点中是否有fixed的表格,如果有,转换excel时先将该dom移除,
const fix = table.querySelectorAll('.el-table__fixed, .el-table__fixed-right')
if (fix.length > 0) {
fix.forEach(ele => {
table.removeChild(ele)
})
}
// 需要删除的项
if (delCols && delCols.length > 0) {
const thead_tr = table.querySelectorAll('.el-table__header-wrapper thead tr')
const thead_th = thead_tr[0].querySelectorAll('th')
delCols.forEach(i => {
thead_tr[0].removeChild(thead_th[i])
})
const tbody_tr = table.querySelectorAll('.el-table__body-wrapper tbody tr')
tbody_tr.forEach(item => {
const thead_td = item.querySelectorAll('td')
delCols.forEach(i => {
item.removeChild(thead_td[i])
})
})
}
// const ws = XLSX.utils.table_to_sheet(table)
const ws = XLSX.utils.table_to_sheet(table, xlsxParam)
XLSX.utils.book_append_sheet(workbook, ws, sheet.sheetName || `sheet${index + 1}`)
}
})
/* get binary string as output */
const wbout = XLSX.write(workbook, { bookType, bookSST: true, type: 'array' })
try {
saveAs(new Blob([wbout], { type: 'application/octet-stream' }), `${filename}.${bookType}`)
} catch (e) {
if (typeof console !== 'undefined') console.log(e, wbout)
}
}
/**
*
* @param {sheetData} 数据格式如下
* [
{
tHeader: [], // 表头
filterVal: [], // 表头对应的字段
data: [], // 数据源
multiHeader: [], // 一级表头
merge: [], // 表头合并数据
sheetName: '', // sheet的名称
sum: [] // 表尾统计数据
}
]
*/
export function export_json_to_excel({ sheetData, filename, bookType, autoWidth }) {
/* original data */
const header = []
const data = []
const sheetnames = []
const merges = []
const multiHeader = []
const sum = []
let item = null
for (let i = 0; i < sheetData.length; i++) {
item = sheetData[i]
header.push(item.tHeader || [])
data.push(formatJson(item.filterVal, item.data) || [])
sheetnames.push(item.sheetName || '')
merges.push(item.merge || [])
multiHeader.push(item.multiHeader || [])
sum.push(item.sum || [])
}
// 第二行表头
for (let i = 0; i < header.length; i++) {
if (header[i].length > 0) {
data[i].unshift(header[i])
}
}
// 第一行表头
if (multiHeader.length > 0) {
for (let n = 0; n < multiHeader.length; n++) {
if (multiHeader[n].length > 0) {
data[n].unshift(multiHeader[n])
}
}
}
if (sum.length > 0) {
for (let index = 0; index < sum.length; index++) {
if (sum[index].length > 0) {
data[index].push(sum[index])
}
}
}
let ws_name = sheetnames
let wb = new Workbook(),
ws = []
for (let j = 0; j < header.length; j++) {
ws.push(sheet_from_array_of_arrays(data[j]))
}
if (merges.length > 0) {
// 表头合并
merges.forEach((merge, i) => {
if (merge.length > 0) {
merge.forEach(item => {
if (!ws[i]['!merges']) ws[i]['!merges'] = []
ws[i]['!merges'].push(XLSX.utils.decode_range(item))
})
}
})
}
if (autoWidth) {
/*设置worksheet每列的最大宽度*/
let colWidth = []
for (let m = 0; m < header.length; m++) {
colWidth.push(
data[m].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 = []
for (let k = 0; k < colWidth.length; k++) {
result[k] = colWidth[k][colWidth[k].length - 2]
for (let i = 0; i < colWidth[k].length; i++) {
for (let j = 0; j < colWidth[k][i].length; j++) {
if (result[k][j]['wch'] < colWidth[k][i][j]['wch']) {
result[k][j]['wch'] = colWidth[k][i][j]['wch']
}
}
}
}
// 分别给sheet表设置宽度
for (let l = 0; l < result.length; l++) {
ws[l]['!cols'] = result[l]
}
}
/* add worksheet to workbook */
for (let a = 0; a < header.length; a++) {
wb.SheetNames.push(ws_name[a])
wb.Sheets[ws_name[a]] = ws[a]
}
let wbout = XLSX.write(wb, {
bookType: bookType,
bookSST: false,
type: 'binary'
})
saveAs(
new Blob([s2ab(wbout)], {
type: 'application/octet-stream'
}),
`${filename}.${bookType}`
)
}
export default function export_excel({ sheetData, exportType, filename = 'excel-list', bookType = 'xlsx', autoWidth = true }) {
if (exportType === 'table') {
export_table_to_excel({ sheetData, filename, bookType })
}
if (exportType === 'json') {
export_json_to_excel({ sheetData, filename, bookType, autoWidth })
}
}