Skip to content
Advertisement

Kendo Spreadsheet Convert time to decimal while parsing the row values

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 see the cell format

enter image description here here are the date and time fields

Advertisement

Answer

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;
}
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement