admin管理员组文章数量:1295863
I am new to Node.js. My requirement is, I need to parse a JSON and export the data into Excel file with all the fields in the JSON.
My JSON is as follows:
{
"id": 1255,
"title": "The Brain and Nervous System (LS1.D)",
"description": "By the time you finish this playlist, you should be able to: 1. Describe how the nervous system is organized and how it works 2. Describe the role of the nervous sytem, and explain how our different senses work to send information to your brain",
"keyTerms": "You should also be able to define the following words: stimulus, response, neuron, sensory neuron, motor neuron, nerve impulse, dendrite, axon, nerve, central nervous system, peripheral nervous system, brain, spinal cord, cerebrum, cerebellum, brain stem, retina, cochlea",
"visible": true,
"introduction": {
"id": 5336,
"title": "Introductory Materials",
"resources": [
{
"id": 23022,
"title": "Vocabulary - Brain and Nervous System",
"description": "",
"purpose": "",
"category": "Website",
"position": 1,
"contentItem": {
"id": 1650,
"url": "",
"itemType": "Website",
"embedUrl": null
}
},
{
"id": 23023,
"title": "The Brain and Nervous System Study Guide",
"description": "Fill out this study guide while studying! It will help you prepare for the assessment!",
"purpose": "",
"category": "Website",
"position": 2,
"contentItem": {
"id": 12581,
"url": "/document/d/1TjF1MY3cyGNKT4s46uk1iz5NvjrY59eNPH8YKYYTC_E/edit",
"itemType": "Website",
"embedUrl": null
}
}
]
},
"objectives": [
{
"id": 10732,
"title": "1. Describe how the nervous system is organized",
"caContribution": 5,
"position": 1,
"resources": [
{
"id": 23024,
"title": "Reading - How the Nervous System Works",
"description": "",
"purpose": "",
"category": "Document",
"position": 1,
"contentItem": {
"id": 1651,
"url": null,
"itemType": "Document",
"embedUrl": "",
"s3Url": "/files/content_items/relateds/000/001/651/original/53d1ddd8f07787731aa7d84f-how_20nervous_20system_20works_001.pdf?1424368501"
}
}
]
},
{
"id": 10734,
"title": "2. Describe the role of the nervous sytem, and explain how our different senses work to send information to your brain",
"caContribution": 5,
"position": 2,
"resources": [
{
"id": 23039,
"title": "Study Jams - The Senses",
"description": "This series of videos explains the different senses: sight, hearing, taste, touch, and smell",
"purpose": "",
"category": "Website",
"position": 1,
"contentItem": {
"id": 1666,
"url": ".htm",
"itemType": "Website",
"embedUrl": null
}
},
{
"id": 23040,
"title": "Nervous System: I'm Sensing Something",
"description": "Format: Article Content: How does the nervous system work and interact with other systems",
"purpose": "",
"category": "Website",
"position": 1,
"contentItem": {
"id": 12582,
"url": ".html",
"itemType": "Website",
"embedUrl": null
}
}
]
}
]
}
I used the json2xls module and I got the spreadsheet with the fields id, title, description, keyTerms, visible, introduction, objectives where introduction and objectives came like [object][object],[object][object]
Here is a code to export into excel file:
var fs = require("fs");
var json2xls = require("json2xls");
fs.readFile('/home/e100093/nodejs/sampleJson.json','utf8',function(err,body){
var jsonData = JSON.parse(body);
var xls = json2xls(jsonData);
fs.writeFileSync('final-test.xlsx', xls, 'binary');
});
But I want the output fields like id, title, description, keyTerms, visible, introduction.id ,introduction.title ,introduction.resources.id ,introduction.resources.title ,..., objectives.id, objectives.title, objectives.description, .....,etc.
I need to parse the object and array dynamically and produce the fields in each column of the spreadsheet.
The sample output with columns and data are as follows:
id title description keyTerms visible introduction.id introduction.title introduction.resources.id introduction.resources.title introduction.resources.description introduction.resources.purpose introduction.resources.category introduction.resources.position introduction.resources.contentItem.id introduction.resources.contentItem.url introduction.resources.contentItem.itemType introduction.resources.contentItem.embedUrl objectives.id objectives.title objectives.caContribution objectives.position objectives.resources.id objectives.resources.title objectives.resources.description objectives.resources.purpose objectives.resources.category objectives.resources.position objectives.resources.contentItem.id objectives.resources.contentItem.url objectives.resources.contentItem.itemType objectives.resources.contentItem.embedUrl objectives.resources.contentItem.s3Url
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true 5336 Introductory Materials 23022 Vocabulary - Brain and Nervous System null null Website 1 1650 /... Website null null null null null null null null null null null null null null null null
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true 5336 Introductory Materials 23023 The Brain and Nervous System Study Guide Fill out this... null Website 2 12581 /... Website null null null null null null null null null null null null null null null null
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true null null null null null null null null null null null null 10732 1. Describe how... 5 1 23024 Reading - How... null null Document 1 1651 null Document ... /files/content...
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true null null null null null null null null null null null null 10734 2. Describe the role... 5 2 23039 Study Jams - The Senses This series of videos null Website 1 1666 ... Website null
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true null null null null null null null null null null null null 10734 2. Describe the role... 5 2 23040 Nervous System: I'm Sensing... Format: Article Content... null Website 1 12582 ... Website null
Please help me how to do this.
I am new to Node.js. My requirement is, I need to parse a JSON and export the data into Excel file with all the fields in the JSON.
My JSON is as follows:
{
"id": 1255,
"title": "The Brain and Nervous System (LS1.D)",
"description": "By the time you finish this playlist, you should be able to: 1. Describe how the nervous system is organized and how it works 2. Describe the role of the nervous sytem, and explain how our different senses work to send information to your brain",
"keyTerms": "You should also be able to define the following words: stimulus, response, neuron, sensory neuron, motor neuron, nerve impulse, dendrite, axon, nerve, central nervous system, peripheral nervous system, brain, spinal cord, cerebrum, cerebellum, brain stem, retina, cochlea",
"visible": true,
"introduction": {
"id": 5336,
"title": "Introductory Materials",
"resources": [
{
"id": 23022,
"title": "Vocabulary - Brain and Nervous System",
"description": "",
"purpose": "",
"category": "Website",
"position": 1,
"contentItem": {
"id": 1650,
"url": "http://quizlet./45497180/flashcards",
"itemType": "Website",
"embedUrl": null
}
},
{
"id": 23023,
"title": "The Brain and Nervous System Study Guide",
"description": "Fill out this study guide while studying! It will help you prepare for the assessment!",
"purpose": "",
"category": "Website",
"position": 2,
"contentItem": {
"id": 12581,
"url": "https://docs.google./a/summitps/document/d/1TjF1MY3cyGNKT4s46uk1iz5NvjrY59eNPH8YKYYTC_E/edit",
"itemType": "Website",
"embedUrl": null
}
}
]
},
"objectives": [
{
"id": 10732,
"title": "1. Describe how the nervous system is organized",
"caContribution": 5,
"position": 1,
"resources": [
{
"id": 23024,
"title": "Reading - How the Nervous System Works",
"description": "",
"purpose": "",
"category": "Document",
"position": 1,
"contentItem": {
"id": 1651,
"url": null,
"itemType": "Document",
"embedUrl": "https://view-api.box./1/sessions/493fca96d46a4559813c3118ebeef8b6/view?theme=light",
"s3Url": "/files/content_items/relateds/000/001/651/original/53d1ddd8f07787731aa7d84f-how_20nervous_20system_20works_001.pdf?1424368501"
}
}
]
},
{
"id": 10734,
"title": "2. Describe the role of the nervous sytem, and explain how our different senses work to send information to your brain",
"caContribution": 5,
"position": 2,
"resources": [
{
"id": 23039,
"title": "Study Jams - The Senses",
"description": "This series of videos explains the different senses: sight, hearing, taste, touch, and smell",
"purpose": "",
"category": "Website",
"position": 1,
"contentItem": {
"id": 1666,
"url": "http://studyjams.scholastic./studyjams/jams/science/human-body/touching.htm",
"itemType": "Website",
"embedUrl": null
}
},
{
"id": 23040,
"title": "Nervous System: I'm Sensing Something",
"description": "Format: Article Content: How does the nervous system work and interact with other systems",
"purpose": "",
"category": "Website",
"position": 1,
"contentItem": {
"id": 12582,
"url": "http://www.biology4kids./files/systems_nervous.html",
"itemType": "Website",
"embedUrl": null
}
}
]
}
]
}
I used the json2xls module and I got the spreadsheet with the fields id, title, description, keyTerms, visible, introduction, objectives where introduction and objectives came like [object][object],[object][object]
Here is a code to export into excel file:
var fs = require("fs");
var json2xls = require("json2xls");
fs.readFile('/home/e100093/nodejs/sampleJson.json','utf8',function(err,body){
var jsonData = JSON.parse(body);
var xls = json2xls(jsonData);
fs.writeFileSync('final-test.xlsx', xls, 'binary');
});
But I want the output fields like id, title, description, keyTerms, visible, introduction.id ,introduction.title ,introduction.resources.id ,introduction.resources.title ,..., objectives.id, objectives.title, objectives.description, .....,etc.
I need to parse the object and array dynamically and produce the fields in each column of the spreadsheet.
The sample output with columns and data are as follows:
id title description keyTerms visible introduction.id introduction.title introduction.resources.id introduction.resources.title introduction.resources.description introduction.resources.purpose introduction.resources.category introduction.resources.position introduction.resources.contentItem.id introduction.resources.contentItem.url introduction.resources.contentItem.itemType introduction.resources.contentItem.embedUrl objectives.id objectives.title objectives.caContribution objectives.position objectives.resources.id objectives.resources.title objectives.resources.description objectives.resources.purpose objectives.resources.category objectives.resources.position objectives.resources.contentItem.id objectives.resources.contentItem.url objectives.resources.contentItem.itemType objectives.resources.contentItem.embedUrl objectives.resources.contentItem.s3Url
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true 5336 Introductory Materials 23022 Vocabulary - Brain and Nervous System null null Website 1 1650 http://quizlet./... Website null null null null null null null null null null null null null null null null
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true 5336 Introductory Materials 23023 The Brain and Nervous System Study Guide Fill out this... null Website 2 12581 https://docs.google./... Website null null null null null null null null null null null null null null null null
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true null null null null null null null null null null null null 10732 1. Describe how... 5 1 23024 Reading - How... null null Document 1 1651 null Document https://view-api.box./1... /files/content...
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true null null null null null null null null null null null null 10734 2. Describe the role... 5 2 23039 Study Jams - The Senses This series of videos null Website 1 1666 http://studyjams.scholastic.... Website null
1255 The Brain and Nervous System (LS1.D) By the time you finish... You should also... true null null null null null null null null null null null null 10734 2. Describe the role... 5 2 23040 Nervous System: I'm Sensing... Format: Article Content... null Website 1 12582 http://www.biology4kids.... Website null
Please help me how to do this.
Share edited Dec 31, 2015 at 12:24 Arunraj asked Dec 30, 2015 at 10:46 ArunrajArunraj 5685 silver badges22 bronze badges 4- Please also provide the code that you are using to create the excel file. stackoverflow./help/how-to-ask – MojoJojo Commented Dec 30, 2015 at 10:52
- @MojoJojo yes, I have added the code that I used to export the JSON data. – Arunraj Commented Dec 30, 2015 at 11:18
- Provide please an example of the data you want to achieve. – Alexandr Lazarev Commented Dec 30, 2015 at 12:29
- @LazarevAlexandr I have added a sample output that I want to achieve. Any other form of output that matches my requirement are also fine. – Arunraj Commented Dec 31, 2015 at 12:26
2 Answers
Reset to default 6You can do it with AlaSQL javascript library with special SEARCH operator, which is designed to do search for nested objects. See the code below, which generates the Excel file with your structure (I did not include all fields):
var alasql = require('alasql');
var data = [{
"id": 1255,
"title": "The Brain and Nervous System (LS1.D)",
// ...
},
{
"id": 1256,
// ...
}];
// Here is search query
alasql('SEARCH / AS @a \
UNION ALL( \
introduction AS @b \
resources / AS @c \
RETURN(@a->id AS id, @a->title AS title, @a->description AS description, \
@a->keyTerms AS keyTerms, @a->visible AS visible, \
@b->id as [introduction.id], @b->title as [introduction.title], \
@c->id AS [introduction.resources.id], \
@c->contentItem->id AS [introduction.resources.contentItem.id] \
) \
, \
objectives AS @b \
resources / AS @c \
RETURN(@a->id AS id, @a->title AS title, @a->description AS description, \
@a->keyTerms AS keyTerms, @a->visible AS visible, \
@b->id as [objectives.id], @b->title as [objectives.title], \
@c->id AS [objectives.resources.id], \
@c->contentItem->id AS [objectives.resources.contentItem.id] \
) \
) INTO XLSX("test411.xlsx",{headers:true})\
FROM ?',[data]);
Some explanations:
- SEARCH - is a special statement for query nested objects
- / - loop over array elements
- UNION ALL(...,...) - union of all nested found records
- AS @variable - save current search position to temporary variable
- introduction - go deep into the property "introduction"
- RETURN(...,...) - create a record (JSON object) with properties
- RETURN(value AS alias) - alias for the value
- @a->id - get id property similar to a.id in JavaScript
- [...] - terms with any special charaters
- INTO XLSX("test411.xlsx",{headers:true}) - save results to Excel file with headers
- FROM ? - get data from parameter
- alasql(sql, [data]) - put data variable as first parameter of the query
You also need to add other columns to finish this query. The sample file will be uploaded here in two hours.
You also can remove this line from the source code: INTO XLSX(), and alasql() will return a JSON object with all properties:
var res = alasql('SEARCH / AS @a UNION ALL(...) FROM ?',[data]);
console.log(res);
Here is the jsFiddle example
You are using the json2xls module which accepts either on object or an array, but nested level object structure. What I mean is that your definition would be fine if introduction and objectives were scalar properties. I.e.
{
"id": 1255,
"title": "...)",
"description": "...",
"keyTerms": "...",
"visible": true,
"introduction": "string/int/float/bool/date",
"objectives": "string/int/float/bool/date"
}
or
[
{
"id": 1255,
"title": "...)",
"description": "...",
"keyTerms": "...",
"visible": true,
"introduction": "string/int/float/bool/date",
"objectives": "string/int/float/bool/date"
},
{
"id": 1256,
"title": "...)",
"description": "...",
"keyTerms": "...",
"visible": true,
"introduction": "string/int/float/bool/date",
"objectives": "string/int/float/bool/date"
}
]
but in your case introduction is an object with nested elements, and objectives is an array of objects, which both are interpreted as [object] [object]
I do not know what you want in your excel file, but you need to decide how to flatten the structure first.
版权声明：本文标题：javascript - Parse a Json(with array and objects) and export the data into Excel file in Node.js - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741614501a2388449.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论