CODEWITHSUNDEEP
javascriptjqueryjsonexcel
Toni Michel Caubet
Toni Michel Caubet

Reputation: 20163

Open excel file and export to json

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

Upvotes: 4

Views: 7858

Answers (4)

Amit Patil
Amit Patil

Reputation: 3067

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

Upvotes: 0

one.beat.consumer
one.beat.consumer

Reputation: 9504

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

Upvotes: 0

robert petranovic
robert petranovic

Reputation: 341

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.

Upvotes: 6

Allen.M
Allen.M

Reputation: 41

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

Upvotes: 0

Related Questions