前端讀取excel 文件夷磕，excel內(nèi)容展示在頁(yè)面上撮慨，用戶(hù)確認(rèn)無(wú)誤后再上傳至后端纹腌，使用的XLSX來(lái)解析excel,讀取日期的時(shí)候是數(shù)字肖抱。

//安裝：
npm install xlsx;//https://www.npmjs.com/package/xlsx

//使用：
import XLSX from "xlsx";

var xhr = new XMLHttpRequest();
xhr.open("get", "http://****/file?url=http://***/***.xlsx");
xhr.responseType = "arraybuffer";
xhr.onload =  (e)=> {
    if (xhr.status === 200) {
        var data = new Uint8Array(xhr.response);
        //cellDates為true轉(zhuǎn)化出來(lái)的日期會(huì)出現(xiàn)多一天，所以用下面的轉(zhuǎn)換函數(shù)
        var workbook = XLSX.read(data, { type: "array",cellDates: false});
        var sheetNames = workbook.SheetNames; // 工作表名稱(chēng)集合["阿里云", "騰訊云", "華為云", "金山云", "百度云", "云碼A", "云碼B"]
        var worksheet = workbook.Sheets[sheetNames[0]];//名稱(chēng)1：阿里云
        var tableDataJson = XLSX.utils.sheet_to_json(worksheet);//將excel的數(shù)據(jù)轉(zhuǎn)成JSON數(shù)據(jù)
    }
};
xhr.send();

excel數(shù)據(jù)解析出阿里云JSON數(shù)據(jù)如下：（tableDataJson）

[
    {
        "賬號(hào)ID": "136阿里云",
        "訂單號(hào)": 136,
        "關(guān)聯(lián)類(lèi)型": "新建",
        "訂單類(lèi)型": "新購(gòu)",
        "支付時(shí)間": 44358,//在excel設(shè)置的日期是：2021/6/11
        "訂單金額": 1,
        "收益": 1,
        "無(wú)收益原因": "阿里云"
    },
    {
        "賬號(hào)ID": "136阿里云",
        "訂單號(hào)": 1366,
        "關(guān)聯(lián)類(lèi)型": "新建",
        "訂單類(lèi)型": "新購(gòu)",
        "支付時(shí)間": 44358.25,//在excel設(shè)置的日期是：2021/6/11 6:00:00
        "訂單金額": 1,
        "收益": 1
    },
    {
        "賬號(hào)ID": "136阿里云",
        "訂單號(hào)": 13660,
        "關(guān)聯(lián)類(lèi)型": "新建",
        "訂單類(lèi)型": "新購(gòu)",
        "支付時(shí)間": 44358.685,//在excel設(shè)置的日期是：2021/6/11 16:26:24
        "訂單金額": 1,
        "收益": 1
    },
    {
        "賬號(hào)ID": "136阿里云",
        "訂單號(hào)": 136607,
        "關(guān)聯(lián)類(lèi)型": "新建",
        "訂單類(lèi)型": "新購(gòu)",
        "支付時(shí)間": 44358.9999884259,//在excel設(shè)置的日期是：2021/6/11 23:59:59
        "訂單金額": 1,
        "收益": 1
    }
]

1623308155(1).jpg

//將excel的日期格式轉(zhuǎn)成Date()對(duì)象;
function getFormatDate_XLSX(serial) {
    var utc_days = Math.floor(serial - 25569);
    var utc_value = utc_days * 86400;
    var date_info = new Date(utc_value * 1000);
    var fractional_day = serial - Math.floor(serial) + 0.0000001;
    var total_seconds = Math.floor(86400 * fractional_day);
    var seconds = total_seconds % 60;
    total_seconds -= seconds;
    var hours = Math.floor(total_seconds / (60 * 60));
    var minutes = Math.floor(total_seconds / 60) % 60;
    var d = new Date(date_info.getFullYear(), date_info.getMonth(), date_info.getDate(), hours, minutes, seconds);
    /*//得到Date()對(duì)象后瓦侮，便可進(jìn)行日期格式化了艰赞！
    var add0 = (m) => m < 10 ? '0' + m : m;
    var d = getFormatDate_XLSX(44358.9999884259);
    var YYYY = d.getFullYear();
    var MM = add0(d.getMonth() + 1);
    var DD = add0(d.getDate());
    var hh = add0(d.getHours());
    var mm = add0(d.getMinutes());
    var ss = add0(d.getSeconds());
    return `${YYYY}-${MM}-${DD} ${hh}:${mm}:${ss}`;
    */
    return d;
}
//將excel的【支付時(shí)間】轉(zhuǎn)成Date()對(duì)象
console.log(getFormatDate_XLSX(44358));//輸出是【Fri Jun 11 2021 00:00:00 GMT+0800 (中國(guó)標(biāo)準(zhǔn)時(shí)間)】
console.log(getFormatDate_XLSX(44358.25));//輸出是【Fri Jun 11 2021 06:00:00 GMT+0800 (中國(guó)標(biāo)準(zhǔn)時(shí)間)】
console.log(getFormatDate_XLSX(44358.685));//輸出是【Fri Jun 11 2021 16:26:24 GMT+0800 (中國(guó)標(biāo)準(zhǔn)時(shí)間)】
console.log(getFormatDate_XLSX(44358.9999884259));//輸出是【Fri Jun 11 2021 23:59:59 GMT+0800 (中國(guó)標(biāo)準(zhǔn)時(shí)間)】