使用插件: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>