引入组件yxg-xlsx-style
导出excel主要逻辑文件（跟业务无关）：excel-style.js
import XLSX from 'yxg-xlsx-style'// 如果单元格是日期类型function datenum (v, date1904) {if (date1904) v += 1462let epoch = Date.parse(v)return (epoch - new Date(Date.UTC(1899, 11, 30))) / (24 * 60 * 60 * 1000)}// 将二维数组(如：data[0][0])变换成excel数据格式(如：A1)function sheet_from_array_of_arrays (data, callback) {let ws = {}let range = {s: {c: 10000000,r: 10000000,},e: {c: 0,r: 0,},}for (let R = 0; R !== data.length; ++R) {for (let C = 0; C !== data[R].length; ++C) {if (range.s.r > R) range.s.r = Rif (range.s.c > C) range.s.c = Cif (range.e.r < R) range.e.r = Rif (range.e.c < C) range.e.c = Clet cell = {v: data[R][C],s: {font: {name: '微软雅黑',sz: 9,bold: false,// color: {// auto: 1// },color: {rgb: callback ? callback(data[R][C]) : '',},},border: {color: {auto: 1, },},alignment: {/// 自动换行wrapText: 1,// 居中horizontal: 'center',vertical: 'center',indent: 0,},},}// 这里生成cell的时候，使用上面定义的默认样式if (cell.v == null) continuelet 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}// 新建excel文件(一个excel文件里面有很多工作表)function Workbook () {if (!(this instanceof Workbook)) return new Workbook()this.SheetNames = []this.Sheets = {}}// 该函数尚不清楚是做啥的function s2ab (s) {let buf = new ArrayBuffer(s.length)let view = new Uint8Array(buf)for (let i = 0; i !== s.length; ++i) view[i] = s.charCodeAt(i) & 0xFFreturn buf}// 生成excel文件function saveAs (obj, fileName) {let tmpa = document.createElement('a')tmpa.download = fileName || '下载'tmpa.href = URL.createObjectURL(obj)tmpa.click()setTimeout(function () {URL.revokeObjectURL(obj)}, 100)}// 生成excel文件总入口函数export function export_json_to_excel ({title,multiHeader = [],header,data,filename,merges = [],autoWidth = true,bookType = 'xlsx',callback,} = {}) {console.log(callback)const getFormattedTime = () => {let today = new Date()let y = today.getFullYear()// JavaScript months are 0-based.let m = today.getMonth() + 1let d = today.getDate()let h = today.getHours()let mi = today.getMinutes()let s = today.getSeconds()return y + '-' + m + '-' + d + '-' + h + '-' + mi + '-' + s}filename = filename || getFormattedTime()data = [ ...data, ]data.unshift(header)if (merges.length > 0) {data.unshift(title)}for (let i = multiHeader.length - 1; i > -1; i--) {data.unshift(multiHeader[i])}let ws_name = 'SheetJS'let wb = new Workbook()let ws = sheet_from_array_of_arrays(data, callback)if (merges.length > 0) {if (!ws['!merges']) ws['!merges'] = []merges.forEach(item => {ws['!merges'].push(XLSX.utils.decode_range(item))})}if (autoWidth) {// 设置worksheet每列的最大宽度const colWidth = data.map(row => row.map(val => {if (val == null) {return {'wch': 10,}} else {let maxWch = 0let wch = 0const arrForVal = val.split('\n')arrForVal.forEach(val2 => {if (val2 == null) {wch = 10} else if (val2.toString().charCodeAt(0) > 255) {wch = val2.toString().length * 2} else {wch = val2.toString().length}if (wch > maxWch) {maxWch = wch}})return {'wch': maxWch,}}}))// 以第一行基准，第二行及其以后的对比第一行的每列宽度，选择最宽的let result = colWidth[0]for (let i = 1; i < colWidth.length; i++) {for (let j = 0; j < colWidth[i].length; j++) {if (result[j]['wch'] < colWidth[i][j]['wch']) {result[j]['wch'] = colWidth[i][j]['wch']}}}ws['!cols'] = result} else {// 设置worksheet每列的固定宽度if (data[0]) {ws['!cols'] = data[0].map(val => {return {'wch': 15,}})}}// add worksheet to workbookwb.SheetNames.push(ws_name)wb.Sheets[ws_name] = wslet dataInfo = wb.Sheets[wb.SheetNames[0]]// 单元格外侧框线const borderAll = {top: {style: 'thin',color: {rgb: 'e8eaec',},},bottom: {style: 'thin',color: {rgb: 'e8eaec',},},left: {style: 'thin',color: {rgb: 'e8eaec',},},right: {style: 'thin',color: {rgb: 'e8eaec',},},color: {auto: 1, },}// 给所有单元格加上边框for (let i in dataInfo) {if (merges.length > 0) {if (i === '!ref' || i === '!merges' || i === '!cols' || i === 'A1') {} else {dataInfo[i + ''].s.border = borderAll}} else {if (i === '!ref' || i === '!merges' || i === '!cols') {} else {dataInfo[i + ''].s.border = borderAll}}}// 去掉标题边框if (merges.length > 0) {let arr = [ 'A1', 'B1', 'C1', 'D1', 'E1', 'F1', 'G1', 'H1', 'I1', 'J1', 'K1', 'L1', 'M1', 'N1', 'O1', 'P1', 'Q1', 'R1', 'S1', 'T1', 'U1', 'V1', 'W1', 'X1', 'Y1', 'Z1', ]arr.some(function (v) {let a = merges[0].split(':')if (v === a[1]) {dataInfo[v].s = {}return true} else {dataInfo[v].s = {}}})}if (merges.length > 0) {// 设置主标题样式dataInfo['A1'].s = {font: {name: '微软雅黑',sz: 9,color: {rgb: 'ff0000', },bold: true,italic: false,underline: false,},alignment: {horizontal: 'center',vertical: 'center',},// fill: {// fgColor: {rgb: "008000"},// },}}let wbout = XLSX.write(wb, {bookType: bookType,bookSST: false,type: 'binary',})saveAs(new Blob([ s2ab(wbout), ], {type: 'application/octet-stream',}), `${filename}.${bookType}`)}
拿到表格数据导出excel之前（即调用第1步总入口函数 export_json_to_excel ）的预处理
export const radarDataExportExcel = (params) => {const {table,} = paramsimport('@/libs/excel-style').then(excel => {const getFormattedTime = () => {let today = new Date()let y = today.getFullYear()// JavaScript months are 0-based.let m = today.getMonth() + 1let d = today.getDate()let h = today.getHours()let mi = today.getMinutes()let s = today.getSeconds()return y + '-' + m + '-' + d + '-' + h + '-' + mi + '-' + s}const title = [] // 标题const tHeader = [] // 表头const filterVal = []table.columns.forEach((item, index) => {tHeader.push(item.title)filterVal.push(item.key)})// 表头对应字段let list = []table.data.forEach(item => {let obj = {}for (let key in item) {if (filterVal.includes(key)) {// 找到表格Header对应的字段obj[key] = item[key]}}list.push(obj)})const data = list.map(v => filterVal.map(j => v[j])) // 该函数将对象数组变成普通二维数组(如：data[0][0])data.map(item => {item.map((i, index) => {if (!i) {item[index] = ''}})})const merges = []excel.export_json_to_excel({title: title,header: tHeader,data,merges,filename: getFormattedTime(),autoWidth: true,bookType: 'xlsx',})}).catch(e => {})}
给一个例子看看
web页面表格样式
导出excel样式
