This is a migrated thread and some comments may be shown as answers.

Excel Export - Foreign Key Columns and Detail Template Grids

14 Answers 354 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Brian Roth
Top achievements
Rank 1
Brian Roth asked on 20 Nov 2014, 04:04 PM
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

14 Answers, 1 is accepted

Sort by
0
Richard
Top achievements
Rank 1
answered on 23 Nov 2014, 02:32 AM
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.
0
mark
Top achievements
Rank 1
answered on 23 Nov 2014, 05:18 PM
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?
0
Atanas Korchev
Telerik team
answered on 24 Nov 2014, 12:44 PM
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
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
0
Brian Roth
Top achievements
Rank 1
answered on 24 Nov 2014, 07:41 PM
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
0
Jimmy
Top achievements
Rank 1
answered on 25 Nov 2014, 11:39 AM
Hi Antanas,
Very interesting solution but how I can do this in MVC?

Regards,
Jimmy
0
Atanas Korchev
Telerik team
answered on 25 Nov 2014, 11:52 AM
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
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
0
Jimmy
Top achievements
Rank 1
answered on 25 Nov 2014, 12:51 PM
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
0
Atanas Korchev
Telerik team
answered on 25 Nov 2014, 01:35 PM
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
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
0
Jimmy
Top achievements
Rank 1
answered on 03 Dec 2014, 09:37 PM
Hi Atanas,

Please say me when release with this changes?

Regards,

Jimmy
0
Jimmy
Top achievements
Rank 1
answered on 03 Dec 2014, 09:37 PM
Hi Atanas,

Please say me when release with this changes?

Regards,

Jimmy
0
Atanas Korchev
Telerik team
answered on 04 Dec 2014, 11:14 AM
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
 
Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
 
0
Jimmy
Top achievements
Rank 1
answered on 05 Dec 2014, 02:14 AM
thank you very much Atanas, worked perfectly
0
Aaron
Top achievements
Rank 1
answered on 11 Dec 2014, 10:41 PM
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];
                }
            }
        }
    }
}
0
Daniel
Top achievements
Rank 1
answered on 30 Oct 2015, 01:17 PM

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.

Tags
Grid
Asked by
Brian Roth
Top achievements
Rank 1
Answers by
Richard
Top achievements
Rank 1
mark
Top achievements
Rank 1
Atanas Korchev
Telerik team
Brian Roth
Top achievements
Rank 1
Jimmy
Top achievements
Rank 1
Aaron
Top achievements
Rank 1
Daniel
Top achievements
Rank 1
Share this question
or