使用Web Worker解析大Excel数据仍UI卡顿，求解决方案

我用Sheet JS处理Web应用上传的Excel文件，解析10万条以上数据集时已使用Web Worker，但UI还是会出现冻结。通过DevTools的Performance标签追踪，发现存在帧丢失问题，求解决办法。

相关代码 #

consolidateWorkerV2 #

export const consolidateWorkerV2 = () => {
  const workerCode = `
    /* Load standalone script from CDN */
    import * as XLSX from "https://cdn.sheetjs.com/xlsx-0.20.1/package/xlsx.mjs";
    /* This callback will run once the main context sends a message */

async function getFirstRow(sheet, isInventory) {
  return await new Promise((resolve) => {
    for(let i = 0; i < 1000; i++) {

      if(isInventory){
        const sheetRange = "A4:Y5"
        const arr = XLSX.utils.sheet_to_json(sheet, { range: sheetRange });
        if(arr.length > 0) {
          resolve(arr);
          break;
        };
      } else {
        const sheetRange = "A1:Y" + i.toString();
        const arr = XLSX.utils.sheet_to_json(sheet, { range: sheetRange });
        if(arr.length > 0) {
          resolve(arr);
          break;
        };
      }
      
    };
  }); 
};

function getWorkbook(startRow, endRow, sheet, range, sheetName, headers){
  const chunkRange = { s: { r: startRow, c: range.s.c }, e: { r: endRow, c: range.e.c } };
  const sheetRange = XLSX.utils.encode_range(chunkRange);
  const SHEET_OPTIONS = { range: sheetRange, skipHeader: false, header: headers }
  const chunk = XLSX.utils.sheet_to_json(sheet, SHEET_OPTIONS);
  const chunkSheet = XLSX.utils.json_to_sheet(chunk);
  const workbook = { SheetNames: [sheetName], Sheets: { [sheetName]: chunkSheet } };
  return workbook;
};

self.addEventListener('message', async (e) => {
  try {
    const files = e.data.files; // Get the File object from the message
    for (const [index, item] of files.entries()) {
      const isFinished = index === files.length - 1;
      const file = item.file;
      const filename = item.file.name
      const data = await item.file.arrayBuffer();
      const readWorkbook = XLSX.read(data);
      
      for (const sheetName of readWorkbook.SheetNames) {
        const sheet = readWorkbook.Sheets[sheetName];
        const OPTIONS = { blankrows : false, defval: ''};
        const rowLength = XLSX.utils.sheet_to_row_object_array(sheet, OPTIONS);
        const totalRows = rowLength.length + 1;

        const inventoryBookPattern = /inventory[-_ ]?book/i;
        const isInventory = inventoryBookPattern.test(filename);
        const firstRow = await getFirstRow(sheet, isInventory);

        const range = XLSX.utils.decode_range(sheet["!ref"]);
        const headers = Object.keys(firstRow[0]);

        // Split the workbook into chunks and send each chunk separately
        if(totalRows > 1000){
          for (let startRow = range.s.r + 1; startRow <= range.e.r; startRow += 1000) {
            const MAXIMUM_ROW = 999;
            const endRow = Math.min(startRow + MAXIMUM_ROW, range.e.r);
            const workbook = getWorkbook(startRow, endRow, sheet, range, sheetName, headers);
            const isEndLoop = endRow >= range.e.r;
            postMessage({ workbook, filename, isFinished: isEndLoop });
          } 
        } else {
          const ADDITIONAL_ROWS = isInventory ? 4 : 1;
          const startRow = range.s.r + ADDITIONAL_ROWS;
          const MAXIMUM_ROW = totalRows - (isInventory ? 3 : 0);
          const endRow = Math.min(startRow + MAXIMUM_ROW, totalRows);
          const workbook = getWorkbook(startRow, endRow, sheet, range, sheetName, headers);
          postMessage({ workbook, filename, isFinished: true });
        }       
      }
    }
  } catch(e) {
    /* Pass the error message back */
    postMessage({ error: String(e.message || e).bold() });
  }
}, false);
`;

const blob = new Blob([workerCode], { type: "text/javascript" });
  const worker = new Worker(URL.createObjectURL(blob), { type: "module" });
  return worker;
};

consolidateReport #

async function consolidateReport(event) {
event.preventDefault();
try {
  if (!checkBoxFile.length || checkBoxFile.length === 1) {
    const errorMsg =
      checkBoxFile.length === 1
        ? "Need two files checked."
        : "No files checked.";

    throw new Error(errorMsg);
  } else if (!selectedDirectory){
    throw new Error("No directory selected.");
  }
  
  const files = checkBoxFile.filter((file) => file.isCheck === true);
  const worker = consolidateWorkerV2();
  
  if (files.find((file) => file.fileName === "Inventory Book")) {
    setDesiredDateTrigger(true);
    await waitForModalClose();
  }
  
  const newWorkbook = XLSX.utils.book_new();
  const [date, time] = getCurrentDateTime().split(", ");
  let fileName = namingConvention("Consolidated_Final_Report", date);
  const fileHandle = await selectedDirectory.getFileHandle(fileName + ".xlsx", { create: true });
  let filesCompleted = [];
  setIsLoading(true);

  worker.postMessage({ files });
  worker.onmessage = async (event) => {
    const { workbook, filename, isFinished, error } = event.data;
    if (isFinished) filesCompleted.push(filename);
    // if existing workbook has the same sheet name, then merge
    if (workbook) {
      const journalType = getJournalType(filename);
      try {
        if (newWorkbook.SheetNames.includes(journalType)) {
          const duplicateSheetName = workbook.SheetNames[0];
          const newWorksheet = newWorkbook.Sheets[journalType];
          const dupWorksheet = getDataRange(workbook.Sheets[duplicateSheetName]);
          const OPTIONS = { raw: false, defval: "", };
          const parsedDupWorksheet = XLSX.utils.sheet_to_json(dupWorksheet, OPTIONS);
          const updatedWorksheet = modifyWorksheet(dupWorksheet, parsedDupWorksheet, duplicateSheetName, journalType, reportDate);
          const newData = updatedWorksheet.map((row) => Object.values(row));
          XLSX.utils.sheet_add_aoa(newWorksheet, newData, { origin: -1, skipHeader: true });
        } else {
          // if no existing sheet name, then add new sheet
          for (const sheetName of workbook.SheetNames) {
            const worksheet = workbook.Sheets[sheetName];
            const worksheetData = getDataRange(worksheet);
            const parsedWorksheet = XLSX.utils.sheet_to_json(worksheetData, { raw: false, defval: "", });
            const updatedWorksheet = modifyWorksheet(worksheetData, parsedWorksheet, sheetName, journalType, reportDate);
            const styledSheet = headerStyling(updatedWorksheet);
            XLSX.utils.book_append_sheet(newWorkbook, styledSheet, journalType);
          }
        }
      
        if (files.length === filesCompleted.length) {
          console.log("Consolidate started.");
          const excelBuffer = XLSX.write(newWorkbook, { bookType: "xlsx", type: "array", });
          const blob = new File([excelBuffer], fileName + ".xlsx");
          const writable = await fileHandle.createWritable();
          await writable.write(blob);
          await writable.close();
          worker.terminate();
          console.log("Consolidated report generated.");
          setIsLoading(false);
        } 
      } catch (error) {
        console.log("Error at consolidate: ", error);
        worker.terminate();
        setIsLoading(false);
      }
    } else if (error) {
      console.log("ERROR: ", error);
      worker.terminate();
      setIsLoading(false);
    }
  };
} catch (error) {
  setIsLoading(false);
  console.log("Error at sidebar: ", error);
  let errorMsg = error.message;
  const typeOfError =
    "An operation that depends on state cached in an interface object was made but the state had changed since it was read from disk";

  if (error.message.includes(typeOfError)) {
    errorMsg =
      "Please close the existing file before generating the report.";
  }
  toast.error(errorMsg, {
    toastId: "error-consolidate", // preventing to show multiple toast
    position: "top-center",
    autoClose: 1500,
    hideProgressBar: true,
    closeOnClick: false,
    pauseOnHover: false,
    draggable: false,
    theme: "light",
  });
 }
}

优化方案 #

1. 消除Worker内重复解析的性能浪费 #

getFirstRow中循环调用sheet_to_json会重复解析单元格，改成直接读取单元格值判断，仅在需要时转一次JSON：

async function getFirstRow(sheet, isInventory) {
  return new Promise((resolve) => {
    if(isInventory){
      const cell = sheet["A4"];
      if(cell && cell.v !== undefined && cell.v !== ''){
        const arr = XLSX.utils.sheet_to_json(sheet, { range: "A4:Y5" });
        resolve(arr);
      } else {
        resolve([]);
      }
    } else {
      for(let i = 1; i < 1000; i++) {
        const cellRef = `A${i}`;
        const cell = sheet[cellRef];
        if(cell && cell.v !== undefined && cell.v !== ''){
          const arr = XLSX.utils.sheet_to_json(sheet, { range: `A1:Y${i}` });
          resolve(arr);
          break;
        }
      }
    }
  }); 
}

2. 避免Worker连续postMessage阻塞主线程 #

原循环连续发送chunk会导致主线程消息队列积压，改成异步循环，每次发送后让出线程：

// 替换原大文件chunk处理逻辑
if(totalRows > 1000){
  let startRow = range.s.r + 1;
  const processChunk = () => {
    const MAXIMUM_ROW = 999;
    const endRow = Math.min(startRow + MAXIMUM_ROW, range.e.r);
    const workbook = getWorkbook(startRow, endRow, sheet, range, sheetName, headers);
    const isEndLoop = endRow >= range.e.r;
    postMessage({ workbook, filename, isFinished: isEndLoop });
    
    startRow += 1000;
    if(startRow <= range.e.r){
      setTimeout(processChunk, 0); // 让出线程给主线程处理UI
    }
  };
  processChunk();
}

3. 主线程异步处理消息，避免阻塞UI #

把工作表合并/新增逻辑放到requestIdleCallback中，让UI线程优先处理用户交互：

worker.onmessage = async (event) => {
  const { workbook, filename, isFinished, error } = event.data;
  if (isFinished) filesCompleted.push(filename);
  if (workbook) {
    const journalType = getJournalType(filename);
    // 利用空闲时间处理工作表操作
    requestIdleCallback(async () => {
      try {
        if (newWorkbook.SheetNames.includes(journalType)) {
          // 原合并逻辑保留
        } else {
          // 原新增sheet逻辑保留
        }
      
        if (files.length === filesCompleted.length) {
          // 原写入文件逻辑保留
        } 
      } catch (error) {
        console.log("Error at consolidate: ", error);
        worker.terminate();
        setIsLoading(false);
      }
    });
  } else if (error) {
    console.log("ERROR: ", error);
    worker.terminate();
    setIsLoading(false);
  }
};

4. 简化总行数计算，避免全表解析 #

原sheet_to_row_object_array会解析整个工作表，改成直接用!ref计算行数：

// 替换原rowLength和totalRows计算
const range = XLSX.utils.decode_range(sheet["!ref"]);
const totalRows = range.e.r - range.s.r + 1;

5. 优化getWorkbook的冗余转换 #

原函数把chunk转JSON再转回工作表，改成直接复制单元格范围，减少性能开销：

function getWorkbook(startRow, endRow, sheet, range, sheetName, headers){
  const chunkRange = { s: { r: startRow, c: range.s.c }, e: { r: endRow, c: range.e.c } };
  const chunkSheet = {};
  // 复制指定范围的单元格
  for(let r = chunkRange.s.r; r <= chunkRange.e.r; r++){
    for(let c = chunkRange.s.c; c <= chunkRange.e.c; c++){
      const cellRef = XLSX.utils.encode_cell({r, c});
      if(sheet[cellRef]){
        chunkSheet[cellRef] = {...sheet[cellRef]};
      }
    }
  }
  // 设置表头
  const headerRow = chunkRange.s.r - 1;
  for(let i = 0; i < headers.length; i++){
    const cellRef = XLSX.utils.encode_cell({r: headerRow, c: range.s.c + i});
    chunkSheet[cellRef] = {v: headers[i], t: 's'};
  }
  chunkSheet["!ref"] = XLSX.utils.encode_range({s: {r: headerRow, c: range.s.c}, e: chunkRange.e});
  return { SheetNames: [sheetName], Sheets: { [sheetName]: chunkSheet } };
};

内容的提问来源于stack exchange，提问作者Almighty Dee