Open excel file and export to json

Question

I want to dynamically create a <select></select> (or maybe a textbox with autocomplete) input file depending on an Excel file with a format like this:

Airport Code    Airport Name    Language Code
 AAC             Al Arish                                            en
 AAE             Annaba Les Saline                                   en
 AAH             Aachen Merzbruck                                    en
 AAL             Aalborg                                             en
 AAN             Al Ain                                              en
 AAQ             Anapa                                               en
 AAR             Aarhus Tirstrup                                     en
 AAU             Asau                                                en

(with many more)

I would need to open the .xls file and read every row so that I can get the two first colums to act as the anchor and value.

This is the most related content I've found and the links are broken How to read and write into file using JavaScript

Is there a solution for this that will work for IE7

What server are you running? LAMP, ISS? Where are you storing the Excel file (locally or on the server)? — Powertieke, Dec 09 '11 at 08:26
the .xls would be stored in the server wich is running under java (i am really not sure what kind of servers uses; i am the front-end but the CMS generates static files) — Toni Michel Caubet, Dec 09 '11 at 08:31

score 6 · Accepted Answer · answered Dec 14 '11 at 16:08

If your file is on the http server you can read it using AJAX.

Let's first define some constants:

var CSV_URL = "http://server.domain/path/file.csv";
var CSV_COLUMN = ';'
var CSV_ROW = '\n'

CSV_URL is the URL of your CSV file.

CSV_COLUMN is delimiter character which devides columns.

CSV_ROW is delimiter character which devides rows.

Now we need to do AJAX query to get the CSV data content. I'm using jQuery to do AJAX requests.

$.get (CSV_URL, null, function (data) {
    var result = parse_csv (data);
    var e = create_select (result);
    document.body.appendChild (e);
});

Ok, now we need to parse the data...

function parse_csv (data) {
    var result = new Array();
    var rows = data.split (CSV_ROW);

    for (var i in rows) {
        if (i == 0) continue; // skip the first row

        var columns = rows[i].split (CSV_COLUMN);
        result.push ({ "value": columns[1], "text": columns[0] });
    }

    return result;
}

... and create the select:

function create_select (data) {
    var e = document.createElement ('select');

    for (var i in data) {
        var option = document.createElement ('option');

        option.value = data[i].value;
        option.innerHTML = data[i].text;

        e.appendChild (option);
    }

    return e;
}

Everything besides AJAX request is pure JavaScript. If you don't want jQuery for some reason, you can write your AJAX request in pure JS as well.

well just trying now and firebug jumps 'data.split is not a function [Detener en este error] var rows = data.split (CSV_ROW); ' — Toni Michel Caubet, Dec 19 '11 at 09:10
@ToniMichelCaubet you might need to check that data is being represented as a string. See http://www.w3schools.com/jsref/jsref_split.asp — penguat, Dec 19 '11 at 11:32

score 0 · Answer 2 · answered Dec 09 '11 at 09:11

0

You should upload this file to your server , and read this file in server ,then return JSON to browser and display it.

answered Dec 09 '11 at 09:11

Allen.M

41
4

i am kind of lost in how to export the excel to json... any info about this? – Toni Michel Caubet Dec 09 '11 at 09:20
This depend on which language you use at server side . You can find the excel keywords in the language's official library or find some open source solution , for example ,search in github.com : https://github.com/search?q=excel&type=Everything&repo=&langOverride=&start_value=1 – Allen.M Dec 09 '11 at 09:24
i need to do it in the client side – Toni Michel Caubet Dec 09 '11 at 09:27
you can't read/write files with browser side javascript. (for security reasons) even if a browser may support it somehow (for example IE), it is generally a bad idea. i also do not understand why you "need" to do it on client side. is it a local webpage? if not, you "can" send the XLS to the server and push the result back into the select box via AJAX. – Kaii Dec 09 '11 at 09:33
i need to do it in the client side because i don't have acces at all to the server side – Toni Michel Caubet Dec 13 '11 at 11:58

one.beat.consumer · Answer 3 · 2011-12-16T18:59:22.560

I would suggest using the Microsoft InterOp libraries server-side to open up the spreadsheet in C#, pull its information into POCOs and either (1) stuff them into JSON or (2) perhaps send the collection of POCOs in your view model when you first render the form page.

Your other option Robert began to explain would work if you would like to keep everything client-side, but you would have to preformat your spreadsheet for CSV notation, and you would have to do some heavy parsing.

I personally would do this server side as permissions to the file system are often easier to handle here too.

Here's info on Microsoft Office Interop Libraries, as well as an example of pulling from Excel.

http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel%28v=office.14%29.aspx

http://www.codeproject.com/KB/dotnet/Exceller.aspx

score 0 · Answer 4 · answered Dec 18 '11 at 11:05

As per my knowledge there is security limitation and u cant read files stored at client side using javascript, There are few alternatives u can use 1) Upload to some server and then read using server side language like c# or php (as someone said before in previous post) 2) Allready read file using server side language and then return data to javascript to parse

Open excel file and export to json

4 Answers4