Kendo UI Grid-具有隐藏列和自定义格式的Excel导出 [英] Kendo UI Grid - Excel Export with hidden columns and custom formatting

问题描述

我尝试使用Grid组件的内置支持导出到excel，并应用以下Telerik文档中所示的自定义单元格格式:

I'm attempting to use the Grid component's built-in support for exporting to excel, applying custom cell formatting as shown in these Telerik docs:

http://docs.telerik.com/kendo-ui/controls/data-management/grid/how-to/excel/cell-format

导出时使用硬编码的行/单元格索引的方法在导出显示有先前隐藏列的网格时会出现一个非常明显的问题-最佳的再现方法是参考以下jsfiddle:

The approach using hard-coded row / cell indexes in the export comes with a rather obvious issue when exporting a grid with a prior hidden column displayed - best way to reproduce is to refer to this jsfiddle:

https://jsfiddle.net/3anqpnqt/1/

1. 运行小提琴
2. 点击导出到excel-注意自定义数字格式
3. 取消隐藏子类别列(使用列菜单)
4. 点击导出到excel-注意第2列的自定义数字格式，该列现在为子类别"

在小提琴中参考此代码:

With reference to this code in the fiddle:

$("#grid").kendoGrid({
    toolbar: ["excel"],
    excel: {
      fileName: "Grid.xlsx",
      filterable: true
    },
    columns: [
      { field: "productName" },
      { field: "category" },
      { field: "subcategory", hidden: true },
      { field: "unitPrice"}
    ],
    dataSource: [
      { productName: "Tea", category: "Beverages", subcategory: "Bev1", unitPrice: 1.5 },
      { productName: "Coffee", category: "Beverages", subcategory: "Bev2", unitPrice: 5.332 },
      { productName: "Ham", category: "Food", subcategory: "Food1", unitPrice: -2.3455 },
      { productName: "Bread", category: "Food", subcategory: "Food2", unitPrice: 6 }
    ],
    columnMenu: true,
    excelExport: function(e) {      
      var sheet = e.workbook.sheets[0];

      for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
        var row = sheet.rows[rowIndex];
        var numericFormat = "#,##0.00;[Red](#,##0.00);-";
        for (var cellIndex = 0; cellIndex < row.cells.length; cellIndex++) {
            var cell = row.cells[cellIndex];
            if (row.type === "data") {
                if (cellIndex == 2) { // how are we able to identify the column without using indexes?
                    cell.format = numericFormat;
                    cell.hAlign = "right";
                }
            }
        }      
      }      
    }
});

我需要做的是将单元格标识为'unitPrice'并应用格式，但是检查excelExport处理程序中的对象模型并不能给我任何方法进行此链接.在我的实际应用程序中，我有几种自定义格式可以应用(百分比，n0，n2等)，因此它不像按$.isNumeric(cell.value)或其他简单方法那样简单.

What I need to be able to do is identify the cell as the 'unitPrice' and apply the format, but inspection of the object model within the excelExport handler doesn't give me any way to make this link. In my real application, I have several custom formats to apply (percentages, n0, n2 etc) so it's not as simple as going $.isNumeric(cell.value) or otherwise.

更新

我还需要使用解决方案来处理列/行组，这会在Excel模型中生成其他标题行/列.

I also need the solution to work with column / row groups, which generate additional header rows / columns in the Excel model.

推荐答案

看起来row [0]是标题行，因此您可以尝试更改

It looks like row[0] is the header row, so you could try changing

if (cellIndex == 2) {

到

if (sheet.rows[0].cells[cellIndex].value == "unitPrice") { 

似乎可以使用列组: https://jsfiddle.net/dwosrs0x/

更新:

工作表的对象模型不是最清楚的.在我研究过的各种情况下，第一行似乎确实是主"标题行.如果unitPrice不在分组中，那么这似乎是可行的.如果unitPrice在分组中，则可能会涉及到组头(row [1])更复杂的事情.难题在于找出所需的列最终将占据什么位置.

The object model for worksheet is not the most clear. The first row does seem to be a "master" header row in the various scenarios that I looked at. Here is something that seems to work if unitPrice is not in a grouping. If unitPrice is in a grouping, then something more complicated involving the group header (row[1]) might be possible. The puzzle is to find out what position the desired column will eventually occupy.

var header = sheet.rows[0];
var upIndex = -1;
var upFound = false;

for (var cellIndex = 0; cellIndex < header.cells.length; cellIndex++) {

    if ('colSpan' in header.cells[cellIndex]) 
        upIndex = upIndex + header.cells[cellIndex].colSpan;
    else 
        upIndex = upIndex + 1;

    if (header.cells[cellIndex].value == "unitPrice") { // wot we want
        upFound = true;
        break;
    }
}

for (var rowIndex = 0; rowIndex < sheet.rows.length; rowIndex++) {
    var row = sheet.rows[rowIndex];
    if (row.type === "data" && upFound) {
        var cell = row.cells[upIndex];
        cell.format = numericFormat;
        cell.hAlign = "right";
    }

}

与组一起演奏- https://jsfiddle.net/dwosrs0x/4/

使用简单的网格进行摆弄(以证明它仍然有效)- https://jsfiddle.net/gde4nr0y/1 /

fiddle with straightforward grid (to prove it still works) - https://jsfiddle.net/gde4nr0y/1/

这肯定带有僵硬"的气息.

This definitely has the whiff of "bodge" about it.

这篇关于Kendo UI Grid-具有隐藏列和自定义格式的Excel导出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！