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

user_17561878 0
Viewed 203

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

xlsx javascript
2 Answers

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 })
user_48736160

You can give this a go too!

// This part converts your original object into an array
let data_arr = [...input].reduce((acc, val) => {
    acc.push(Object.values(val))
    return acc
}, [])

// The array is feed here
hdl.addEventListener('click', function() {
    var wb = XLSX.utils.book_new();

    wb.Props = {
        Title: window.sheet_title,
        Subject: "Sheet Subject",
        Author: "Name of author",
        CreatedDate: new Date(window.page_time)
    };

    wb.SheetNames.push("Sheet Subject");

    var ws = XLSX.utils.aoa_to_sheet(data_arr);
    wb.Sheets["Sheet Subject"] = ws;

    var wbout = XLSX.write(wb, {
        bookType: 'xlsx',
        type: 'binary'
    });

    function s2ab(s) {

        var buf = new ArrayBuffer(s.length);
        var view = new Uint8Array(buf);
        for (var i = 0; i < s.length; i++) view[i] = s.charCodeAt(i) & 0xFF;
        return buf;

    }

    saveAs(new Blob([s2ab(wbout)], {
        type: "application/octet-stream"
    }), `${window.sheet_title}.xlsx`);
})
user_175406640
Related