npm install xlsx-js-style

<script lang="ts" setup>
import { computed, nextTick, ref, watch } from "vue"

import * as XLSX from "xlsx-js-style"

interface Props {
  modelValue: boolean
  downloadList: any
  columnList: any
}

const props = withDefaults(defineProps<Props>(), {
  modelValue: false,
  downloadList: [],
  columnList: []
})

const downloadList = computed(() => props.downloadList)

const emit = defineEmits<{
  (e: "update:modelValue", value: boolean): boolean
}>()

const columnList = computed(() => props.columnList)

//#region 導(dǎo)出
const tableRef = ref<any>(null)
// 導(dǎo)出為 Excel
const exportToExcel = () => {
  // 獲取 el-table 的引用
  tableRef.value = document.querySelector("#download-table")

  // 將 el-table 數(shù)據(jù)轉(zhuǎn)換為二維數(shù)組
  const dataArray = []
  const headers: any = []

  tableRef.value.querySelectorAll(".el-table__header-wrapper th").forEach((th: any) => {
    headers.push(th.textContent.trim())
  })
  dataArray.push(headers)

  const rowsToExport = tableRef.value.querySelectorAll(".el-table__body-wrapper tbody tr")
  console.log(rowsToExport, "rowsToExport")

  rowsToExport.forEach((row: any) => {
    const rowData: any = []
    row.querySelectorAll("td").forEach((cell: any) => {
      rowData.push(cell.textContent.trim())
    })
    dataArray.push(rowData)
  })

  // 創(chuàng)建一個(gè)新的工作簿
  const workbook = XLSX.utils.book_new()

  // 創(chuàng)建一個(gè)新的工作表
  const worksheet = XLSX.utils.aoa_to_sheet(dataArray)

  console.log(dataArray, "dataArray")

  // 設(shè)置列寬
  const columnWidth = 20 // 列寬為20個(gè)字符
  const numColumns = dataArray[0].length // 獲取列數(shù)
  worksheet["!cols"] = Array(numColumns).fill({ wch: columnWidth })

  // // 設(shè)置列高
  const rowHeight = 105 // 行高為110像素
  const numRows = dataArray.length // 獲取行數(shù)
  worksheet["!rows"] = Array(numRows).fill({ hpx: rowHeight })
  worksheet["!rows"][0] = { hpx: 15 } // 單獨(dú)設(shè)置第一行的行高
  // 設(shè)置單元格樣式
  const cellStyle = {
    font: {
      name: "微軟黑體", // 字體名稱
      sz: 13, // 字體大小
      color: { rgb: "000000" }, // 字體顏色
      bold: false // 字體不加粗
    },
    border: {
      top: { style: "thin", color: { rgb: "000000" } },
      bottom: { style: "thin", color: { rgb: "000000" } },
      left: { style: "thin", color: { rgb: "000000" } },
      right: { style: "thin", color: { rgb: "000000" } }
    },
    alignment: {
      horizontal: "center",
      vertical: "center",
      wrapText: true // 設(shè)置文字自動(dòng)換行
    }
  }

  // 遍歷數(shù)據(jù)數(shù)組，為每個(gè)單元格應(yīng)用樣式
  dataArray.forEach((row, rowIndex) => {
    row.forEach((cell: any, columnIndex: any) => {
      const cellAddress = XLSX.utils.encode_cell({ c: columnIndex, r: rowIndex })
      worksheet[cellAddress].s = cellStyle
    })
  })

  // 表頭顏色加粗
  if (dataArray[0]) {
    dataArray[0].forEach((cell: any, columnIndex: any) => {
      const cellAddress = XLSX.utils.encode_cell({ c: columnIndex, r: 0 })
      worksheet[cellAddress].s = { ...cellStyle, font: { ...cellStyle.font, bold: true } } // 確保表頭字體加粗
    })
  }

  // 將工作表添加到工作簿
  XLSX.utils.book_append_sheet(workbook, worksheet, "Sheet1")

  // 將工作簿保存為 Excel 文件

  const timestamp = new Date().getTime()
  const fileName = `寶貝統(tǒng)計(jì)_${timestamp}.xlsx`
  XLSX.writeFile(workbook, fileName)
  emit("update:modelValue", false)
}
//#endregion

watch(
  () => props.modelValue,
  (value: any) => {
    if (value) {
      console.log(downloadList.value)
      nextTick(() => {
        exportToExcel()
      })
    }
  }
)
</script>
<template>
  <el-table style="display: none" :data="downloadList" id="download-table">
    <el-table-column
      v-for="(item, index) in columnList"
      :key="index"
      :prop="item.prop"
      :label="item.label"
      align="center"
    />
  </el-table>
</template>
<style lang="scss" scoped></style>

import DownloadExcel from "@/components/DownloadExcel/index.vue"
const columnList = [
  {
    prop: "index",
    label: "序號(hào)"
  },
  {
    prop: "username",
    label: "用戶名"
  },
  {
    prop: "roles",
    label: "角色"
  },
  {
    prop: "phone",
    label: "手機(jī)號(hào)"
  },
  {
    prop: "email",
    label: "郵箱"
  },
  {
    prop: "status",
    label: "狀態(tài)"
  },
  {
    prop: "createTime",
    label: "創(chuàng)建時(shí)間"
  }
]
const downloadList = ref<any[]>([])
const isDownload = ref<boolean>(false)
const exportToExcel = () => {
  if (multipleSelection.value.length == 0) {
    ElMessage.error("請(qǐng)選擇要下載的寶貝")
    return
  }
  downloadList.value = multipleSelection.value.map((item: any, index: number) => {
    return {
      index: index, // 序號(hào)
      username: item.username, // 用戶名
      roles: item.roles, // 角色
      phone: item.phone, // 手機(jī)號(hào)
      email: item.email, // 郵箱
      status: item.status ? "啟用" : "禁用", // 狀態(tài)
      createTime: item.createTime // 創(chuàng)建時(shí)間
    }
  })
  isDownload.value = true
}

<download-excel v-model="isDownload" :downloadList="downloadList" :columnList="columnList" />
<div>
  <el-tooltip content="下載">
    <el-button type="primary" :icon="Download" circle @click="exportToExcel" />
  </el-tooltip>
</div>

npm install xlsx --save

import * as XLSX from "xlsx"

<el-upload
  class="upload-demo"
  ref="upload"
  action=""
  :auto-upload="false"
  accept=""
  :on-change="analysisExcel"
  multiple
  :show-file-list="false"
>
  <el-button type="primary" :icon="Link">導(dǎo)入文件列表</el-button>
</el-upload>

import * as XLSX from "xlsx"
const loading = ref<boolean>(false)
const tableData = ref<any[]>([])
const analysisExcel = (file: any) => {
  loading.value = true
  console.log(file)
  // 只能上傳一個(gè)Excel，重復(fù)上傳會(huì)覆蓋之前的
  file = file.raw
  const reader = new FileReader()
  reader.readAsArrayBuffer(file)
  reader.onload = function () {
    const buffer: any = reader.result

    const bytes = new Uint8Array(buffer)
    const length = bytes.byteLength
    let binary = ""
    for (let i = 0; i < length; i++) {
      binary += String.fromCharCode(bytes[i])
    }
    const wb = XLSX.read(binary, {
      type: "binary"
    })
    const outdata = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[0]])
    console.log(outdata)
    let da = [...outdata]
    // 這里是把表格里面的名稱改成表格里面的字段
    da = da.map((item: any) => {
      const newItem = {
        index: Number(item["序號(hào)"]), // 序號(hào)
        username: item["用戶名"], // 用戶名
        roles: item["角色"], // 角色
        phone: item["手機(jī)號(hào)"], // 手機(jī)號(hào)
        email: item["郵箱"], // 郵箱
        status: item["狀態(tài)"], // 狀態(tài)
        createTime: item["創(chuàng)建時(shí)間"] // 創(chuàng)建時(shí)間
      }
      return newItem
    })
    console.log(da)

    tableData.value = da
    loading.value = false
  }
}