simple-excel-to-json
v2.3.0
Published
parse excel to json object
Downloads
7,216
Readme
Simple-Excel-to-json
Read excel file and parse it to javascript Object.
Install
npm install simple-excel-to-jsonSimplified Usage
You can just require the modure "simple-excel-to-json" and execute the method 'parseXls2Json'
var parser = require('simple-excel-to-json')
var doc = parser.parseXls2Json('./example/sample8.xlsx');
Apply transform function
This approach will use the same parser instance to convert a xls into the json data. If you have many xls which need different particular transform function, you can create many XlsParser instances then apply the transform functions(see basic usage and advance usage).
Basic Usage
- Normal case
- Output Nested JSON
Case (Normal)
.parseXls2Json(path)
Where
- path is your excel file path
Example
// Create an instance for XlsParser
var parser = new (require('simple-excel-to-json').XlsParser)();
var doc = parser.parseXls2Json('./example/sample.xlsx');
//print the data of the first sheet
console.log(doc[0]);Input
| item | price | number | | :------------- | :--------- | :------------- | | apple |100 |2 | | banana | 200 | 12 | | coffee | 150| 3 |
Output
[
[
{
"item":"apple",
"price":100,
"number":2
},
{
"item":"banana",
"price":200,
"number":12
},
{
"item":"coffee",
"price":150,
"number":3
}
]
]Case (Nested JSON)
.parseXls2Json(path, { isNested: true })- Assign true as second parameter to enable output nested JSON
Example
// Create an instance for XlsParser
var parser = new (require('simple-excel-to-json').XlsParser)();
var doc = parser.parseXls2Json('./example/sample.xlsx', { isNested: true });
//print the data of the first sheet
console.log(doc[0]);Input
|Type|Dealership.us[0].location|Dealership.us[1].location|Dealership.jp[0].location | :-------------| :--------- | :--------- | :--------- | |Sedan|New York|Dallas|Tokyo| |SUV|Ohio||Osaka|
Output
[
[
{
"type": "Sedan",
"dealership":
{
"us":
[
{
"location": "New York"
},
{
"location": "Dallas"
}
],
"jp":
[
{
"location": "Tokyo"
}
]
}
},
{
"type": "SUV",
"dealership":
{
"us":
[
{
"location": "Ohio"
},
{
"location": ""
}
],
"jp":
[
{
"location": "Osaka"
}
]
}
}
]
]Case (To Camel Case)
If you want to have output json with camel case properties, you can apply an 'option' { isToCamelCase: true } to parseXls2Json() e.g 'Car Name' to 'carName' 'product.Type.hasGPS' to 'product.type.hasGPS'
Example
// Create an instance for XlsParser
var parser = new (require('simple-excel-to-json').XlsParser)();
var option =
{
isToCamelCase: true,
isNested: true,
}
var doc = parser.parseXls2Json('./example/sample6.xlsx', option );Input
|price|product.Type.hasGPS|Model Name| | :-------------| :--------- | :--------- | |100|y|sedan 01| |150|y|SUV 22| |200|n|Sport Cars IV|
Output
[
[
{
'price': 100,
'product':
{
'type':
{
'hasGPS': 'y'
}
},
'modelName': 'sedan 01'
},
{
'price': 150,
'product':
{
'type':
{
'hasGPS': 'y'
}
},
'modelName': 'SUV 22'
},
{
'price': 200,
'product':
{
'type':
{
'hasGPS': 'n'
}
},
'modelName': 'Sport Cars IV'
},
]
]Advance Usage
You can apply transfomation function to transform the output
.setTranseform(func)
Where
- func is your transfomation function to convert the output into you expected
Example
// Create an instance for XlsParser
var parser = new (require('simple-excel-to-json').XlsParser)();
parse.setTranseform( [
function(sheet1){
sheet1.number = sheet1.number.trim();
sheet1.buyer = sheet1.buyer.split(';').filter( item=>item.trim()!=='');
sheet1.buyer.forEach( (e,i,arr) => {
arr[i]=e.trim();
});
},
function(sheet2){
sheet2.Type = sheet2.Type.toLowerCase();
}
]);
var doc = parser.parseXls2Json('./example/sample2.xlsx');
Input
sheet 1
| item | price | number | buyer | | :------------- | :--------- | :------------- | :---------- | | apple | 100 | two | Andy;Bob | | banana | 200 | twelve | Tom; | | coffee| 150 | three | Mary; Calvin |
sheet 2
|Type|Price| | :------------- | :--------- | |Car|10000| |Bus|200000|
Output
[
[
{
"item":"apple",
"price":100,
"number":"two",
"buyer": ["Andy","Bob"]
},
{
"item":"banana",
"price":200,
"number":"twelve",
"buyer":["Tom"]
},
{
"item":"coffee",
"price":150,
"number":"three",
"buyer":["Mary","Calvin"]
}
],
[
{
"Type":"car",
"Price":10000
},
{
"Type":"bus",
"Price":20000
}
]
]
Case (Nested JSON)
Example
// Create an instance for XlsParser
var parser = new (require('simple-excel-to-json').XlsParser)();
parse.setTranseform( [
function(sheet1){
sheet1['type'] = sheet1['type'].toLowerCase();
sheet1['price'] = sheet1['price'].split(';').map( e => e.trim());
sheet1['dealership.us[0].location'] = sheet1['dealership.us[0].location'].trim();
},
]);
var doc = parser.parseXls2Json('./example/sample2.xlsx', { isNested: true });
Input
|type|price|dealership.us[0].location| | :------------- | :--------- | :--------- | |Sedan|2000;1000|New York| |SUV|2000;500|Ohio|
Output
[
[
{
"type": "sedan",
"price": ["2000","1000"],
"dealership":
{
"us":
[
{
"location": "new york"
}
]
}
},
{
"type": "suv",
"price": ["2000";"500"],
"dealership":
{
"us":
[
{
"location": "ohio"
}
}
}
}
]
]Sheet has empty cell
If your sheet contains empty cell, simple-excel-to-json will give "" for this cell in result object.
- Normal case
- Output Nested JSON case
Case (Normal)
|Type|Price| | :------------- | :--------- | |Car|| |Bus|200000|
[
{
"Type":"car",
"Price":""
},
{
"Type":"bus",
"Price":20000
}
]
]
Case (Nested JSON)
|Type|Price|Dealership.us[0].location| | :------------- | :--------- | :--------- | |Sedan|2000|| |SUV|2000|Ohio|
[
[
{
"type": "Sedan",
"price": 2000,
"dealership":
{
"us":
[
{
"location": ""
}
]
}
},
{
"type": "SUV",
"price": 2000,
"dealership":
{
"us":
[
{
"location": "ohio"
}
}
}
}
]
]Release Note:
2.3.0
Support passing options to XLSX parser(node-xlsx) library
2.2.3
Fix vulnerability issue
2.2.2
Fix vulnerability issue
2.2.1
Fix bug
2.2.0
Export the constructor XlsParser that can be used to create multiple parser instances
2.1.1
Issue fix
2.1.0
Replace white space with '_' for property. for example, 'card number' in header --becomes--> 'card_number:'
Throw 'failedToTransformError' when failed to perform transformation
For example:
var parser = require('simple-excel-to-json');
parse.setTranseform( [
function(sheet1){
sheet1.number = sheet1.number.trim();
sheet1.buyer = sheet1.buyer.split(';').filter( item=>item.trim()!=='');
sheet1.buyer.forEach( (e,i,arr) => {
arr[i]=e.trim();
});
//lets throw an error for invalid array length
if(sheet1.buyer.length <1)
{
throw new Error('length of sheet1.buyer < 1 ')
}
},
function(sheet2){
sheet2.Type = sheet2.Type.toLowerCase();
}
]);
try
{
var doc = parser.parseXls2Json('./example/sample8.xlsx');
}
catch(err)
{
//capture the error
if(err instanceof parser.failedToTransformError)
{
console.log('name: '+ err.name)
console.log('message: '+ err.message)
console.log('stack: '+ err.stack)
}
}
//--------------output---------------
name: failedToTransformError
message: {"sheet number":0,"element":"{\"item\":\"banana\",\"price\":200,\"number\":\"twelve\",\"buyer\":[]}","index":0,"errorMessage":"length of sheet1.buyer < 1 "}
stack:
at C:\Users\andyl\Documents\excel-to-json\test\test.js:494:27
at C:\Users\andyl\Documents\excel-to-json\index.js:73:17
...2.0.0
add a parameter 'option' to decide the output format
option = {
isNested: true,
toLowerCase: true
}
parseXls2Json(path, option)isNested is true: convert excel data to nested json object toLowerCase is true: property in output json data will be lower case
1.1.8
Update README.md
1.1.7
Usage in README.md. Add example for Nested Case of Advance Usage
1.1.6
Support Nested JSON format