utils/xlsxExport.js
// const trans2Base64 = (content) => {
// return window.btoa(unescape(encodeURIComponent(content)))
// }
export const exportExcelFromFront = (prop) => {
const _prop = {
styles: {
tableBorder: '1',
table: 'border-collapse: collapse; width: 100%;',
caption: '',
header: 'text-align: center; background-color: #0070c0; color: #ffffff;',
cell: 'text-align: center;',
},
isDownload: true,
}
const params = Object.assign({}, _prop, prop)
const {
data = {},
caption,
exportName = 'exportName',
styles,
isDownload,
} = params
let tableHtml = ''
if (data.tableQuery) {
let tableEle = document.querySelector(data.tableQuery)
if (!tableEle) {
console.error(`无法找到tableQuery为${data.tableQuery}的table`)
return
}
tableEle = tableEle.cloneNode(true)
tableEle.setAttribute('border', styles.tableBorder || '1')
tableEle.setAttribute('border', styles.table || '')
//删除选中列
let checkboxAll = tableEle.querySelectorAll('table label.el-checkbox')
console.log('checkboxAll', checkboxAll)
checkboxAll.forEach((checkbox) => {
// console.log('checkbox', checkbox.parentElement)
let td = checkbox?.parentElement?.parentElement
console.log('td', td)
if (td?.classList?.contains('el-table_1_column_1')) {
// console.log('td', td)
td.remove()
} else {
if (checkbox?.classList?.contains('is-checked')) {
checkbox.innerHTML = '是'
} else {
checkbox.innerHTML = '否'
}
}
})
tableEle
.querySelectorAll(
'.el-table-fixed-column--right:not(.el-table_1_column_1)'
)
.forEach((td) => td.remove())
tableHtml = tableEle.innerHTML
} else {
const captionEle = caption
? `<caption style="${styles.caption || ''}">${caption}</caption>`
: ''
let headerEle = ''
let cellEle = ''
// Process header
if (Array.isArray(data.header) && data.header.length > 0) {
data.header.forEach((headerRow) => {
headerEle += '<tr>'
headerRow.forEach((headerCell) => {
headerEle += `<th colspan="${headerCell.colspan || 1}" rowspan="${
headerCell.rowspan || 1
}" style="${styles.header || ''}">${headerCell.title}</th>`
})
headerEle += '</tr>'
})
}
// Process data cells
if (Array.isArray(data.cells) && data.cells.length > 0) {
cellEle += data.cells
.map(
(itemRow) =>
`<tr>${itemRow
.map(
(itemCell) =>
`<td style="${styles.cell || ''}">${
typeof itemCell == 'string'
? ' ' + itemCell
: itemCell || ''
}</td>`
)
.join('')}</tr>`
)
.join('')
}
tableHtml = `<table border='${styles.tableBorder || '1'}' style='${
styles.table || 'border-collapse: collapse; width: 100%;'
}'>
${captionEle}${headerEle}${cellEle}
</table>`
}
let excelFile = `
<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>
${tableHtml}
</body>
</html>
`
const binaryData = window.btoa(unescape(encodeURIComponent(excelFile)))
if (isDownload) {
const link = `data:application/vnd.ms-excel;base64,${binaryData}`
const a = document.createElement('a')
a.download = `${exportName}.xlsx`
a.href = link
a.click()
}
return binaryData
}
// const params = {
// data: {
// header: [
// [{ title: 'Header 1', colspan: 2 }, { title: 'Header 2', rowspan: 2 },
// { title: 'Header 3' }
// ],
// [{ title: 'Subheader 1' }, { title: 'Subheader 2' }, { title: 'Subheader 3' }],
// ],
// cells: [
// ['Row 1 Cell 1', 'Row 1 Cell 2', 'Row 1 Cell 3'],
// ['Row 2 Cell 1', 'Row 2 Cell 2', 'Row 2 Cell 3'],
// ],
// },
// caption: 'My Excel Export',
// exportName: 'exportName',
// styles: {
// tableBorder: '1',
// table: 'border-collapse: collapse; width: 100%;',
// caption: '',
// header: 'text-align: center; background-color: #0070c0; color: #ffffff;',
// cell: 'text-align: center;',
// },
// };
export const setExcelData = (
tableColumns,
filterColumnProp,
list,
headers = [],
propName = 'dataKey'
) => {
// 处理header
let header
let columns = tableColumns.filter(
(column) => !filterColumnProp.includes(column[propName])
)
if (headers.length) {
console.log('传了')
header = headers
} else {
console.log('没床')
header = [[]]
columns.forEach((ele) => {
header[0].push({
title: ele.title,
})
})
}
// 处理cells
let cells = []
const data = list
data.forEach((row, rowIndex) => {
let dataArr = []
columns.forEach((parentEle) => {
console.log('parentEle', parentEle)
// 假如column有children
if (parentEle.title === '序号') {
dataArr.push(rowIndex + 1)
return
}
if (parentEle.hasChildren) {
parentEle.children.forEach((childEle) => {
// 如果为空
if (!row[childEle[propName]] && row[childEle[propName]] !== 0) {
dataArr.push('')
return
}
// childEle[propName]
dataArr.push(row[childEle[propName]])
})
} else {
// 如果为空
if (!row[parentEle[propName]] && row[parentEle[propName]] !== 0) {
dataArr.push('')
return
}
// parentEle[propName]
dataArr.push(row[parentEle[propName]])
console.log('这里', row[parentEle[propName]])
}
})
cells.push(dataArr)
})
console.log('===========header', header)
return { header, cells }
}
export const tableHeader = (columns) => {
// 只处理两层
let list = [[], []]
columns.forEach((parentEle) => {
// 区分父元素有无子元素
const { title, hasChildren } = parentEle
if (hasChildren) {
list[0].push({ title, colspan: parentEle.children.length })
parentEle.children.forEach((childEle) => {
const { title } = childEle
list[1].push({ title })
})
} else {
list[0].push({ title, rowspan: 2 })
}
})
// let headerList = [
// [
// { title: '序号', rowspan: 2 },
// { title: '一级分类部门', rowspan: 2 },
// { title: '二级分类部门', rowspan: 2 },
// { title: '开始日期', rowspan: 2 },
// { title: '结束日期', rowspan: 2 },
// { title: '期初金额', rowspan: 2 },
// { title: '充值', colspan: 2 },
// { title: '消费', colspan: 2 },
// { title: '退款', colspan: 2 },
// { title: '期末金额', rowspan: 2 },
// { title: '早餐次数', rowspan: 2 },
// { title: '早餐金额', rowspan: 2 },
// { title: '午餐次数', rowspan: 2 },
// { title: '午餐金额', rowspan: 2 },
// { title: '晚餐次数', rowspan: 2 },
// { title: '晚餐金额', rowspan: 2 },
// { title: '夜宵次数', rowspan: 2 },
// { title: '夜宵金额', rowspan: 2 },
// { title: '总消费(包含小卖部)', colspan: 2 },
// { title: '部门人数', rowspan: 2 },
// ],
// [
// { title: '个人' },
// { title: '补贴' },
// { title: '饭堂' },
// { title: '小卖部' },
// { title: '个人' },
// { title: '补贴' },
// { title: '个人' },
// { title: '补贴' },
// ],
// ]
return list
}
实例
table
<el-table-v2
border
class="table-wrap visual-table el-table--border"
:columns="choosedColumns"
:data="chooseList"
fixed
header-class="blue-header-row"
:header-height="40"
:height="maxHeightNum"
:row-height="40"
:scrollbar-always-on="true"
:v-scrollbar-size="12"
:width="choosedTableWidth"
>
<template #footer>
<div class="visual-table__footer">
<span class="visual-table__footer-sum">合计</span>
<span class="visual-table__footer-label">
补贴额：
</span>
<span class="visual-table__footer-value">
{{ totalRenewmoney }} 元
</span>
</div>
</template>
</el-table-v2>
data
choosedColumns: [
// 勾选 selection
{
key: 'empcode',
dataKey: 'empcode',
type: 'selection', // 此属性用于导出
width: 48,
align: 'center',
headerCellRenderer: () => (
// todo
<el-checkbox
v-model={state.isSelectAllChoosed}
class="visual-checkbox"
size="small"
onChange={changeSelectAllChoosed}
disabled={!state.chooseList.length}
></el-checkbox>
),
cellRenderer: ({ cellData }) => (
<el-checkbox
v-model={state.selectRowsChoosed}
label={cellData}
class="visual-checkbox"
size="small"
></el-checkbox>
),
},
// 序号
{
key: 'index',
dataKey: 'index',
type: 'index', // 此属性用于导出
title: '序号',
width: 48,
align: 'center',
cellRenderer: ({ rowIndex }) => h('span', rowIndex + 1),
},
{
key: 'typename',
dataKey: 'typename',
type: 'typename', // 此属性用于导出
title: '部门',
width: 80,
align: 'center',
},
{
key: 'jobno',
dataKey: 'jobno',
type: 'jobno', // 此属性用于导出
title: '工号',
width: 85,
align: 'center',
},
{
key: 'empname',
dataKey: 'empname',
type: 'empname', // 此属性用于导出
title: '姓名',
width: 80,
align: 'center',
},
{
key: 'cardno',
dataKey: 'cardno',
type: 'cardno', // 此属性用于导出
title: '消费卡号',
width: 85,
align: 'center',
},
{
key: 'renewmoney',
dataKey: 'renewmoney',
type: 'renewmoney', // 此属性用于导出
title: '补贴额',
width: 73,
align: 'center',
cellRenderer: ({ cellData }) =>
h(
'span',
<span class="primary-text">{transformMoney(cellData)}</span>
),
},
{
key: 'butie_macno',
dataKey: 'butie_macno',
type: 'butie_macno', // 此属性用于导出
title: '补贴月份',
width: 100,
align: 'center',
cellRenderer: ({ cellData }) =>
h('span', <span class="danger-text">{cellData}</span>),
},
{
key: 'renewdate',
dataKey: 'renewdate',
type: 'renewdate', // 此属性用于导出
title: '补贴日期',
width: 140,
align: 'center',
},
{
key: 'note',
dataKey: 'note',
type: 'note', // 此属性用于导出
title: '增减方式',
width: 80,
align: 'center',
cellRenderer: ({ cellData }) => h('span', getNote(cellData)),
},
{
key: 'pro',
dataKey: 'pro',
type: 'pro', // 此属性用于导出
title: '性质',
width: 73,
align: 'center',
cellRenderer: ({ cellData }) => h('span', getPro(cellData)),
},
{
key: 'makeman',
dataKey: 'makeman',
type: 'makeman', // 此属性用于导出
title: '制单人',
width: 73,
align: 'center',
},
{
key: 'Empcode',
dataKey: 'empcode',
type: 'empcode', // 此属性用于导出
title: 'Empcode',
width: 85,
align: 'center',
cellRenderer: ({ cellData }) =>
h('span', <span class="danger-text">{cellData}</span>),
headerCellRenderer: () => <span class="danger-text">Empcode</span>,
},
{
title: '操作',
width: 55,
align: 'center',
fixed: 'right',
dataKey: 'operation',
type: 'operation', // 此属性用于导出
cellRenderer: ({ rowData: row }) => (
<div class="action-bar-two-column">
<el-button
size="small"
type="text"
onClick={handleEdit.bind(this, row)}
>
编辑
</el-button>
</div>
),
},
],
method
const handleExport = () => {
// 本地导出
// 没数据就不用导出
if (!state.chooseList.length) {
$baseMessage('暂无数据', 'error')
return
}
state.loading = true
const list = state.chooseList
let filterColumnProp = ['selection', 'operation'] // type
const { header, cells } = setExcelData(
// 有的columns要筛选掉
state.choosedColumns,
filterColumnProp,
list,
[],
'type' // 使用什么属性对column进行筛选
)
const params = {
data: {
header,
cells,
},
caption: state.title,
exportName: state.title,
}
exportExcelFromFront(params)
state.loading = false
}