Kendo电子表格在解析行值时将时间转换为十进制 [英] Kendo Spreadsheet Convert time to decimal while parsing the row values

问题描述

我试图用js保存kendo电子表格数据.在解析行值时，我看到kendo电子表格将日期转换为数字，将时间转换为十进制.我已使用moment js将数字转换为日期，但无法将十进制数字转换为确切时间.如何转换它，或者如果我可以得到电子表格中显示的日期和时间，则意味着只有字符串，那将是很好的.下面是示例代码和屏幕截图.

I was trying to save the kendo spreadsheet data with js. while parsing the row value I saw the kendo spreadsheet convert the date to numbers and time to decimal. I've converted the number to date using moment js but can't convert the decimal number to the exact time. how to convert that or it would be nice if I can get the date and time as shown in the spreadsheet meant only string. below is the sample code and screenshot.

var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
    var spreadsheedata = spreadsheet.toJSON();
    var JsonArray = [];
        for (var i = 1; i < spreadsheedata.sheets[0].rows.length; i++) {
                var dataObject = {};
                dataObject.SortOrder = spreadsheedata.sheets[0].rows[i].cells[0].value;
                dataObject.RoundNo = spreadsheedata.sheets[0].rows[i].cells[1].value;
                dataObject.Team = spreadsheedata.sheets[0].rows[i].cells[2].value;
                dataObject.MatchDate = moment(new Date((new Date('01/01/1900')).setDate(spreadsheedata.sheets[0].rows[i].cells[3].value - 1))).format(spreadsheedata.sheets[0].rows[i].cells[3].format.replace("dd", "DD").replace("mm", "MM"));
                dataObject.StartTime = spreadsheedata.sheets[0].rows[i].cells[4].value;
                dataObject.EndTime = spreadsheedata.sheets[0].rows[i].cells[5].value;
                dataObject.Location = spreadsheedata.sheets[0].rows[i].cells[6].value;
                dataObject.Field = spreadsheedata.sheets[0].rows[i].cells[7].value;
                JsonArray.push(dataObject);
    }

查看单元格编号格式和值

see the cell number format and value

这是日期和时间字段

推荐答案

使用功能ConvertExcelTime和momentjs转换了StartTime和EndTime.

Converted the StartTime and EndTime using the function ConvertExcelTime and momentjs.

var spreadsheet = $("#spreadsheet").data("kendoSpreadsheet");
var spreadsheedata = spreadsheet.toJSON();
var JsonArray = [];
    for (var i = 1; i < spreadsheedata.sheets[0].rows.length; i++) {
            var dataObject = {};
            dataObject.SortOrder = spreadsheedata.sheets[0].rows[i].cells[0].value;
            dataObject.RoundNo = spreadsheedata.sheets[0].rows[i].cells[1].value;
            dataObject.Team = spreadsheedata.sheets[0].rows[i].cells[2].value;
            dataObject.MatchDate = moment(new Date((new Date('01/01/1900')).setDate(spreadsheedata.sheets[0].rows[i].cells[3].value - 1))).format(spreadsheedata.sheets[0].rows[i].cells[3].format.replace("dd", "DD").replace("mm", "MM").replace("-", "/").replace("-", "/"));
            dataObject.StartTime = moment(new Date(OrgTrainingCalendar.ConvertExcelTime(spreadsheedata.sheets[0].rows[i].cells[4].value))).format(spreadsheedata.sheets[0].rows[i].cells[4].format.replace("AM/PM", "a"));
            dataObject.EndTime = moment(new Date(OrgTrainingCalendar.ConvertExcelTime(spreadsheedata.sheets[0].rows[i].cells[5].value))).format(spreadsheedata.sheets[0].rows[i].cells[5].format.replace("AM/PM", "a"));
            dataObject.Location = spreadsheedata.sheets[0].rows[i].cells[6].value;
            dataObject.Field = spreadsheedata.sheets[0].rows[i].cells[7].value;
            JsonArray.push(dataObject);
}
ConvertExcelTime: function (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;
}

这篇关于Kendo电子表格在解析行值时将时间转换为十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！