Excel Export - Foreign Key Columns and Detail Template Grids

[Brian Roth]

Hello,

I'm starting to play around with the new excel export functionality and have questions for a couple of different scenarios where I'm looking for some guidance on how to approach them. 

First scenario: We use the MVC foreign key columns pretty frequently in our grids.  We have a numeric identity column that then ties to another table which contains the display value for the column.  When I try the export the numeric identity value is what shows up in the resulting document.  Is there a way to tell the export to use the display value instead?

Second scenario: We have a grid that uses a detail template which contains a sub-grid.  Is there a way that I would be able to have the sub-grids included in the export?

Thanks in advance for your advice.

Regards,
Brian

[Richard]

Second that. Exporting display fields (rather than id fields) in foreign key columns is hugely important for us.
Similarly, I would also like to know if there is a way to export the ClientTemplate fields as well - for example something like: 
          columns.Bound(x => x.SSN.ClientTemplate("#=getDisplaySsn(data)#");
... where "getDisplaySsn" performs logic and returns "Unauthorized"  under certain circumstances.  This isn't my specific business case (I could work around it pretty easily in my viewmodel) but it illustrates what I'm trying to do in more complex scenarios.

[mark]

ditto, I "Third that". GridForeignKey template is utilized in sooooo many grid.  How do we get Export to Excel to chose the dataFieldText instead of the dataFieldValue?

[Atanas Korchev]

Hi guys,

Great feedback!

Foreign key columns

We will look into implementing this as a built-in feature. Until then here is how this can be done: http://dojo.telerik.com/@korchev/uHEla

Detail Template

Unfortunately we can't support this out of the box. The detail template can be arbitrary HTML which obviously doesn't translate well to Excel rows and columns. Here is a demo how this can be done manually: http://docs.telerik.com/kendo-ui/web/grid/how-to/Excel/detail-grid-export

Client Template

Client template isn't exported for the same reason as detail template. Fortunately you can easily implement that if your client templates don't contain HTML: http://docs.telerik.com/kendo-ui/web/grid/how-to/excel/column-template-export



Regards,
Atanas Korchev
Telerik

 

 

[Brian Roth]

Hi Atanas,

Thanks so much for your response!  I look forward to the foreign keys being a part of the core export functionality, but in the meantime your post was extremely helpful.  I was able to implement both the foreign key columns and the detail template in my solution.

Regards,
Brian

[Jimmy]

Hi Antanas,
Very interesting solution but how I can do this in MVC?

Regards,
Jimmy

[Atanas Korchev]

Hello Jimmy,

The approach is very similar because the solution needs to be done in JavaScript. You need to handle the ExcelExport, DetailInit events and use the same implementations.

@Html.Kendo().Grid().Events(e => e.ExcelExport("masterGridExcelExport"))

<script id="detail-template">
@Html.Kendo().Grid().Events(e => e.ExcelExport("detailGridExportGridExcelExport"))
</script>

<script>

function masterGridExcelExport(e) {
   // rest of imlementation
}

function detailGridExcelExport(e) {

}

</script>

Regards,
Atanas Korchev
Telerik

 

 

[Jimmy]

Hi Antanas,

Thanks for the quick response. I have the latest version (2014.3.1119.545) and can not find the event ExcelExport. In the line " }).Events(e => e.ExcelExport("masterGridExcelExport"))" the error message is "CS1061: 'Kendo.Mvc.UI.Fluent.GridEventBuilder' not contains a definition of 'ExcelExport'".

Regards,

Jimmy

[Atanas Korchev]

Hello Jimmy,

You are absolutely right! This event isn't exposed yet. We will do this ASAP and release it in the next internal build. I am very sorry for misleading you.

Regards,
Atanas Korchev
Telerik

 

 

[Jimmy]

Hi Atanas,

Please say me when release with this changes?

Regards,

Jimmy

[Jimmy]

Hi Atanas,

Please say me when release with this changes?

Regards,

Jimmy

[Atanas Korchev]

Hello Jimmy,

The latest internal build contains those changes. A sample is available in our documentation: http://docs.telerik.com/kendo-ui/aspnet-mvc/helpers/grid/excel-export#detail-template

Regards,
Atanas Korchev
Telerik

 

 

[Jimmy]

thank you very much Atanas, worked perfectly

[Aaron]

The solution provided for ForeignKey columns is limited and hard-coded to certain columns. This one's a bit more robust and should handle any column with the "values" set. The only assumption it makes to your data is that your values are using the standard "text" and "value" for your values.

function excelHandler(e) {
    var workbook = e.workbook;
    var sheet = workbook.sheets[0];
    var columnValueMap = {},
        i,
        j,
        visibleColumns = $.grep(e.sender.columns, function (col) {
            return !col.hidden;
        });

    for (i = 0; i < visibleColumns.length; i++) {
        if (visibleColumns[i].values) {
            columnValueMap[i] = {};
            var values = visibleColumns[i].values;
            for (j = 0; j < values.length; j++) {
                columnValueMap[i][values[j].value] = values[j].text;
            }
        }
    }

    for (i = 1; i < sheet.rows.length; i++) {
        for (var columnIndex in columnValueMap) {
            if (columnValueMap.hasOwnProperty(columnIndex)) {
                var value = sheet.rows[i].cells[columnIndex].value;
                if (value) {
                    sheet.rows[i].cells[columnIndex].value = columnValueMap[columnIndex][value];
                }
            }
        }
    }
}

[Daniel]

Hi Atanas,

I'm trying to work out a grid detail excel export with the code example from kendo documentation. However We are using TypeScript definitions and I'm trying to find the TS definition for the "new kendo.ExcelExporter" mention in exportChildData function as shown below:

1.var exporter = new kendo.ExcelExporter({<br>    columns: [{<br>        field: "OrderID"<br>    }, {<br>        field: "Freight"<br>    }, {<br>        field: "ShipName"<br>    }, {<br>        field: "ShipCity"<br>    }],<br>    dataSource: $scope.kendoGridWidget.dataSource<br>});

 

Thanks in Advance!

Daniel.