JavaScript xlsx自己导入任意excel文件的表头复杂并且数据有合并单元格和空数据怎么处理成json的完全案例

处理一个复杂表头和合并单元格的 Excel 文件并将其转换为 JSON 格式需要以下步骤：

1. 安装依赖：

npm install xlsx
npm install lodash

2. 读取 Excel 文件：

const XLSX = require('xlsx');
const workbook = XLSX.readFile('path/to/file.xlsx');

3. 解析表头：

const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

const headers = {};
let range = XLSX.utils.decode_range(worksheet['!ref']);

for (let C = range.s.c; C <= range.e.c; ++C) {
  const cell = worksheet[XLSX.utils.encode_cell({ r: 0, c: C })];
  const hdr = `header_${C}`;

  if (!cell || !cell.v) {
    headers[hdr] = `empty_${C}`;
    continue;
  }

  headers[hdr] = cell.v;
}

此代码段将创建一个名为 `headers` 的对象，其中包含每个列的标题。如果某个单元格为空，则将其设置为 `empty_列号`。

4. 解析数据：

const rows = [];

for (let R = range.s.r + 1; R <= range.e.r; ++R) {
  const row = {};

  for (let C = range.s.c; C <= range.e.c; ++C) {
    const cell = worksheet[XLSX.utils.encode_cell({ r: R, c: C })];
    const header = headers[`header_${C}`];

    if (!cell || !cell.v) {
      row[header] = null;
      continue;
    }

    row[header] = cell.v;
  }

  rows.push(row);
}

此代码段将创建一个名为 `rows` 的数组，其中包含每个行的数据。如果某个单元格为空，则将其设置为 `null`。

5. 处理合并单元格：

const _ = require('lodash');

_.forEach(worksheet['!merges'], (mergeCell) => {
  const startRow = mergeCell.s.r;
  const endRow = mergeCell.e.r;
  const startCol = mergeCell.s.c;
  const endCol = mergeCell.e.c;

  let prevValue = null;

  for (let r = startRow; r <= endRow; ++r) {
    for (let c = startCol; c <= endCol; ++c) {
      const cell = worksheet[XLSX.utils.encode_cell({ r, c })];
      const header = headers[`header_${c}`];

      if (prevValue === null && cell && cell.v) {
        prevValue = cell.v;
        rows[r - 1][header] = prevValue;
      } else {
        rows[r - 1][header] = prevValue;
      }
    }
  }
});

此代码段将遍历表中所有合并单元格，如果单元格中的值为空，则将其设置为上一个非空单元格的值。

6. 将结果转换为 JSON：

const result = JSON.stringify(rows);

完整代码如下：

const XLSX = require('xlsx');
const _ = require('lodash');

const workbook = XLSX.readFile('path/to/file.xlsx');

const sheetName = workbook.SheetNames[0];
const worksheet = workbook.Sheets[sheetName];

const headers = {};
let range = XLSX.utils.decode_range(worksheet['!ref']);

for (let C = range.s.c; C <= range.e.c; ++C) {
  const cell = worksheet[XLSX.utils.encode_cell({ r: 0, c: C })];
  const hdr = `header_${C}`;

  if (!cell || !cell.v) {
    headers[hdr] = `empty_${C}`;
    continue;
  }

  headers[hdr] = cell.v;
}

const rows = [];

for (let R = range.s.r + 1; R <= range.e.r; ++R) {
  const row = {};

  for (let C = range.s.c; C <= range.e.c; ++C) {
    const cell = worksheet[XLSX.utils.encode_cell({ r: R, c: C })];
    const header = headers[`header_${C}`];

    if (!cell || !cell.v) {
      row[header] = null;
      continue;
    }

    row[header] = cell.v;
  }

  rows.push(row);
}

_.forEach(worksheet['!merges'], (mergeCell) => {
  const startRow = mergeCell.s.r;
  const endRow = mergeCell.e.r;
  const startCol = mergeCell.s.c;
  const endCol = mergeCell.e.c;

  let prevValue = null;

  for (let r = startRow; r <= endRow; ++r) {
    for (let c = startCol; c <= endCol; ++c) {
      const cell = worksheet[XLSX.utils.encode_cell({ r, c })];
      const header = headers[`header_${c}`];

      if (prevValue === null && cell && cell.v) {
        prevValue = cell.v;
        rows[r - 1][header] = prevValue;
      } else {
        rows[r - 1][header] = prevValue;
      }
    }
  }
});

const result = JSON.stringify(rows);