Open excel file and export to json

Question

I want to dynamically create an input <select></select> file (or possibly an autocomplete text field) depending on an Excel file with this format:

 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 others)

I need to open a .xls file and read each row so that I can get the first two columns as an anchor and value.

This is the most related content I found and the links are broken. How to read and write to a file using JavaScript

Is there a solution for this that will work for IE7

Answer 1

If your file is located on an http server, you can read it using AJAX.

Let us 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 a delimiter character that separates columns.

CSV_ROW is a delimiter character that selects lines.

---

Now we need to execute an AJAX request to get the contents of the CSV data. I am using jQuery to execute AJAX queries.

 $.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 analyze 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 a selection:

 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 except the AJAX request is pure JavaScript. If you don't want jQuery for any reason, you can also write your AJAX request in pure JS.

Answer 2

You must upload this file to your server and read this file on the server, and then return the JSON to the browser and display it.

Answer 3

I would suggest using the Microsoft InterOp server on the server side to open the spreadsheet in C #, pull out its information in POCOs and either (1) write them in JSON, or (2) maybe send a collection of POCOs in your view model at the first displaying the form page.

Your other option, which Robert began to explain, will work if you want to keep everything on the client side, but you have to pre-format the spreadsheet for CSV notation and you have to do heavy parsing.

I personally would do this server side, since it is also easier to handle permissions for the file system here.

Here is information about 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

Answer 4

According to my knowledge, there is a security restriction, and you cannot read files stored on the client side using javascript. There are several alternatives that you can use 1) Download to a server, and then read using the server-side language, for example, C # or php (as mentioned in the previous post) 2) Allready read file using the server language and then return the data in javascript for parsing