如何在使用JavaScript將json資料頭下載為xls格式時對其進行排序

我有一個 Json 資料，其中我正在生成具有財政年度季度和相應值的動態密鑰，我需要將資料下載為我能夠成功執行的 xls 格式，但問題是當我下載資料時xls 標頭的內容與我的 json 鍵不同。下面是我的示例資料。

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

在這里剪斷代碼我正在下載資料

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

將其轉換為 xls 后的結果標題是這樣的 我除了輸出是

uj5u.com熱心網友回復：

在您的代碼片段中，將第二行更改為：

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

uj5u.com熱心網友回復：

你也可以試試這個！

// 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`);
})

標籤：javascript xlsx

上一篇：遞回搜索回傳未定義

下一篇：會話存盤沒有給出實際價值