Showing nested excel data into one single data in json

Question

I used react-excel-renderer to get data from an excel file. The excel file has data like the following image:

In the frontend, I got json data like this:

How do I make group them as one based on the accId? I plan to have a table to render their id and name and there will button to view the rest of the data.

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>
  );
};

Edit: Here's is the JSON I want

    {
      "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 }
      ]
    }

Answer 1

This is really just a group-by but instead of returning the accumulator object you can return the Object.values() of the accumulator. Properties that will be used to construct the new object in the accumulator, or are not needed, are destructured in the callback parameters.

const input = [ { accId: 'ABC0001', accName: 'JOHN DOE', accYearCurr: 1250000, accYearPrev: 1000000, key: 0, productClass: 'General', }, { accId: 'ABC0001', accName: undefined, accYearCurr: 1750000, accYearPrev: 1500000, key: 1, productClass: 'Marine Cargo', }, { accId: 'ABC0001', accName: undefined, accYearCurr: 1000000, accYearPrev: 1125000, key: 2, productClass: 'Motor', }, { accId: 'ABC0001', accName: undefined, accYearCurr: 7500000, accYearPrev: 5500000, key: 3, productClass: 'Property', }, { accId: 'ABC0002', accName: 'JANE DOE', accYearCurr: 5000000, accYearPrev: 7500000, key: 4, productClass: 'Engineering', }, { accId: 'ABC0002', accName: undefined, accYearCurr: 10000000, accYearPrev: 10000000, key: 5, productClass: 'Property', }, { accId: 'ABC0003', accName: 'JILL DOE', accYearCurr: 75000000, accYearPrev: 70500000, key: 6, productClass: 'Marine Cargo', }, { accId: 'ABC0003', accName: undefined, accYearCurr: 10000000, accYearPrev: 10000000, key: 7, productClass: 'Motor', }, { accId: 'ABC0003', accName: undefined, accYearCurr: 35000000, accYearPrev: 25000000, key: 8, productClass: 'Engineering', }, ];

const result = Object.values(
  input.reduce((a, { accId, accName, key, ...prod }) => {
    (a[accId] ??= { accId, accName, production: [] }).production.push(prod);

    return a;
  }, {})
);

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

The above snippet uses the logical nullish assignment (??=) operator, but if you can't use it for compatiblity reasons it can be replaced with an OR(||) short-circuit.

(a[accId] || (a[accId] = { accId, accName, production: [] })).production.push(_production);

It's unclear if you want the accYearCurr/Prev values changed, but you can handle any changes needed before pushing to the accumulator.

const result = Object.values(
  input.reduce((a, { accId, accName, key, ..._production }) => {
    _production.accYearCurr /= 1000;
    _production.accYearPrev /= 1000;

    (a[accId] ??= { accId, accName, production: [] }).production.push(_production);

    return a;
  }, {})
);