How can I format an excel file with empty rows to have nested arrays and match a JSON object that I need to render?

Question

I am facing an issue with an excel file. I receive some data from the DB and the user should be able to replace that data with a spreadsheet that looks like this:

This is how the data comes from the DB and how the excel file should be finally formatted:

                "employers": [{
                    "id": "4147199311345513",
                    "shifts": [{
                        "url": "https://zoom.com/983493unsdkd/",
                        "days": "Mon,Tue,Wed,Thu,Fri",
                        "name": "Morning",
                        "endTime": "12:00",
                        "timezone": "CST",
                        "startTime": "8:00"
                    }, {
                        "url": "https://zoom.com/983493unsdkd/",
                        "days": "Mon,Tue,Wed,Thu,Fri",
                        "name": "Afternoon",
                        "endTime": "12:00",
                        "timezone": "CST",
                        "startTime": "8:00"
                    }],
                    "employerUrl": "http://www.google.com",
                    "employerName": "AT&T",
                    "employerUrlText": "URL Text",
                    "employerLogoSmall": "assets/images/att-logo.png",
                    "employerDescription": "AT&T is a world premier employer with a bunch of stuff here and there."
                }, {
                    "id": "3763171269270198",
                    "shifts": [{
                        "url": "https://zoom.com/983493unsdkd/",
                        "days": "Mon,Tue,Wed,Thu,Fri",
                        "name": "Morning",
                        "endTime": "12:00",
                        "timezone": "CST",
                        "startTime": "8:00"
                    }, {
                        "url": "https://zoom.com/983493unsdkd/",
                        "days": "Mon,Tue,Wed,Thu,Fri",
                        "name": "Afternoon",
                        "endTime": "12:00",
                        "timezone": "CST",
                        "startTime": "8:00"
                    }],
                    "employerUrl": "http://www.google.com",
                    "employerName": "AT&T",
                    "employerUrlText": "URL Text",
                    "employerLogoSmall": "assets/images/att-logo.png",
                    "employerDescription": "AT&T is a world premier employer with a bunch of stuff here and there."
                }]

So I need to take that spreadsheet and format it to look like that JSON above. All of this with Javascript/React.

This is what I have so far to format my excel file and render it:

  const [excelData, setExcelData] = useState({ rows: [], fileName: "" });

  const fileHandler = (event) => {
    let fileObj = event.target.files[0];

    ExcelRenderer(fileObj, (err, resp) => {
      if (err) {
        console.log(err);
      } else {
        let newRows = [];
        let shiftRows = [];
        console.log(resp.rows);
        resp.rows.slice(1).map((row, index) => {
          if (row && row !== "undefined") {
            return newRows.push({
              key: index,
              employer: {
                name: row[0],
                description: row[1],
                employerUrl: row[2],
                employerUrlText: row[3],
                shifts: shiftRows.push({ shift: row[2] }),
              },
            });
          }

          return false;
        });

        setExcelData({ rows: newRows, fileName: fileObj.name });
      }
    });
  };

That console.log above (console.log(resp.rows)) returns this: Where the first row are the headers of the excel file.

And the code above ends up like this and it should be exactly as the JSON I mentioned:

      rows: [
        {
          key: 0,
          employer: {
            name: 'AT&T',
            description: 'AT&T is a world premier employer with a bunch of stuff here and there.',
            shifts: 1
          }
        },
        {
          key: 1,
          employer: {
            shifts: 2
          }
        },
        {
          key: 2,
          employer: {
            shifts: 3
          }
        },
        {
          key: 3,
          employer: {
            shifts: 4
          }
        },
        {
          key: 4,
          employer: {
            name: 'Verizon',
            description: 'Verizon is a world premier employer with a bunch of stuff here and there.',
            shifts: 5
          }
        },
        {
          key: 5,
          employer: {
            shifts: 6
          }
        },
        {
          key: 6,
          employer: {
            shifts: 7
          }
        },
        {
          key: 7,
          employer: {
            shifts: 8
          }
        }
      ],
      fileName: 'EmployerChats.xlsx',
      false: {
        rows: [
          {
            url: 'https://www.youtube.com/kdfjkdjfieht/',
            title: 'This is a video',
            thumbnail: '/assets/images/pages/5/links/0/link.png',
            description: 'This is some text'
          },
          {
            url: 'https://www.youtube.com/kdfjkdjfieht/',
            title: 'This is a video',
            thumbnail: '/assets/images/pages/5/links/1/link.png',
            description: 'This is some text'
          }
        ]
      },

I am using this plugin to help me render the excel file: https://www.npmjs.com/package/react-excel-renderer

Any ideas on what can I do to make format the spreadsheet data as the JSON?

Please notice those empty rows.

For example every time there is a new employer name, that's a new row or item in the array, then all of the columns and rows below and after Shift Name is a new nested array of objects. Hence, this file contains an array with a length of 2 and then it contains another array of items when it hits the Shift Name column.

Is it clear?

xadm · Accepted Answer · 2020-05-21 00:23:27Z

1st of all - you don't need to follow 'original', class based setState. In FC you can just use two separate useState.

const [rows, setRows] = useState([]);
const [fileName, setFileName] = useState("");

Data conversion

I know that you need a bit different workflow, but this can be usefull (common point - data structure), too - as conversion guide, read on.

You don't need to use ExcelRenderer to operate on data from db and render it as sheet. Converted data can be exported to file later.

You can just create array of array (aoa) that follows expected view (rows = array of row cells array). To do this you need very easy algorithm:

let newData = []
map over emplyers, for each (emp):
- set flag let first = true;
- map over shifts, for each (shift):
  - if( first ) { newData.push( [emp.name, emp.descr, shift.name, shift.timezone...]); first = false;
  - } else newData.push( [null, null, shift.name, shift.timezone...]);
- setRows( newData );

Rendering

<OutTable/> operates on data and colums props - structures similar to internal state. 'datais ourrows, we only needcolumns` prop, just another state value:

const [columns, setColumns] = useState([
  { name: "Employer name", key: 0 },
  { name: "Employer description", key: 1 },
  { name: "Shift name", key: 2 },
  // ...
]);

and finally we can render it

return (
  <OutTable data={rows] columns />

Later

User can operate on sheet view - f.e. insert rows using setRows() or download this as file (XLSX.writeFile()) after simple conversion:

 var ws = XLSX.utils.aoa_to_sheet( columns.concat( rows ) );

There is a lot of utils you can use for conversions - see samples.

Back to your needs

We have data loaded from db, data in aoa form, rendered as sheet. I don't fully understand format you need, but for your db format conversion is simple (opposite to above) - you can follow it and adjust to your needs.

let newEmployers = [];
let empCounter = -1;
// itarate on rows, on each (`row`):
rows.map( (row) => {
  // new employer
  if( row[0] ) { 
    newEmployers.push( {
      // id should be here
      "employerName": row[0],
      "employerDescription": row[1],
      "shifts": [
        {
          "shiftName": row[3], 
          "shiftDescription": row[4], 
          // ...
        }
      ]
    } );
    empCounter ++;
  } else {
    // new shift for current employer
    newEmployers[empCounter].shifts.push(
      {
        "shiftName": row[3], 
        "shiftDescription": row[4], 
        // ...
      }
    );
  }
});

// newEmployers can be sent to backend (as json) to update DB

Collectives™ on Stack Overflow

How can I format an excel file with empty rows to have nested arrays and match a JSON object that I need to render?

1 Answer 1

Data conversion

Rendering

Later

Back to your needs

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Data conversion

Rendering

Later

Back to your needs

Comments

Your Answer

Sign up or log in

Post as a guest

Related