有没有一种方法可以从具有空行的excel文件创建对象的嵌套数组?

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: enter image description here

这是数据来自数据库的方式:

          {
            menuName: 'Employer Chat',
            pageType: 'EmployerChat',
            employers: [
              {
                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'
                  }
                ],
                employerLogoLarge: '/assets/images/pages/1/employers/0/att-logo.png',
                employerLogoSmall: '/assets/images/pages/1/employers/0/att-logo.png'
              }

因此,我需要获取该电子表格并将其格式化为上面的JSON。或者,我也可以使用电子表格来简化它。

这是我到目前为止的内容:

  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 = [];
        resp.rows.slice(1).map((row, index) => {
          if (row && row !== "undefined") {
            return newRows.push({
              key: index,
              employer: {
                name: row[0],
                description: row[1],
                shifts: shiftRows.push({ shift: row[2] }),
              },
            });
          }

          return false;
        });

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

但是它最终像这样,应该与我提到的JSON完全一样:

      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

关于如何使电子表格数据格式为JSON的任何想法?

请注意那些空行。

例如,每次有一个新的雇主名称,即一个新行,那么“班次名称”下方和之后的所有列和行都是一个新的嵌套对象数组。清楚吗?

评论