从 Kendo Grid 导出到 Excel 后，DateTime 列中的 28 秒差异 [英] 28 seconds difference in DateTime column after exporting from Kendo Grid to Excel

问题描述

我们的 Web 应用程序中有一些网格，我们将 Kendo 用于 ASP.Net MVC.一些客户报告说，当他们将网格数据导出到 Excel 时，日期不同.

We have a few grids in our web application and we use Kendo for ASP.Net MVC. Some clients are reporting that dates are different when they export the grid data to Excel.

例如，第一行来自控制器 { "SaleDate": "2018-05-30T00:00:00", "SaleDateAndTime": "2018-05-30T08:01:40.673"}.导出到excel后，对应的单元格值分别为:05/29/2018 23:59:32和05/30/2018 08:01:12.

As an example, the first row comes from the controller as { "SaleDate": "2018-05-30T00:00:00", "SaleDateAndTime": "2018-05-30T08:01:40.673" }. After exporting to excel, the corresponding cell values are: 05/29/2018 23:59:32 and 05/30/2018 08:01:12, respectively.

• 我尝试导出具有不同时间值的日期，导出到 excel 后的差异始终为 28 秒.
• 这仅在某些客户端上发生(时区与服务器相同并且设置正确).
• 这仅在使用 Chrome 浏览器时发生.

有人遇到过这个问题吗?我打开了 Telerik 的票，但他们帮不了我.

Has anyone faced this issue? I oppened a ticket to Telerik but they couldn't help me.

推荐答案

对于任何浏览器，我也遇到了同样的问题.就我而言，差异是 6 小时.我没有找到合适的解决方案.我通过在导出到 excel 时将 DateTime 转换为 ISO 日期字符串来解决它.

I also faced the same issue for any browser. In my case, the difference was 6 hours. I didn't find a proper solution. I solved it by converting DateTime to ISO date string while exporting to excel.

希望这能解决您的问题.

I hope this will solve your issue.

@(Html.Kendo().Grid<DataModel>()    
    .Name("grid")
    .ToolBar(tools => tools.Excel())
    .Events(e => e.ExcelExport("excelExport"))
    /* Other configuration. */
)

function excelExport(e) {
    var rows = e.workbook.sheets[0].rows;
    for (var ri = 0; ri < rows.length; ri++) {
        var row = rows[ri];
        for (var ci = 0; ci < row.cells.length; ci++) {
            var cell = row.cells[ci];
            if (row.type === "data" && isValidDate(cell.value)) {
                var dateTime = new Date(Date.parse(cell.value))
                if (dateTime != "Invalid Date") {
                    cell.value = ExcelISODateString(dateTime)
                    cell.format = "MM-dd-yyyy hh:mm:ss"
                }
            }
        }
    }
}
 
function ExcelISODateString(datetime) {
    ISODate= "";
    if (datetime != null || datetime != undefined) {
        ISODate= new Date(datetime.getTime() + (datetime.getTimezoneOffset() * 60000));
    }
    return ISODate;
}

//To check whether the cell value is datetime or not
function isValidDate(d) {
    return d instanceof Date && !isNaN(d);
}

这篇关于从 Kendo Grid 导出到 Excel 后，DateTime 列中的 28 秒差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！