解决从excel导入\导出日期格式数据时的问题

本文链接：https://blog.youkuaiyun.com/lionheart922/article/details/108535393

在Excel中，日期常被转化为数字格式，表示从1900年1月1日开始的天数。导入时需注意该数字对应格林威治时间，导出时同样考虑时区差异。要正确处理时区转换，确保日期数据的准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

从excel 导入日期会自动转换成一个数字(number格式）这个数字是减掉1900年1月1日的天数后得到的

例如 25569就是 1970-01-01

 public import(target: DataTransfer): Observable<SearchResult> {
        const subject = new ReplaySubject<SearchResult>();
        const reader: FileReader = new FileReader();
        const result: SearchResult = {
            isSuccessful: true,
            errMessage: '',
            records: []
        };
        reader.onload = (e: any) => {
            try {
                /* read workbook */
                const bstr: string = e.target.result;
                const wb: XLSX.WorkBook = XLSX.read(bstr, { type: 'binary' });
                /* grab first sheet */
                const wsname: string = wb.SheetNames[0];
                const ws: XLSX.WorkSheet = wb.Sheets[wsname];
                /* save data */
                this.trimExcelFormat(ws);
                const data: any[] = XLSX.utils.sheet_to_json(ws, { raw: true });
                result.records = this.convertExcelDataList(data);

            } catch (error) {
                result.isSuccessful = false;
                result.errMessage = error.message;
            }
            subject.next(result);
            subject.complete();
        };
        reader.readAsBinaryString(target.files[0]);
        return subject;
    }

无论你从哪个时区导入的日期，都会被认为是格林威治时间，所以在把数字转换成时间时需要转换成格林威治时间。

    protected convertDate(serial: number): Date {
        // 25569 is 1970-01-01
        // serial is the days - 1900-01-01;
        // UTC/GTM // the standard time.
        return serial ? DateTimes.parseISO
            (this.datePipe.transform((serial - 25569) * 24 * 60 * 60 * 1000, 'yyyy-MM-dd', 'UTC/GMT')) : undefined;
    }

导出到excel的时候，无论当前是哪个时区，都会被当成格林威治时间导出，所以需要先除去timezone difference

  private getTimezoneOffset(d: Date): Date {
        const date = new Date(d);
        // get the time zone difference, in minutes, from current locale (host system settings) to UTC
        const offset = date.getTimezoneOffset() * 60000;
        return new Date(date.getTime() - offset);
    }


    private convertDateFormat(data: any): any {
        const result = JSON.parse(JSON.stringify(data));
        result.xxxDate = DateTimes.filterInvalidDate(this.getTimezoneOffset(data.tradeDate));
        return result;
    }

    private exportXlsx(data: any[], headers: any[], fileName: string, necessaryCellId: string[]): void {
        const workbook = new ExcelJS.Workbook();
        const worksheet = workbook.addWorksheet(fileName);
        worksheet.columns = headers;
        worksheet.addRows(data);
        this.setExcelStyle(worksheet, necessaryCellId);
        workbook.xlsx.writeBuffer().then((items) => {
            const blob = new Blob([items], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });
            fs.saveAs(blob, this.formatFileName(fileName, 'xlsx'));
        });
    }