Node.js读取Excel文件并处理合并单元格_Node.js

在现代的数据处理任务中，excel 文件是一种非常常见的数据存储格式。无论是数据分析、报表生成，还是数据迁移，excel 文件都扮演着重要的角色。然而，处理 excel 文件时，尤其是包含合并单元格的文件，可能会遇到一些挑战。本文将介绍如何使用 node.js 读取 excel 文件，并处理其中的合并单元格。

准备工作

首先，我们需要安装一些必要的 node.js 库。我们将使用 xlsx 库来读取和解析 excel 文件，使用 lodash 库来处理字符串。你可以通过以下命令安装这些库：

npm install xlsx lodash

读取 excel 文件

我们首先需要读取 excel 文件。假设我们的文件名为 yd-tp2025-03-22.xlsx，并且它位于当前项目的根目录下。我们可以使用 xlsx.readfile 方法来读取文件：

const xlsx = require('xlsx');
const path = require('path');

const filepath = path.join(__dirname, 'yd-tp2025-03-22.xlsx');
const workbook = xlsx.readfile(filepath);

获取工作表

接下来，我们需要获取 excel 文件中的特定工作表。假设我们的工作表名为建设全流程明细，我们可以通过以下代码获取该工作表：

const sheetname = "建设全流程明细";
const sheet = workbook.sheets[sheetname];

处理合并单元格

在处理 excel 文件时，合并单元格是一个常见的挑战。合并单元格的值通常只存储在合并区域的左上角单元格中，其他单元格的值为空。为了正确处理合并单元格，我们需要获取合并单元格的信息，并根据这些信息来获取正确的值。

我们可以通过以下代码获取工作表的合并单元格信息：

const merges = sheet['!merges'] || [];

然后，我们定义一个函数 getmergedcellvalue，用于获取合并单元格的值：

function getmergedcellvalue(sheet, row, col) {
    for (const merge of merges) {
        const { s, e } = merge; // s: 合并区域的起始位置，e: 合并区域的结束位置
        if (row >= s.r && row <= e.r && col >= s.c && col <= e.c) {
            return sheet[xlsx.utils.encode_cell(s)]?.v;
        }
    }
    return sheet[xlsx.utils.encode_cell({ r: row, c: col })]?.v;
}

获取表头数据

假设我们的表头数据位于第 4 行，我们可以通过以下代码获取表头数据：

const headertemplates = [];
const rowindex = 3; // 第 4 行的索引（从 0 开始）
const range = xlsx.utils.decode_range(sheet['!ref']);

for (let col = range.s.c; col <= range.e.c; col++) {
    const cellvalue = getmergedcellvalue(sheet, rowindex, col);
    if (cellvalue) {
        const address = xlsx.utils.encode_cell({ r: rowindex, c: col }) + '';
        headertemplates.push({
            address: address,
            value: removetabsandnewlines(cellvalue),
            col: col + 1  // excel 列从 1 开始计数
        });
    }
}

输出表头模板数据

最后，我们可以将获取到的表头数据输出为 json 格式：

console.log(json.stringify(headertemplates, null, 2));

[running] node "c:\users\xiong\webstormprojects\backendnodejs\src\models\scripts\tempcoderunnerfile.js"
[
  {
    "address": "a4",
    "value": "序号",
    "col": 1
  },
  {
    "address": "b4",
    "value": "区县",
    "col": 2
  },
  {
    "address": "c4",
    "value": "线下收到需求时间（以邮件为准等书面通知为准）",
    "col": 3
  },
  {
    "address": "d4",
    "value": "订单编号",
    "col": 4
  },
  {
    "address": "e4",
    "value": "未进系统原因",
    "col": 5
  },
  {
    "address": "f4",
    "value": "年份",
    "col": 6
  },
  {
    "address": "g4",
    "value": "运营商",
    "col": 7
  },

将数据转换为结构化 json

接下来，我们从第 5 行（索引为 4）开始读取数据，并将每一行数据转换为一个对象，其中键为表头字段，值为单元格内容。最后，将所有数据存储在一个数组中

const xlsx = require('xlsx');
const path = require('path');

// 读取 excel 文件
const filepath = path.join(__dirname, 'yd-tp2025-03-22.xlsx');
const workbook = xlsx.readfile(filepath);

// 获取名为 "建设全流程明细" 的工作表
const sheetname = "建设全流程明细";
const sheet = workbook.sheets[sheetname];

// 获取工作表的合并单元格信息
const merges = sheet['!merges'] || [];

// 定义一个函数，用于获取合并单元格的值
function getmergedcellvalue(sheet, row, col) {
    for (const merge of merges) {
        const { s, e } = merge;
        if (row >= s.r && row <= e.r && col >= s.c && col <= e.c) {
            return sheet[xlsx.utils.encode_cell(s)]?.v;
        }
    }
    return sheet[xlsx.utils.encode_cell({ r: row, c: col })]?.v;
}

// 获取表头数据（第 4 行）
const headerrow = [];
const headerrowindex = 3; // 第 4 行的索引（从 0 开始）
const range = xlsx.utils.decode_range(sheet['!ref']);

// 获取表头
for (let col = range.s.c; col <= range.e.c; col++) {
    const cellvalue = getmergedcellvalue(sheet, headerrowindex, col);
    headerrow.push(cellvalue || '');
}

// 将数据转换为列表
const datalist = [];

// 从第5行开始读取数据（索引4）
for (let row = headerrowindex + 1; row <= range.e.r; row++) {
    const rowdata = {};
    
    // 遍历每一列
    for (let col = range.s.c; col <= range.e.c; col++) {
        const cellvalue = getmergedcellvalue(sheet, row, col);
        // 使用表头作为键名
        rowdata[headerrow[col]] = cellvalue || '';
    }
    
    datalist.push(rowdata);
}

// 输出前5条数据作为示例
console.log('数据总条数:', datalist.length);
console.log('前5条数据示例:');
console.log(json.stringify(datalist.slice(5, 25), null, 2));

[running] node "c:\users\xiong\webstormprojects\backendnodejs\src\models\scripts\excel_tolist.js"
数据总条数: 9
前5条数据示例:
[
{
"序号": 5,
"区县": "长沙县",
"线下收到需求时间\n（以邮件为准等书面通知为准）": 45523,
"订单编号": "新建选址",
"未进系统原因": "新建选址完成后进系统",
"年份": "2024年",
"运营商": "移动",
"项目归属": "岳麓山景区",
"运营商批次": "普通5g",
"重点项目打标": "",
"需求站址名称": "长沙岳麓岳麓山东门路口微站h-h5x",
"铁塔站址名称": "",
"铁塔站址编码": "",
"项目编码/存量满足": "",
"立项方式（存量满足常规塔类/区域化塔类/非标改造)": "微站",
"运营商建设方式（新址新建、共址新建、共址改造）": "新址新建",
"铁塔建设方式（新建、改造、存满）": "新建",
"产品单元数": 1,
"建设类型（地面站，楼面站）": "地面站",
"建设方案\n（含所有建设工程量）": "利旧电力路灯杆，新增光电一体箱，新增支臂，外市电",
"打标": "2、地面新建",
"订单导入时间": "",
............