如何在NodeJs中使用XLSX库将JSON数据转换为Excel文件

huangapple go评论82阅读模式
英文:

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:

[ 
{
&quot;slug&quot;: &quot;vertical-lift-module-market&quot;,
&quot;id&quot;: 68055,
&quot;short_title&quot;: &quot;Vertical Lift Module (VLM) Market&quot;,
&quot;related_reports_updated&quot;: {
&quot;sub_categories&quot;: [
{
&quot;slug&quot;: &quot;audience-analytics-market&quot;,
&quot;id&quot;: 66684,
&quot;short_title&quot;: &quot;Audience Analytics Market&quot;
},
{
&quot;slug&quot;: &quot;mobile-wallet-market&quot;,
&quot;id&quot;: 68830,
&quot;short_title&quot;: &quot;Mobile Wallet Market&quot;
},
{
&quot;id&quot;: 46625,
&quot;slug&quot;: &quot;north-america-3d-4d-technology-market&quot;,
&quot;short_title&quot;: &quot;NA 3D 4D Technology Market&quot;
},
{
&quot;short_title&quot;: &quot;North America Chatbot Market&quot;,
&quot;slug&quot;: &quot;north-america-chatbot-market&quot;,
&quot;id&quot;: 72309
},
{
&quot;short_title&quot;: &quot;Optical Wavelength Services Market&quot;,
&quot;slug&quot;: &quot;optical-wavelength-services-market&quot;,
&quot;id&quot;: 71348
}
],
&quot;categories&quot;: [
{
&quot;id&quot;: 48402,
&quot;slug&quot;: &quot;artificial-intelligence-impact-and-future-in-modern-warfare&quot;,
&quot;short_title&quot;: &quot;AI in Modern Warfare Market&quot;
},
{
&quot;short_title&quot;: &quot;Certificate Authority Market&quot;,
&quot;slug&quot;: &quot;certificate-authority-market&quot;,
&quot;id&quot;: 70769
},
{
&quot;short_title&quot;: &quot;Global Mobile Identification Market&quot;,
&quot;slug&quot;: &quot;global-mobile-identification-market&quot;,
&quot;id&quot;: 91316
},
{
&quot;slug&quot;: &quot;identity-verification-market&quot;,
&quot;id&quot;: 69133,
&quot;short_title&quot;: &quot;Identity Verification Market&quot;
},
{
&quot;slug&quot;: &quot;signature-verification-market&quot;,
&quot;id&quot;: 59014,
&quot;short_title&quot;: &quot;Signature Verification Market&quot;
}
],
&quot;other_reports&quot;: [
{
&quot;id&quot;: 48102,
&quot;slug&quot;: &quot;global-artificial-lift-systems-market-industry&quot;,
&quot;short_title&quot;: &quot;Artificial Lift System Market&quot;
},
{
&quot;id&quot;: 51724,
&quot;slug&quot;: &quot;latin-america-artificial-lift-systems-market-industry&quot;,
&quot;short_title&quot;: &quot;Latin America Artificial Lift System Market&quot;
},
{
&quot;id&quot;: 55702,
&quot;slug&quot;: &quot;medical-lifting-sling-market&quot;,
&quot;short_title&quot;: &quot;Medical Lifting Slings Market&quot;
},
{
&quot;id&quot;: 52020,
&quot;slug&quot;: &quot;north-america-artificial-lift-systems-market-industry&quot;,
&quot;short_title&quot;: &quot;North America Artificial Lift Systems Market&quot;
},
{
&quot;slug&quot;: &quot;vertical-farming-market&quot;,
&quot;id&quot;: 61898,
&quot;short_title&quot;: &quot;Vertical Farming Market&quot;
}
]
}
},
{
&quot;slug&quot;: &quot;united-states-real-estate-services---growth-trends-and-forecast-2022---2027&quot;,
&quot;id&quot;: 68056,
&quot;short_title&quot;: &quot;United States Real Estate Services Market&quot;,
&quot;related_reports_updated&quot;: {
&quot;sub_categories&quot;: [
{
&quot;slug&quot;: &quot;canada-real-estate-services-market---growth-trends-and-forecast-2020---2025&quot;,
&quot;id&quot;: 68051,
&quot;short_title&quot;: &quot;Canada Real Estate Services Market&quot;
},
{
&quot;slug&quot;: &quot;germany-real-estate-services-market--growth-trends-and-forecast-2020---2025&quot;,
&quot;id&quot;: 68054,
&quot;short_title&quot;: &quot;Germany Real Estate Services Market&quot;
},
{
&quot;short_title&quot;: &quot;Office Real Estate Market&quot;,
&quot;slug&quot;: &quot;office-real-estate-market&quot;,
&quot;id&quot;: 80022
},
{
&quot;slug&quot;: &quot;uk-real-estate-services-market---growth-trends-and-forecast-2020---2025&quot;,
&quot;id&quot;: 68057,
&quot;short_title&quot;: &quot;United Kingdom Real Estate Services Market&quot;
},
{
&quot;short_title&quot;: &quot;United States Senior Living Market &quot;,
&quot;slug&quot;: &quot;united-states-senior-living-market&quot;,
&quot;id&quot;: 72583
}
],
&quot;categories&quot;: [
{
&quot;slug&quot;: &quot;uae-real-estate-market-services&quot;,
&quot;id&quot;: 68040,
&quot;short_title&quot;: &quot;United Arab Emirates Real Estate Services Market&quot;
},
{
&quot;id&quot;: 46257,
&quot;slug&quot;: &quot;residential-real-estate-market-in-uae&quot;,
&quot;short_title&quot;: &quot;United Arab Emirates Residential Real Estate Market&quot;
},
{
&quot;id&quot;: 54710,
&quot;slug&quot;: &quot;commercial-real-estate-market-in-usa&quot;,
&quot;short_title&quot;: &quot;United States Commercial Real Estate Market&quot;
},
{
&quot;short_title&quot;: &quot;United States Luxury Residential Real Estate Market&quot;,
&quot;slug&quot;: &quot;united-states-luxury-residential-real-estate-market&quot;,
&quot;id&quot;: 90838
},
{
&quot;short_title&quot;: &quot;United States Office Real Estate Market&quot;,
&quot;slug&quot;: &quot;united-states-office-real-estate-market&quot;,
&quot;id&quot;: 72479
}
],
&quot;other_reports&quot;: [
{
&quot;short_title&quot;: &quot;United States (US) MEP Services Market&quot;,
&quot;slug&quot;: &quot;united-states-mep-services-market&quot;,
&quot;id&quot;: 71420
},
{
&quot;slug&quot;: &quot;united-states-hvac-services-market&quot;,
&quot;id&quot;: 67903,
&quot;short_title&quot;: &quot;US HVAC Services Market&quot;
},
{
&quot;short_title&quot;: &quot;United States IT Services Market &quot;,
&quot;slug&quot;: &quot;united-states-it-services-market&quot;,
&quot;id&quot;: 91565
},
{
&quot;short_title&quot;: &quot;United States Managed Services Market&quot;,
&quot;slug&quot;: &quot;united-states-managed-services-market&quot;,
&quot;id&quot;: 71366
},
{
&quot;short_title&quot;: &quot;United States Pet Care and Services Market&quot;,
&quot;slug&quot;: &quot;united-states-pet-care-and-services-market&quot;,
&quot;id&quot;: 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(&#39;xlsx&#39;);
const path = require(&#39;path&#39;);
const data = require(&#39;./related_reports.json&#39;);
const workSheetColumnNames = [&#39;Report_Title&#39;,&#39;Appearance&#39;,&#39;slug&#39;];
const sheetName = &#39;Reports&#39;;
const filePath = &#39;./report.xlsx&#39;;
console.log(data.length);
var value = [];
const exportSheetToExcel = (data,workSheetColumnNames,sheetName,filePath) =&gt; {
for(var i=0;i&lt;data.length;i++) {
for(var j=0;j&lt;data.length;j++) {
for(var k=0;k&lt;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&lt;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&lt;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(&#39;xlsx&#39;);
const path = require(&#39;path&#39;);
const data = require(&#39;./related_reports.json&#39;);
const workSheetColumnNames = [&#39;Report_Title&#39;,&#39;Appearance&#39;,&#39;slug&#39;];
const sheetName = &#39;Reports&#39;;
const filePath = &#39;./report.xlsx&#39;;
console.log(data.length);
var rows = [];
const exportSheetToExcel = (data,workSheetColumnNames,sheetName,filePath) =&gt; {
var value = [];
for(var i=0;i&lt;data.length;i++) {
for(var j=0;j&lt;data.length;j++) {
[&#39;sub_categories&#39;,&#39;categories&#39;, &#39;other_reports&#39;].forEach(key=&gt;{
var subTree = data[j].related_reports_updated[key];
for(var k=0;k&lt;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(&#39;xlsx&#39;);
const path = require(&#39;path&#39;);
const data = require(&#39;./related_reports.json&#39;);
const workSheetColumnNames = [&#39;Report_Title&#39;,&#39;Appearance&#39;,&#39;slug&#39;];
const sheetName = &#39;Reports&#39;;
const filePath = &#39;./report.xlsx&#39;;
console.log(data.length);
var rows = [];
const exportSheetToExcel = (data,workSheetColumnNames,sheetName,filePath) =&gt; {
for(var i=0;i&lt;data.length;i++) {
let count = 0
for(var j=0;j&lt;data.length;j++) {
[&#39;sub_categories&#39;,&#39;categories&#39;, &#39;other_reports&#39;].forEach(key=&gt;{
var subTree = data[j].related_reports_updated[key];
for(var k=0;k&lt;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);

huangapple
  • 本文由 发表于 2023年1月5日 15:05:23
  • 转载请务必保留本文链接:https://go.coder-hub.com/75014871.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定