How can I output an excel files data in a certain way on my webpage?

1

I’m trying to make a program which allows a user to upload an excel file that contains mixed up random addresses, then after this the data will be outputted onto the webpage in a specific order based on how close the addresses are closest together. However, I’m not sure how to do the next step within my jsfiddle, which is to output all of the address within the same postcode.

Here is an example of the addresses process to make it more clear for you. For example, within the excel sheet if there was 100 addresses mixed up together with different postal addresses listed next to each other the program would re order the address list by putting on the N1 post codes together.

Currently I have finished step one and two, which the upload of the excel file and for the data to be outputted to the webpage as you can see in my jsfiddle. However, I’m not sure how to do the next step, which is re order the original uploaded list to output all of the address within the same postcode together.

Here is the jsfiddle.

print('Please upload the excel file by clicking on the button Choose File')

loadFile = function(event) {
	alasql('SELECT * FROM FILE(?,{headers:true})',[event],function(data){
		print(data)
	});
}

function print(x){
    document.getElementById('output').textContent += JSON.stringify(x,  null, '\t')+"\n";
}
3

Well, you never responded back with what fields were in the file, but if you were to have an Excel file with data that looked like this:

then the following JavaScript should print out the sorted file in JSON format:

print('Please upload the excel file by clicking on the button Choose File\n\n')

loadFile = function(event) {
  alasql('SELECT * FROM FILE(?,{headers:true})',[event],function(data){
    data.sort(function(a, b){
      return a.Zip - b.Zip;	
    });
    print('\n' + JSON.stringify(data,  null, '\t')+'\n\n');
  });
};

function print(x){
  document.getElementById('output').textContent += x;    
}

After creating the above test file, I ran the script and got the following:

Please upload the excel file by clicking on the button Choose File

[
	{
		"Name": "Suzy",
		"Street": "2663 lskgjsl js",
		"City": "San Jose",
		"State": "CA",
		"Zip": 11111
	},
	{
		"Name": "Frank",
		"Street": "7877 Harrsion Drive",
		"City": "Aptos",
		"State": "CA",
		"Zip": 22222
	},
	{
		"Name": "John",
		"Street": "233 Lave Ave",
		"City": "Santa Cruz",
		"State": "CA",
		"Zip": 33333
	},
	{
		"Name": "Bob",
		"Street": "633 glue street",
		"City": "Portland",
		"State": "OR",
		"Zip": 77777
	}
]
3 Likes
4

Hi,

Sorry for not responding quicker I was really busy. This is exactly what I’m looking for and your time is great appreciated that you went on this :slight_smile:

Do you have a working JSFIDDLE?

7

I got the jsiffdle working now which is great.

However, one small problem that I just noticed is that since I’m located in the UK, I was wondering how I could change “zip” to “post code” and for the program to still list it in the same method example as the “zip” .

Example
http://imgur.com/a/mL0aJ

I put the “post code” within the address if you want to make it easier maybe you can change it to be in a operate column if it makes it easier for you.

Thanks for the help!

10

Thanks for posting the in-depth clear post which explained everything regarding the program much appreciated. I will 100% send you the final version. However, one small issue I can’t seem to get the jsfiddle working. http://jsfiddle.net/3ve90afo/98/ Could it be the code or am I clicking something wrong?

12

Mine exactly looks like your data and the file format is the correct choice. Strange… I’m going to give it ago again later on when I wake up I’ll keep you updated. Could you maybe email me your excel file if you don’t mind, I’ll give that a try see if it works. My email is Liam_docherty@outlook.com.