前段切片上传Excel文件到后端解析并添加到数据库

前端

<script setup lang="ts">
import { ref } from "vue";
import SparkMD5 from "spark-md5";
 
const CHUNK_SIZE = 1024 * 1024; // 1MB
const fileName = ref(""); // 文件名称
const fileHash = ref(""); // 文件hash
 
// 创建文件分片
const createChunks = (file: File) => {
  let start = 0;
  const chunks = [];
  while (start < file.size) {
    chunks.push(file.slice(start, start + CHUNK_SIZE));
    start += CHUNK_SIZE;
  }
  return chunks;
};
 
// 计算文件内容hash值
const calculateHash = (file: File): Promise<string> => {
  return new Promise((resolve) => {
    const fileReader = new FileReader();
    fileReader.readAsArrayBuffer(file);
    fileReader.onload = function (e) {
      const spark = new SparkMD5.ArrayBuffer();
      spark.append((e.target as FileReader).result as ArrayBuffer);
      resolve(spark.end());
    };
  });
};
 
// 控制请求并发
const concurRequest = (
  taskPool: Array<() => Promise<Response>>,
  max: number
): Promise<Array<Response | unknown>> => {
  return new Promise((resolve) => {
    if (taskPool.length === 0) {
      resolve([]);
      return;
    }
 
    const results: Array<Response | unknown> = [];
    let index = 0;
    let count = 0;
 
    const request = async () => {
      if (index === taskPool.length) return;
      const i = index;
      const task = taskPool[index];
      index++;
      try {
        results[i] = await task();
      } catch (err) {
        results[i] = err;
      } finally {
        count++;
        if (count === taskPool.length) {
          resolve(results);
        }
        request();
      }
    };
 
    const times = Math.min(max, taskPool.length);
    for (let i = 0; i < times; i++) {
      request();
    }
  });
};
 
// 合并分片请求
const mergeRequest = () => {
  fetch("http://localhost:3000/merge", {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      fileHash: fileHash.value,
      fileName: fileName.value,
    }),
  });
};
 
// 上传文件分片
const uploadChunks = async (
  chunks: Array<Blob>,
  existChunks: Array<string>
) => {
  const formDatas = chunks
    .map((chunk, index) => ({
      fileHash: fileHash.value,
      chunkHash: fileHash.value + "-" + index,
      chunk,
    }))
    .filter((item) => !existChunks.includes(item.chunkHash))
    .map((item) => {
      const formData = new FormData();
      formData.append("fileHash", item.fileHash);
      formData.append("chunkHash", item.chunkHash);
      formData.append("chunk", item.chunk);
      return formData;
    });
 
  const taskPool = formDatas.map(
    (formData) => () =>
      fetch("http://localhost:3000/upload", {
        method: "POST",
        body: formData,
      })
  );
 
  // 控制请求并发
  await concurRequest(taskPool, 6);
 
  // 合并分片请求
  mergeRequest();
};
 
// 校验文件、文件分片是否存在
const verify = (fileHash: string, fileName: string) => {
  return fetch("http://localhost:3000/verify", {
    method: "POST",
    headers: {
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      fileHash,
      fileName,
    }),
  }).then((res) => res.json());
};
 
// 绑定上传事件
const handleUpload = async (e: Event) => {
  const file = (e.target as HTMLInputElement).files?.[0];
  if (!file) return;
 
  fileName.value = file.name;
 
  // 创建文件分片
  const chunks = createChunks(file);
 
  // 计算文件内容hash值
  fileHash.value = await calculateHash(file);
 
  // 校验文件、文件分片是否存在
  const verifyRes = await verify(fileHash.value, fileName.value);
  const { existFile, existChunks } = verifyRes.data;
  if (existFile) return;
 
  // 上传文件分片
  uploadChunks(chunks, existChunks);
};
</script>
 
<template>
  <h1>大文件上传</h1>
  <input type="file" @change="handleUpload" />
</template>

后端：

const express = require('express');
const router = express.Router();
const fse = require("fs-extra");
const path = require("path");
const multipart = require("connect-multiparty");
const multipartMiddleware = multipart();
const xlsx = require('xlsx'); // 引入xlsx模块
const { dataModel } = require('../model/module');

// 所有上传的文件存放在该目录下
const UPLOADS_DIR = path.resolve("uploads");

// 接收文件分片
router.post("/upload", multipartMiddleware, (req, res) => {
    const { fileHash, chunkHash } = req.body;

    // 清空临时文件夹，确保只有当前一个文件
    const chunkDir = path.resolve(UPLOADS_DIR, fileHash);
    fse.emptyDirSync(chunkDir);

    // 将用户上传的分片移到临时文件夹里
    const chunkPath = path.resolve(chunkDir, chunkHash);
    if (!fse.existsSync(chunkPath)) {
        fse.moveSync(req.files.chunk.path, chunkPath);
    }

    res.send({
        success: true,
        msg: "上传成功",
    });
});


// 合并文件分片
router.post("/merge", async (req, res) => {
    const { fileHash, fileName } = req.body;

    // 最终合并的文件路径
    const filePath = path.resolve(UPLOADS_DIR, fileHash + path.extname(fileName));
    // 临时文件夹路径
    const chunkDir = path.resolve(UPLOADS_DIR, fileHash);

    // 读取临时文件夹，获取该文件夹下“所有文件（分片）名称”的数组对象
    const chunkPaths = fse.readdirSync(chunkDir);

    // 读取临时文件夹获得的文件（分片）名称数组可能乱序，需要重新排序
    chunkPaths.sort((a, b) => a.split("-")[1] - b.split("-")[1]);

    // 遍历文件（分片）数组，将分片追加到文件中
    const pool = chunkPaths.map(
        (chunkName) =>
            new Promise((resolve) => {
                const chunkPath = path.resolve(chunkDir, chunkName);
                // 将分片追加到文件中
                fse.appendFileSync(filePath, fse.readFileSync(chunkPath));
                // 删除分片
                fse.unlinkSync(chunkPath);
                resolve();
            })
    );
    await Promise.all(pool);
    // 等待所有分片追加到文件后，删除临时文件夹
    fse.removeSync(chunkDir);

    // 读取合并后的Excel文件数据并打印
    const workbook = xlsx.readFile(filePath);
    const sheetName = workbook.SheetNames[0]; // 假设只有一个工作表
    const worksheet = workbook.Sheets[sheetName];
    const data = xlsx.utils.sheet_to_json(worksheet);


    // 创建一个新数组来存储数据
    const dataToInsert = [];

    for (let i = 0; i < data.length; i++) {
        let employee = {
            PatientName: data[i]['患者姓名'],
            MedicalCardNumber: data[i]['就诊卡号'],
            IDNumber: data[i]['身份证号'],
            PhoneNumber: data[i]['手机号码'],
            Department: data[i]['挂号科室'],
            RegistrationType: data[i]['挂号类型'],
            RegistrationFee: data[i]['挂号费'],
            RegistrationStatus: data[i]['挂号状态'],
            ConsultationDate: data[i]['就诊日期'],
            WaitingTime: data[i]['候诊时间'],
            AppointmentTime: data[i]['预约时间'],
        };
        dataToInsert.push(employee);
    }
    // 使用 Promise.all 并行处理数据插入
    await Promise.all(dataToInsert.map(item => dataModel.create(item)));


    console.log(dataToInsert, 'dddddddddddddddddd');

    // 删除UPLOADS_DIR中的文件
    const filesToDelete = fse.readdirSync(UPLOADS_DIR);
    filesToDelete.forEach(file => {
        fse.unlinkSync(path.resolve(UPLOADS_DIR, file));
    });

    res.send({
        success: true,
        msg: "合并成功",
        data: {
            excelData: data
        }
    });
});

router.post("/verify", (req, res) => {
    const { fileHash, fileName } = req.body;

    // 判断服务器上是否存在该hash值的文件
    const filePath = path.resolve(UPLOADS_DIR, fileHash + path.extname(fileName));
    const existFile = fse.existsSync(filePath);

    // 获取已经上传到服务器的文件分片
    const chunkDir = path.resolve(UPLOADS_DIR, fileHash);
    const existChunks = [];
    if (fse.existsSync(chunkDir)) {
        existChunks.push(...fse.readdirSync(chunkDir));
    }

    res.send({
        success: true,
        msg: "校验文件",
        data: {
            existFile,
            existChunks,
        },
    });
});

module.exports = router;