vue3 用xlsx 解决 excel 低版本office无法打开问题

需求背景

原使用 vue3-json-excel ，导致在笔记本office环境下，出现兼容性问题

 <vue3-json-excel class="export-btn" :fetch="excelGetList" :fields="jsonFields" header="告警配置列表" name="告警配置列表.xls" type="xls"><span>批量导出</span></vue3-json-excel>

生成的.xls文件，提示文件格式和扩展名不匹配，文件可能已损坏或不安全
生成的.xlsx文件，提示文件格式或文件扩展名无效

解决思路

既然 vue3-json-excel 达不到效果，就改用xlsx

解决效果

将json导出为excel

/*** 将json数据导出为excel* @param {object} options* @param {[]} data 数据源 // {'供热系统': "大唐热电供热系统", '供热面积(万m²)': 34.099, '分布式水泵数量': 0, '当前供热系统': "大唐热电供热系统"}* @param {string} fileName 文件名称* @param [] keySort  字段排序 // ['供热系统', '供热面积(万m²)', '分布式水泵数量','当前供热系统']* @param {string} sheetName sheet名称* @param {boolean} titleNum 表头数*/
import XLSXStyle from "xlsx-style-vite" //"^0.0.2"
import FileSaver from "file-saver";// "^2.0.5"
import * as XLSX from "xlsx";//"^0.17.0",export const exportToExcel = (data, fileName, keySort, sheetName = "sheet1", titleNum = 1) => {// const sheet = XLSX.utils.json_to_sheet(data)const temp = data.map(item => {const arr = []keySort.forEach(k => arr.push(item[k]))return arr})temp.unshift(keySort)const sheet = XLSX.utils.aoa_to_sheet(temp)const wb = {SheetNames: [sheetName],Sheets: {[sheetName]: sheet}}const range = XLSX.utils.decode_range(wb.Sheets[sheetName]['!ref']);//单元格边框样式const borderStyle = {top: {style: "thin",color: {rgb: "000000"}},bottom: {style: "thin",color: {rgb: "000000"}},left: {style: "thin",color: {rgb: "000000"}},right: {style: "thin",color: {rgb: "000000"}}};const cWidth = [];for (let C = range.s.c; C < range.e.c + 1; ++C) {   //SHEET列let len = 100; //默认列宽const len_max = 400; //最大列宽for (let R = range.s.r; R <= range.e.r; ++R) {  //SHEET行const cell = {c: C, r: R};                    //二维 列行确定一个单元格const cell_ref = XLSX.utils.encode_cell(cell);  //单元格 A1、A2if (wb.Sheets[sheetName][cell_ref]) {if (R < titleNum) {wb.Sheets[sheetName][cell_ref].s = {  //设置第一行单元格的样式 stylefont: {sz: 14, // 标题字号color: {rgb: '060B0E'},// 颜色bold: true//加粗},alignment: {horizontal: 'center',vertical: 'center',},fill: {fgColor: {rgb: 'E4E4E4'}, // 背景},border: borderStyle,//用上面定义好的边框样式};} else {wb.Sheets[sheetName][cell_ref].s = {alignment: {horizontal: 'center',vertical: 'center',},border: borderStyle,//用上面定义好的边框样式};}//动态自适应：计算列宽const va = JSON.parse(JSON.stringify(wb.Sheets[sheetName][cell_ref].v || ''));const card1 = JSON.parse(JSON.stringify(va.toString())).match(/[\u4e00-\u9fa5]/g); //匹配中文let card11 = "";if (card1) card11 = card1.join("")const card2 = JSON.parse(JSON.stringify(va.toString())).replace(/([^\u0000-\u00FF])/g, "");  //剔除中文let st = 0;if (card11) st += card11.length * 20  //中文字节码长度if (card2) st += card2.length * 10  //非中文字节码长度if (st > len) len = st;}}cWidth.push({'wpx': len > len_max ? len_max : len});     //列宽}wb.Sheets[sheetName]['!cols'] = cWidth;const wopts = {bookType: 'xlsx', bookSST: false, type: 'binary'};const wbout = XLSXStyle.write(wb, wopts); //一定要用XLSXStyle不要用XLSX，XLSX是没有格式的！FileSaver(new Blob([s2ab(wbout)], {type: ""}), fileName + '.xlsx');function s2ab(s) {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;}
}

将table导为excel

/*** 将table导出为excel* @param {object} options* @param {[]} table 表格元素* @param {string} fileName 文件名称* @param {string} sheetName sheet名称* @param {boolean} titleNum 表头数*/
export const exportTableToExcel = (dom, fileName, sheetName = "sheet1", titleNum = 1) => {const wb = XLSX.utils.table_to_book(dom, {sheet: sheetName, raw: true});const range = XLSX.utils.decode_range(wb.Sheets[sheetName]['!ref']);//单元格边框样式const borderStyle = {top: {style: "thin",color: {rgb: "000000"}},bottom: {style: "thin",color: {rgb: "000000"}},left: {style: "thin",color: {rgb: "000000"}},right: {style: "thin",color: {rgb: "000000"}}};const cWidth = [];for (let C = range.s.c; C < range.e.c + 1; ++C) {   //SHEET列let len = 100; //默认列宽const len_max = 400; //最大列宽for (let R = range.s.r; R <= range.e.r; ++R) {  //SHEET行const cell = {c: C, r: R};                    //二维 列行确定一个单元格const cell_ref = XLSX.utils.encode_cell(cell);  //单元格 A1、A2if (wb.Sheets[sheetName][cell_ref]) {// if (R == 0){if (R < titleNum) {wb.Sheets[sheetName][cell_ref].s = {  //设置第一行单元格的样式 stylefont: {sz: 14,color: {rgb: '060B0E'},bold: true},alignment: {horizontal: 'center',vertical: 'center',},fill: {fgColor: {rgb: 'E4E4E4'},},border: borderStyle,//用上面定义好的边框样式};} else {wb.Sheets[sheetName][cell_ref].s = {alignment: {horizontal: 'center',vertical: 'center',},border: borderStyle,//用上面定义好的边框样式};}//动态自适应：计算列宽const va = JSON.parse(JSON.stringify(wb.Sheets[sheetName][cell_ref].v || ''));const card1 = JSON.parse(JSON.stringify(va.toString())).match(/[\u4e00-\u9fa5]/g); //匹配中文let card11 = "";if (card1) {card11 = card1.join("")}const card2 = JSON.parse(JSON.stringify(va.toString())).replace(/([^\u0000-\u00FF])/g, "");  //剔除中文let st = 0;if (card11) {st += card11.length * 20  //中文字节码长度}if (card2) {st += card2.length * 10  //非中文字节码长度}if (st > len) {len = st;}}}if (len > len_max) {//最大宽度len = len_max;}cWidth.push({'wpx': len});     //列宽}wb.Sheets[sheetName]['!cols'] = cWidth.slice(1, -1);// 删除列-----重点-----0就是第一列。。// wb.Sheets[sheetName]['!cols'][0] = {hidden: true}deleteCol(wb.Sheets[sheetName], 0)deleteCol(wb.Sheets[sheetName], cWidth.length - 1)// 合并列// wb.Sheets[sheetName]["!merges"] = [//   {  //合并A1A2单元格//     s: {//s为开始//       c: 0,//开始列//       r: 0//开始取值范围//     },//     e: {//e结束//       c: 1,//结束列//       r: 0//结束范围//     }//   }// ]const wopts = {bookType: 'xlsx', bookSST: false, type: 'binary'};const wbout = XLSXStyle.write(wb, wopts); //一定要用XLSXStyle不要用XLSX，XLSX是没有格式的！FileSaver(new Blob([s2ab(wbout)], {type: ""}), fileName + '.xlsx');function encodeCell(r, c) {return XLSX.utils.encode_cell({r, c});}// 删除行function deleteRow(ws, index) {const range = XLSX.utils.decode_range(ws['!ref']);for (let row = index; row < range.e.r; row++) {for (let col = range.s.c; col <= range.e.c; col++) {ws[encodeCell(row, col)] = ws[encodeCell(row + 1, col)];}}range.e.r--;ws['!ref'] = XLSX.utils.encode_range(range.s, range.e);}// 删除列function deleteCol(ws, index) {const range = XLSX.utils.decode_range(ws['!ref']);for (let col = index; col < range.e.c; col++) {for (let row = range.s.r; row <= range.e.r; row++) {ws[encodeCell(row, col)] = ws[encodeCell(row, col + 1)];}}range.e.c--;ws['!ref'] = XLSX.utils.encode_range(range.s, range.e);}function s2ab(s) {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;}
}

导出样式

应粉丝要求，附上导出样式

import {h} from "vue";
import {ElMessageBox} from "element-plus";ElMessageBox({title: '导出Excel表格',draggable: true,showCancelButton: true,showConfirmButton: false,message: h('div', null, [ // 这里用到了h函数h(ElButton, { text: true, type: 'primary', innerHTML: '导出选中数据', onClick: assignExport }),h(ElButton, { text: true, type: 'success', innerHTML: '导出所有数据', onClick: allExport })]),cancelButtonText: '取消',
})
// 导出
const assignExport = () =>{console.log(111)
}
const allExport = () =>{console.log(222)
}