将Excel时间转换为moment.js [英] Converting Excel Time to moment.js

问题描述

我有一个Electron应用程序，其中需要导入带有两列包含时间值的ExcelSheet.在我的应用程序中，这些值被循环转换为momentjs对象以进行进一步处理:

I have an Electron app where an Excel-Sheet with a couple of columns containing time values needs to be imported. In my app those values are converted in a loop to momentjs object for further manipulation:

x['Time'] = moment(x['Time'], ['HH:mm','HH:mm:ss']).format('HH:mm:ss');

只要Excel包含格式化为文本的时间值，此方法就可以正常工作.但是，如果按原本的方式设置Excel，则Cell的值就是介于0和1之间的数字(Excel在内部将时间视为浮点数-因此，例如0,5转换为12:00:00).

This works fine as long the Excel contains time values formatted as text. But if the Excel is set up the way it's meant to be, then the value of the Cell is a Number between 0 and 1 (Excel counts time internally as floating point - so e.g. 0,5 translates to 12:00:00).

有人知道我如何将其转换回momentjs可读的Timevalue吗?

Does anyone know how I can translate that back to a readable Timevalue for momentjs?

推荐答案

export const parseDateExcel = (excelTimestamp) => {
    const secondsInDay = 24 * 60 * 60;
    const excelEpoch = new Date(1899, 11, 31);
    const excelEpochAsUnixTimestamp = excelEpoch.getTime();
    const missingLeapYearDay = secondsInDay * 1000;
    const delta = excelEpochAsUnixTimestamp - missingLeapYearDay;
    const excelTimestampAsUnixTimestamp = excelTimestamp * secondsInDay * 1000;
    const parsed = excelTimestampAsUnixTimestamp + delta;
    return isNaN(parsed) ? null : parsed;
};

用法:

new Date(parseDateExcel(36902.49097)) //=> Thu Jan 11 2001 11:46:59 GMT+0000 (Greenwich Mean Time)

来源

这篇关于将Excel时间转换为moment.js的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！