Excel xlsx file to Json with JavaScript

Convert an Excel .xlsx file to Json using JavaScript.

excel_to_json.htm

<script src="xlsx.full.min.js"></script>
<script>
// Adaptado por kio4.com de: https://blog.bitsrc.io/csv-excel-to-json-in-javascript-70e61a1dc32d
    my_file = AppInventor.getWebViewString(); // INPUT
// read Excel file and convert to json format using fetch
fetch(my_file).then(function (res) {
    /* get the data as a Blob */
    if (!res.ok) throw new Error("fetch failed");
    return res.arrayBuffer();
})
.then(function (ab) {
    /* parse the data when it is received */
    var data = new Uint8Array(ab);
    var workbook = XLSX.read(data, {
        type: "array"
    });

    /* *****************************************************************
    * DO SOMETHING WITH workbook: Converting Excel value to Json       *
    ********************************************************************/
    var first_sheet_name = workbook.SheetNames[0];
    /* Get worksheet */
    var worksheet = workbook.Sheets[first_sheet_name];

    var _JsonData = XLSX.utils.sheet_to_json(worksheet, {raw: true});
    /************************ End of conversion ************************/
 // AppInventor.setWebViewString(JSON.stringify(_JsonData)); // No Pretty
	AppInventor.setWebViewString(JSON.stringify(_JsonData, undefined, 2)); // OUTPUT
});
</script>

excel_javascript

p70B_excel_javascript.aia (333.9 KB)

10 Likes

Brilliant Juan :sunglasses:

1 Like

It works.but how to read file if it's in asd path.i tried but doesn't work.please help.

You should first copy the html file, and any other related files to the ASD, so that the xlsx file is in the same directory, or in a relative path to the html file

1 Like

I had this idea, but it didn't work for me, I copied all three files to ASD.
p70B2_excel_javascript.aia (335.5 KB)

In this example, Button3 - JavaScript - sieve.htm in ASD works
but /excel_to_json.htm doesn't work

xlsx.js not working when the url like 'file://storage /emulated/0/...', it has to be like 'http://....'
one solution is to load the xlsx file in App inventor and change to base64, then read the base64 in javascript by XLSX.read(base64,{type:'base64')

sheetjs (1).aia (360.2 KB)

2 Likes

Very good @Kevinkun !!, I also had that idea, send the content of the file as a string and have xls.js convert it.
I think the problem is fetch(), check...

Another version of @Kevinkun 's code

sheetjs_2.aia (368.8 KB)

<script src="xlsx.full.min.js"></script>
<script>
datos =  window.AppInventor.getWebViewString().split("||");
base64str = datos[0];
sheetIndex = datos[1];
	
basestr = base64str.substring(base64str.indexOf(',')+1);
wb = XLSX.read(basestr,{type:'base64'});
json = XLSX.utils.sheet_to_json(wb.Sheets[wb.SheetNames[sheetIndex-1]]);
window.AppInventor.setWebViewString(JSON.stringify(json, undefined, 2));
</script>
1 Like