Menu
I want to convert excel sheet data to json. It has to be dynamic, so there is an upload button where user uploads the excel sheet and the data is then converted into json. Could you please provide me the javascript code? I tried SheetJS but couldn't figure out. I would prefer something straight forward :)
I really appreciate your help!
222
In JavaScript we can add the event listeners to elements like this: We can read the data in the excel file by using a file reader as a binary string in JavaScript. Then we use XLSX which has a built-in facility of SheetJS to convert our binary string into a JSON object.
There is no predefined method in Excel to convert the Excel data to JSON. You can either use online Excel to JSON conversion software or download an add-in from the Microsoft store for this task to get done.
Use this method for easy undertsanding and parsing : npm install --save excel' var xls = require('excel'); xls('Sheet. xlsx', function(err, data) { if(err) throw err; // data is an array of arrays }); As it says, the data it returns is an array of arrays.
NOTE: Not 100% Cross Browser
Check browser compatibility @ http://caniuse.com/#search=FileReader
as you will see people have had issues with the not so common browsers, But this could come down to the version of the browser.. I always recommend using something like caniuse to see what generation of browser is supported... This is only a working answer for the user, not a final copy and paste code for people to just use..
The Fiddle: http://jsfiddle.net/d2atnbrt/3/
THE HTML CODE:
<input type="file" id="my_file_input" /> <div id='my_file_output'></div> THE JS CODE:
var oFileIn; $(function() { oFileIn = document.getElementById('my_file_input'); if(oFileIn.addEventListener) { oFileIn.addEventListener('change', filePicked, false); } }); function filePicked(oEvent) { // Get The File From The Input var oFile = oEvent.target.files[0]; var sFilename = oFile.name; // Create A File Reader HTML5 var reader = new FileReader(); // Ready The Event For When A File Gets Selected reader.onload = function(e) { var data = e.target.result; var cfb = XLS.CFB.read(data, {type: 'binary'}); var wb = XLS.parse_xlscfb(cfb); // Loop Over Each Sheet wb.SheetNames.forEach(function(sheetName) { // Obtain The Current Row As CSV var sCSV = XLS.utils.make_csv(wb.Sheets[sheetName]); var oJS = XLS.utils.sheet_to_row_object_array(wb.Sheets[sheetName]); $("#my_file_output").html(sCSV); console.log(oJS) }); }; // Tell JS To Start Reading The File.. You could delay this if desired reader.readAsBinaryString(oFile); } This also requires https://cdnjs.cloudflare.com/ajax/libs/xls/0.7.4-a/xls.js to convert to a readable format, i've also used jquery only for changing the div contents and for the dom ready event.. so jquery is not needed
This is as basic as i could get it,
EDIT - Generating A Table
The Fiddle: http://jsfiddle.net/d2atnbrt/5/
This second fiddle shows an example of generating your own table, the key here is using sheet_to_json to get the data in the correct format for JS use..
One or two comments in the second fiddle might be incorrect as modified version of the first fiddle.. the CSV comment is at least
Test XLS File: http://www.whitehouse.gov/sites/default/files/omb/budget/fy2014/assets/receipts.xls
This does not cover XLSX files thought, it should be fairly easy to adjust for them using their examples.
141
js-xlsx library makes it easy to convert Excel/CSV files into JSON objects.
Download the xlsx.full.min.js file from here. Write below code on your HTML page Edit the referenced js file link (xlsx.full.min.js) and link of Excel file
<!doctype html> <html> <head> <title>Excel to JSON Demo</title> <script src="xlsx.full.min.js"></script> </head> <body> <script> /* set up XMLHttpRequest */ var url = "http://myclassbook.org/wp-content/uploads/2017/12/Test.xlsx"; var oReq = new XMLHttpRequest(); oReq.open("GET", url, true); oReq.responseType = "arraybuffer"; oReq.onload = function(e) { var arraybuffer = oReq.response; /* convert data to binary string */ var data = new Uint8Array(arraybuffer); var arr = new Array(); for (var i = 0; i != data.length; ++i) arr[i] = String.fromCharCode(data[i]); var bstr = arr.join(""); /* Call XLSX */ var workbook = XLSX.read(bstr, { type: "binary" }); /* DO SOMETHING WITH workbook HERE */ var first_sheet_name = workbook.SheetNames[0]; /* Get worksheet */ var worksheet = workbook.Sheets[first_sheet_name]; console.log(XLSX.utils.sheet_to_json(worksheet, { raw: true })); } oReq.send(); </script> </body> </html> Input:
Output:
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
