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

我有一个大的JSON文件，其数据结构如下：

[ 
 {
    "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
            }
        ]
     }
   },
 ]

所以这里我必须比较每个id的short_title和related_reports_updated字段中可用数组的sort_title。我在这个字段中有3个数组sub_categories，categories和other_reports。如果它与那些相似，那么我想将那些short_title添加到excel文件中。
下面是我的代码：

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);

但处理了一段时间后它抛出错误说

Error: aoa_to_sheet expects an array of arrays

有人让我知道如何才能得到想要的结果。

试试这个：

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);

主要差异：

变量行数=[];
行. push（值）
常量工作表数据=[工作表列名，...行];
["子类别"、"类别"、"其他报告"]的统一过程

workSheetData应该类似于[[colNameA，colNameB]，[cell1，cell2]，...]，在错误代码中，它类似于[[colNameA，colNameB]，cell1，cell2]，因为每次匹配新数据时，value都会被覆盖。
祝你好运!

--编辑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) => {
    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);

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

1条答案

相关问题

热门标签

最新问答