Reading excel file into array using javascript

Question

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.

When you say "excel file" do you mean .xls / .xlsx or is it a .csv file? — Stephen P
– Stephen P, Commented Aug 12, 2019 at 20:27
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
– 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
– David d C e Freitas, Commented Oct 20, 2022 at 13:10

webketje · Accepted Answer · 2024-07-01 10:09:08Z

19

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>

edited Jul 1, 2024 at 10:09

answered Aug 12, 2019 at 21:07

webketje

11.2k3 gold badges28 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Prerona Kundu Over a year ago

Which lines of code in this are writing the array to the document? I'm trying to get it to just store the array as a variable a, but I can't figure out how to have it not write to the document.

webketje Over a year ago

@PreronaKundu, what do you mean, "to the document"? The lines under the comment // data preview write the JSON output of the variable result (in which the xlsx data is stored) to the <pre> tag. If you want to re-export it in xlsx, you can. Search for xlsx.utils.aoa_to_sheet in the xlsx-js docs.

Prerona Kundu Over a year ago

instead of writing the JSON output to the <pre> tag, I want to simply store the array data in a variable labeled a, so that I can continue to use this data in another function.

webketje Over a year ago

Well, you can. In the code above it's called result but you can label it a...

Prerona Kundu Over a year ago

Yes, but I don't want it to print out the <pre> tag to the page. I don't want to visually see the array, I just want to store it in a variable. Is there a way to do this without the <pre> tag or have the tag not appear on the document?

|

irrefl · Accepted Answer · 2022-06-22 15:18:55Z

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 */
})();

Collectives™ on Stack Overflow

Reading excel file into array using javascript

2 Answers 2

7 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related