Search code examples
javascriptxlsx

how to order json data header while downloading it into xls format using JavaScript

I have a Json data in which I am generating dynamic keys which is having fiscal year quarter and respective values,I need to download the data into xls format which I am successfully able to do, but the problem is when I download the data the order of the xls header is not same as my json keys.Below is my sample data.

var input = [
    {
        "FPH Level 1": "iphone",
        "Geo Level 2": "Austria",
        "Geo Level 7": "DACH",
        "RTM": "Retail",
        "Account": "Austria-epos",
        "FY202004": "20%",
        "FY202101": "20%",
        "FY202102": "20%",
        "FY202103": "20%",
        "FY202104": "20%",
        "Y/Y pt Change": "5%",
        "Commentary Y/Y": "TESTING",
        "Q/Q pt Change": "4%",
        "Commentary Q/Q": "TESTING"
    },
    {
        "FPH Level 1": "iphone",
        "Geo Level 2": "Austria",
        "Geo Level 7": "DACH",
        "RTM": "Retail",
        "Account": "Austria-epos",
        "FY202004": "20%",
        "FY202101": "20%",
        "FY202102": "20%",
        "FY202103": "20%",
        "FY202104": "20%",
        "Y/Y pt Change": "5%",
        "Commentary Y/Y": "TESTING",
        "Q/Q pt Change": "4%",
        "Commentary Q/Q": "TESTING"
    },
    {
        "FPH Level 1": "iphone",
        "Geo Level 2": "Austria",
        "Geo Level 7": "DACH",
        "RTM": "Retail",
        "Account": "Austria-epos",
        "FY202004": "20%",
        "FY202101": "20%",
        "FY202102": "20%",
        "FY202103": "20%",
        "FY202104": "20%",
        "Y/Y pt Change": "5%",
        "Commentary Y/Y": "TESTING",
        "Q/Q pt Change": "4%",
        "Commentary Q/Q": "TESTING"
    },
    {
        "FPH Level 1": "iphone",
        "Geo Level 2": "Austria",
        "Geo Level 7": "DACH",
        "RTM": "Retail",
        "Account": "Austria-epos",
        "FY202004": "20%",
        "FY202101": "20%",
        "FY202102": "20%",
        "FY202103": "20%",
        "FY202104": "20%",
        "Y/Y pt Change": "5%",
        "Commentary Y/Y": "TESTING",
        "Q/Q pt Change": "4%",
        "Commentary Q/Q": "TESTING"
    },
    {
        "FPH Level 1": "iphone",
        "Geo Level 2": "Austria",
        "Geo Level 7": "DACH",
        "RTM": "Retail",
        "Account": "Austria-epos",
        "FY202004": "20%",
        "FY202101": "20%",
        "FY202102": "20%",
        "FY202103": "20%",
        "FY202104": "20%",
        "Y/Y pt Change": "5%",
        "Commentary Y/Y": "TESTING",
        "Q/Q pt Change": "4%",
        "Commentary Q/Q": "TESTING"
    },
]

here to snipped code I am working to download the data

const xlsData = input
        const ws = XLSX.utils.json_to_sheet(xlsData);
        const wb = { Sheets: { 'data': ws }, SheetNames: ['data'] };
        const excelBuffer = XLSX.write(wb, { bookType: 'xlsx', type: 'array' });
        const data = new Blob([excelBuffer], { type: fileType });
        let fileName = `test`
        FileSaver.saveAs(data, fileName + fileExtension);

result after the converted it into xls the header are like this enter image description here I am excepting the output the be enter image description here

Solution

  • In your code snippet change the second line to:

    const header = ["FPH Level 1", "Geo Level 2", "Geo Level 7", "RTM", "Account"]
const fy = Object.keys(input[0]).filter(s => s.startsWith("FY")).sort()
header.push(...fy)
header.push("Y/Y pt Change", "Commentary Y/Y", "Q/Q pt Change", "Commentary Q/Q")
const ws = XLSX.utils.json_to_sheet(xlsData, { header })