Execl to an array of objects in Javascript

Question

I have an excel file with data as below

Sl.No   Company Name    Designation Salary
n1      ABC     Sam     Architect   100
n2      ABC     Bill    Engineer    200
n3      ABC     Jill    HR          300
n4      XYZ     Bill    Engineer    250
n5      XYZ     Tom     Mechanic    150

I want to convert this to a array of a objects as below

arrayObject = [{'Sl.No':'n1', 'company':'ABC', 'Name':'Sam', 'Designation':'Architect','Salary':'100.},
{'Sl.No':'n2', 'company':'ABC', 'Name':'Bill', 'Designation':'Engineer','Salary':'200.},
{'Sl.No':'n3', 'company':'ABC', 'Name':'Jill', 'Designation':'HR','Salary':'300.},
{'Sl.No':'n4', 'company':'XYZ', 'Name':'Bill', 'Designation':'Engineer','Salary':'250.},
{'Sl.No':'n5', 'company':'XYZ', 'Name':'Tom', 'Designation':'Mechanic','Salary':'150.}]

I want this structure so that I can later filter items based on company, Designation, Salary etc. If another structure is good for the purpose, I am open for that as well.

I tried sheet.js in the path https://oss.sheetjs.com/sheetjs/ & I get the output as below & I feel that is not useful for my purpose.

{
  "Tabelle1": [
    [
      "Sl.No",
      "Company",
      "Name",
      "Designation",
      "Salary"
    ],
    [
      "n1",
      "ABC",
      "Sam",
      "Architect",
      100
    ],
    [
      "n2",
      "ABC",
      "Bill",
      "Engineer",
      200
    ],
    [
      "n3",
      "ABC",
      "Jill",
      "HR",
      300
    ],
    [
      "n4",
      "XYZ",
      "Bill",
      "Engineer",
      250
    ],
    [
      "n5",
      "XYZ",
      "Tom",
      "Mechanic",
      150
    ]
  ]
}

score 0 · Answer 1 · answered Dec 13 '21 at 07:05

I found the answer to my question in here Excel to JSON javascript code?

The answer that was useful for me was the answer from user7456320 (the code is copied below as well).

All that was needed was the XLSX library.

<!doctype html>
<html>

<head>
    <title>Excel to JSON Demo</title>
    <script src="xlsx.full.min.js"></script>
</head>

<body>

    <script>
        /* set up XMLHttpRequest */
        var url = "http://myclassbook.org/wp-content/uploads/2017/12/Test.xlsx";
        var oReq = new XMLHttpRequest();
        oReq.open("GET", url, true);
        oReq.responseType = "arraybuffer";

        oReq.onload = function(e) {
            var arraybuffer = oReq.response;

            /* convert data to binary string */
            var data = new Uint8Array(arraybuffer);
            var arr = new Array();
            for (var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]);
            var bstr = arr.join("");

            /* Call XLSX */
            var workbook = XLSX.read(bstr, {
                type: "binary"
            });

            /* DO SOMETHING WITH workbook HERE */
            var first_sheet_name = workbook.SheetNames[0];
            /* Get worksheet */
            var worksheet = workbook.Sheets[first_sheet_name];
            console.log(XLSX.utils.sheet_to_json(worksheet, {
                raw: true
            }));
        }

        oReq.send();
    </script>
</body>
</html>

Execl to an array of objects in Javascript

1 Answers1

Linked