時間緊任務重推薦第一種，直接把表格整個拿過來；第二種根據表格的配置和數據進行設置 1、html下載，原生JS downTable(name) { const uri = 'data:application/vnd.ms-excel;base64,', template = '<html xmlns: ...
時間緊任務重推薦第一種，直接把表格整個拿過來；第二種根據表格的配置和數據進行設置
1、html下載，原生JS
downTable(name) { const uri = 'data:application/vnd.ms-excel;base64,', 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>{table}</table></body></html>', base64 = function (s) { return window.btoa(unescape(encodeURIComponent(s))) }, format = function (s, c) { return s.replace(/{(\w+)}/g, function (m, p) { return c[p]; }) } //el-table表格設置列浮動，會多出一個隱藏table，需要過濾掉 const table = document.getElementById('table')?.innerHTML.replace(/<div class="el-table__fixed".*>[\s\S]*?<\/div>/img, ""); const ctx = {worksheet: name || 'Worksheet', table} // 通過創建a標簽實現 const link = document.createElement("a"); link.href = uri + base64(format(template, ctx)); // 對下載的文件命名 link.download = name + '.xlsx'; link.click(); }
優點：快
缺點：時間會被轉義，樣式會受頁面CSS影響
2、XLSX
async downTable(type: number) { let tableColumn: any[] = [], tableData: any[] = [], fileName = ''; if (type === 1) { tableColumn = this.trendSaleColumn; tableData = this.trendSaleData; fileName = '售前介入趨勢（銷售）.xlsx'; }else if (type === 3) { await this.getPreSalesConvertInfo(this.transferSaleType, 1); tableColumn = this.transferSaleColumn; tableData = this.transferSaleData; fileName = '售前部門轉化（銷售）.xlsx'; } let data: any[] = [[]]; tableColumn.forEach((item: any) => { data[0].push(item.label); }) tableData.forEach((item: any) => { let row: any[] = []; tableColumn.forEach((column: any) => { row.push(item[column.prop]); }) data.push(row); }) const sheet = XLSX.utils.aoa_to_sheet(data);
//合併單元格配置，需要計算需要合併的行列數 if (type === 3 || type === 4) { if (this.productType[1] > 0 && this.productType[2] > 0) { sheet['!merges'] = [ {s: {r: 1, c: 0}, e: {r: this.productType[1], c: 0}},//s表示開始，e表示結束，r表示行，c表示列； {s: {r: this.productType[1] + 1, c: 0}, e: {r: this.productType[2], c: 0}} ]; } else if (this.productType[1] > 0 || this.productType[2] > 0) { sheet['!merges'] = [ {s: {r: 1, c: 0}, e: {r: this.productType[1] || this.productType[2], c: 0}},//s表示開始，e表示結束，r表示行，c表示列； ]; } } // 通過創建a標簽實現 const link = document.createElement("a"); link.href = URL.createObjectURL(sheet2blob(sheet)); // 對下載的文件命名 link.download = fileName; link.click(); }
sheet2blob(sheet: any, sheetName: string = 'sheet1') { const workbook: any = { SheetNames: [sheetName], Sheets: {} }; workbook.Sheets[sheetName] = sheet; // 生成excel的配置項 const wopts: any = { bookType: 'xlsx', // 要生成的文件類型 bookSST: false, // 是否生成Shared String Table，官方解釋是，如果開啟生成速度會下降，但在低版本IOS設備上有更好的相容性 type: 'binary' }; const wbout = XLSX.write(workbook, wopts); const blob = new Blob([s2ab(wbout)], {type: "application/octet-stream"}); // 字元串轉ArrayBuffer function s2ab(s: any) { const buf = new ArrayBuffer(s.length); const view = new Uint8Array(buf); for (let i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xFF; return buf; } return blob; }
優點：單元格內容可控，可以設置單元合併等XLSX支持的配置
缺點：複雜表格需要計算合併單元格的位置和數量，比如多級表頭
