Monday, May 20, 2024
 Popular · Latest · Hot · Upcoming
136
rated 0 times [  137] [ 1]  / answers: 1 / hits: 20003  / 5 Years ago, mon, august 12, 2019, 12:00:00

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.


More From » html

 Answers
12

Not sure what you're using to parse the Excel, is it IgniteUI ? For what it's worth, 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 docs are a bit messy, but they are complete, the most interesting sections for this use-case are: Browser file upload form element under Parsing workbooks and XLSX.utils.sheet_to_json. You can run a test with the type of spreadsheet you provided in the code sample below:





$(#input).on(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.14.5/xlsx.full.min.js></script>
<script src=https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js></script>
<input type=file id=input accept=.xls,.xlsx,.ods>
<pre id=result></pre>




[#51777] Friday, August 2, 2019, 5 Years  [reply] [flag answer]
Only authorized users can answer the question. Please sign in first, or register a free account.
sidneyh

Total Points: 118
Total Questions: 108
Total Answers: 105

Location: Mali
Member since Fri, Jun 18, 2021
3 Years ago
sidneyh questions
Tue, Jun 7, 22, 00:00, 2 Years ago
Wed, Apr 13, 22, 00:00, 2 Years ago
Wed, Aug 12, 20, 00:00, 4 Years ago
Wed, Jun 3, 20, 00:00, 4 Years ago
Fri, Apr 24, 20, 00:00, 4 Years ago
;