I'm trying to read an excel file and create a multidimensional array in javascript with it. The excel file will look like:

AA11 AA22 AN65
AB11 AB22 AN64
...

I need it to create an array that looks like:

[
    [AA11, AA22, AN65],
    [AB11, AB22, AN64]
]

So far, I've been able to bring up a file selection window, and I believe it's reading the file, I just think it might not be putting the data into the array correctly. This is what I have so far:

    <script type="text/javascript">
        $(function () {
            $("#input").on("change", function () {
            var excelFile,
            var array = [[],[]];
                fileReader = new FileReader();

            $("#result").hide();

            fileReader.onload = function (e) {
                var buffer = new Uint8Array(fileReader.result);

                $.ig.excel.Workbook.load(buffer, function (workbook) {
                    var column, row, newRow, cellValue, columnIndex, i,
                        worksheet = workbook.worksheets(0),
                        columnsNumber = 0,
                        gridColumns = [],
                        data = [],
                        worksheetRowsCount;
                    while (worksheet.rows(0).getCellValue(columnsNumber)) {
                        columnsNumber++;
                    }
                    for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                        column = worksheet.rows(0).getCellText(columnIndex);
                        gridColumns.push({ headerText: column, key: column });
                    }
                    for (i = 1, worksheetRowsCount = worksheet.rows().count() ; i < worksheetRowsCount; i++) {
                        newRow = {};
                        row = worksheet.rows(i);

                        for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                            cellValue = row.getCellText(columnIndex);
                            //newRow[gridColumns[columnIndex].key] = cellValue;
                            array[row,columnIndex] = cellValue;
                        }

                        window.alert(array[0][0]);
                        data.push(array);
                    }

    </script>

Any help would be greatly appreciated.

I'm trying to read an excel file and create a multidimensional array in javascript with it. The excel file will look like:

AA11 AA22 AN65
AB11 AB22 AN64
...

I need it to create an array that looks like:

[
    [AA11, AA22, AN65],
    [AB11, AB22, AN64]
]

So far, I've been able to bring up a file selection window, and I believe it's reading the file, I just think it might not be putting the data into the array correctly. This is what I have so far:

    <script type="text/javascript">
        $(function () {
            $("#input").on("change", function () {
            var excelFile,
            var array = [[],[]];
                fileReader = new FileReader();

            $("#result").hide();

            fileReader.onload = function (e) {
                var buffer = new Uint8Array(fileReader.result);

                $.ig.excel.Workbook.load(buffer, function (workbook) {
                    var column, row, newRow, cellValue, columnIndex, i,
                        worksheet = workbook.worksheets(0),
                        columnsNumber = 0,
                        gridColumns = [],
                        data = [],
                        worksheetRowsCount;
                    while (worksheet.rows(0).getCellValue(columnsNumber)) {
                        columnsNumber++;
                    }
                    for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                        column = worksheet.rows(0).getCellText(columnIndex);
                        gridColumns.push({ headerText: column, key: column });
                    }
                    for (i = 1, worksheetRowsCount = worksheet.rows().count() ; i < worksheetRowsCount; i++) {
                        newRow = {};
                        row = worksheet.rows(i);

                        for (columnIndex = 0; columnIndex < columnsNumber; columnIndex++) {
                            cellValue = row.getCellText(columnIndex);
                            //newRow[gridColumns[columnIndex].key] = cellValue;
                            array[row,columnIndex] = cellValue;
                        }

                        window.alert(array[0][0]);
                        data.push(array);
                    }

    </script>

Any help would be greatly appreciated.

• javascript
• html

• 1 When you say "excel file" do you mean .xls / .xlsx or is it a .csv file? – Stephen P Commented Aug 12, 2019 at 20:27
• 1 It appears as though you're using some kind of third-party library to help you with reading the workbook. You may want to edit your question to include the name of that library in the question (and in the tags, if it exists). – Heretic Monkey Commented Aug 12, 2019 at 20:29
• Oneliner let input = "A B C DD EE FF"; // tab seperated \t let clean = input.replace(/^/g, '["').replace(/\t/g, '","').replace(/(?:\r\n|\r|\n)/g, '"], ["').replace(/, \[$/, ''); clean = clean.substring(0, clean.length-4); let arr = JSON.parse('['+clean+']') – David d C e Freitas Commented Oct 20, 2022 at 13:10

2 Answers 2

The free (community edition) of SheetJS, js-xlsx provides a few functions that produce exactly the output you needed, given the spreadsheet you provided.

The most interesting docs sections for this use-case are: Getting Started > Examples > Inspect worksheet data, Import > Parse file and XLSX.utils.sheet_to_json. You can run a test with the type of spreadsheet you provided in the code sample below:

document.getElementById('input').addEventListener('change', function(e) {
   var file = e.target.files[0];
   // input canceled, return
   if (!file) return;
   
   var FR = new FileReader();
   FR.onload = function(e) {
     var data = new Uint8Array(e.target.result);
     var workbook = XLSX.read(data, {type: 'array'});
     var firstSheet = workbook.Sheets[workbook.SheetNames[0]];
     
     // header: 1 instructs xlsx to create an 'array of arrays'
     var result = XLSX.utils.sheet_to_json(firstSheet, { header: 1 });
     
     // data preview
     var output = document.getElementById('result');
     output.innerHTML = JSON.stringify(result, null, 2);
   };
   FR.readAsArrayBuffer(file);
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/xlsx/0.18.5/xlsx.full.min.js"></script>
<input type="file" id="input" accept=".xls,.xlsx,.ods">
<pre id="result"></pre>

Here the full solution aditionally I've added a group by category function to demostrante that we can apply functions to the json array.

(async() => {
  const url = "./yourfile.xlsx";
  const data = await (await fetch(url)).arrayBuffer();
  /* data is an ArrayBuffer */
  const workbook = XLSX.read(data);

  const firstSheetName = workbook.SheetNames[0];
  const worksheet = workbook.Sheets[firstSheetName];
  const sheetValues = XLSX.utils.sheet_to_json(worksheet);

  const groupByCategory = sheetValues.reduce((group, product) => {
  const { category } = product;
  group[category] = group[category] ?? [];
  group[category].push(product);
  return group;
  }, {});

  console.log(groupByCategory)
  /* DO SOMETHING WITH workbook HERE */
})();