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

Excel Export with Client Template columns dynamically

9 Answers 1718 Views

Grid

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

Chris asked on 09 Jun 2016, 09:02 PM

Currently, the Grid Excel export will not export columns that have a client template defined on them.

The answer from Telerik HERE, solves the issue in a static way. Meaning that for each column that has a client template definition, you will have to alter the script to add the other columns.

function excelExport(e) {
var sheet = e.workbook.sheets[0];
var employeeTemplate = kendo.template(this.columns[0].template);
var employerTemplate = kendo.template(this.columns[1].template);
//Add more template variables and set them to the column index in the grid.

var data = this.dataSource.view();

for (var i = 0; i < data.length; i++) {
sheet.rows[i + 1].cells[0].value = employeeTemplate(data[i]);
sheet.rows[i + 1].cells[1].value = employerTemplate (data[i]);
//Add the cell values by column index from the template v
}
}

Here is a script to dynamically get all of the columns with client templates :

Grid HTML Helper:

@(Html.Kendo().Grid<Employee>()
.Name("employeeGrid")
.ToolBar(t => { t.Create(); t.Save(); t.Excel(); })
.Events(e => e.ExcelExport("excelExportDynamic"))
.Columns(col =>
{
col.Bound(c => c.Employee).ClientTemplate("#: data.Employee#");
col.Bound(c => c.Employer).ClientTemplate("#: data.Employer#);
col.ForeignKey(c => c.Status, Model, "StatusID", "StatusCode").Title("Status");
col.Command(c => c.Destroy());
})
//Removed for brevity

Javascript function:

//colTemplate array has been built now: Example of array at this point) colTemplate: { templateCol[0], null, templateCol[3], null, null }

for (var i = 0; i < colTemplates.length; i++) {
for (var j = 0; j < data.length; j++) {
if (colTemplates[i] != null) {
sheet.rows[j + 1].cells[i].value = colTemplates[i](data[j]); //Loop through all colTemplates and all data in the grid to build the excel sheet. Skip null colTemplates.
}
}
}
}
</script>

Hope this helps!!!!!

9 Answers, 1 is accepted

answered on 04 Nov 2016, 08:08 PM

Ok, after digging and experimenting. I have not been able to modify my original solution to be able to include the grouping as you have requested. However, i did come up with an alternative, albeit more of custom solution that may or may not work for you. This has only been . Also, this alternative does not rely upon the jszip.min.js script. Nor does it use the OTB Export To Excel functionality. Instead, a custom toolbar object with the HtmlAttributes' id property set to the javascript function name. The javascript function wires up to the custom toolbar object's 'onclick' event on load.

I will continue to work on a more stable and performant solution as i feel like this solution isnt the best as is. I just dont have the time right now.

Tested in IE 11 and Chrome 54.0.2840.59 only.
Tested the Grid filtering, grouping, sorting exporting.
Tested only a limited dataset (10 records).

Here is the View and Javascript:

Grid HTML Helper (MVC View):

h2>Reporting</h2>

@(Html.Kendo().Grid<Report>()
.Name("grid")
.HtmlAttributes(new { style = "height:700px" })
.ToolBar(t =>
{
t.Custom().Text("Export").HtmlAttributes(new { id = "exportToExcelCustom" });
})
.Columns(col =>
{
col.Bound(c => c.ID);
col.Bound(c => c.Title);
col.Bound(c => c.Category).ClientTemplate("#: data.Category ? data.Category.CategoryCode : 'N/A' #");
col.Bound(c => c.Active);
})
.Scrollable()
.Sortable()
.Groupable()
.Filterable()
.Navigatable()
.DataSource(ds =>
{
ds.Ajax()
.ServerOperation(false)
.Model(m =>
{
m.Id(r => r.ID);
})
.Read(r => r.Action("Get_ReportList", "Report"));
})
)

JavaScript:

answered on 09 Nov 2016, 09:56 AM

Hi Christopher,

Thanks for the nice feedback. We really appreciate when customers value our efforts.

And please don't get me wrong. It is perfectly fine that you share your own approaches with other developers as yourself, we even promote it in our forums. What I had in mind was that someone may take this solution as posted and approved by a Telerik official admin and take it for granted as a tested implementation.

Thanks once again for your pleasant attitude and contribution. I've updated your Telerik points as a token of gratitude.

Regards,
Eyup
Telerik by Progress

Check out the new UI for ASP.NET Core, the most complete UI suite for ASP.NET Core development on the market, with 60+ tried-and-tested widgets, based on Kendo UI.