之前由于公司有excel导出的需求，后端开发人手不足，为了及时完成项目需求，就基于sheet.js中的xlsx.js结合网上案例做了一个单sheet和多sheet的Excel导出功能。该示例是单sheet的Excel导出，多sheet的导出过几天会整理发布出来！
1、html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<meta http-equiv="X-UA-Compatible" content="ie=edge" />
<title>excel导出</title>
</head>
<body>
<button onclick="downLoad()">导出</button>
<script src="./xlsx.core.min.js"></script>
<script src="./01-单表导出.js"></script>
<script src="./index01.js"></script>
</body>
</html>
2、01-单表导出.js
// 这是基于 xlsx.js 二次封装的导出功能。
;(function(window) {
/**
* 需要5个参数
* @header 表头 [{name:value},{age:value}]
* @body 表身 数据 [{name:value,age:vlaue},{name:value,age:vlaue}]
* @title excel的文件名称
* @sheetName 表名
* @hasTitle 是否有标题 Boolean
* */
// const header = [
// { name: '名称' },
// { age: '年龄' },
// { sex: '性别' },
// { work: '经验' },
// { edu: '学历' }
// ]
// const body = [
// { age: '18', sex: '女', work: 2, edu: '本科', name: '张一' },
// { name: '张二', age: '23', sex: '男', work: 5, edu: '博士' },
// { name: '张三', age: '34', sex: '女', work: 2, edu: '大专' },
// { name: '张四', age: '56', sex: '男', work: 6, edu: '本科' }
// ]
function exportExcel(
header = [],
body = [],
title = 'excel',
sheetName = 'sheet1',
hasTitle = true
) {
const _headers = header
.map((v, i) => {
let key = Object.keys(v)
return Object.assign(
{},
{
v: `${v[key[0]]}<key>${key[0]}`,
position: String.fromCharCode(65 + i) + (hasTitle ? 1 : 0)
}
)
})
.reduce(
(prev, next) =>
Object.assign({}, prev, {
[next.position]: { v: next.v }
}),
{}
)
console.log('_headers', _headers)
const _body = body
.map((v, i) =>
header.map((k, j) => {
let key = Object.keys(k)
return Object.assign(
{},
{
v: v[key[0]],
position: String.fromCharCode(65 + j) + (i + (hasTitle ? 2 : 1))
}
)
})
)
.reduce((prev, next) => prev.concat(next))
.reduce(
(prev, next) =>
Object.assign({}, prev, {
[next.position]: { v: next.v }
}),
{}
)
const _thead = setTableThead(_headers)
const output = Object.assign({}, _thead, _body)
const outputPos = Object.keys(output).sort()
let flagStr = outputPos[outputPos.length - 1].substr(0, 1)
let lastStrArr = outputPos.filter(item => item.includes(flagStr))
// const ref = outputPos[0] + ':' + outputPos[outputPos.length - 1]
const ref = outputPos[0] + ':' + `${flagStr}${lastStrArr.length}`
console.log('_thead', _thead)
console.log('output', output)
console.log('outputPos', outputPos)
console.log('ref', ref)
console.log('mySheet', Object.assign({}, output, { '!ref': ref }))
// const wb = {
// SheetNames: ['mySheet'],
// Sheets: {
// mySheet: Object.assign({}, output, { '!ref': ref })
// }
// }
let Sheets = {}
Sheets[sheetName] = Object.assign({}, output, { '!ref': ref })
const wb = {
SheetNames: [sheetName],
Sheets
}
save(wb, 'demo.xlsx')
}
function setTableThead(wb) {
for (let key in wb) {
let i = wb[key].v.indexOf('<key>')
if (wb[key].v.includes('<key>')) {
wb[key].v = wb[key].v.substr(0, i)
}
}
return wb
}
function save(wb, fileName) {
let wopts = {
bookType: 'xlsx',
bookSST: false,
type: 'binary'
}
let xw = XLSX.write(wb, wopts)
let obj = new Blob([s2ab(xw)], {
type: ''
})
let elem = document.createElement('a')
elem.download = fileName || '下载'
elem.href = URL.createObjectURL(obj)
elem.click()
setTimeout(function() {
URL.revokeObjectURL(obj)
}, 100)
}
function s2ab(s) {
if (typeof ArrayBuffer !== 'undefined') {
let buf = new ArrayBuffer(s.length)
let view = new Uint8Array(buf)
for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff
return buf
} else {
let buf = new Array(s.length)
for (let i = 0; i != s.length; ++i) buf[i] = s.charCodeAt(i) & 0xff
return buf
}
}
window.exportExcel = exportExcel
})(window)
3、index01.js 示例使用
const header = [
{ name: '名称' },
{ age: '年龄' },
{ sex: '性别' },
{ work: '经验' },
{ edu: '学历' }
]
const data = [
{ age: '18', sex: '女', work: 2, edu: '本科', name: '张一' },
{ name: '张二', age: '23', sex: '男', work: 5, edu: '博士' },
{ name: '张三', age: '34', sex: '女', work: 2, edu: '大专' },
{ name: '张四', age: '56', sex: '男', work: 6, edu: '本科' }
]
/**
* @param {Array} header 表格头部
* @param {Array} body 表格数据
* @param {String} title 表格导出名称
* @param {Boolean} hasTitle 是否需要表格标题
*/
function downLoad() {
exportExcel(header, data)
}
- 注意：该方法有性能问题，一次性导出超过一千条数据，建议还是走后端导出。
