使用react-excel-renderer解析Excel后如何按accId生成嵌套JSON

我使用react-excel-renderer读取Excel文件数据，Excel文件结构如下：

前端解析后得到的原始JSON结构如下：

需求是按accId字段对数据分组，同一账户下的生产类数据整合为production数组，最终得到指定结构的嵌套JSON，用于表格渲染账户ID、名称，搭配按钮查看该账户下的所有生产数据。

现有代码 #

import React, { useState } from "react";
import { Table, Button, message, Upload } from "antd";
import { ExcelRenderer } from "react-excel-renderer";

export const ExcelPageMod = () => {
  const [selected, setSelected] = useState([]);
  const [cols, setCols] = useState([]);
  const [rows, setRows] = useState([]);

  const { Column } = Table;

  const fileHandler = (fileList) => {
    let fileObj = fileList;

    if (!fileObj) {
      message.error("No file uploaded!");
      return false;
    }
    console.log("fileObj.type:", fileObj.type);

    if (
      !(
        fileObj.type === "application/vnd.ms-excel" ||
        fileObj.type ===
          "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
      )
    ) {
      message.error("Unknown file format. Only Excel files are uploaded!");
    }

    ExcelRenderer(fileObj, (err, resp) => {
      if (err) {
        console.log(err);
      } else {
        let newRows = [];
        resp.rows.slice(1).map((row, index) => {
          if (row && row !== "undefined") {
            newRows.push({
              key: index,
              accId: row[0],
              accName: row[1],
              productClass: row[2],
              accYearPrev: row[3],
              accYearCurr: row[4],
            });
          }
        });

        if (newRows.length === 0) {
          message.error("No data found in file!");
          return false;
        } else {
          console.log(newRows);
          console.log(resp)
          setCols(resp.cols);
          setRows(newRows);
        }
      }
    });
    return false;
  };

  const handleDownload = (key) => {
    const selectedRow = [...rows];

    console.log(selectedRow[key]);
    setSelected(selectedRow[key]);
  };

  return (
    <div style={{ padding: "20px" }}>
      <h1>Production PDF Generator</h1>
      <div>
        <Upload
          name="file"
          multiple={false}
          beforeUpload={fileHandler}
          onRemove={() => setRows([])}
        >
          <Button type="primary">Upload</Button>
        </Upload>
      </div>
      <div style={{ marginTop: 20 }}>
        <Table dataSource={rows}>
          <Column title="ID" dataIndex="accId" key="accId" />
          <Column title="Name" dataIndex="accName" key="accName" />
          <Column title="Product Class" dataIndex="productClass" key="productClass" />
          <Column
            title="Year Prev"
            dataIndex="accYearPrev"
            key="accYearPrev"
            render={(accYearPrev) => {
              return (
                <>{"Rp. " + parseFloat(accYearPrev).toLocaleString("id")}</>
              );
            }}
          />
          <Column
            title="Year Curr"
            dataIndex="accYearCurr"
            key="accYearCurr"
            render={(accYearCurr) => {
              return (
                <>{"Rp. " + parseFloat(accYearCurr).toLocaleString("id")}</>
              );
            }}
          />
          <Column
            title="Action"
            key="action"
            render={(text, record) => (
              <Button type="primary" onClick={() => handleDownload(record.key)}>
                Get Data
              </Button>
            )}
          />
        </Table>
      </div>
    </div>
  );
};

期望输出JSON结构 #

[
  {
    "accId": "ABC001",
    "accName": "John Doe",
    "production": [
      { "productClass": "General", "accYearPrev": 2000, "accYearCurr": 2500 },
      {
        "productClass": "Engineering",
        "accYearPrev": 7000,
        "accYearCurr": 5500
      }
    ]
  },
  {
    "accId": "ABC002",
    "accName": "Jane Doe",
    "production": [
      { "productClass": "General", "accYearPrev": 2000, "accYearCurr": 2500 },
      {
        "productClass": "Engineering",
        "accYearPrev": 7000,
        "accYearCurr": 5500
      },
      { "productClass": "Marine", "accYearPrev": 7000, "accYearCurr": 5500 }
    ]
  }
]

修改步骤 #

• 新增存储分组后数据的状态

const [groupedData, setGroupedData] = useState([])

• 在Excel解析完成生成newRows后，添加分组逻辑，替换原有setRows附近的逻辑

// 按accId分组
const groupMap = {}
newRows.forEach(item => {
  // 首次遇到该账户时初始化结构
  if (!groupMap[item.accId]) {
    groupMap[item.accId] = {
      key: item.accId,
      accId: item.accId,
      accName: item.accName,
      production: []
    }
  }
  // 把生产类数据推入对应账户的production数组
  groupMap[item.accId].production.push({
    productClass: item.productClass,
    accYearPrev: item.accYearPrev,
    accYearCurr: item.accYearCurr
  })
})
// 转成数组存入状态
const groupedResult = Object.values(groupMap)
setGroupedData(groupedResult)
setCols(resp.cols)
setRows(newRows) // 原始数据可保留，不需要可以删除

• 修改表格的数据源和列配置，只展示账户基础信息，操作按钮点击时直接获取当前行的production数据

<Table dataSource={groupedData}>
  <Column title="账户ID" dataIndex="accId" key="accId" />
  <Column title="账户名称" dataIndex="accName" key="accName" />
  <Column
    title="操作"
    key="action"
    render={(text, record) => (
      <Button type="primary" onClick={() => handleDownload(record)}>
        查看生产数据
      </Button>
    )}
  />
</Table>

• 修改handleDownload方法，直接拿到对应账户的全量生产数据

const handleDownload = (record) => {
  // record.production就是当前账户的所有生产类数据
  console.log('当前账户生产数据：', record.production)
  setSelected(record.production)
}

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