如何用Node对一个50w条数据的excel做聚合

一、背景

临近下班接到个紧急的活，需要对50W行数据按url和分类聚合

难点

• 需要处理的excel数据量大-50W行数据
• url中有模糊匹配-如detail?id=xx

二、技术预研

node xlsx处理第三方库

文档处理

• xlsx
• exceljs
• fast-csv

这三个库都是 Node.js 中处理表格数据的工具，但它们的定位和适用场景有所不同。以下是核心对比：

功能定位

特性	xlsx (SheetJS)	exceljs	fast-csv
支持格式	XLSX, XLS, CSV	XLSX, CSV	仅 CSV
读写能力	读写	读写	读写
复杂操作	基础操作	高级样式、图表	无
适用场景	通用表格处理	复杂 Excel 报表	纯 CSV 高效处理

核心优势

1. xlsx (SheetJS)

• 优势：
  • 格式兼容性强：支持几乎所有 Excel 格式（XLSX、XLS、CSV 等）。
  • 社区成熟：GitHub 星标超 2.5w，文档完善。
  • 纯 JS 实现：无需依赖外部程序。
• 适用场景：
  • 简单的 Excel/CSV 导入导出。
  • 格式转换（如 XLS → CSV）。
  • 服务端和浏览器通用。

2. exceljs

• 优势：
  • 丰富的样式控制：支持单元格样式、合并单元格、图表等复杂操作。
  • 流式处理：支持大文件的高效读写。
  • 模板功能：可基于现有 Excel 模板生成新文件。
• 适用场景：
  • 生成带样式的财务报表、数据看板。
  • 需要操作图表或复杂格式的 Excel。

3. fast-csv

• 优势：
  • 极致性能：专注 CSV，处理速度远超其他库。
  • 流式处理：适合 GB 级大文件（内存占用低）。
  • 简单易用：API 极简，学习成本低。
• 适用场景：
  • 纯 CSV 文件的高效读写。
  • 数据导入/导出管道（如数据库批量操作）。

性能对比

场景	xlsx	exceljs	fast-csv
小文件读写	中等	中等	最快
大文件读写	较慢（内存占用高）	中等（支持流式）	最快（流式优化）
复杂样式处理	有限支持	完整支持	不支持

代码示例

1. xlsx - 读取 Excel 并转换为 JSON

const XLSX = require('xlsx');

// 读取文件
const workbook = XLSX.readFile('data.xlsx');

// 获取第一个工作表
const worksheet = workbook.Sheets[workbook.SheetNames[0]];

// 转换为 JSON
const jsonData = XLSX.utils.sheet_to_json(worksheet);
console.log(jsonData);

2. exceljs - 创建带样式的 Excel

const ExcelJS = require('exceljs');

const workbook = new ExcelJS.Workbook();
const worksheet = workbook.addWorksheet('Sheet1');

// 添加数据
worksheet.addRow(['姓名', '年龄']);
worksheet.addRow(['张三', 25]);

// 设置样式
worksheet.getCell('A1').font = { bold: true };
worksheet.getColumn(1).width = 15;

// 保存文件
workbook.xlsx.writeFile('output.xlsx');

3. fast-csv - 高效处理 CSV

const fs = require('fs');
const csv = require('fast-csv');

// 读取 CSV
fs.createReadStream('data.csv')
  .pipe(csv.parse({ headers: true }))
  .on('data', (row) => console.log(row))
  .on('end', () => console.log('处理完成'));

// 写入 CSV
const stream = csv.format({ headers: true });
stream.pipe(fs.createWriteStream('output.csv'));
stream.write({ name: '李四', age: 30 });
stream.end();

如何选择？

• 选 xlsx：需要跨格式兼容（如 XLSX/CSV 互转），且操作较简单。
• 选 exceljs：需要复杂样式、图表或基于模板生成 Excel。
• 选 fast-csv：纯 CSV 场景，追求极致性能或大文件流式处理。

总结

需求	最佳选择
简单 Excel/CSV 读写	xlsx
复杂 Excel 报表	exceljs
高性能 CSV 处理	fast-csv
浏览器端兼容	xlsx

三、超大数据量的选型

处理 50 万条数据的 Excel 文件（约 100MB+）需要特别关注内存占用和处理效率。以下是针对超大数据量的选型建议和优化方案：

选型核心考量因素

1. 内存管理
   • 避免一次性加载全量数据到内存（如 Node.js 单线程处理易导致 OOM）。
2. 处理速度
   • 纯 JS 解析（如 xlsx）通常比依赖原生库的方案（如 exceljs）慢 30%+。
3. 格式兼容性
   • XLSX 格式比 CSV 复杂得多，处理效率更低。
4. 功能需求
   • 是否需要保留样式、公式？是否需要写入复杂格式？

方案对比与推荐

1. 优先方案：转换为 CSV 处理

工具链：
Excel → CSV 转换（推荐使用专业工具）+ fast-csv 或 Node.js 原生流处理。

优势：

• 性能提升 10 倍+：CSV 是纯文本格式，解析速度远快于 XLSX。
• 内存占用极低：通过流式处理可控制内存峰值 < 50MB。

示例代码（Node.js + fast-csv）：

const fs = require('fs');
const csv = require('fast-csv');

// 流式读取 CSV
fs.createReadStream('large_data.csv')
  .pipe(csv.parse({ headers: true }))
  .on('data', (row) => {
    // 逐行处理数据（如写入数据库）
    processRow(row);
  })
  .on('end', () => console.log('处理完成'));

适用场景：

• 无需保留 Excel 格式（如样式、公式）。
• 数据需导入数据库或进行批量计算。

2. 次优方案：XLSX 流式处理

工具：

• exceljs（支持 XLSX 流式读写，但内存占用较高）。
• xlsx-populate（轻量，但大文件处理较慢）。

示例代码（exceljs 流式读取）：

const ExcelJS = require('exceljs');

const workbook = new ExcelJS.Workbook();
workbook.xlsx.readFile('large_file.xlsx')
  .then(() => {
    const worksheet = workbook.getWorksheet(1);
    worksheet.eachRow({ includeEmpty: false }, (row, rowNumber) => {
      // 逐行处理（避免一次性加载全量数据）
      console.log(`Row ${rowNumber}: ${row.values}`);
    });
  });

注意事项：

• 需配合 eachRow() 等流式 API，避免使用 getSheetValues() 一次性加载全量数据。
• 内存峰值可能达到 200MB+，需确保 Node.js 堆内存足够（node --max-old-space-size=4096 app.js）。

3. 备选方案：分块处理 + 临时文件

策略：

1. 将大 Excel 按行分割为多个小文件（如每 10 万行一个文件）。
2. 并行处理每个小文件。
3. 合并处理结果。

工具：

• exceljs 或 xlsx 用于分割 Excel。
• Promise.all 或工作队列（如 Bull）管理并行任务。

优势：

• 降低单线程内存压力。
• 可利用多核 CPU 加速处理。

性能优化建议

1. 预处理优化

• 转换为 CSV：优先将 Excel 转为 CSV（可用 LibreOffice 等工具批量转换）。
• 压缩数据：处理前去除冗余列，减少数据量。

2. 代码优化

• 异步非阻塞：使用 async/await + 流处理，避免阻塞事件循环。
• 分批写入：数据库操作采用批量写入（如 INSERT INTO ... VALUES (...)），减少事务开销。

3. 硬件优化

• 增加内存：确保服务器内存 > 500MB（处理 50 万行数据）。
• 使用 SSD：提升磁盘 I/O 速度。

选型决策树

1. 是否必须保留 Excel 格式？

   • 是 → 使用 exceljs 流式处理（需接受较慢速度）。
   • 否 → 转 CSV + fast-csv（推荐）。

2. 数据是否需要实时处理？

   • 是 → 采用分块并行处理。
   • 否 → 夜间批量处理，牺牲时间换资源。

3. 是否有复杂计算需求？

   • 是 → 使用 Node.js 流 + 内存数据库（如 SQLite）。
   • 否 → 直接数据库批量导入。

避坑指南

1. 避免 OOM（内存溢出）

   • 禁用 Node.js 的内存限制：node --max-old-space-size=8192 app.js。
   • 使用 process.memoryUsage() 监控内存峰值。

2. 慎用同步 API

   • 避免 readFileSync()、toJSON() 等一次性加载全量数据的方法。

3. 测试边界情况

   • 用 100 万行数据测试内存峰值和处理时间（预估处理时间：CSV ~5 分钟，XLSX ~30 分钟）。

参考实现（CSV 流式处理 + 数据库导入）

const fs = require('fs');
const csv = require('fast-csv');
const { Pool } = require('pg'); // 以 PostgreSQL 为例

// 数据库连接池
const pool = new Pool({
  user: 'postgres',
  host: 'localhost',
  database: 'mydb',
  password: 'password',
  port: 5432,
});

// 批量插入大小
const BATCH_SIZE = 1000;
let batch = [];

fs.createReadStream('large_data.csv')
  .pipe(csv.parse({ headers: true }))
  .on('data', (row) => {
    batch.push(row);
    if (batch.length >= BATCH_SIZE) {
      // 异步批量插入，避免阻塞
      insertBatch(batch);
      batch = [];
    }
  })
  .on('end', async () => {
    // 处理最后一批数据
    if (batch.length > 0) {
      await insertBatch(batch);
    }
    console.log('全部数据导入完成');
    pool.end();
  });

async function insertBatch(rows) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const insertQuery = buildInsertQuery(rows);
    await client.query(insertQuery);
    await client.query('COMMIT');
  } catch (e) {
    await client.query('ROLLBACK');
    console.error('批量插入失败', e);
  } finally {
    client.release();
  }
}

function buildInsertQuery(rows) {
  // 构建批量插入 SQL（简化示例）
  const values = rows.map(row => 
    `('${row.name.replace(/'/g, "''")}', ${row.age})`
  ).join(',');
  return `INSERT INTO users (name, age) VALUES ${values}`;
}

小结

• 选 xlsx：需要跨格式兼容（如 XLSX/CSV 互转），且操作较简单。
• 选 exceljs：需要复杂样式、图表或基于模板生成 Excel。
• 选 fast-csv：纯 CSV 场景，追求极致性能或大文件流式处理。

50W行数据按url和分类聚合可选择 fast-csv处理，实测下来耗时 5s 左右

参考

• 正则表达式工具