You can easily convert Excel file to JSON format with SheetJS
Let's start with downloading the latest release from Github
Then put the library to your web directory
All the js file are under the "dist" folder
There are many files but the one that you need is the "xlsx.full.min.js"
Noted that you can use the "xlsx.core.min.js" for a smaller size to reduce page load. However, some feature might not be available (Need to test first)
For this tutorial, we'll go with the full one
"test.xlsx" is the Excel file that we'll soon convert it to JSON
(All the figure were made up so they're not accurate)
Please observe the table structure before we convert it to JSON
The first step of using the SheetJS is to open the Excel file
However, since client-side javascript can't access the server file directly, we need an alternative approach
For this tutorial, we'll get the file by AJAX (make a GET request for the file)
Good news is there is a code snippet on SheetJS which we can use
Basically, we just need to change the URL parm to your Excel file
For this case, our "test.xlsx" is at the top level so using just "test.xlsx" as the URL is fine
Now we can open the workbook. Next is to get the worksheet that we want to make a JSON
Working with Excel file is very easy and straight forward in SheetJS
From the workbook instance, you can access SheetNames array to get the all the sheet names
And get the Sheet instance from the Sheets array (using the sheet name as index)
Now we have the worksheet instance. Next is to convert it to JSON
We'll use a built in sheet_to_json() utility function
Just call XLSX.utils.sheet_to_json and pass the worksheet instance as a parm
I'll log the JSON to the console output
The header will be used as a key and the next row will be used as value
You can specify an option to when generating JSON to disregard the header and treat it as a data
Just pass {header:1} as the second parm
Now that there's no header, only integer indexes
You can also specify a {raw:true} option to convert all the possible number value into a number instead of a string
For example, $700 will become a 700 instead of "$700" string
Thanks for watching and don't forget to subscribe for more tutorials :)