基于Vue3 实现自定义拆分Excel 表格

一、功能实现

1.基础功能图示

2.功能详解

1.表头所在行：原Excel 文件表头所在行，用作拆分后表格的表头

2.数据开始行：原Excel 文件中你想从第几行开始拆分，一般情况下“2”为除去表头为所有数据；如果原文件中前两行（除去表头）不想要，填写“4”即可

3.合并单元格处理：原Excel 文件某一列合并了，拆分后的表格会自动展开数据（建议开启）

4.拆分依据列：可根据原Excel 文件的某一列或者多列进行拆分，可以直接写第几列也可以某一列表头名，填写多个时使用逗号或顿号隔开

5.写入起始行、写入起始列：拆分后的表格数据在新的Excel 文件中从第几行第几列开始写入

6.基础校验：数据开始行和拆分依据列等有基础校验，数据开始行填写的行号大于原文件中数据行时进行提示；拆分依据列填写的列号大于原文件的表头列、填写的表头名不存在、列号和表头名实际指向的是同一列时会进行提示

7.导出：默认情况下最多同时导出 4 个文件（部分浏览器会有恶意拦截，记得打开，允许多个文件同时导出），超出 4 个时会下载一个.zip 压缩包，里边包含所有拆分导出文件

8.拆分后表格样式：可自定义表格样式，比如字体、字号、加粗、背景色等，表头行和数据行可单独设置

3.Demo

二、代码

1.安装依赖

xlsx-js-style用于自定义拆分后表格样式

npm install xlsx-js-style

jszip file-saver用于导出过多的Excel 时，以压缩包的形式导出

npm install jszip file-saver

2.核心代码

<template>
  <div class="p-4">
    <el-button type="primary" @click="openDialog">上传并拆分 Excel (完美数据版)</el-button>
 
    <el-dialog v-model="dialogVisible" title="Excel 拆分配置" width="600px">
      <el-form label-width="140px">
        <el-form-item label="上传文件">
          <input type="file" ref="fileInput" accept=".xlsx, .xls" @change="handleFileChange" />
        </el-form-item>
 
        <el-divider content-position="left">读取与预处理</el-divider>
        <el-row :gutter="20">
          <el-col :span="12">
            <el-form-item label="表头所在行">
              <el-input-number v-model="headerRowNum" :min="1" />
            </el-form-item>
          </el-col>
          <el-col :span="12">
            <el-form-item label="数据开始行">
              <el-input-number v-model="dataStartRow" :min="1" />
            </el-form-item>
          </el-col>
        </el-row>
 
        <el-form-item label="合并单元格处理">
          <el-switch v-model="enableFill" active-text="自动向下填充空值(处理合并行)" />
          <div class="text-gray-400 text-xs mt-1">开启后，合并单元格产生的空行将自动补齐数据，确保拆分不遗漏。</div>
        </el-form-item>
 
        <el-form-item label="拆分依据列">
          <el-input v-model="splitInput" placeholder="例如：2、3或者部门、姓名或者2、姓名" />
          <div class="text-gray-400 text-xs mt-1">
            支持多个列名或列号，用逗号或顿号隔开。
          </div>
        </el-form-item>
 
        <el-divider content-position="left">写入配置 (新文件起始位置)</el-divider>
        <el-row :gutter="20">
          <el-col :span="12">
            <el-form-item label="写入起始行">
              <el-input-number v-model="writeStartRow" :min="1" />
            </el-form-item>
          </el-col>
          <el-col :span="12">
            <el-form-item label="写入起始列">
              <el-input-number v-model="writeStartCol" :min="1" />
            </el-form-item>
          </el-col>
        </el-row>
      </el-form>
 
      <template #footer>
        <el-button @click="dialogVisible = false">取消</el-button>
        <el-button type="primary" :loading="processing" @click="processExcel" :disabled="!file">
          执行拆分导出
        </el-button>
      </template>
    </el-dialog>
  </div>
</template>
 
<script setup>
import { ref, watch } from 'vue';
import XLSX from 'xlsx-js-style';
import JSZip from 'jszip';
import { saveAs } from 'file-saver';
import { ElMessage, ElMessageBox } from 'element-plus';
 
const dialogVisible = ref(false);
const processing = ref(false);
const file = ref(null);
const fileInput = ref(null);
 
const headerRowNum = ref(1);
const dataStartRow = ref(2);
const splitInput = ref('');
const enableFill = ref(true); // 默认开启填充
const writeStartRow = ref(1);
const writeStartCol = ref(1);
 
const openDialog = () => {
  file.value = null;
  if (fileInput.value) fileInput.value.value = '';
  headerRowNum.value = 1;
  dataStartRow.value = 2;
  splitInput.value = '';
  writeStartRow.value = 1;
  writeStartCol.value = 1;
  dialogVisible.value = true;
};
 
watch(headerRowNum, (newVal) => { dataStartRow.value = newVal + 1; });
 
const handleFileChange = (e) => { file.value = e.target.files[0]; };
 
/**
 * 辅助方法：向下填充逻辑
 */
const fillDataLogic = (rows) => {
  if (rows.length < 2) return rows;
  // 遍历列
  for (let c = 0; c < rows[0].length; c++) {
    for (let r = 1; r < rows.length; r++) {
      // 如果当前单元格是空的，且上一行同列有值，则继承
      if ((rows[r][c] === null || rows[r][c] === '') && (rows[r - 1][c] !== null && rows[r - 1][c] !== '')) {
        rows[r][c] = rows[r - 1][c];
      }
    }
  }
  return rows;
};
 
const processExcel = async () => {
  if (!file.value || !splitInput.value) {
    ElMessage.warning("请检查配置");
    return;
  }
 
  processing.value = true;
  const zip = new JSZip();
  const exportFiles = [];
 
  try {
    const data = await file.value.arrayBuffer();
    const workbook = XLSX.read(data, { type: 'buffer' });
    const worksheet = workbook.Sheets[workbook.SheetNames[0]];
    const fullData = XLSX.utils.sheet_to_json(worksheet, { header: 1, defval: '' });
 
    if (fullData.length === 0) throw new Error("Excel内容为空");
 
    const hIdx = headerRowNum.value - 1;
    const headerRow = fullData[hIdx];
    const dIdx = dataStartRow.value - 1;
 
    // 提取数据行
    let dataRows = fullData.slice(dIdx).filter(row => row.some(cell => cell !== ''));
 
    // --- 预处理：合并单元格填充 ---
    if (enableFill.value) {
      dataRows = fillDataLogic(dataRows);
    }
 
    // --- 校验：重复列拦截 ---
    const splitKeys = splitInput.value.split(/[、，,\s]/).map(s => s.trim()).filter(s => s);
    const taskMap = new Map();
    const duplicates = [];
 
    for (const key of splitKeys) {
      let colIdx = /^\d+$/.test(key) ? parseInt(key) - 1 : headerRow.findIndex(item => String(item).trim() === key);
      if (colIdx === -1 || colIdx >= headerRow.length) throw new Error(`找不到列: ${key}`);
 
      if (taskMap.has(colIdx)) {
        duplicates.push(`[${key}] 对应索引 ${colIdx + 1} 已由 [${taskMap.get(colIdx)}] 指定`);
      } else {
        taskMap.set(colIdx, key);
      }
    }
 
    if (duplicates.length > 0) {
      throw new Error(`列输入重复拦截：${duplicates.join('；')}`);
    }
 
    const validTasks = Array.from(taskMap).map(([colIdx, key]) => ({ key, colIdx }));
 
    // --- 核心拆分与导出 ---
    validTasks.forEach(task => {
      const { key, colIdx } = task;
      const groups = {};
      dataRows.forEach(row => {
        const gKey = String(row[colIdx] || '未分类').trim();
        if (!groups[gKey]) groups[gKey] = [];
        groups[gKey].push(row);
      });
 
      Object.keys(groups).forEach(val => {
        const finalAOA = [];
        const startR = writeStartRow.value - 1;
        const startC = writeStartCol.value - 1;
 
        for (let i = 0; i < startR; i++) finalAOA.push([]);
        const headWithOffset = [...Array(startC).fill(""), ...headerRow];
        finalAOA.push(headWithOffset);
 
        groups[val].forEach(row => {
          finalAOA.push([...Array(startC).fill(""), ...row]);
        });
 
        const newWS = XLSX.utils.aoa_to_sheet(finalAOA);
 
        // 样式处理 (确保白底黑字)
        const range = XLSX.utils.decode_range(newWS['!ref']);
        for (let R = range.s.r; R <= range.e.r; ++R) {
          for (let C = range.s.c; C <= range.e.c; ++C) {
            const cellRef = XLSX.utils.encode_cell({ r: R, c: C });
            if (!newWS[cellRef] || newWS[cellRef].v === "") continue;
 
            const isHeader = (R === startR && C >= startC);
            newWS[cellRef].s = {
              font: { name: "Arial", sz: isHeader ? 12 : 10, bold: isHeader, color: isHeader ? { rgb: "FFFFFF" } : { rgb: "000000" } },
              fill: { patternType: "solid", fgColor: { rgb: isHeader ? "4F81BD" : "FFFFFF" } },
              alignment: { vertical: "center", horizontal: "center", wrapText: true },
              border: { top: { style: "thin" }, bottom: { style: "thin" }, left: { style: "thin" }, right: { style: "thin" } }
            };
          }
        }
 
        newWS['!cols'] = Array(startC + headerRow.length).fill({ wch: 15 });
        newWS['!rows'] = finalAOA.map((_, i) => ({ hpx: i === startR ? 30 : 22 }));
 
        const newWB = XLSX.utils.book_new();
        XLSX.utils.book_append_sheet(newWB, newWS, "Sheet1");
 
        // const colLabel = /^\d+$/.test(key) ? `第${key}列` : key;
        const fileName = `${file.value.name.split('.')[0]}_${val}.xlsx`.replace(/[\\/:*?"<>|]/g, '_');
 
        const wbout = XLSX.write(newWB, { bookType: 'xlsx', type: 'array' });
        exportFiles.push({ name: fileName, data: wbout });
      });
    });
 
    // 导出判定
    if (exportFiles.length > 5) {
      exportFiles.forEach(f => zip.file(f.name, f.data));
      const blob = await zip.generateAsync({ type: "blob" });
      saveAs(blob, `拆分结果集_${new Date().getTime()}.zip`);
      ElMessage.success(`检测到 ${exportFiles.length} 个文件，已打包为 ZIP`);
    } else {
      exportFiles.forEach(f => saveAs(new Blob([f.data]), f.name));
      ElMessage.success(`导出完成，共 ${exportFiles.length} 个文件`);
    }
 
    dialogVisible.value = false;
  } catch (error) {
    ElMessageBox.alert(error.message, '操作终止', { type: 'error' });
  } finally {
    processing.value = false;
  }
};
</script>