vue3纯前端导出Excel

1、下载插件

1. npm install xlsx –save

2. npm install xlsx-style-vite –save

3. npm install file-saver -save

主要Excel操作：

wb"!cols" 行操作

wb"!rows" 列操作

wb"!merges三" 合并单元格

2、在外部文件夹中定义js文件，在js文件中写入Excel导入方法（抛出“文件名”和“节点Dom”）

import {write as StyleWtite} from "xlsx-style-vite";
import {utils} from "xlsx";

/**
 * @description: 导出Excel
 * @param {String} fileName 文件名
 * @param {String} tableid 表格id
 * */

// 样式的文档地址
// https://www.npmjs.com/package/xlsx-style
// 定义表头
const ExcelHeader=['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']

export function exportExcelWeb(fileName,tableid) {
  var xlsxParam = {raw: true}; //这个保证表格只进行解析 不做运算
  var wb = utils.table_to_sheet(
    document.querySelector(`#${tableid}`),
    xlsxParam
  );
  let wbKeyArray=[]
  let SameKeyArray=[]
  for (let wbkey in wb) {
    wbKeyArray.push(wbkey)
  }
  wbKeyArray.map(item1=>{
    ExcelHeader.map(item2=>{
      if(item2==item1){
        SameKeyArray.push(item2)
      }
    })
  })

  for (var i = 0; i < SameKeyArray.length; i++) {
    wb["!cols"][i] = {wpx: 300};
  }
  for (const key in wb) {
    if (key.indexOf("!") === -1 && wb[key].v) {
      if ( ExcelHeader.includes(key) ) {
        wb[key].s = {
          fill: { //背景色
            fgColor: {rgb: 'C0C0C0'}
          },
          font: {
            //字体设置
            sz: 13,
            bold: true,
            color: {
              rgb: "000000", //十六进制，不带#
            },
          },
          alignment: {
            //文字居中
            horizontal: "center",
            vertical: "center",
            wrap_text: true,
          },
        }
      } else {
        hpx: 30,
          wb[key].s = {
            font: {
              //字体设置
              sz: 13,
              color: {
                rgb: "000000", //十六进制，不带#
              },
            },
            alignment: {
              //文字居中
              horizontal: "center",
              vertical: "center",
              wrap_text: true,
            },
          };
      }
    }
  }
  var data = addRangeBorder(wb["!merges"], wb) //合并项添加边框
  var filedata = sheet2blob(data)
  openDownloadDialog(filedata, `${fileName}.xlsx`)
}

function addRangeBorder(range, ws) {
  let arr = [
    "A",
    "B",
    "C",
    "D",
    "E",
    "F",
    "G",
    "H",
    "I",
    "J",
    "K",
    "L",
    "M",
    "N",
    "O",
    "P",
    "Q",
    "R",
    "S",
    "T",
    "U",
    "V",
    "W",
    "X",
    "Y",
    "Z",
  ];
  if (range) {
    range.forEach((item) => {
      let startColNumber = Number(item.s.r),
        endColNumber = Number(item.e.r);
      let startRowNumber = Number(item.s.c),
        endRowNumber = Number(item.e.c);
      const test = ws[arr[startRowNumber] + (startColNumber + 1)];
      for (let col = startColNumber; col <= endColNumber; col++) {
        for (let row = startRowNumber; row <= endRowNumber; row++) {
          ws[arr[row] + (col + 1)] = test;
        }
      }
    });
  }
  return ws;
}

function sheet2blob(sheet, sheetName) {
  sheetName = sheetName || "sheet1";
  var workbook = {
    SheetNames: [sheetName],
    Sheets: {},
  };
  workbook.Sheets[sheetName] = sheet; // 生成excel的配置项

  var wopts = {
    bookType: "xlsx", // 要生成的文件类型
    bookSST: false, // 是否生成Shared String Table，官方解释是，如果开启生成速度会下降，但在低版本IOS设备上有更好的兼容性
    type: "binary",
  };
  var wbout = StyleWtite(workbook, wopts);
  var blob = new Blob([s2ab(wbout)], {
    type: "application/octet-stream",
  }); // 字符串转ArrayBuffer
  function s2ab(s) {
    var buf = new ArrayBuffer(s.length);
    var view = new Uint8Array(buf);
    for (var i = 0; i != s.length; ++i) view[i] = s.charCodeAt(i) & 0xff;
    return buf;
  }

  return blob;
}

function openDownloadDialog(url, saveName) {
  if (typeof url == "object" && url instanceof Blob) {
    url = URL.createObjectURL(url); // 创建blob地址
  }
  var aLink = document.createElement("a");
  aLink.href = url;
  aLink.download = saveName || ""; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
  var event;
  if (window.MouseEvent) event = new MouseEvent("click");
  else {
    event = document.createEvent("MouseEvents");
    event.initMouseEvent(
      "click",
      true,
      false,
      window,
      0,
      0,
      0,
      0,
      0,
      false,
      false,
      false,
      false,
      0,
      null
    );
  }
  aLink.dispatchEvent(event);
}

3、在所要导出Excel的vue文件中进行调用

<template>
    <el-button type="success" icon="Download" @click="handleExport">导出</el-button>

    <div v-if="modelValue" id="tableDownload">……</div>
</template>	

<script setup>
    import { exportExcelWeb} from "@/views/exam/tjfx/exportExcel";
    /** 导出按钮操作 */
    function handleExport() {
      exportExcelWeb('练习考核统计列表','tableDownload')
    }
</script>