Object Array to HTML table

JavaScript
1

Hello all,

The following code enables me to upload an excel file which is than parsed into an object array.
The result is good in the console but I can’t get it into an HTML table.
Any ideas?

This code makes use of the alaSQL library

function loadFile(event) {
        alasql('SELECT * FROM FILE(?,{headers:false})',[event],function(data){
        console.table(data);
        
        $('#excel_table').innerHTML = data;
        });
    }
2

After having retrieved data from an excel sheet I get for example the following object array:

[
{“B”:“B2”,“C”:“C2”,“D”:“D2”},
{“B”:“B3”,“C”:“C3”,“E”:“E3”},
{},
{“B”:“B5”,“D”:“D5”,“E”:“E5”,“F”:“F5”},
{“B”:“B6”,“C”:“C6”,“D”:“D6”,“E”:“E6”},
{“B”:“B7”,“C”:“C7”,“D”:“D7”}
]
How do I make an HTML table out of this? The values in this example are the cell numbers in excel. The preceding characters are the column number, where the value is retrieved from.

The array will not always have the number of columns or values. It depends on the workbook that has been uploaded.

Desired outcome:

3

you have to loop over the data and build the HTML elements that make the table from that.

4

Hello Dormilich,
I am not sure how to do this.
How key in each item is a colum-‘number’ in excel. How do I make the loop to take this into account?

5

First you should manually create the HTML for the table that you want. That makes it much easier to see what of the data goes where (and how to get it there).

6

I’ve searched the internet but coukd not find a way to turn this associative array into an HTML table.
All help is welcome.

The array is dynamic (depends on the uploaded excel file)

7

Roughly, something like this

  • Set up table
    • Setup table header
    • Set up table body
    • Set up loop for rows
      • Set up row ( <tr>)
        • Loop through rows
          • Set up loop for cells in row
            • Loop through cells (<td>)
            • Add cell data
            • Close cell (</td>)
          • Next cell
      • Close row (</tr>)
      • Next row
  • Close table
8

That part won’t be a problem if I knew how to loop through a multidimensional associative array as the one I provided.

[
{"B":"B2","C":"C2","D":"D2"},
{"B":"B3","C":"C3","E":"E3"},
{},
{"B":"B5","D":"D5","E":"E5","F":"F5"},
{"B":"B6","C":"C6","D":"D6","E":"E6"},
{"B":"B7","C":"C7","D":"D7"}
]

the following part let me store all keys into an array:

var keys = [];
for (var i = 0; i < data.length; i++) {
		for (var categoryid in data[i]) {
			var category = data[i][categoryid];
			keys.push(categoryid);
		}
	}

result = Array [ "B", "C", "D", "G", "B", "C", "E", "B", "D", "E", … ]

after having done so I change all the keys (which are excel-based column letters) into numbers.
This enables me to calculate the numbers of columns needed in the to be created HTML-table.

var foo = function(val) {
	var base = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', i, j, result = 0;
	for (i = 0, j = val.length - 1; i < val.length; i += 1, j -= 1) {
		result += Math.pow(base.length, j) * (base.indexOf(val[i]) + 1);
	}
	return result;
};

keysConverted = keys.map(foo);

result = Array [ 2, 3, 4, 7, 2, 3, 5, 2, 4, 5, … ]

After having done so I check the lowest and highest value and by deducting them from eachother I get the number of columns needed.

vMin = Math.min.apply(null, keysConverted); // result = 2
vMax = Math.max.apply(null, keysConverted); // result = 10
vMax  - vMin  // result = 8
data.length // result = 6 (number of rows)

Although I am not finished yet, I think there should be a way to loop through the associative array and create an table from it

9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.