代码
/**
* column 表头
* {
* label: string 名称,
* prop: string 对应data字段,
* placeholder: boolean 是否把单元格变为占位符，用于头部行合并,
* headerslot: boolean 是否在表头上方插入数据,
* boottomslot: boolean 是否在表底部插入数据,
* style: string 单元格样式,
* colspan: number 列合并,
* rowspan: number 行合并,
* noExport: boolean 是否导出此列,
* exceltype: string 列类型
* } array
*
* data 表格数据 array
* title 表名称 string
* retract 缩进字段 string array
* tableHead 拼接表头
* tableBody 拼接表格
*/
import { dateFormat } from '@/views/accounting/utils/date'
export const excel = {
column: [],
data: [],
title: '',
tableBody: '',
tableHead: '',
retract: '',
option: function(option = { title: '', column: null, data: null }) {
const msg = {
column: 'the column is null',
data: 'the data is null'
}
for (const key in msg) {
if (!option[key]) {
console.error(msg[key])
return false
}
}
this.title = option.title
this.column = this.filterColumn(option.column)
this.data = option.data
this.retract = option.retract
this.download()
},
/**
* 下载
*/
download: async function() {
const table = await this.generateTable()
const worksheet = `${this.title}_${dateFormat(new Date(), 'yyyyMMddhhmmss')}`
const uri = 'data:application/vnd.ms-excel;base64,'
const template = `
<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">
<head><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet>
<x:Name>${worksheet}</x:Name>
<x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet>
</x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]-->
</head><body>${table}</body></html>`
const a = document.createElement('a')
a.href = uri + window.btoa(unescape(encodeURIComponent(template)))
a.download = `${worksheet}.xls`
a.dispatchEvent(new MouseEvent('click', { bubbles: true, cancelable: true, view: window }))
this.done()
},
/**
* 生成table
*/
generateTable: async function() {
// 判断是否需要复杂表头 （tips：目前只支持表头两层嵌套）
if (this.column.some(item => item.children && item.children.length && !item.headerslot && !item.bottomslot)) {
await this.generateColspanHead()
this.column = await this.flagColumn()
}
await this.generateHead()
await this.generateBody(this.data)
return `<table border="1" cellpadding="0" cellspacing="0" style="vnd.ms-excel.numberformat:@;border-collapse:collapse; text-align: center;">
${this.generateSlot('headerslot')}
${this.tableHead}
${this.tableBody}
${this.generateSlot('bottomslot')}
</table>`
},
/**
* 生成table header
*/
generateHead: function() {
this.tableHead += '<tr>'
for (let i = 0; i < this.column.length; i++) {
const item = this.column[i]
if (item.headerslot || item.bottomslot) {
continue
}
if (item.placeholder) {
continue
}
this.tableHead += `<td style="font-weight: bold">${item.label}</td>`
}
this.tableHead += '</tr>'
},
/**
* 生成复杂 head
*/
generateColspanHead: function() {
this.tableHead += '<tr>'
for (let i = 0; i < this.column.length; i++) {
const item = this.column[i]
if (item.headerslot || item.bottomslot) {
continue
}
if (item.children && item.children.length) {
this.tableHead += `<td style="font-weight: bold" colspan="${item.children?.length}">${item.label}</td>`
} else {
this.tableHead += `<td style="font-weight: bold" rowspan="2">${item.label}</td>`
}
}
this.tableHead += '</tr>'
},
/**
* 生成table 主体
*/
generateBody: function(data, level = 0) {
if (!data.length) {
return false
}
data.forEach(item => {
this.tableBody += `<tr>`
for (let i = 0; i < this.column.length; i++) {
const col = this.column[i]
if (!col.headerslot && !col.bottomslot) {
if (isNaN(Number(item[col.prop])) || col.exceltype === 'string') {
this.tableBody += `<td style="padding-left: ${this.retract && (this.retract.includes(col.prop) || this.retract === col.prop) ? level * 30 : 0}px">${item[col.prop] || ''}</td>`
} else {
this.tableBody += `<td style="vnd.ms-excel.numberformat:#,##0.00;">${Number(item[col.prop]) || ''}</td>`
}
}
}
this.tableBody += `</tr>`
if (item.children && item.children.length) {
this.generateBody(item.children, level + 1)
}
})
},
/**
* slottype：[headerslot, bottomslot]
* 生成底部或者头的插入行
*/
generateSlot: function(slottype) {
let slot = ''
for (let i = 0; i < this.column.length; i++) {
const item = this.column[i]
if (item[slottype]) {
if (item.children && item.children.length) {
slot += `<tr>`
item.children.forEach(d => {
slot += `<td rowspan="${d.rowspan}" colspan="${d.colspan}" style="${d.style}">${d.label}</td>`
})
slot += `</tr>`
continue
}
slot += `
<tr>
<td rowspan="${item.rowspan}" colspan="${item.colspan}" style="${item.style}">${item.label}</td>
</tr>
`
}
}
return slot
},
/**
* 过滤不需导入的列
*/
filterColumn(column) {
const newColumn = []
for (let i = 0; i < column.length; i++) {
const item = column[i]
if (!item.noExport) {
newColumn.push(item)
}
}
return newColumn
},
/**
* 扁平化head column
*/
flagColumn: function() {
const newColumn = []
for (let i = 0; i < this.column.length; i++) {
const item = this.column[i]
if (item.children && item.children.length && !item.headerslot && !item.bottomslot) {
newColumn.push(...item.children)
continue
}
item.placeholder = true
newColumn.push(item)
}
return newColumn
},
/**
* 下载完成
*/
done() {
this.tableBody = ''
this.tableHead = ''
this.column = []
this.data = []
this.title = ''
this.retract = ''
}
}
示例
excel.option({
title: '资产负债表',
column: [
{
label: '资产负债表',
headerslot: true,
style: 'text-align: center; height: 40px; font-size: 20px',
colspan: this.column.length
},
{
label: dateFormat(new Date(), 'yyyy-MM-dd'),
headerslot: true,
style: 'text-align: center',
colspan: this.column.length
},
{
headerslot: true,
children: [
{
label: `编制单位：${this.bizInfo.warehouse.deptName}`,
style: 'text-align: left; border-right: 0',
colspan: this.column.length / 2
},
{
label: `单位：元`,
style: 'text-align: right; border-left: 0',
colspan: this.column.length / 2
}
]
},
...this.column
],
data: this.data
})
导出效果
完全根据自己的业务编写，如果您要借鉴，请自行修改！
