Excel xlsx file to Json with JavaScript

App Showcase
, , ,
1

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
2

Brilliant Juan :sunglasses:

1 Like
3

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

IMG_20220904_225740
IMG_20220904_2257401080×512 92.6 KB

4

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
5

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

javasiss
javasiss658×1161 80.7 KB

6

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
7

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...

8

Another version of @Kevinkun 's code

sheetjs_2.aia (368.8 KB)

xlxl1
xlxl11183×783 68.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