如何用JavaScript获取Excel单元格验证规则？解决ExcelJS内存溢出问题

一、优化ExcelJS读取逻辑，解决内存溢出 #

即便调大Node内存上限至16GB，ExcelJS默认全量加载模式仍可能因文件包含大量空单元格、复杂样式或数据验证规则导致内存溢出，可通过以下方式优化：

1. 精简解析范围与选项
   仅读取必要内容，关闭样式、公式等非必需解析项，同时跳过空行空单元格：

   const ExcelJS = require('exceljs');
   const fs = require("fs");
   
   async function readExcelFile(filePath) {
     const workbook = new ExcelJS.Workbook();
     const stream = fs.createReadStream(filePath);
   
     // 关闭非必需解析项，降低内存占用
     await workbook.xlsx.read(stream, {
       ignoreStyles: true,
       ignoreFormulas: true,
       sharedStrings: 'cache'
     });
   
     const worksheet = workbook.getWorksheet(1);
     // 仅处理有数据的行和单元格
     worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
       row.eachCell({ includeEmpty: false }, (cell, colNumber) => {
         const validation = cell.dataValidation || cell._dataValidation;
         console.log(`Row ${rowNumber}, Col ${colNumber} - 验证规则:`, validation);
       });
     });
   }
   
   readExcelFile('PAG KIC TAB A (1)(1).xlsx');
   

2. 分批处理数据
   针对超大型工作表，按批次读取并处理，处理完一批后手动释放内存：

   async function readExcelInBatches(filePath, batchSize = 1000) {
     const workbook = new ExcelJS.Workbook();
     await workbook.xlsx.readFile(filePath, { ignoreStyles: true });
     const worksheet = workbook.getWorksheet(1);
     const rowCount = worksheet.rowCount;
   
     for (let i = 1; i <= rowCount; i += batchSize) {
       const endRow = Math.min(i + batchSize - 1, rowCount);
       const batchRows = worksheet.getRows(i, endRow);
       batchRows.forEach(row => {
         row.eachCell({ includeEmpty: false }, (cell, colNumber) => {
           const validation = cell.dataValidation || cell._dataValidation;
           // 按需处理数据
         });
       });
       // 手动释放批次内存
       batchRows.forEach(row => row.values = null);
     }
   }
   

二、用SheetJS（xlsx）读取数据验证（含下拉菜单选项） #

SheetJS支持读取数据验证规则，需解析工作表的!dataValidations属性，以下是提取下拉选项的具体实现：

const XLSX = require('xlsx');

function readDataValidation(filePath) {
  const workbook = XLSX.readFile(filePath, { cellStyles: false });
  const worksheet = workbook.Sheets[workbook.SheetNames[0]];
  
  const dvList = worksheet['!dataValidations'] || [];
  
  dvList.forEach(dv => {
    const cellRange = dv.sqref;
    if (dv.type === 'list') {
      let options = [];
      if (dv.formula1.startsWith('"')) {
        // 直接定义的选项（如"Option1,Option2"）
        options = dv.formula1.replace(/^"|"$/g, '').split(',');
      } else {
        // 引用其他单元格的选项（如"Sheet2!A1:A3"）
        const [refSheetName, refRange] = dv.formula1.split('!');
        const refSheet = workbook.Sheets[refSheetName];
        const refCells = XLSX.utils.sheet_to_json(refSheet, { header: 1, range: refRange });
        options = refCells.flat().filter(val => val !== undefined);
      }
      console.log(`单元格范围 ${cellRange} 的下拉选项:`, options);
    }
  });
}

readDataValidation('PAG KIC TAB A (1)(1).xlsx');

三、极端场景：直接解析XLSX的XML内容 #

若上述方案仍无法解决内存问题，可直接读取XLSX压缩包内的工作表XML文件，流式解析数据验证节点：

const AdmZip = require('adm-zip');
const sax = require('sax');

function parseXLSXValidation(filePath) {
  const zip = new AdmZip(filePath);
  const sheetXml = zip.readAsText('xl/worksheets/sheet1.xml');
  
  const parser = sax.createStream(true, { trim: true });
  let currentDV = null;
  let currentFormula = '';

  parser.on('opentag', node => {
    if (node.name === 'dataValidation') {
      currentDV = { sqref: node.attributes.sqref, type: node.attributes.type };
    } else if (node.name === 'formula1' && currentDV) {
      currentFormula = '';
    }
  });

  parser.on('text', text => {
    if (currentFormula !== undefined) currentFormula += text;
  });

  parser.on('closetag', tagName => {
    if (tagName === 'formula1' && currentDV) {
      currentDV.formula1 = currentFormula;
      if (currentDV.type === 'list') {
        let options = [];
        if (currentDV.formula1.startsWith('"')) {
          options = currentDV.formula1.replace(/^"|"$/g, '').split(',');
        }
        console.log(`单元格范围 ${currentDV.sqref} 的下拉选项:`, options);
      }
      currentDV = null;
      currentFormula = undefined;
    }
  });

  parser.write(sheetXml);
  parser.end();
}

parseXLSXValidation('PAG KIC TAB A (1)(1).xlsx');

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