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,} = paramslet 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?.parentElementconsole.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 headerif (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 cellsif (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 = linka.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'
) => {// 处理headerlet headerlet 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,})})}// 处理cellslet cells = []const data = listdata.forEach((row, rowIndex) => {let dataArr = []columns.forEach((parentEle) => {console.log('parentEle', parentEle)// 假如column有childrenif (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 } = parentEleif (hasChildren) {list[0].push({ title, colspan: parentEle.children.length })parentEle.children.forEach((childEle) => {const { title } = childElelist[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-v2borderclass="table-wrap visual-table el-table--border":columns="choosedColumns":data="chooseList"fixedheader-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-checkboxv-model={state.isSelectAllChoosed}class="visual-checkbox"size="small"onChange={changeSelectAllChoosed}disabled={!state.chooseList.length}></el-checkbox>),cellRenderer: ({ cellData }) => (<el-checkboxv-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-buttonsize="small"type="text"onClick={handleEdit.bind(this, row)}>编辑</el-button></div>),},],
method
const handleExport = () => {// 本地导出// 没数据就不用导出if (!state.chooseList.length) {$baseMessage('暂无数据', 'error')return}state.loading = trueconst list = state.chooseListlet filterColumnProp = ['selection', 'operation'] // typeconst { 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}