英文:
How to convert JSON data into Excel file using XLSX library in NodeJs
问题
以下是您要翻译的代码部分:
const XLSX = require('xlsx');
const path = require('path');
const data = require('./related_reports.json');
const workSheetColumnNames = ['报告标题', '外观', 'slug'];
const sheetName = '报告';
const filePath = './报告.xlsx';
console.log(data.length);
var value = [];
const exportSheetToExcel = (data, workSheetColumnNames, sheetName, filePath) => {
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data.length; j++) {
for (var k = 0; k < data[j].related_reports_updated.sub_categories.length; k++) {
if (data[j].related_reports_updated.sub_categories[k].short_title.includes(data[i].short_title)) {
value = [data[i].short_title, 0, data[j].related_reports_updated.sub_categories[k].short_title];
} else {
continue;
}
}
for (var l = 0; l < data[j].related_reports_updated.categories.length; l++) {
if (data[j].related_reports_updated.categories[l].short_title.includes(data[i].short_title)) {
value = [data[i].short_title, 0, data[j].related_reports_updated.categories[l].short_title];
} else {
continue;
}
}
for (var m = 0; m < data[j].related_reports_updated.other_reports.length; m++) {
if (data[j].related_reports_updated.other_reports[m].short_title.includes(data[i].short_title)) {
value = [data[i].short_title, 0, data[j].related_reports_updated.other_reports[m].short_title];
} else {
continue;
}
}
}
}
const workSheetData = [workSheetColumnNames, ...value];
const workBook = XLSX.utils.book_new();
const workSheet = XLSX.utils.aoa_to_sheet(workSheetData);
XLSX.utils.book_append_sheet(workBook, workSheet, sheetName);
XLSX.writeFile(workBook, path.resolve(filePath));
return true;
}
exportSheetToExcel(data, workSheetColumnNames, sheetName, filePath);
请注意,我将变量和注释翻译成了中文,但代码逻辑和结构保持不变。如果您有任何其他问题,请随时提出。
英文:
I have a big JSON file that has data structure like this below:
[
{
"slug": "vertical-lift-module-market",
"id": 68055,
"short_title": "Vertical Lift Module (VLM) Market",
"related_reports_updated": {
"sub_categories": [
{
"slug": "audience-analytics-market",
"id": 66684,
"short_title": "Audience Analytics Market"
},
{
"slug": "mobile-wallet-market",
"id": 68830,
"short_title": "Mobile Wallet Market"
},
{
"id": 46625,
"slug": "north-america-3d-4d-technology-market",
"short_title": "NA 3D 4D Technology Market"
},
{
"short_title": "North America Chatbot Market",
"slug": "north-america-chatbot-market",
"id": 72309
},
{
"short_title": "Optical Wavelength Services Market",
"slug": "optical-wavelength-services-market",
"id": 71348
}
],
"categories": [
{
"id": 48402,
"slug": "artificial-intelligence-impact-and-future-in-modern-warfare",
"short_title": "AI in Modern Warfare Market"
},
{
"short_title": "Certificate Authority Market",
"slug": "certificate-authority-market",
"id": 70769
},
{
"short_title": "Global Mobile Identification Market",
"slug": "global-mobile-identification-market",
"id": 91316
},
{
"slug": "identity-verification-market",
"id": 69133,
"short_title": "Identity Verification Market"
},
{
"slug": "signature-verification-market",
"id": 59014,
"short_title": "Signature Verification Market"
}
],
"other_reports": [
{
"id": 48102,
"slug": "global-artificial-lift-systems-market-industry",
"short_title": "Artificial Lift System Market"
},
{
"id": 51724,
"slug": "latin-america-artificial-lift-systems-market-industry",
"short_title": "Latin America Artificial Lift System Market"
},
{
"id": 55702,
"slug": "medical-lifting-sling-market",
"short_title": "Medical Lifting Slings Market"
},
{
"id": 52020,
"slug": "north-america-artificial-lift-systems-market-industry",
"short_title": "North America Artificial Lift Systems Market"
},
{
"slug": "vertical-farming-market",
"id": 61898,
"short_title": "Vertical Farming Market"
}
]
}
},
{
"slug": "united-states-real-estate-services---growth-trends-and-forecast-2022---2027",
"id": 68056,
"short_title": "United States Real Estate Services Market",
"related_reports_updated": {
"sub_categories": [
{
"slug": "canada-real-estate-services-market---growth-trends-and-forecast-2020---2025",
"id": 68051,
"short_title": "Canada Real Estate Services Market"
},
{
"slug": "germany-real-estate-services-market--growth-trends-and-forecast-2020---2025",
"id": 68054,
"short_title": "Germany Real Estate Services Market"
},
{
"short_title": "Office Real Estate Market",
"slug": "office-real-estate-market",
"id": 80022
},
{
"slug": "uk-real-estate-services-market---growth-trends-and-forecast-2020---2025",
"id": 68057,
"short_title": "United Kingdom Real Estate Services Market"
},
{
"short_title": "United States Senior Living Market ",
"slug": "united-states-senior-living-market",
"id": 72583
}
],
"categories": [
{
"slug": "uae-real-estate-market-services",
"id": 68040,
"short_title": "United Arab Emirates Real Estate Services Market"
},
{
"id": 46257,
"slug": "residential-real-estate-market-in-uae",
"short_title": "United Arab Emirates Residential Real Estate Market"
},
{
"id": 54710,
"slug": "commercial-real-estate-market-in-usa",
"short_title": "United States Commercial Real Estate Market"
},
{
"short_title": "United States Luxury Residential Real Estate Market",
"slug": "united-states-luxury-residential-real-estate-market",
"id": 90838
},
{
"short_title": "United States Office Real Estate Market",
"slug": "united-states-office-real-estate-market",
"id": 72479
}
],
"other_reports": [
{
"short_title": "United States (US) MEP Services Market",
"slug": "united-states-mep-services-market",
"id": 71420
},
{
"slug": "united-states-hvac-services-market",
"id": 67903,
"short_title": "US HVAC Services Market"
},
{
"short_title": "United States IT Services Market ",
"slug": "united-states-it-services-market",
"id": 91565
},
{
"short_title": "United States Managed Services Market",
"slug": "united-states-managed-services-market",
"id": 71366
},
{
"short_title": "United States Pet Care and Services Market",
"slug": "united-states-pet-care-and-services-market",
"id": 90805
}
]
}
},
]
SO here I have to compare short_title
of every id with the sort_title of arrays available in related_reports_updated
field.I have 3 arrays inside this field sub_categories
, categories
and other_reports
.If it is similar to those then i want to add those short_title
into the excel file.
Below is my code:
const XLSX = require('xlsx');
const path = require('path');
const data = require('./related_reports.json');
const workSheetColumnNames = ['Report_Title','Appearance','slug'];
const sheetName = 'Reports';
const filePath = './report.xlsx';
console.log(data.length);
var value = [];
const exportSheetToExcel = (data,workSheetColumnNames,sheetName,filePath) => {
for(var i=0;i<data.length;i++) {
for(var j=0;j<data.length;j++) {
for(var k=0;k<data[j].related_reports_updated.sub_categories.length;k++) {
if(data[j].related_reports_updated.sub_categories[k].short_title.includes(data[i].short_title)){
value = [data[i].short_title,0,data[j].related_reports_updated.sub_categories[k].short_title];
}
else{
continue;
}
}
for(var l=0;l<data[j].related_reports_updated.categories.length;l++) {
if(data[j].related_reports_updated.categories[l].short_title.includes(data[i].short_title)){
value = [data[i].short_title,0,data[j].related_reports_updated.categories[l].short_title];
}
else{
continue;
}
}
for(var m=0;m<data[j].related_reports_updated.other_reports.length;m++) {
if(data[j].related_reports_updated.other_reports[m].short_title.includes(data[i].short_title)){
value = [data[i].short_title,0,data[j].related_reports_updated.other_reports[m].short_title];
}
else{
continue;
}
}
}
}
const workSheetData = [workSheetColumnNames,...value];
const workBook = XLSX.utils.book_new();
const workSheet = XLSX.utils.aoa_to_sheet(workSheetData);
XLSX.utils.book_append_sheet(workBook,workSheet,sheetName);
XLSX.writeFile(workBook,path.resolve(filePath));
return true;
}
exportSheetToExcel(data,workSheetColumnNames,sheetName,filePath);
But after processing for a sometime its throwing error saying
Error: aoa_to_sheet expects an array of arrays
Someone let me know how can I get the desired result.
答案1
得分: 1
以下是您要求的代码的翻译部分:
const XLSX = require('xlsx');
const path = require('path');
const data = require('./related_reports.json');
const workSheetColumnNames = ['Report_Title', 'Appearance', 'slug'];
const sheetName = 'Reports';
const filePath = './report.xlsx';
console.log(data.length);
var rows = [];
const exportSheetToExcel = (data, workSheetColumnNames, sheetName, filePath) => {
var value = [];
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data.length; j++) {
['sub_categories', 'categories', 'other_reports'].forEach(key => {
var subTree = data[j].related_reports_updated[key];
for (var k = 0; k < subTree.length; k++) {
if (subTree[k].short_title.includes(data[i].short_title)) {
value = [data[i].short_title, 0, subTree[k].short_title];
rows.push(value)
}
}
})
}
}
const workSheetData = [workSheetColumnNames, ...rows];
const workBook = XLSX.utils.book_new();
const workSheet = XLSX.utils.aoa_to_sheet(workSheetData);
XLSX.utils.book_append_sheet(workBook, workSheet, sheetName);
XLSX.writeFile(workBook, path.resolve(filePath));
return true;
}
exportSheetToExcel(data, workSheetColumnNames, sheetName, filePath);
希望这可以帮助您理解代码的内容。如果您有任何其他问题,请随时提出。
英文:
Try this:
const XLSX = require('xlsx');
const path = require('path');
const data = require('./related_reports.json');
const workSheetColumnNames = ['Report_Title','Appearance','slug'];
const sheetName = 'Reports';
const filePath = './report.xlsx';
console.log(data.length);
var rows = [];
const exportSheetToExcel = (data,workSheetColumnNames,sheetName,filePath) => {
var value = [];
for(var i=0;i<data.length;i++) {
for(var j=0;j<data.length;j++) {
['sub_categories','categories', 'other_reports'].forEach(key=>{
var subTree = data[j].related_reports_updated[key];
for(var k=0;k<subTree.length;k++) {
if(subTree[k].short_title.includes(data[i].short_title)){
value = [data[i].short_title,0,subTree[k].short_title];
rows.push(value)
}
}
})
}
}
const workSheetData = [workSheetColumnNames,...rows];
const workBook = XLSX.utils.book_new();
const workSheet = XLSX.utils.aoa_to_sheet(workSheetData);
XLSX.utils.book_append_sheet(workBook,workSheet,sheetName);
XLSX.writeFile(workBook,path.resolve(filePath));
return true;
}
exportSheetToExcel(data,workSheetColumnNames,sheetName,filePath);
Key differences:
- var rows = [];
- rows.push(value)
- const workSheetData = [workSheetColumnNames,...rows];
- uniform process of ['sub_categories','categories', 'other_reports']
workSheetData
should be like [[colNameA, colNameB],[cell1, cell2],...], in buggy code it's like [[colNameA, colNameB], cell1, cell2] because value
is overwritten every time new data is matched.
Good luck!
---Edit 1---
Add counter:
const XLSX = require('xlsx');
const path = require('path');
const data = require('./related_reports.json');
const workSheetColumnNames = ['Report_Title','Appearance','slug'];
const sheetName = 'Reports';
const filePath = './report.xlsx';
console.log(data.length);
var rows = [];
const exportSheetToExcel = (data,workSheetColumnNames,sheetName,filePath) => {
for(var i=0;i<data.length;i++) {
let count = 0
for(var j=0;j<data.length;j++) {
['sub_categories','categories', 'other_reports'].forEach(key=>{
var subTree = data[j].related_reports_updated[key];
for(var k=0;k<subTree.length;k++) {
if(subTree[k].short_title.includes(data[i].short_title)){
count ++;
var value = [data[i].short_title,count,subTree[k].short_title];
rows.push(value)
}
}
})
}
}
const workSheetData = [workSheetColumnNames,...rows];
const workBook = XLSX.utils.book_new();
const workSheet = XLSX.utils.aoa_to_sheet(workSheetData);
XLSX.utils.book_append_sheet(workBook,workSheet,sheetName);
XLSX.writeFile(workBook,path.resolve(filePath));
return true;
}
exportSheetToExcel(data,workSheetColumnNames,sheetName,filePath);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论