vue3前端实现excel表格两表合并

最新推荐文章于 2025-06-12 17:26:50 发布

原创最新推荐文章于 2025-06-12 17:26:50 发布 · 265 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#前端

使用插件：xlxs

<template>
  <div>
    <input type="file" @change="handleFileMain" accept=".xlsx" />
    <input type="file" @change="handleFileBbb" accept=".xls,.xlsx" />
    <button @click="mergeExcelFiles">合并表格</button>
  </div>
</template>
<script setup lang="ts">
import { getCurrentInstance, ref, onMounted } from 'vue';
import * as XLSX from "xlsx";

const fileMain = ref(null);
const fileBbb = ref(null);

// 处理 main 文件上传
const handleFileMain = (event) => {
  fileMain.value = event.target.files[0];
};

// 处理 bbb 文件上传
const handleFileBbb = (event) => {
  fileBbb.value = event.target.files[0];
};

// 合并 Excel 文件函数
const mergeExcelFiles = async () => {
  if (!fileMain.value || !fileBbb.value) {
    alert("请先选择两个文件");
    return;
  }

  // 读取 main.xlsx 文件
  const workbookMain = XLSX.read(await fileMain.value.arrayBuffer(), { type: "array" });
  const sheetMain = workbookMain.Sheets[workbookMain.SheetNames[0]];
  const dataMain = XLSX.utils.sheet_to_json(sheetMain, { header: 1 });

  // 读取 bbb.xls 文件
  const workbookBbb = XLSX.read(await fileBbb.value.arrayBuffer(), { type: "array" });
  const sheetBbb = workbookBbb.Sheets[workbookBbb.SheetNames[0]];
  const dataBbb = XLSX.utils.sheet_to_json(sheetBbb, { header: 1 });

  // 获取表头
  const headersMain = dataMain[0];
  const headersBbb = dataBbb[0];
  console.log(headersMain,'---headersMain');
  console.log(headersBbb,'---headersBbb');


  

  // 遍历 bbb 中的数据并按 main 表头对齐
  dataBbb.slice(1).forEach((rowBbb) => {
    const newRow = Array(headersMain.length).fill(null);  // 创建与 main 表头长度一致的行
    console.log(rowBbb,'---rowBbb');
    
    rowBbb.forEach((cell, index) => {
      const columnB = headersBbb[index];
      const columnAIndex = headersMain.indexOf(columnB);

      if (columnAIndex !== -1) {
        newRow[columnAIndex] = cell;
      }
    });

    dataMain.push(newRow);  // 将新行追加到 main 的数据中
  });

  // 将合并数据写入 main 工作表
  const newSheetMain = XLSX.utils.aoa_to_sheet(dataMain);
  workbookMain.Sheets[workbookMain.SheetNames[0]] = newSheetMain;

  // 导出合并后的新 Excel 文件
  const newExcel = XLSX.write(workbookMain, { bookType: "xlsx", type: "array" });
  const blob = new Blob([newExcel], { type: "application/octet-stream" });

  const url = URL.createObjectURL(blob);
  const link = document.createElement("a");
  link.href = url;
  link.download = "111.xlsx";
  link.click();
  URL.revokeObjectURL(url);
};
</script>
<style scoped lang="scss"></style>